MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

MySQL requires Dual?

with one comment

All this time I thought MySQL was so progressive by eliminating the DUAL pseudo table when selecting a string literal. I also thought it interesting that they also supported selecting a string literal from the DUAL pseudo table. While playing around with examples for my students, I found out that there is a use case when MySQL requires you to use the DUAL pseudo table.

You must use the DUAL pseudo table when you select a literal that includes a WHERE clause. For example, this fails:

mysql> SELECT 'Truth' WHERE 'STRING1' >= 'string1';

with the following error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'WHERE 'string1' >= 'STRING1'' at line 1

It’s complaining that you haven’t designated a table. However, this succeeds when you add the FROM dual and it does case promotion or demotion to resolve whether the strings are case insensitive matches.

mysql> SELECT 'Truth' FROM dual WHERE 'STRING1' >= 'string1';

It looks so much like Oracle. 😉 The only difference is that Oracle does compare them on the basis of their ASCII values, and would return no rows for this query.

Written by maclochlainn

October 27th, 2009 at 10:19 pm

Posted in MySQL,sql