Bug #32009 PREPARE using reference to DUAL fails

• Submitted: 31 Oct 2007 22:01
• Modified: 8 Nov 2007 14:18
• Reporter: Todd Farmer (OCA)
• Status: Not a Bug
• 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)