MySQL Bugs: #48997: SELECT statement behaviour changes from empty to non-empty set

Bug #48997	SELECT statement behaviour changes from empty to non-empty set
Submitted:	23 Nov 2009 18:07	Modified:	23 Nov 2009 18:55
Reporter:	Dallan Hunt	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S3 (Non-critical)
Version:	5.1.36	OS:	Other (AMD Turion 64)
Assigned to:		CPU Architecture:	Any

Description:
Hello,

I came into a problem when I was trying to do a conditional insert, where I would use the following SELECT statement to return a value or not, depending on some condition.  For the purpose of the bug, I have set the condition to TRUE, as I have observed some unexpected behaviour.

SELECT 5,'HELLO' FROM my_table WHERE TRUE LIMIT 1;

When the table 'my_table' is empty, the empty set is returned.
When the table contains at least one row, then the expected values (5,'Hello') is returned.

It seems to me that if the WHERE receives a TRUE, that the SELECT should always return the expected values, but it doesnt - namely when the calling table is empty.  But since the condition is hard coded to TRUE, why should the state of the table affect the call?

Dallan

How to repeat:
Create a table which is empty and make the above call.  Then add an arbitrary row and make the same call.  In the first case the empty set will be returned, and in the latter the expected set (5,'Hello') will be returned.

Suggested fix:
When the condition in the WHERE is TRUE, the expected set should *always* be returned.

Your WHERE condition is true for every row, but there are NO rows in the table. Hence NO rows in the result. SELECT from the table can not select something that does not exist in the table.

The SELECT statement is not necessarily intended to select a row which is in the table.  

The following statement will always return (0,'SUCCESS'):
SELECT 0,'SUCCESS';

But the following I expect to be different because of the WHERE clause.
SELECT 0,'SUCCESS' FROM my_table WHERE TRUE LIMIT 1;
SELECT 0,'SUCCESS' FROM my_table WHERE FALSE LIMIT 1;

The ('0',SUCCESS) may or may not be in the table.  In the case of TRUE, I expect 0,'SUCCESS' to be returned, and in the case of FALSE, I expect the empty set.  Are my expectations incorrect?

Your expectations are correct for a table having at least one row. But if table does not have any rows, the result of any SELECT from it is empty.

As for:

mysql> select 1;
+---+
| 1 |
+---+
| 1 | 
+---+
1 row in set (0.00 sec)

this is a special case. You do not select from table at all (or, if you prefer, you are selecting from a table with exactly one row). 

Other RDBMSes, like Oracle, may even have a special table for that, with single row, called, surprise, DUAL. We emulate it in MySQL also:

mysql> select 1 from dual;
+---+
| 1 |
+---+
| 1 | 
+---+
1 row in set (0.00 sec)

mysql> select 1 from dual where true;
+---+
| 1 |
+---+
| 1 | 
+---+
1 row in set (0.00 sec)

mysql> select 1 from dual where false;
Empty set (0.00 sec)

My original problem was when trying to do a conditional insert:

INSERT INTO my_table (id,name) SELECT 0,'SUCCESS' FROM my_table WHERE NOT EXISTS (SELECT * FROM my_table WHERE id = 0) LIMIT 1;

I found the solution here:
http://forums.devshed.com/mysql-help-4/can-mysql-do-a-conditional-insert-439435.html

This only works when the table contains at least one row; then the conditional insert works correctly.  If this won't work when the table is empty, do you know of a solution which will also work in this case?

Thanks