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

Set function aggregated in outer select used in view definition

Details

    Description

      $ ./mtr main.delete --view
      Logging: ./mtr  main.delete --view
      MariaDB Version 10.4.29-MariaDB-debug
      ==============================================================================
      TEST                                      RESULT   TIME (ms) or COMMENT
      --------------------------------------------------------------------------
      main.delete                              [ fail ]
              Test ended at 2023-02-17 09:40:49
       
      CURRENT_TEST: main.delete
      mysqltest: At line 607: query 'select * from t1
      where $c' failed: 1356: View 'test.mysqltest_tmp_v' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
       
      The result from queries just before the failure was:
      < snip >
      #
      # MDEV-30586: DELETE with WHERE containing nested subquery
      #             with set function aggregated in outer subquery
      #
      create table t1 (a int);
      insert into t1 values (3), (7), (1);
      create table t2 (b int);
      insert into t2 values (2), (1), (4), (7);
      create table t3 (a int, b int);
      insert into t3 values (2,10), (7,30), (2,30), (1,10), (7,40);
      select * from t1
      where t1.a in (select t3.a from t3 group by t3.a
      having t3.a > any (select t2.b from t2
      where t2.b*10 < sum(t3.b)));
      --------------------------------------------------------------------------
      The servers were restarted 0 times
      Spent 0.000 of 3 seconds executing testcases
       
      Failure: Failed 1/1 tests, 0.00% were successful.
       
      Failing test(s): main.delete
      

      Attachments

        Issue Links

          Activity

            igor Igor Babaev added a comment -

            This bug is not caused by the fix of MDEV-30586. The test case added in the patch for MDEV-30586 demonstrates this bug with --view-protocol.

            igor Igor Babaev added a comment - This bug is not caused by the fix of MDEV-30586 . The test case added in the patch for MDEV-30586 demonstrates this bug with --view-protocol.
            igor Igor Babaev added a comment -

            With the view protocol this query is executed:

            select * from v;
            

            where v is specified as follows:

            create view v  as
            select * from t1
              where t1.a in (select t3.a from t3 group by t3.a
                               having t3.a > any (select t2.b from t2
                                                    where t2.b*10 < sum(t3.b)));
            

            The query returns:

            MariaDB [test]> select * from v;
            ERROR 1356 (HY000): View 'test.v' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
            

            Note that if we use CTE with the same specification query as for the view we get the expected result

            MariaDB [test]> with cte as 
                -> (select * from t1
                ->   where t1.a in (select t3.a from t3 group by t3.a
                ->                    having t3.a > any (select t2.b from t2
                ->                                         where t2.b*10 < sum(t3.b))))
                -> select * from cte;
            +------+
            | a    |
            +------+
            |    7 |
            +------+
            1 row in set (0.006 sec)
            

            igor Igor Babaev added a comment - With the view protocol this query is executed: select * from v; where v is specified as follows: create view v as select * from t1 where t1.a in ( select t3.a from t3 group by t3.a having t3.a > any ( select t2.b from t2 where t2.b*10 < sum (t3.b))); The query returns: MariaDB [test]> select * from v; ERROR 1356 (HY000): View 'test.v' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them Note that if we use CTE with the same specification query as for the view we get the expected result MariaDB [test]> with cte as -> (select * from t1 -> where t1.a in (select t3.a from t3 group by t3.a -> having t3.a > any (select t2.b from t2 -> where t2.b*10 < sum(t3.b)))) -> select * from cte; +------+ | a | +------+ | 7 | +------+ 1 row in set (0.006 sec)

            OK to push

            sanja Oleksandr Byelkin added a comment - OK to push

            People

              igor Igor Babaev
              serg Sergei Golubchik
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.