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

          is caused by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30586 DELETE with aggregation in subquery of WHERE returns bogus error

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28570 Select from view fails if definition of view has 'HAVING' in query

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28571 Select from view fails if definition of view has aggregate function ( like 'GROUP_CONCAT(COUNT(a))' ) in query

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

          Activity

            Ascending order - Click to sort in descending order
            igor Igor Babaev (Inactive) 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 (Inactive) 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 (Inactive) 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 (Inactive) 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)
            sanja Oleksandr Byelkin added a comment -

            OK to push

            sanja Oleksandr Byelkin added a comment - OK to push

            People

              igor Igor Babaev (Inactive)
              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.