Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.2.40, 10.3.31, 10.4.21, 10.5.12, 10.6.3
-
None
Description
While working on the task MDEV-25973 (Fix tests that were temporary disabled in PS mode) I stumbled across the use case that could be classified as a bug.
This use case is about handling the query containing recursive CTE and referencing unknown column in the WHERE clause (in our case unknown columns are s1.i1 and cte.i2).
EXPLAIN WITH RECURSIVE cte AS ( SELECT * FROM t1 UNION SELECT s1.* FROM t1 AS s1, cte WHERE s1.i1 = cte.i2 ) SELECT * FROM t1 AS t; |
Complete use case is below.
MariaDB [test]> CREATE TABLE t1 (a INT); |
Query OK, 0 rows affected (0,237 sec) |
|
|
MariaDB [test]> INSERT INTO t1 VALUES (1), (2), (3), (4); |
Query OK, 4 rows affected (0,002 sec) |
Records: 4 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> EXPLAIN WITH RECURSIVE cte AS ( SELECT * FROM t1 UNION SELECT s1.* FROM t1 AS s1, cte WHERE s1.i1 = cte.i2 ) SELECT * FROM t1 AS t; |
+------+-------------+-------+------+---------------+------+---------+------+------+-------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+-------+------+---------------+------+---------+------+------+-------+ |
| 1 | PRIMARY | t | ALL | NULL | NULL | NULL | NULL | 4 | | |
+------+-------------+-------+------+---------------+------+---------+------+------+-------+ |
1 row in set (0,001 sec) |
As seen, this statement is executed successfully despite of the fact the query referencing unknown columns.
If the same query be run in PS mode it results in error
MariaDB [test]> PREPARE stmt FROM 'EXPLAIN WITH RECURSIVE cte AS ( SELECT * FROM t1 UNION SELECT s1.* FROM t1 AS s1, cte WHERE s1.i1 = cte.i2 ) SELECT * FROM t1 AS t'; |
ERROR 1054 (42S22): Unknown column 's1.i1' in 'where clause' |
The above error is what prompt me to create this bug report.
If the above use case be run on MySQL server 8.0.24 it produces the following output:
mysql> CREATE TABLE t1 (a INT); |
Query OK, 0 rows affected (0,05 sec) |
|
|
mysql> INSERT INTO t1 VALUES (1), (2), (3), (4); |
Query OK, 4 rows affected (0,04 sec) |
Records: 4 Duplicates: 0 Warnings: 0
|
|
|
mysql> EXPLAIN WITH RECURSIVE cte AS ( SELECT * FROM t1 UNION SELECT s1.* FROM t1 AS s1, cte WHERE s1.i1 = cte.i2 ) SELECT * FROM t1 AS t; |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ |
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ |
| 1 | SIMPLE | t | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | NULL | |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ |
1 row in set, 1 warning (0,00 sec) |
|
|
mysql> SHOW WARNINGS;
|
+-------+------+------------------------------------------------------------------+ |
| Level | Code | Message | |
+-------+------+------------------------------------------------------------------+ |
| Note | 1003 | /* select#1 */ select `test`.`t`.`a` AS `a` from `test`.`t1` `t` | |
+-------+------+------------------------------------------------------------------+ |
1 row in set (0,00 sec) |
|
|
mysql> PREPARE stmt FROM 'EXPLAIN WITH RECURSIVE cte AS ( SELECT * FROM t1 UNION SELECT s1.* FROM t1 AS s1, cte WHERE s1.i1 = cte.i2 ) SELECT * FROM t1 AS t'; |
Query OK, 0 rows affected (0,00 sec) |
Statement prepared
|
|
|
mysql> EXECUTE stmt; |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ |
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ |
| 1 | SIMPLE | t | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | NULL | |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ |
1 row in set, 1 warning (0,00 sec) |
|
|
mysql> SHOW WARNINGS;
|
+-------+------+------------------------------------------------------------------+ |
| Level | Code | Message | |
+-------+------+------------------------------------------------------------------+ |
| Note | 1003 | /* select#1 */ select `test`.`t`.`a` AS `a` from `test`.`t1` `t` | |
+-------+------+------------------------------------------------------------------+ |
1 row in set (0,00 sec) |
That is, for MySQL server running of the EXPLAIN statement for the query with recursive CTE referencing unknown column results in successful execution with extra warning.
To get the full picture I ran the original use case on PostgreSQL 13
postgres=# CREATE TABLE t1(a INT); |
CREATE TABLE |
postgres=# INSERT INTO t1 VALUES (1), (2), (3), (4); |
INSERT 0 4 |
postgres=# EXPLAIN WITH RECURSIVE cte AS ( SELECT * FROM t1 UNION SELECT s1.* FROM t1 AS s1, cte WHERE s1.i1 = cte.i2 ) SELECT * FROM t1 AS t; |
ERROR: column s1.i1 does not exist |
LINE 1: ...ROM t1 UNION SELECT s1.* FROM t1 AS s1, cte WHERE s1.i1 = ct... |
As seen, running of the EXPLAIN for the query with recursive CTE referencing unknown column results in error. From my point of view such behavior is most correct.
So, this task is to decide what is correct way for handling EXPLAIN statement for the query with recursive CTE referencing unknown column and implement it uniformly both for PS and regular mode.
Addendum: the following statements executed sequentially results in server crash
MariaDB [test]> PREPARE stmt FROM 'EXPLAIN WITH RECURSIVE cte AS ( SELECT * FROM t1 UNION SELECT s1.* FROM t1 AS s1, cte WHERE s1.i1 = cte.i2 ) SELECT * FROM t1 AS t'; |
ERROR 1054 (42S22): Unknown column 's1.i1' in 'where clause' |
MariaDB [test]> SHUTDOWN;
|
Query OK, 0 rows affected (0.001 sec) |
#0 __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
|
#1 0x00007f69807c692e in __GI_abort () at abort.c:100
|
#2 0x00007f69807c6729 in __assert_fail_base (fmt=0x7f698095c588 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=0x562cf73e5d70 "status_var.local_memory_used == 0 || !debug_assert_on_not_freed_memory", file=0x562cf73e5828 "/home/shulga/projects/mariadb/server-10.6/sql/sql_class.cc", line=1757, function=<optimized out>) at assert.c:92
|
#3 0x00007f69807d7f36 in __GI___assert_fail (assertion=0x562cf73e5d70 "status_var.local_memory_used == 0 || !debug_assert_on_not_freed_memory", file=0x562cf73e5828 "/home/shulga/projects/mariadb/server-10.6/sql/sql_class.cc", line=1757, function=0x562cf73e5d16 "virtual THD::~THD()") at assert.c:101
|
#4 0x0000562cf65ec1d2 in THD::~THD (this=0x7f692c000db8, __in_chrg=<optimized out>) at /home/shulga/projects/mariadb/server-10.6/sql/sql_class.cc:1757
|
#5 0x0000562cf65ec41e in THD::~THD (this=0x7f692c000db8, __in_chrg=<optimized out>) at /home/shulga/projects/mariadb/server-10.6/sql/sql_class.cc:1763
|
#6 0x0000562cf6825233 in do_handle_one_connection (connect=0x0, put_in_cache=true) at /home/shulga/projects/mariadb/server-10.6/sql/sql_connect.cc:1451
|
#7 0x0000562cf6824de1 in handle_one_connection (arg=0x562cfae10298) at /home/shulga/projects/mariadb/server-10.6/sql/sql_connect.cc:1312
|
#8 0x0000562cf6d52af9 in pfs_spawn_thread (arg=0x562cfacf7908) at /home/shulga/projects/mariadb/server-10.6/storage/perfschema/pfs.cc:2201
|
#9 0x00007f6980cf0609 in start_thread (arg=<optimized out>) at pthread_create.c:477
|
#10 0x00007f69808c3293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95
|
Attachments
Issue Links
- blocks
-
-
- Stalled
-