How do I get MySQL to throw a conditional runtime exception in SQL

Question

I am writing a migration test to ensure that a user was created by the migration. If the user does not exist, the test should throw an error. At first, I thought I could just use a division by zero error to get what I wanted:

SET SESSION sql_mode = 'error_for_division_by_zero';
SELECT 1/COUNT(*) FROM mysql.user WHERE user = 'foo';

However, this does not throw an error if foo does not exist. Turns out that error_for_division_by_zero affects only INSERT and UPDATE statements.

Then I thought maybe I could just call some function with the wrong number of arguments:

SELECT IF(COUNT(*) = 1, 1, date_format(1, 2, 3))
  FROM mysql.user WHERE user = 'foo';

But this dies even when foo does exist, presumably because the parser notices the incorrect parameter count.

I could write a a function that emulates raising an exception, but I was trying to avoid that. Is there no way to coerce MySQL into conditionally throwing a runtime exception?

Answer 1

You can raise an exception without function / procedure by simply running a subquery returning MORE THAN ONE row but EXPECTING ONLY ONE row. Usually in a database there is a table for that already, but here I included a table for that purpose:

create table t (a int);
insert into t values(1);
insert into t values(1);
select * from t;

The below select raises exception because the outer select EXPECTS one value BUT the inner select returns MORE THAN ONE value.

select (select a from t);

You can apply that for your case:

select case when 
    2*2 = 4 
    then 'OK' 
    else (select a from t)
    end;

select case when 
    2*2 = 5 
    then 'OK' 
    else (select a from t)
    end;

See also: https://dba.stackexchange.com/questions/78594/how-to-conditionally-raise-an-error-in-mysql-without-stored-procedure