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

after changing SUM(dkx26.c2),the col_2's value unexpectedly change affected by UNION ALL

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Cannot Reproduce
    • 12.1.2
    • N/A
    • Server
    • None
    • Not for Release Notes

    Description

      description: after changing SUM(dkx26.c2),the col_2's value unexpectedly change affected by UNION ALL

      mysql> (SELECT SQRT(dkx26.c15) AS col_1, SUM(dkx26.c2) AS col_2 FROM t3 AS dkx26 WHERE (HOUR('2023-01-01 12:00:00') = 80) GROUP BY SQRT(dkx26.c15), dkx26.c11 ORDER BY dkx26.c11 DESC) UNION ALL SELECT SUM(14) AS col_1, MIN('2023-01-01 12:00:00') AS col_2;
      +-------+------------------------------------------+
      | col_1 | col_2                                    |
      +-------+------------------------------------------+
      |    14 | 0x323032332D30312D30312031323A30303A3030 |
      +-------+------------------------------------------+
      1 row in set (0.00 sec)
       
      mysql> (SELECT SQRT(dkx26.c15) AS col_1, -SUM(-dkx26.c2) AS col_2 FROM t3 AS dkx26 WHERE (HOUR('2023-01-01 12:00:00') = 80) GROUP BY SQRT(dkx26.c15), dkx26.c11 ORDER BY dkx26.c11 DESC) UNION ALL SELECT SUM(14) AS col_1, MIN('2023-01-01 12:00:00') AS col_2;
      +-------+---------------------+
      | col_1 | col_2               |
      +-------+---------------------+
      |    14 | 2023-01-01 12:00:00 |
      +-------+---------------------+
      1 row in set (0.00 sec)
      

      How to repeat

      DROP DATABASE IF EXISTS test;
      CREATE DATABASE IF NOT EXISTS test;
      SET GLOBAL sort_buffer_size = 64 * 1024 * 1024;
      SET GLOBAL read_rnd_buffer_size = 8 * 1024 * 1024;
      USE test;
       
      CREATE TABLE t3 (
          c1 INT NOT NULL AUTO_INCREMENT,
          c2 INT NOT NULL,
          c3 INT NOT NULL,
          c4 YEAR NOT NULL,
          c5 DATETIME NULL,
          c6 TINYINT NULL,
          c7 SMALLINT NULL,
          c8 MEDIUMINT NULL,
          c9 BIGINT NULL,
          c10 LONGTEXT NULL,
          c11 GEOMETRY NULL,
          c12 TINYTEXT NULL,
          c13 TINYBLOB NULL,
          c14 SET('x','y','z') NULL,
          c15 TINYINT(1) NULL,
          PRIMARY KEY (c1)
      );
       
      INSERT INTO t3 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15) VALUES (9473, 3166, 7923, 2009, '2025-10-28 00:33:04', 64, 33, 23, 5, 'sample_zTTcDyJQiepslGYJ3zx7HK7FrfiC27HYfiM2DyjEqVaxZXyJQZAk243c5b7yltsU3bDVzF2sv1hGOpsKD5u5Vsxz394dHEEgy83H8xb47ex2oB6Sq0o7pOfwoVHbKZu6RgdQZDvfErgKcrI6jjhxlh5vvf4A5bMHspZVCHf6xQTLobsltrxVRPWcVEpcFa5j1I95z3Z4Rjv7rhDXeACav9XF6AozF9GisKKaR6TZV1uqxe9tbHTweWshIEX8yNnrU3IxQZKPJKdvzuZULMFQtInIhk96d8YG30AdQ5pBtYaX65GusSaJXGMp3Ihk3IErQfBpVTu29NJzaMcfVkaVMp2xAwfBSI3cvVabVv8biqnHJEPV5TWpfuyEyNERmQB3uT4QchyRMQMwZvjD7dLdhzAswCYPfR1cgVswYIJ1uRUY5JmnVxXSazO9HaCdk42BhmyWoXGzKbcNQ4D5uw3C9jIuse73AKzFL2LkIeEwgZAsimZuiM7Q4EwQWxy7B09PFzXlmKpILqGuT0hKw5wsTyKNl0wjI2FNH2bPoVq4ujSAQDwJr4jSz38', ST_GeomFromText('POINT(-7.208789 -111.046928)'), 'sample_lo1SDGmMjznqDAbPH7lv1PLIycmmGnug3w9c1EMVaOOvVY0FM7GLWSZHVE6MVSrkzdR1XlrSer0Ae6U0ldCAgDDUG', X'CBBA34E99F8CDA95E4A2AFDF8210', 'z', NULL);
       
      (SELECT SQRT(dkx26.c15) AS col_1, SUM(dkx26.c2) AS col_2 FROM t3 AS dkx26 WHERE (HOUR('2023-01-01 12:00:00') = 80) GROUP BY SQRT(dkx26.c15), dkx26.c11 ORDER BY dkx26.c11 DESC) UNION ALL SELECT SUM(14) AS col_1, MIN('2023-01-01 12:00:00') AS col_2;
      (SELECT SQRT(dkx26.c15) AS col_1, -SUM(-dkx26.c2) AS col_2 FROM t3 AS dkx26 WHERE (HOUR('2023-01-01 12:00:00') = 80) GROUP BY SQRT(dkx26.c15), dkx26.c11 ORDER BY dkx26.c11 DESC) UNION ALL SELECT SUM(14) AS col_1, MIN('2023-01-01 12:00:00') AS col_2;
      
      

      Attachments

        Activity

          People

            serg Sergei Golubchik
            chen7897 cl hl
            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.