Queries generated for nested unions fail with MySQL

Bug #309276 reported by James Henstridge on 2008-12-18
4
Affects Status Importance Assigned to Milestone
Storm
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.union(resultset2).union(resultset3). Two things that could be done to help with this are:

 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 :

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.

Changed in storm:
importance: Undecided → Low
status: New → Triaged
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers