Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.3.31, 10.6.3, 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5, 10.6
-
None
-
debian, windows
Description
When a double encapsulated CTE query calls a function which reads a table which has been aliased in the CTE query, the optimizer cannot find the table anymore and ends with an error 1146 Table db.t1 doesn't exist.
Script to reproduce:
CREATE TABLE t1 (id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY); |
/* Generate 10'000 entries */
|
INSERT INTO t1(id) SELECT @row := @row + 1 AS id FROM |
(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t, |
(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t2, |
(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t3, |
(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t4, |
(SELECT @row:=0) numbers; |
 |
CREATE OR REPLACE FUNCTION check_my_id(in_id INT) RETURNS INTEGER |
READS SQL DATA
|
RETURN (SELECT id FROM t1 WHERE t1.id = in_id); |
 |
 |
WITH c1 AS (SELECT id FROM t1 a WHERE check_my_id(id) = id GROUP BY id), |
c2 AS (SELECT pt.id FROM c1 pt GROUP BY pt.id) |
SELECT id FROM c2 GROUP BY id |
UNION ALL |
SELECT id FROM c2; |
If the table alias (a) is removed in the CTE c1, the query works.
When I try on my real query (and not in the abbreviated example above), I sometimes get the error message and sometimes mysqld crashes with the error log attached.
Attachments
Issue Links
- relates to
-
MDEV-26025 Server crashes while executing query with CTE in PS/SP
- Closed