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

SUM(distinct) gives wrong result when reducing max_heap_table_size

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5.28a, 5.3.11, 5.2.13, 5.1.67
    • 5.5.29
    • None

    Description

      CREATE TABLE t1 (id INTEGER);
      		 
      CREATE TABLE t2 (id INTEGER);
      		 
      INSERT INTO t1 VALUES (1), (1), (1),(1);
      		 
      INSERT INTO t1 SELECT id FROM t1;
      		 
      INSERT INTO t1 SELECT id FROM t1;
      		 
      INSERT INTO t1 SELECT id FROM t1;
      		 
      INSERT INTO t1 SELECT id FROM t1;
      		 
      INSERT INTO t1 SELECT id FROM t1;
      		 
      INSERT INTO t1 SELECT id+1 FROM t1;
      		 
      INSERT INTO t1 SELECT id+2 FROM t1;
      		 
      INSERT INTO t1 SELECT id+4 FROM t1;
      		 
      INSERT INTO t1 SELECT id+8 FROM t1;
      		 
      INSERT INTO t1 SELECT id+16 FROM t1;
      		 
      INSERT INTO t1 SELECT id+32 FROM t1;
      		 
      INSERT INTO t1 SELECT id+64 FROM t1;
      		 
      INSERT INTO t1 SELECT id+128 FROM t1;
      		 
      INSERT INTO t1 SELECT id+256 FROM t1;
      		 
      INSERT INTO t1 SELECT id+512 FROM t1;
      		 
      INSERT INTO t1 SELECT id+1024 FROM t1;
      		 
      INSERT INTO t1 SELECT id+2048 FROM t1;
      		 
      INSERT INTO t1 SELECT id+4096 FROM t1;
      		 
      INSERT INTO t1 SELECT id+8192 FROM t1;
      		 
      INSERT INTO t2 SELECT id FROM t1 ORDER BY id*rand();
      		 
      SELECT SUM(DISTINCT id) sm FROM t2;
      		 
      SET max_heap_table_size=16384;
      		 
      SHOW variables LIKE 'max_heap_table_size';
      		 
      SELECT SUM(DISTINCT id) sm FROM t2;
      		 
      DROP TABLE t1;
      		 
      DROP TABLE t2;

      second SUM(DISTINCT) returns NULL

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-4311 Wrong result of COUNT(distinct) with low tmp_table_size / max_heap_table_size, part #2

          • Major - Major loss of function.
          • Closed
          links to

          Web Link Bug #56927 SUM(distinct) gives wrong result when reducing max_heap_table_size

          Activity

            Ascending order - Click to sort in descending order
            igor Igor Babaev (Inactive) created issue -
            serg Sergei Golubchik made changes -
            Field Original Value New Value
            Affects Version/s 5.2.13 [ 10800 ]
            Affects Version/s 5.3.11 [ 11700 ]
            serg Sergei Golubchik made changes -
            Description MySQL bug #56927 is reproducible in all versions of MariaDB. {code:sql}
            CREATE TABLE t1 (id INTEGER);
            CREATE TABLE t2 (id INTEGER);
            INSERT INTO t1 VALUES (1), (1), (1),(1);
            INSERT INTO t1 SELECT id FROM t1;
            INSERT INTO t1 SELECT id FROM t1;
            INSERT INTO t1 SELECT id FROM t1;
            INSERT INTO t1 SELECT id FROM t1;
            INSERT INTO t1 SELECT id FROM t1;
            INSERT INTO t1 SELECT id+1 FROM t1;
            INSERT INTO t1 SELECT id+2 FROM t1;
            INSERT INTO t1 SELECT id+4 FROM t1;
            INSERT INTO t1 SELECT id+8 FROM t1;
            INSERT INTO t1 SELECT id+16 FROM t1;
            INSERT INTO t1 SELECT id+32 FROM t1;
            INSERT INTO t1 SELECT id+64 FROM t1;
            INSERT INTO t1 SELECT id+128 FROM t1;
            INSERT INTO t1 SELECT id+256 FROM t1;
            INSERT INTO t1 SELECT id+512 FROM t1;
            INSERT INTO t1 SELECT id+1024 FROM t1;
            INSERT INTO t1 SELECT id+2048 FROM t1;
            INSERT INTO t1 SELECT id+4096 FROM t1;
            INSERT INTO t1 SELECT id+8192 FROM t1;
            INSERT INTO t2 SELECT id FROM t1 ORDER BY id*rand();
            SELECT SUM(DISTINCT id) sm FROM t2;
            SET max_heap_table_size=16384;
            SHOW variables LIKE 'max_heap_table_size';
            SELECT SUM(DISTINCT id) sm FROM t2;
            DROP TABLE t1;
            DROP TABLE t2;
            {code}

            second {{SUM(DISTINCT)}} returns {{NULL}}
            Summary Fix mysql bug #56927 reproducible in MariaDB 5.1/5.5 SUM(distinct) gives wrong result when reducing max_heap_table_size
            serg Sergei Golubchik made changes -
            Labels upstream
            serg Sergei Golubchik made changes -
            serg Sergei Golubchik made changes -
            Assignee Igor Babaev [ igor ] Sergei Golubchik [ serg ]
            serg Sergei Golubchik made changes -
            Assignee Sergei Golubchik [ serg ] Igor Babaev [ igor ]
            serg Sergei Golubchik made changes -
            Resolution Fixed [ 1 ]
            Status Open [ 1 ] Closed [ 6 ]
            elenst Elena Stepanova made changes -
            serg Sergei Golubchik made changes -
            Workflow defaullt [ 25939 ] MariaDB v2 [ 46238 ]
            ratzpo Rasmus Johansson (Inactive) made changes -
            Workflow MariaDB v2 [ 46238 ] MariaDB v3 [ 64312 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 64312 ] MariaDB v4 [ 146353 ]

            People

              igor Igor Babaev (Inactive)
              igor Igor Babaev (Inactive)
              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.