How do I write an SQL query that fails?

Question

I need this query for testing exception handling, so I would prefer that the query is not schema dependent. I am looking for something like SELECT 1; but of course that doesn't fail.

I am using Java and MySQL but I hope to find answers that doesn't depend on programming languages and/or RDBMSs.

Why don't you take a moment to point out what's wrong with this question, before you downvote it? — muhuk, Jun 04 '13 at 04:40

score 6 · Answer 1 · answered Jun 04 '13 at 04:18

6

What about "SELECT 1/0" for starters?

answered Jun 04 '13 at 04:18

Jim O'Neil

23,344
7
42
67

well, that's annoying and IMHO not behavior I would consider correct as the default, but I'm not a MySQL guy :) See @HeadofCatering comment below about the sql_mode setting (of course that disqualifies this answer as RDBMS-independent.) There are also different type of exceptions that you may or many not want to test, syntax errors versus engine errors, for example - not knowing how/what you are testing, might it be possible those exceptions take a different path through your app? – Jim O'Neil Jun 04 '13 at 15:21

score 5 · Answer 2 · answered Jun 04 '13 at 04:19

5

You could put an invalid token into the query

select doesnotexist.* from something_else

Or of course, what you should do is mock out the method and have it throw the exception during your test.

answered Jun 04 '13 at 04:19

Jonathan Henson

8,076
3
28
52

score 4 · Accepted Answer · answered Jun 04 '13 at 04:20

4

there are tons of ways to make a query fail, like mispelling a field, or selecting from non existing tables. for example:

SELECT some_fake_field FROM table_that_doesnt_exists

answered Jun 04 '13 at 04:20

Julien

2,217
2
28
49

score 1 · Answer 4 · edited May 23 '17 at 12:28

1

One way to trigger a failure is to call a stored procedure with the wrong number of parameters. Another similar idea is to write an update/insert statement with the wrong number of arguments...

More ideas here: How to raise an error within a MySQL function

edited May 23 '17 at 12:28

Community

1
1

answered Jun 04 '13 at 04:18

TGH

38,769
12
102
135

score 0 · Answer 5 · answered Jun 04 '13 at 04:20

0

Any old syntax error will do... like an unterminated string

select 'bob

answered Jun 04 '13 at 04:20

joshuahealy

3,529
22
29

score 0 · Answer 6 · answered Jun 04 '13 at 05:04

To get 1/0 to raise an error in MySQL, you need to set sql_mode to ERROR_FOR_DIVISION_BY_ZERO.

Try this:

SET sql_mode = 'ERROR_FOR_DIVISION_BY_ZERO';
SELECT 1/0;

If this sql_mode isn't set, MySQL will return a NULL instead of an error.

You can check what your current settings are with the following:

SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;

How do I write an SQL query that fails?

6 Answers6