Queries generated for nested unions fail with MySQL
| Affects | Status | Importance | Assigned to | Milestone | |
|---|---|---|---|---|---|
| Storm |
Triaged
|
Low
|
Unassigned | ||
Bug Description
It seems MySQL doesn't support nesting of the union operator, treating the following expression as invalid:
mysql> ((select 1) union (select 2)) union (select 3);
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 'union (select 2)) union (select 3)' at line 1
Removing the parentheses around the first union expression fixes the problem:
mysql> (select 1) union (select 2) union (select 3);
+---+
| 1 |
+---+
| 1 |
| 2 |
| 3 |
+---+
3 rows in set (0.01 sec)
It is possible to trigger this bug using something like resultset1.
1. make the expression compiler treat union as left associative and not add the extra parentheses.
2. make the ResultSet code flatten set expressions as described in bug 242813.
| James Henstridge (jamesh) wrote | #1 |
| Changed in storm: | |
| importance: | Undecided → Low |
| status: | New → Triaged |
Bug 242813 has been fixed now so homogeneous unions should be fine now.
It is probably still worth tweaking the expression compiler so that the SQL we generate for other cases is more likely to pass MySQL's finicky parser.