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

spider group by handler wrong result on order by aggregate

Details

    Description

      if I change mdev_29008.test to use ORDER BY without aliases to an aggregate function, like

      SELECT MIN(t2.a) AS f1, t1.b AS f2 FROM tbl_a AS t1 JOIN tbl_a AS t2 GROUP BY f2 ORDER BY MIN(t2.a), MAX(t2.a), f2;

      Then I can see that the spider will generate the query

      select max(t1.`a`) ``,min(t1.`a`) ``,min(t1.`a`) `f1`,t0.`b` `b` from `auto_test_remote`.`tbl_a` t0,`auto_test_remote`.`tbl_a` t1 group by t0.`b` order by ``,``,t0.`b`

      which is clearly wrong, it doesn't distinguish between min and max. It needs to auto-generate names for aggregates as needed.

      Also might apply to HAVING

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29480 spider group by handler wrong result on order by aggregate alias

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26345 SELECT MIN on Spider table returns more rows than expected

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

          Activity

            Ascending order - Click to sort in descending order
            serg Sergei Golubchik created issue -
            serg Sergei Golubchik made changes -
            Field Original Value New Value
            serg Sergei Golubchik made changes -
            Description if I change {{mdev_29008.test}} to use {{ORDER BY}} without aliases to an aggregate function, like
            {code:sql}
            SELECT MIN(t2.a) AS f1, t1.b AS f2 FROM tbl_a AS t1 JOIN tbl_a AS t2 GROUP BY f2 ORDER BY MIN(t2.a), MAX(t2.a), f2;
            {code}
            Then I can see that the spider will generate the query
            {code:sql}
            select max(t1.`a`) ``,min(t1.`a`) ``,min(t1.`a`) `f1`,t0.`b` `b` from `auto_test_remote`.`tbl_a` t0,`auto_test_remote`.`tbl_a` t1 group by t0.`b` order by ``,``,t0.`b`
            {code}
            which is clearly wrong, it doesn't distinguish between {{min}} and {{max}}. It needs to auto-generate names for aggregates as needed.             		if I change {{mdev_29008.test}} to use {{ORDER BY}} without aliases to an aggregate function, like
            {code:sql}
            SELECT MIN(t2.a) AS f1, t1.b AS f2 FROM tbl_a AS t1 JOIN tbl_a AS t2 GROUP BY f2 ORDER BY MIN(t2.a), MAX(t2.a), f2;
            {code}
            Then I can see that the spider will generate the query
            {code:sql}
            select max(t1.`a`) ``,min(t1.`a`) ``,min(t1.`a`) `f1`,t0.`b` `b` from `auto_test_remote`.`tbl_a` t0,`auto_test_remote`.`tbl_a` t1 group by t0.`b` order by ``,``,t0.`b`
            {code}
            which is clearly wrong, it doesn't distinguish between {{min}} and {{max}}. It needs to auto-generate names for aggregates as needed.

            Also _might_ apply to HAVING
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 10.7 [ 24805 ]
            ycp Yuchen Pei made changes -
            Assignee Nayuta Yanagisawa [ JIRAUSER47117 ] Yuchen Pei [ JIRAUSER52627 ]
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 10.3 [ 22126 ]
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 10.8 [ 26121 ]
            ycp Yuchen Pei made changes -
            Labels spider-gbh
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 10.9 [ 26905 ]
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 10.4 [ 22408 ]
            ycp Yuchen Pei made changes -
            ycp Yuchen Pei made changes -
            Priority Major [ 3 ] Critical [ 2 ]
            ycp Yuchen Pei added a comment -

            Fixed by a MDEV-26345 patch

            ycp Yuchen Pei added a comment - Fixed by a MDEV-26345 patch
            ycp Yuchen Pei made changes -
            Fix Version/s 10.5.27 [ 29902 ]
            Fix Version/s 10.5 [ 23123 ]
            Fix Version/s 10.6 [ 24028 ]
            Resolution Fixed [ 1 ]
            Status Open [ 1 ] Closed [ 6 ]
            JIraAutomate JiraAutomate made changes -
            Fix Version/s 10.6.20 [ 29903 ]
            Fix Version/s 10.11.10 [ 29904 ]
            Fix Version/s 11.2.6 [ 29906 ]
            Fix Version/s 11.4.4 [ 29907 ]

            People

              ycp Yuchen Pei
              serg Sergei Golubchik
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.