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

Wrong result with GROUP BY + multipart key + MIN/MAX loose scan and a subquery

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.3.12
    • 5.3.13
    • None
    • None

    Description

      The following query (initially submitted as MDEV-765):

      SELECT MIN(a), b
      FROM t1
      WHERE a > ( SELECT a FROM t2 WHERE a = 0 )
      GROUP BY b;

      does not return as many rows as if the subquery is replaced with a constant.

      explain:

      id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
      1       PRIMARY t1      range   NULL    b       10      NULL    10      Using where; Using index for group-by
      2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    2       Using where

      optimizer switch:

      index_merge=on,
      index_merge_union=on,
      index_merge_sort_union=on,
      index_merge_intersection=on,
      index_merge_sort_intersection=off,
      index_condition_pushdown=off,
      derived_merge=off,
      derived_with_keys=off,
      firstmatch=off,
      loosescan=off,
      materialization=off,
      in_to_exists=on,
      semijoin=off,
      partial_match_rowid_merge=on,
      partial_match_table_scan=on,
      subquery_cache=off,
      mrr=off,
      mrr_cost_based=off,
      mrr_sort_keys=off,
      outer_join_with_cache=off,
      semijoin_with_cache=off,
      join_cache_incremental=on,
      join_cache_hashed=on,
      join_cache_bka=on,
      optimize_join_buffer_size=off,
      table_elimination=on

      test case:

      CREATE TABLE t1 (a int, b int, KEY (b, a)) ;
      INSERT INTO t1 VALUES (0,99),(9,99),(4,0),(7,0),(99,0),(7,0),(8,0),(99,0),(1,0);
      CREATE TABLE t2 (c int) ;
      INSERT INTO t2 VALUES (0),(1);
      SELECT MIN(a), b FROM t1 WHERE a > ( SELECT c FROM t2 WHERE c = 0 ) GROUP BY b;

      returns:

      MIN(a)  b
      1       0

      SELECT MIN(a), b
      FROM t1
      WHERE a > 0
      GROUP BY b;

      returns:

      MIN(a)  b
      1       0
      9       99

      bzr version-info:
       
      date: 2011-08-11 22:34:41 -0700
      build-date: 2011-08-12 12:20:36 +0300
      revno: 3151
      branch-nick: maria-5.3

      Repeatable on maria-5.3. Not repeatable on maria-5.2. Not repeatable with other subquery operators.
      To repeat on 5.5 one needs that the subquery is expensive, that is, it must examine >100 rows.

      Attachments

        Activity

          People

            timour Timour Katchaounov (Inactive)
            timour Timour Katchaounov (Inactive)
            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.