CODEWITHSUNDEEP
mysqlsqltestingassertions
muhuk
muhuk

Reputation: 16095

How do I write an SQL query that fails?

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.

Upvotes: 4

Views: 2558

Answers (6)

Jon Crowell
Jon Crowell

Reputation: 22358

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;

Upvotes: 0

Julien
Julien

Reputation: 2329

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

Upvotes: 4

joshuahealy
joshuahealy

Reputation: 3569

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

select 'bob

Upvotes: 0

Jonathan Henson
Jonathan Henson

Reputation: 8206

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.

Upvotes: 5

Jim O'Neil
Jim O'Neil

Reputation: 23784

What about "SELECT 1/0" for starters?

Upvotes: 7

TGH
TGH

Reputation: 39278

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

Upvotes: 1

Related Questions