Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-23449

alias do not exist and a query do not report an error

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

          Activity

            massimo.disaro Massimo created issue -
            massimo.disaro Massimo made changes -
            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.



            elenst Elena Stepanova made changes -
            Key MENT-904 MDEV-23449
            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 ]
            elenst Elena Stepanova made changes -
            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.



            elenst Elena Stepanova made changes -
            Status Open [ 1 ] Confirmed [ 10101 ]
            elenst Elena Stepanova made changes -
            Component/s Server [ 13907 ]
            Assignee Oleksandr Byelkin [ sanja ]
            varun Varun Gupta (Inactive) made changes -
            Assignee Oleksandr Byelkin [ sanja ] Varun Gupta [ varun ]
            varun Varun Gupta (Inactive) made changes -
            Status Confirmed [ 10101 ] In Progress [ 3 ]
            varun Varun Gupta (Inactive) added a comment - - edited

            Some context:

            There are subquery rewrites done mostly for IN/ALL/ANY/EXISTS subquery at the prepare stage.

            The rewrites are done in these 2 functions:
            1) remove_redundant_subquery_clauses

            • This function removes the GROUP BY clause and DISTINCT from IN/ALL/ANY/EXISTS subquery.
            • This function is called before fix_fields is called by the GROUP BY clause

            2) check_and_do_in_subquery_rewrites

            • This function removes the ORDER BY clause from IN/ALL/ANY subquery.
            • This function also picks the strategy for execution of the subquery (materialization or IN->EXISTS conversion)
            • This function is called after fix_fields is called for ORDER BY clause.
            varun Varun Gupta (Inactive) added a comment - - edited Some context: There are subquery rewrites done mostly for IN/ALL/ANY/EXISTS subquery at the prepare stage. The rewrites are done in these 2 functions: 1) remove_redundant_subquery_clauses This function removes the GROUP BY clause and DISTINCT from IN/ALL/ANY/EXISTS subquery. This function is called before fix_fields is called by the GROUP BY clause 2) check_and_do_in_subquery_rewrites This function removes the ORDER BY clause from IN/ALL/ANY subquery. This function also picks the strategy for execution of the subquery (materialization or IN->EXISTS conversion) This function is called after fix_fields is called for ORDER BY clause.

            Lets take an example

            SELECT * FROM t2 WHERE t2.a IN (SELECT t1.b FROM t1 GROUP by t1.a);
            

            First such queries are not allowed with sql_mode=ONLY_FULL_GROUP_BY.
            But lets cover the cases when the query is allowed.
            First thing we need to do is resolve the GROUP BY clause. This would check the validity of the query and if the query is invalid (like the case in the description), an error will be thrown.

            But after the columns are resolved then there is no need of the grouping operation.

            So to frame it better:
            For a query like:

            SELECT t1.b FROM t1 GROUP by t1.a
            

            this means, for each GROUP-BY-group of t1.a get some value of t1.b
            This is the semantics of the subquery.

            Now extending this for the query

            SELECT * FROM t2 WHERE t2.a IN (SELECT t1.b FROM t1 GROUP by t1.a);
            

            for each row of t2:
            get a value of t2.a
            if none of the GROUP-BY groups have a value of t1.b that's equal to t2.a (no matches ever)
            then we must return false.
            otherwise, if every row in every group matches, then we must return true.
            if there are both matches and non-matches - we may return either true or false
            if the table is empty, return false.

            so with this the semantics of the query is the same as this query

            SELECT * FROM t2 WHERE t2.a IN (SELECT t1.b FROM t1);
            

            varun Varun Gupta (Inactive) added a comment - Lets take an example SELECT * FROM t2 WHERE t2.a IN ( SELECT t1.b FROM t1 GROUP by t1.a); First such queries are not allowed with sql_mode=ONLY_FULL_GROUP_BY. But lets cover the cases when the query is allowed. First thing we need to do is resolve the GROUP BY clause. This would check the validity of the query and if the query is invalid (like the case in the description), an error will be thrown. But after the columns are resolved then there is no need of the grouping operation. So to frame it better: For a query like: SELECT t1.b FROM t1 GROUP by t1.a this means, for each GROUP-BY-group of t1.a get some value of t1.b This is the semantics of the subquery. Now extending this for the query SELECT * FROM t2 WHERE t2.a IN ( SELECT t1.b FROM t1 GROUP by t1.a); for each row of t2: get a value of t2.a if none of the GROUP-BY groups have a value of t1.b that's equal to t2.a (no matches ever) then we must return false. otherwise, if every row in every group matches, then we must return true. if there are both matches and non-matches - we may return either true or false if the table is empty, return false. so with this the semantics of the query is the same as this query SELECT * FROM t2 WHERE t2.a IN ( SELECT t1.b FROM t1);
            varun Varun Gupta (Inactive) made changes -
            Assignee Varun Gupta [ varun ] Sergei Petrunia [ psergey ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            varun Varun Gupta (Inactive) made changes -
            Assignee Sergei Petrunia [ psergey ] Michael Widenius [ monty ]

            Patch ok. I have just asked Varun for a small update to the commit message to make more clear why it's safe to move the code over this if:
            mixed_implicit_grouping= false;
            if ((~thd->variables.sql_mode & MODE_ONLY_FULL_GROUP_BY) &&
            select_lex->with_sum_func && !group_list)
            {

            monty Michael Widenius added a comment - Patch ok. I have just asked Varun for a small update to the commit message to make more clear why it's safe to move the code over this if: mixed_implicit_grouping= false; if ((~thd->variables.sql_mode & MODE_ONLY_FULL_GROUP_BY) && select_lex->with_sum_func && !group_list) {

            ok to push

            monty Michael Widenius added a comment - ok to push
            monty Michael Widenius made changes -
            Status In Review [ 10002 ] Stalled [ 10000 ]
            monty Michael Widenius made changes -
            Assignee Michael Widenius [ monty ] Varun Gupta [ varun ]
            varun Varun Gupta (Inactive) made changes -
            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 ]
            dbart Daniel Bartholomew made changes -
            Fix Version/s 10.2.38 [ 25207 ]
            Fix Version/s 10.2.37 [ 25112 ]
            psergei Sergei Petrunia made changes -
            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 ]
            serg Sergei Golubchik made changes -
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 112300 ] MariaDB v4 [ 158226 ]

            People

              varun Varun Gupta (Inactive)
              massimo.disaro Massimo
              Votes:
              1 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.