Bug #30650 DUAL and ORDER BY seemingly incompatible

• Submitted: 27 Aug 2007 18:53
• Modified: 12 Nov 2007 13:58
• Reporter: Jess Balint
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.1.19, 4.1, 5.0., 5.1, 5.2 BK
• OS: Linux
• Assigned to: Alexander Nozdrin
• CPU Architecture: Any

[27 Aug 2007 18:53] Jess Balint

Description:
ORDER BY doesn't work if the query contains the dual table

How to repeat:
-- ========== Working Queries (no dual+order by in the same query)
> select 1 order by 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
> select 1 from dual where 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
> select 1 from yyyy where 1 order by 1;
Empty set (0.00 sec)

-- ========== Problem Queries (dual+order by in the same query)
> select 1 from dual order by 1;
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 'order by 1' at line 1
> select 1 from dual where 1 order by 1;
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 'order by 1' at line 1
> select 1 from dual, yyyy where 1 order by 1;
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 ' yyyy where 1 order by 1' at line 1

[27 Aug 2007 19:32] Sveta Smirnova

Thank you for the report.

Verified as described.

[29 Oct 2007 12:48] Timour Katchaounov

P2 as wrong error.

[12 Nov 2007 13:58] Alexander Nozdrin

The MySQL Reference Manual says:
(http://dev.mysql.com/doc/refman/5.1/en/select.html)
<quote>

DUAL is purely for the convenience of people who require that all
SELECT statements should have FROM and possibly other clauses.
</quote>

"Possibly" means "maybe". There is no promise that any particular
clause will work, other than FROM.