[MDEV-6341] INSERT ... SELECT UNION with parenthesis Created: 2014-06-15  Updated: 2018-07-09  Resolved: 2018-07-09

Status: Closed
Project: MariaDB Server
Component/s: Parser
Affects Version/s: 5.5.38, 10.0.12, 10.1, 10.3.4
Fix Version/s: 10.4.0

Type: Bug Priority: Major
Reporter: Federico Razzoli Assignee: Oleksandr Byelkin
Resolution: Duplicate Votes: 0
Labels: upstream-fixed

Issue Links:
Duplicate
duplicates MDEV-11953 support of brackets (parentheses) in ... Closed
Relates
relates to MDEV-10028 Syntax error on ((SELECT ...) UNION (... Closed

 Description   

I never noticed this or never thought it is a problem, but please look at this post:
http://blog.mclaughlinsoftware.com/2014/06/15/mysql-insert-from-query/

The "problem" is that parenthesis are allowed for INSERT (SELECT), but not for INSERT (SELECT UNION), and someone can conclude that INSERT SELECT UNION is not supported:

MariaDB [test]> INSERT INTO t3 (SELECT a, NULL AS b FROM t2);
 
Query OK, 3 rows affected (0.08 sec)
 
Records: 3  Duplicates: 0  Warnings: 0
 
MariaDB [test]> INSERT INTO t3 (SELECT a, NULL AS b FROM t1 UNION SELECT * FROM t2);
 
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT * FROM t2)' at line 1
 
MariaDB [test]> INSERT INTO t3 SELECT a, NULL AS b FROM t1 UNION SELECT * FROM t2;
 
Query OK, 6 rows affected (0.08 sec)
 
Records: 6  Duplicates: 0  Warnings: 0



 Comments   
Comment by roberto spadim [ 2014-06-15 ]

considering: https://mariadb.com/kb/en/insert-select/

MariaDB [test]> INSERT INTO t3 (SELECT a, NULL AS b FROM t2);
this should not work

MariaDB [test]> INSERT INTO t3 (SELECT a, NULL AS b FROM t1 UNION SELECT * FROM t2);
this should not work

MariaDB [test]> INSERT INTO t3 SELECT a, NULL AS b FROM t1 UNION SELECT * FROM t2;
this should work

Comment by Johannes Jordan [ 2016-03-18 ]

This might be related to this bug:
https://bugs.mysql.com/bug.php?id=25734

They claim they fixed it last month. How about MariaDB?
If it is not related I would also open a separate issue.

Comment by Sergei Golubchik [ 2016-04-17 ]

Sorry, no parser changes in 5.5 — they tend to be too big and risky for GA versions. We'll fix it in the trunk, just as MySQL did.

Comment by Alexander Barkov [ 2017-01-24 ]

The same problem is repeatable in CREATE .. SELECT:

CREATE OR REPLACE TABLE t2 AS (SELECT 1 AS a);
 
CREATE OR REPLACE TABLE t2 AS SELECT 1 AS a UNION SELECT 2;
 
CREATE OR REPLACE TABLE t2 AS (SELECT 1 AS a UNION SELECT 2);

The first and the second statements succeed, the third statement fails.

Comment by Federico Razzoli [ 2017-01-27 ]

The same goes for CREATE VIEW:

CREATE OR REPLACE VIEW t2 AS (SELECT 1 AS a);
 
CREATE OR REPLACE VIEW t2 AS SELECT 1 AS a UNION SELECT 2;
 
CREATE OR REPLACE VIEW t2 AS (SELECT 1 AS a UNION SELECT 2);

1 and 2 work, 3 fails.

Comment by Daniel Black [ 2018-01-01 ]

Fix in 8.0 as per mysql bug 25734

Comment by Oleksandr Byelkin [ 2018-04-11 ]

Duplicate of https://jira.mariadb.org/browse/MDEV-11953 . Test will be there.

Comment by Oleksandr Byelkin [ 2018-07-09 ]

fixed in MDEV-6341

Generated at Thu Feb 08 07:11:10 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.