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

MDEV-31466 name resolution fails in --view

Details

    Description

      ./mtr main.derived --view

      Attachments

        Issue Links

          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-36474 Prepared statement with derived table column name specification within used view causing memory use-after-free

          • Major - Major loss of function.
          • In Review
          is caused by

          New Feature - A new feature of the product, which has yet to be developed. MDEV-31466 Add optional correlation column list for derived tables

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            Johnston Rex Johnston added a comment - 

            create table t1 (c1 int, c2 int, c3 int);
            		 
            insert into t1 values (1,2,3),(4,5,6);
            		 
            create table t2 like t1;
            		 
            insert into t2 values (7,8,9),(10,11,12);
            		 
            create view v1 as select a1 from   (select * from t1 union select * from t2 order by c1) as d3 (a1, a2, a3);

            view is created as

            TYPE=VIEW
            		 
            query=select `d3`.`a1` AS `a1` from (select `test`.`t1`.`c1` AS `a1`,`test`.`t1`.`c2` AS `a2`,`test`.`t1`.`c3` AS `a3` from `test`.`t1` union select `test`.`t2`.`c1` AS `c1`,`test`.`t2`.`c2` AS `c2`,`test`.`t2`.`c3` AS `c3` from `test`.`t2` order by `c1`) `d3`(`a1`,`a2`,`a3`)

            which, because the derived table's unit contains a union, isn't valid

            MariaDB [test]> select c1 as a1, c2 as a2, c3 as a3 from t1 union select c1, c2, c3 from t2 order by c1;
            		 
            ERROR 1054 (42S22): Unknown column 'c1' in 'order clause'
            		 
            MariaDB [test]> select c1 as a1, c2 as a2, c3 as a3 from t1 order by c1;
            		 
            +------+------+------+
            		 
            | a1   | a2   | a3   |
            		 
            +------+------+------+
            		 
            |    1 |    2 |    3 |
            		 
            |    4 |    5 |    6 |
            		 
            +------+------+------+

            Johnston Rex Johnston added a comment - create table t1 (c1 int , c2 int , c3 int ); insert into t1 values (1,2,3),(4,5,6); create table t2 like t1; insert into t2 values (7,8,9),(10,11,12); create view v1 as select a1 from ( select * from t1 union select * from t2 order by c1) as d3 (a1, a2, a3); view is created as TYPE=VIEW query=select `d3`.`a1` AS `a1` from (select `test`.`t1`.`c1` AS `a1`,`test`.`t1`.`c2` AS `a2`,`test`.`t1`.`c3` AS `a3` from `test`.`t1` union select `test`.`t2`.`c1` AS `c1`,`test`.`t2`.`c2` AS `c2`,`test`.`t2`.`c3` AS `c3` from `test`.`t2` order by `c1`) `d3`(`a1`,`a2`,`a3`) which, because the derived table's unit contains a union, isn't valid MariaDB [test]> select c1 as a1, c2 as a2, c3 as a3 from t1 union select c1, c2, c3 from t2 order by c1; ERROR 1054 (42S22): Unknown column 'c1' in 'order clause' MariaDB [test]> select c1 as a1, c2 as a2, c3 as a3 from t1 order by c1; + ------+------+------+ | a1 | a2 | a3 | + ------+------+------+ | 1 | 2 | 3 | | 4 | 5 | 6 | + ------+------+------+
            Johnston Rex Johnston added a comment -

            We need to reset the original names within each SELECT_LEX (that was altered) representing a derived table before writing out the view definition file.
            This is done by changing the simple boolean flag (original_names_are_saved) in the TABLE_LIST structure to a pointer (original_names_source) to the SELECT_LEX where the item_list was altered, allowing us later on in mysql_create_view() to loop through all the TABLE_LISTS associated with all the SELECT_LEXs looking for altered item_lists and resetting where needed.

            Johnston Rex Johnston added a comment - We need to reset the original names within each SELECT_LEX (that was altered) representing a derived table before writing out the view definition file. This is done by changing the simple boolean flag (original_names_are_saved) in the TABLE_LIST structure to a pointer (original_names_source) to the SELECT_LEX where the item_list was altered, allowing us later on in mysql_create_view() to loop through all the TABLE_LISTS associated with all the SELECT_LEXs looking for altered item_lists and resetting where needed.
            Johnston Rex Johnston added a comment - - edited

            Hi igor
            Can you please review this?

            https://github.com/MariaDB/server/tree/bb-11.7-MDEV-34931-MDEV-31466-fix
            https://github.com/MariaDB/server/commit/6c7177fc5318034510ced5e5f164b84674b40be0

            Thanks, Rex

            Johnston Rex Johnston added a comment - - edited Hi igor Can you please review this? https://github.com/MariaDB/server/tree/bb-11.7-MDEV-34931-MDEV-31466-fix https://github.com/MariaDB/server/commit/6c7177fc5318034510ced5e5f164b84674b40be0 Thanks, Rex
            serg Sergei Golubchik added a comment -

            Johnston, I later found it that it's not only name resolution, I saw other issues too, I guess. So, please, verify that

            ./mtr main.derived --view

            succeeds

            serg Sergei Golubchik added a comment - Johnston , I later found it that it's not only name resolution, I saw other issues too, I guess. So, please, verify that ./mtr main.derived --view succeeds
            Johnston Rex Johnston added a comment -

            It certainly does.

            Logging: /home/rex/src/mariadb/bb-11.7-MDEV-31466/mysql-test/mtr  main.derived --view
            		 
            VS config: 
            vardir: /home/rex/src/mariadb/bb-11.7-MDEV-31466/mysql-test/var
            		 
            Checking leftover processes...
            		 
            Removing old var directory...
            		 
             - WARNING: Using the 'mysql-test/var' symlink
            		 
            Creating var directory '/home/rex/src/mariadb/bb-11.7-MDEV-31466/mysql-test/var'...
            		 
            Checking supported features...
            		 
            MariaDB Version 11.7.0-MariaDB-debug
            		 
             - SSL connections supported
            		 
             - binaries are debug compiled
            		 
             - binaries built with wsrep patch
            		 
            Collecting tests...
            		 
            Installing system database...
            		 
             
            		 
            ==============================================================================
            		 
             
            		 
            TEST                                      RESULT   TIME (ms) or COMMENT
            		 
            --------------------------------------------------------------------------
            		 
             
            		 
            worker[01] Using MTR_BUILD_THREAD 300, with reserved ports 19000..19029
            		 
            main.derived                             [ pass ]    981
            		 
            --------------------------------------------------------------------------
            		 
            The servers were restarted 0 times
            		 
            Spent 0.981 of 3 seconds executing testcases
            		 
             
            		 
            Completed: All 1 tests were successful.

            Johnston Rex Johnston added a comment - It certainly does. Logging: /home/rex/src/mariadb/bb-11.7-MDEV-31466/mysql-test/mtr main.derived --view VS config: vardir: /home/rex/src/mariadb/bb-11.7-MDEV-31466/mysql-test/var Checking leftover processes... Removing old var directory... - WARNING: Using the 'mysql-test/var' symlink Creating var directory '/home/rex/src/mariadb/bb-11.7-MDEV-31466/mysql-test/var'... Checking supported features... MariaDB Version 11.7.0-MariaDB-debug - SSL connections supported - binaries are debug compiled - binaries built with wsrep patch Collecting tests... Installing system database...   ==============================================================================   TEST RESULT TIME (ms) or COMMENT --------------------------------------------------------------------------   worker[01] Using MTR_BUILD_THREAD 300, with reserved ports 19000..19029 main.derived [ pass ] 981 -------------------------------------------------------------------------- The servers were restarted 0 times Spent 0.981 of 3 seconds executing testcases   Completed: All 1 tests were successful.
            Roel Roel Van de Paar added a comment - - edited

            I gave main.derived a solid workout (including with --view), and did not see any failures in the fixed branch, but 95k+ passes in dbg+opt builds.

            As a counter confirmation, in the non-fixed branch the main.derived test, with the --view option, fails every single run with:

            bb-11.7-MDEV-31466 CS 11.7.0 5d30009c58a428c34bb0fc32b40db1acd0bae9a9 (Debug)

            		 
            mysqltest: At line 1820: query 'select a1 from
            		 
            (select * from t1 union select * from t2 order by c1) as d3 (a1, a2, a3)' failed: ER_BAD_FIELD_ERROR (1054): Unknown column 'c1' in 'order clause'

            Roel Roel Van de Paar added a comment - - edited I gave main.derived a solid workout (including with --view ), and did not see any failures in the fixed branch, but 95k+ passes in dbg+opt builds. As a counter confirmation, in the non-fixed branch the main.derived test, with the --view option, fails every single run with: bb-11.7-MDEV-31466 CS 11.7.0 5d30009c58a428c34bb0fc32b40db1acd0bae9a9 (Debug) mysqltest: At line 1820: query 'select a1 from (select * from t1 union select * from t2 order by c1) as d3 (a1, a2, a3)' failed: ER_BAD_FIELD_ERROR (1054): Unknown column 'c1' in 'order clause'
            igor Igor Babaev (Inactive) added a comment -

            Ok to push.

            igor Igor Babaev (Inactive) added a comment - Ok to push.

            People

              Johnston Rex Johnston
              serg Sergei Golubchik
              Votes:
              0 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.