Details
Description
There is a complex query where there is a internal subquery with no alias reported anywhere and there is not error fire once the query run.
Tested a simple case:
MariaDB [test]> select user from mysql.user m where user in (select user from mysql.user m2 group by s.id);
|
+---------------+
|
| user |
|
+---------------+
|
| data_engineer |
|
| ro1 |
|
|
|
as you see there is no alias for 's' table. |
|
|
MariaDB [test]> explain extended select user from mysql.user m where user in (se
|
lect user from mysql.user m2 group by s.id);
|
+------+--------------+-------------+-------+---------------+---------+---------
|
+------+------+----------+------------------------------------------------------
|
--------+
|
| id | select_type | table | type | possible_keys | key | key_len
|
| ref | rows | filtered | Extra
|
|
|
+------+--------------+-------------+-------+---------------+---------+---------
|
+------+------+----------+------------------------------------------------------
|
--------+
|
| 1 | PRIMARY | <subquery2> | ALL | distinct_key | NULL | NULL |
| NULL | 20 | 100.00 | |
|
|
| 1 | PRIMARY | m | index | NULL | PRIMARY | 420 |
| NULL | 20 | 100.00 | Using where; Using index; Using join buffer (flat, BN |
L join) |
|
| 2 | MATERIALIZED | m2 | index | NULL | PRIMARY | 420 |
| NULL | 20 | 100.00 | Using index |
|
|
+------+--------------+-------------+-------+---------------+---------+---------
|
+------+------+----------+------------------------------------------------------
|
--------+
|
3 rows in set, 1 warning (0.023 sec) |
MariaDB [test]> show warnings\G
|
*************************** 1. row *************************** |
Level: Note
|
Code: 1003 |
Message: select `mysql`.`m`.`User` AS `user` from `mysql`.`user` `m` semi join (
|
`mysql`.`user` `m2`) where `mysql`.`m`.`User` = `mysql`.`m2`.`User`
|
1 row in set (0.001 sec) |
So there is no alias and it should fire an error.
Attachments
Issue Links
- causes
-
MDEV-24779 main.subselect fails in buildbot with --ps-protocol
-
- Closed
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Description |
There is a complex query where there is a internal subquery with no alias reported anywhere and there is not error fire once the query run.
Tested a simple case: MariaDB [test]> select user from mysql.user m where user in (select user from mysql.user m2 group by s.id); +---------------+ | user | +---------------+ | data_engineer | | ro1 | as you see there is no alias for 's' table. MariaDB [test]> explain extended select user from mysql.user m where user in (se lect user from mysql.user m2 group by s.id); +------+--------------+-------------+-------+---------------+---------+--------- +------+------+----------+------------------------------------------------------ --------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+--------------+-------------+-------+---------------+---------+--------- +------+------+----------+------------------------------------------------------ --------+ | 1 | PRIMARY | <subquery2> | ALL | distinct_key | NULL | NULL | NULL | 20 | 100.00 | | | 1 | PRIMARY | m | index | NULL | PRIMARY | 420 | NULL | 20 | 100.00 | Using where; Using index; Using join buffer (flat, BN L join) | | 2 | MATERIALIZED | m2 | index | NULL | PRIMARY | 420 | NULL | 20 | 100.00 | Using index | +------+--------------+-------------+-------+---------------+---------+--------- +------+------+----------+------------------------------------------------------ --------+ 3 rows in set, 1 warning (0.023 sec) MariaDB [test]> show warnings\G *************************** 1. row *************************** Level: Note Code: 1003 Message: select `mysql`.`m`.`User` AS `user` from `mysql`.`user` `m` semi join ( `mysql`.`user` `m2`) where `mysql`.`m`.`User` = `mysql`.`m2`.`User` 1 row in set (0.001 sec) So there is no alias and it should fire an error. |
There is a complex query where there is a internal subquery with no alias reported anywhere and there is not error fire once the query run.
Tested a simple case: {code:java} MariaDB [test]> select user from mysql.user m where user in (select user from mysql.user m2 group by s.id); +---------------+ | user | +---------------+ | data_engineer | | ro1 | as you see there is no alias for 's' table. MariaDB [test]> explain extended select user from mysql.user m where user in (se lect user from mysql.user m2 group by s.id); +------+--------------+-------------+-------+---------------+---------+--------- +------+------+----------+------------------------------------------------------ --------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+--------------+-------------+-------+---------------+---------+--------- +------+------+----------+------------------------------------------------------ --------+ | 1 | PRIMARY | <subquery2> | ALL | distinct_key | NULL | NULL | NULL | 20 | 100.00 | | | 1 | PRIMARY | m | index | NULL | PRIMARY | 420 | NULL | 20 | 100.00 | Using where; Using index; Using join buffer (flat, BN L join) | | 2 | MATERIALIZED | m2 | index | NULL | PRIMARY | 420 | NULL | 20 | 100.00 | Using index | +------+--------------+-------------+-------+---------------+---------+--------- +------+------+----------+------------------------------------------------------ --------+ 3 rows in set, 1 warning (0.023 sec) MariaDB [test]> show warnings\G *************************** 1. row *************************** Level: Note Code: 1003 Message: select `mysql`.`m`.`User` AS `user` from `mysql`.`user` `m` semi join ( `mysql`.`user` `m2`) where `mysql`.`m`.`User` = `mysql`.`m2`.`User` 1 row in set (0.001 sec) {code} So there is no alias and it should fire an error. |
| Key |
|
|
| Affects Version/s | 10.3 [ 22126 ] | |
| Affects Version/s | 10.4 [ 22408 ] | |
| Affects Version/s | 10.3 [ 23605 ] | |
| Affects Version/s | 10.4.12-6 [ 24213 ] | |
| Project | MariaDB Enterprise [ 11500 ] | MariaDB Server [ 10000 ] |
| Fix Version/s | 10.2 [ 14601 ] | |
| Fix Version/s | 10.3 [ 22126 ] | |
| Fix Version/s | 10.4 [ 22408 ] | |
| Fix Version/s | 10.5 [ 23123 ] | |
| Affects Version/s | 10.1 [ 16100 ] | |
| Affects Version/s | 10.2 [ 14601 ] | |
| Affects Version/s | 10.5 [ 23123 ] | |
| Description |
There is a complex query where there is a internal subquery with no alias reported anywhere and there is not error fire once the query run.
Tested a simple case: {code:java} MariaDB [test]> select user from mysql.user m where user in (select user from mysql.user m2 group by s.id); +---------------+ | user | +---------------+ | data_engineer | | ro1 | as you see there is no alias for 's' table. MariaDB [test]> explain extended select user from mysql.user m where user in (se lect user from mysql.user m2 group by s.id); +------+--------------+-------------+-------+---------------+---------+--------- +------+------+----------+------------------------------------------------------ --------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+--------------+-------------+-------+---------------+---------+--------- +------+------+----------+------------------------------------------------------ --------+ | 1 | PRIMARY | <subquery2> | ALL | distinct_key | NULL | NULL | NULL | 20 | 100.00 | | | 1 | PRIMARY | m | index | NULL | PRIMARY | 420 | NULL | 20 | 100.00 | Using where; Using index; Using join buffer (flat, BN L join) | | 2 | MATERIALIZED | m2 | index | NULL | PRIMARY | 420 | NULL | 20 | 100.00 | Using index | +------+--------------+-------------+-------+---------------+---------+--------- +------+------+----------+------------------------------------------------------ --------+ 3 rows in set, 1 warning (0.023 sec) MariaDB [test]> show warnings\G *************************** 1. row *************************** Level: Note Code: 1003 Message: select `mysql`.`m`.`User` AS `user` from `mysql`.`user` `m` semi join ( `mysql`.`user` `m2`) where `mysql`.`m`.`User` = `mysql`.`m2`.`User` 1 row in set (0.001 sec) {code} So there is no alias and it should fire an error. |
There is a complex query where there is a internal subquery with no alias reported anywhere and there is not error fire once the query run.
Tested a simple case: {code:java} MariaDB [test]> select user from mysql.user m where user in (select user from mysql.user m2 group by s.id); +---------------+ | user | +---------------+ | data_engineer | | ro1 | as you see there is no alias for 's' table. MariaDB [test]> explain extended select user from mysql.user m where user in (se lect user from mysql.user m2 group by s.id); +------+--------------+-------------+-------+---------------+---------+--------- +------+------+----------+------------------------------------------------------ --------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+--------------+-------------+-------+---------------+---------+--------- +------+------+----------+------------------------------------------------------ --------+ | 1 | PRIMARY | <subquery2> | ALL | distinct_key | NULL | NULL | NULL | 20 | 100.00 | | | 1 | PRIMARY | m | index | NULL | PRIMARY | 420 | NULL | 20 | 100.00 | Using where; Using index; Using join buffer (flat, BN L join) | | 2 | MATERIALIZED | m2 | index | NULL | PRIMARY | 420 | NULL | 20 | 100.00 | Using index | +------+--------------+-------------+-------+---------------+---------+--------- +------+------+----------+------------------------------------------------------ --------+ 3 rows in set, 1 warning (0.023 sec) MariaDB [test]> show warnings\G *************************** 1. row *************************** Level: Note Code: 1003 Message: select `mysql`.`m`.`User` AS `user` from `mysql`.`user` `m` semi join ( `mysql`.`user` `m2`) where `mysql`.`m`.`User` = `mysql`.`m2`.`User` 1 row in set (0.001 sec) {code} So there is no alias and it should fire an error. |
| Status | Open [ 1 ] | Confirmed [ 10101 ] |
| Component/s | Server [ 13907 ] | |
| Assignee | Oleksandr Byelkin [ sanja ] |
| Assignee | Oleksandr Byelkin [ sanja ] | Varun Gupta [ varun ] |
| Status | Confirmed [ 10101 ] | In Progress [ 3 ] |
| Assignee | Varun Gupta [ varun ] | Sergei Petrunia [ psergey ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Assignee | Sergei Petrunia [ psergey ] | Michael Widenius [ monty ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Assignee | Michael Widenius [ monty ] | Varun Gupta [ varun ] |
| Fix Version/s | 10.2.37 [ 25112 ] | |
| Fix Version/s | 10.3.28 [ 25111 ] | |
| Fix Version/s | 10.4.18 [ 25110 ] | |
| Fix Version/s | 10.5.9 [ 25109 ] | |
| Fix Version/s | 10.2 [ 14601 ] | |
| Fix Version/s | 10.3 [ 22126 ] | |
| Fix Version/s | 10.4 [ 22408 ] | |
| Fix Version/s | 10.5 [ 23123 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |
| Fix Version/s | 10.2.38 [ 25207 ] | |
| Fix Version/s | 10.2.37 [ 25112 ] |
| Fix Version/s | 10.5.10 [ 25204 ] | |
| Fix Version/s | 10.4.19 [ 25205 ] | |
| Fix Version/s | 10.3.29 [ 25206 ] | |
| Fix Version/s | 10.5.9 [ 25109 ] | |
| Fix Version/s | 10.4.18 [ 25110 ] | |
| Fix Version/s | 10.3.28 [ 25111 ] |
| Link |
This issue causes |
| Workflow | MariaDB v3 [ 112300 ] | MariaDB v4 [ 158226 ] |