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

LP:685952 - Wrong result with "union" in maria-5.3-wl21

    XMLWordPrintable

Details

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

    Description

      The following query:

      SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4)
      WHERE f2
      AND ( f3 IS NULL OR f1 = 7 )
      AND f1 NOT BETWEEN 255 AND 0 OR f3 BETWEEN 2 AND 2 AND
      ( f3 IS NULL OR f4 != 1 ) ;

      returns no rows when executed with "union" in maria-5.1-wl21 and 1 row when executed with other optimizations and other trees. In 5.1 , the "union" optimization is not used.

      test case:

      CREATE TABLE t1 (f1 int,f2 int,f3 int,f4 int,
      PRIMARY KEY (f1),KEY (f3),KEY (f4)) ;
      INSERT IGNORE INTO t1 VALUES ('9','0','2','6'),('9930','0','0',NULL);

      SET SESSION optimizer_switch='index_merge_intersection=off,index_merge_sort_intersection=off';
      SET SESSION optimizer_switch='index_merge_union=on,index_merge_sort_union=off';

      SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4)
      WHERE f2 AND
      ( f3 IS NULL OR f1 = 7 )
      AND f1 NOT BETWEEN 255 AND 0 OR f3 BETWEEN 2 AND 2 AND
      ( f3 IS NULL OR f4 != 1 ) ;

      explain:

      id: 1
      select_type: SIMPLE
      table: t1
      type: index_merge
      possible_keys: PRIMARY,f3,f4
      key: f3,PRIMARY
      key_len: 5,4
      ref: NULL
      rows: 2
      Extra: Using union(f3,PRIMARY); Using where

      Attachments

        Activity

          People

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