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

Dependent query cannot recognize expr in more than 1 nesting level

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Not a Bug
    • 10.5, 10.6, 10.9(EOL)
    • N/A
    • OTHER
    • None

    Description

      repro: https://dbfiddle.uk/1BpvGW2V

      here is the same repro working for MySQL: https://dbfiddle.uk/wa8Kc48N
      and for PostgreSQL: https://dbfiddle.uk/6Z-IfLMx

      The failing query patten is important to be supported. In complex queries, it allows to deduplicate subqueries used later more than one without having to put them in the main select.

      I would be grateful if it can be fixed.

      Attachments

        Issue Links

          duplicates

          New Feature - A new feature of the product, which has yet to be developed. MDEV-19078 Support lateral derived tables

          • Major - Major loss of function.
          • Open

          Activity

            Ascending order - Click to sort in descending order
            serg Sergei Golubchik added a comment -

            Test case from the dbfiddle:

            CREATE TABLE invoice (
            		 
              id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,
            		 
              amount DOUBLE DEFAULT NULL,
            		 
              name VARCHAR(100) DEFAULT NULL
            		 
            );
            		 
            INSERT INTO invoice (amount, name) 
            VALUES (4.0, 'Michael'), (15.0, 'Bara'), (4.0, 'Michael'), (6.0, 'John');
            		 
            SELECT sum(amount), name
            		 
            from invoice
            		 
            group by name
            		 
            having sum(amount) > 6;
            		 
            SELECT sum(amount), name
            		 
            from invoice
            		 
            group by name
            		 
            having (select name = 'John'); -- refer parent data in subquery - ok!
            		 
            SELECT sum(amount), name
            		 
            from invoice
            		 
            group by name
            		 
            having (select sum(amount) > 6); -- refer parent aggregate data in subquery - ok!
            		 
            SELECT sum(amount), name
            		 
            from invoice
            		 
            group by name
            		 
            having (select sum(amount) > 6 from (select 1) t); -- still ok!
            		 
            SELECT sum(amount), name
            		 
            from invoice
            		 
            group by name
            		 
            having (select v > 6 from (select sum(amount) v) t); -- the same, only the `sum()` expr
            		 
                                                                -- is moved inside the subsubquery
            		 
            SELECT sum(amount), name
            		 
            from invoice
            		 
            group by name
            		 
            having (select v = 'John' from (select name v) t); -- even simple column reference
            		 
                                                              -- is broken

            serg Sergei Golubchik added a comment - Test case from the dbfiddle: CREATE TABLE invoice ( id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL , amount DOUBLE DEFAULT NULL , name VARCHAR (100) DEFAULT NULL ); INSERT INTO invoice (amount, name ) VALUES (4.0, 'Michael' ), (15.0, 'Bara' ), (4.0, 'Michael' ), (6.0, 'John' ); SELECT sum (amount), name from invoice group by name having sum (amount) > 6; SELECT sum (amount), name from invoice group by name having ( select name = 'John' ); -- refer parent data in subquery - ok! SELECT sum (amount), name from invoice group by name having ( select sum (amount) > 6); -- refer parent aggregate data in subquery - ok! SELECT sum (amount), name from invoice group by name having ( select sum (amount) > 6 from ( select 1) t); -- still ok! SELECT sum (amount), name from invoice group by name having ( select v > 6 from ( select sum (amount) v) t); -- the same, only the `sum()` expr -- is moved inside the subsubquery SELECT sum (amount), name from invoice group by name having ( select v = 'John' from ( select name v) t); -- even simple column reference -- is broken
            mv Michael added a comment -

            Can this MariaDB limitation be fixed, please?

            For repro see the dbfiddle.uk snippet.

            All other DB vendors support unlimited nesting levels so this MariaDB limitation can be seen as a bug and there is no easy user workaround.

            mv Michael added a comment - Can this MariaDB limitation be fixed, please? For repro see the dbfiddle.uk snippet. All other DB vendors support unlimited nesting levels so this MariaDB limitation can be seen as a bug and there is no easy user workaround.
            sanja Oleksandr Byelkin added a comment - - edited

            from (select name v) is a subquery in the from clause AKA derived tables, and mariadb does not support dependences in derived tabes (independent of the level), so it is not a bug but feature request, see MDEV-19078

            sanja Oleksandr Byelkin added a comment - - edited from (select name v) is a subquery in the from clause AKA derived tables, and mariadb does not support dependences in derived tabes (independent of the level), so it is not a bug but feature request, see MDEV-19078

            People

              sanja Oleksandr Byelkin
              mv Michael
              Votes:
              0 Vote for this issue
              Watchers:
              4 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.