Bug #32009 PREPARE using reference to DUAL fails
Submitted: 31 Oct 2007 22:01 Modified: 8 Nov 2007 14:18
Reporter: Todd Farmer (OCA) Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Prepared statements Severity:S3 (Non-critical)
Version:5.0.41 OS:Any
Assigned to: Alexander Nozdrin CPU Architecture:Any

[31 Oct 2007 22:01] Todd Farmer
Description:
The MySQL server refuses to prepare a statement which contains a reference to the DUAL psuedo-table:

mysql> prepare stmt from 'select * from dual where 1 = ?';
ERROR 1096 (HY000): No tables used

The error message is somewhat misleading, as it might suggest that the following cannot be done (but it can):

mysql> prepare stmt from 'select 1 = ?';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> set @a = 2;
Query OK, 0 rows affected (0.00 sec)

mysql> execute stmt using @a;
+-------+
| 1 = ? |
+-------+
|     0 |
+-------+
1 row in set (0.00 sec)

How to repeat:
prepare stmt from 'select * from dual where 1 = ?';

Suggested fix:
Allow statements to be prepared using the DUAL table.  If not, please clarify in documentation regarding limitations of prepared statements.
[8 Nov 2007 14:18] Alexander Nozdrin
Everything works as it should:

mysql> select * from dual;
ERROR 1096 (HY000): No tables used

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

mysql> prepare s1 from 'select 1 from dual where ? = 1';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> set @v = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> execute s1 using @v;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql> set @v = 2;
Query OK, 0 rows affected (0.00 sec)

mysql> execute s1 using @v;
Empty set (0.01 sec)