Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2.17, 10.3.9, 10.2(EOL), 10.3(EOL)
-
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.
Attachments
Activity
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'
|
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