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

LP:993459 - Execution of PS for a query with GROUP BY returns wrong result

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Won't Fix
    • None
    • None
    • None

    Description

      The following sequence of commands returns a wrong result in MariaDb 5.2 (but not in 5.3/5.5) :

      PREPARE s1 FROM "
      SELECT c1, t2.c2, count(c3)
      FROM ( SELECT 3 as c2 FROM dual WHERE @x = 1
      UNION
      SELECT 2 FROM dual WHERE @x = 1 OR @x = 2) AS t1,
      ( SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual
      UNION
      SELECT '2012-03-01 02:00:00', 3, 2 FROM dual
      UNION
      SELECT '2012-03-01 01:00:00', 2, 1 FROM dual) AS t2
      WHERE t2.c2 = t1.c2
      GROUP BY c1, c2";

      SET @x = 1;

      SELECT c1, t2.c2, count(c3)
      FROM ( SELECT 3 as c2 FROM dual WHERE @x = 1
      UNION SELECT 2 FROM dual WHERE @x = 1 OR @x = 2 ) AS t1,
      ( SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual
      UNION
      SELECT '2012-03-01 02:00:00', 3, 2 FROM dual
      UNION
      SELECT '2012-03-01 01:00:00', 2, 1 FROM dual ) AS t2
      WHERE t2.c2 = t1.c2
      GROUP BY c1, c2;
      EXECUTE s1;
      SET @x = 2;
      SELECT c1, t2.c2, count(c3)
      FROM ( SELECT 3 as c2 FROM dual WHERE @x = 1
      UNION
      SELECT 2 FROM dual WHERE @x = 1 OR @x = 2 ) AS t1,
      ( SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual
      UNION SELECT '2012-03-01 02:00:00', 3, 2 FROM dual
      UNION SELECT '2012-03-01 01:00:00', 2, 1 FROM dual ) AS t2
      WHERE t2.c2 = t1.c2
      GROUP BY c1, c2;
      EXECUTE s1;
      SET @x = 1;
      SELECT c1, t2.c2, count(c3)
      FROM ( SELECT 3 as c2 FROM dual WHERE @x = 1
      UNION SELECT 2 FROM dual WHERE @x = 1 OR @x = 2 ) AS t1,
      ( SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual
      UNION SELECT '2012-03-01 02:00:00', 3, 2 FROM dual
      UNION SELECT '2012-03-01 01:00:00', 2, 1 FROM dual ) AS t2
      WHERE t2.c2 = t1.c2
      GROUP BY c1, c2;
      EXECUTE s1;

      For the last statement we have:
      MariaDB [test]> EXECUTE s1;
      --------------------------------

      c1 c2 count(c3)

      --------------------------------

      2012-03-01 01:00:00 3 2
      2012-03-01 02:00:00 3 1

      --------------------------------
      though the expected result is:
      MariaDB [test]> EXECUTE s1;
      --------------------------------

      c1 c2 count(c3)

      --------------------------------

      2012-03-01 01:00:00 2 1
      2012-03-01 01:00:00 3 1
      2012-03-01 02:00:00 3 1

      --------------------------------

      (see also bug #13805127 for mysql-5.5)

      Attachments

        Activity

          People

            sanja Oleksandr Byelkin
            igor Igor Babaev (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            0 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.