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

            Transition Time In Source Status Execution Times
            Yuchen Pei made transition -
            Open Closed
            		760d 19h 30m 1

            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.