MacLochlainns Weblog

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.