MySQL Bugs: #34665: select 'a' union (select 'b') into outfile 'tmp'; gives parse error

Bug #34665	select 'a' union (select 'b') into outfile 'tmp'; gives parse error
Submitted:	19 Feb 2008 14:03	Modified:	20 Feb 2008 14:57
Reporter:	Sven Sandberg	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	5.0, 5.1	OS:	Any
Assigned to:	Martin Hansson	CPU Architecture:	Any
Tags:	into outfile, parse error, SELECT, UNION

Description:
The following query gives a parse error:

SELECT 'a' UNION (SELECT 'b') INTO OUTFILE 'tmp';

It seems that union where the second operand is a select inside parentheses does not work together with INTO OUTFILE.
If I remove the parentheses or remove the INTO OUTFILE, it works as expected.

How to repeat:
SELECT 'a' UNION (SELECT 'b') INTO OUTFILE 'tmp';

Expected behavior. Check the manual for the syntax of SELECT: http://dev.mysql.com/doc/refman/5.0/en/select.html
INTO OUTFILE is not allowed by the syntax in the place where you've put it.

This reveals a parser problem. The problem is that we have no rule for this construction:

<query> UNION <query> UNION <query> INTO [OUTFILE|DUMPFILE|<variable>]

Queries like

SELECT 1 UNION SELECT a FROM t1 INTO OUTFILE 'file.txt';

Work by coincidence. It's because the INTO grammatically attaches to the second query. Then there is a hack that makes the INTO apply to the whole UNION. This hack works ok most of the time but queries like

SELECT 'a' UNION (SELECT a FROM t1 INTO OUTFILE 'file.txt');

will include the 'a' at the start of the file.

Postgres has a similar issue, but they allow INTO only on the first UNION branch (as opposed to the last):

>(SELECT 'a' INTO a) UNION SELECT 'b';
SELECT
>SELECT * FROM a;
 ?column?
----------
 a
 b

OTOH, postgres allows one to use parentheses anywhere, i.e.:

>(SELECT 'a' INTO a) UNION (SELECT 'b');
SELECT
>SELECT * FROM a;
 ?column?
----------
 a
 b

And, yes, they allow any number of parentheses:
>((SELECT 'a' INTO a)) UNION (((SELECT 'b'))); select * FROM a;
 ?column?
----------
 a
 b