[MDEV-17154] Multiple selects from parametrized CTE fails with syntax error Created: 2018-09-07  Updated: 2020-04-04  Resolved: 2018-09-16

Status: Closed
Project: MariaDB Server
Component/s: Optimizer - CTE
Affects Version/s: 10.2.17, 10.3.9, 10.2, 10.3
Fix Version/s: 10.2.18

Type: Bug Priority: Major
Reporter: Johan Björk Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: None


 Description   

The following query works fine:

PREPARE stmt FROM
 
'WITH t1 AS (SELECT 1)
 
SELECT * FROM t1
 
 UNION ALL
 
SELECT * FROM t1'

When extracting the "1" into a parameter the query fails with error 1064:

PREPARE stmt FROM
 
'WITH t1 AS (SELECT ?)
 
SELECT * FROM t1
 
 UNION ALL
 
SELECT * FROM t1'

Tested on OSX 10.13.6. MySQL 8 seems to handle this without trouble.

 Comments   
Comment by Alice Sherepa [ 2018-09-07 ]

Thanks for the report! Reproduced as described, if cte is used minimum twice in the prepared (in join, union, intersect/except), then the error is returned

MariaDB [test]> prepare test from "with cte as (select ? ) select 1 from cte a, cte b";
 
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 '?' at line 1

Comment by Oleksandr Byelkin [ 2018-09-14 ]

ok to push

Comment by Igor Babaev [ 2018-09-15 ]

A similar problem with local variables of SP can be seen here:

MariaDB [test]> delimiter |
 
MariaDB [test]> create table t1 (a int, b int) |
 
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [test]> create procedure p() begin declare i int; set i = 0; while i < 4 do   insert into t1 with cte(a) as (select i) select * from cte as a, cte as b; set  i = i+1; end while; end|
 
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [test]> call p() |
 
ERROR 1054 (42S22): Unknown column 'i' in 'field list'

Comment by Igor Babaev [ 2018-09-16 ]

A fix for this bug was pushed into 10.2.

Generated at Thu Feb 08 08:34:19 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.