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

LP:823301 - Wrong result with index_merge / sort_union and LEFT JOIN

        Details

        • Type: Bug
        • Status: Closed
        • Priority: Critical
        • Resolution: Fixed
        • Affects Version/s: None
        • Fix Version/s: None
        • Component/s: None
        • Labels:

          Description

          The following query

          SELECT *
          FROM t1
          LEFT OUTER JOIN t2 ON t1.b = t2.a
          WHERE t1.c > 7 AND t1.e > 1 AND t1.e != 0 AND
          t1.e NOT IN ( 2 , 8 ) OR t1.e >= 7 AND t1.e < 8 OR t1.e > 7 ;

          returns 4 instead of 5 rows when executed with index_merge / sort_union .

          Explain:

          id select_type table type possible_keys key key_len ref rows Extra
          1 SIMPLE t1 index_merge PRIMARY,e,e_2 e,e_2 5,5 NULL 8 Using sort_union(e,e_2); Using where
          1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where

          test case:

          CREATE TABLE t1 ( b int, c int NOT NULL , d int, e int, KEY (e), PRIMARY KEY (c), KEY (e,c,d)) ;
          INSERT INTO t1 VALUES (0,58,7,7),(0,63,2,0),(0,64,186974208,8),(0,65,1,'-205389824'),
          (0,71,1901395968,'-258670592'),(0,72,321323008,'-749993984'),(0,73,0,3),(0,74,5,74252288),(0,75,5,3);

          CREATE TABLE t2 ( a int) ;
          INSERT INTO t2 VALUES (1),(1);

          SELECT *
          FROM t1
          LEFT OUTER JOIN t2 ON t1.b = t2.a
          WHERE t1.c > 7 AND t1.e > 1 AND t1.e != 0 AND t1.e NOT IN
          ( 2 , 8 )
          OR t1.e >= 7 AND t1.e < 8 OR t1.e > 7 ;

          bzr version-info:

          revision-id: <email address hidden>
          date: 2011-08-09 10:28:57 +0300
          build-date: 2011-08-09 16:40:36 +0300
          revno: 3147
          branch-nick: maria-5.3

          Reproducible in maria-5.3, Not reproducible in maria-5.2, mysql-5.5

            Gliffy Diagrams

              Attachments

                Activity

                Ascending order - Click to sort in descending order
                Hide
                Permalink
                philipstoev Philip Stoev added a comment -

                Re: Wrong result with index_merge / sort_union and LEFT JOIN
                A test case without a join:

                CREATE TABLE t1 ( a int NOT NULL , b int, c varchar(32), KEY (c,b), PRIMARY KEY (a)) ;
                INSERT INTO t1 VALUES (9,7,'s'),(10,1,'j'),(16,1,NULL),(17,1,'r'),(18,9,'v'),(19,1,NULL),(20,5,'r');

                SELECT *
                FROM t1
                WHERE a = 0 AND c LIKE 'l' AND
                ( a = 134 OR b = 157 )
                OR c < 'j' OR c > 'bj' ;

                explain:

                                                                    • 1. row ***************************
                                                                      id: 1
                                                                      select_type: SIMPLE
                                                                      table: t1
                                                                      type: index_merge
                                                                      possible_keys: PRIMARY,col_varchar_key
                                                                      key: PRIMARY,col_varchar_key
                                                                      key_len: 4,35
                                                                      ref: NULL
                                                                      rows: 6
                                                                      Extra: Using sort_union(PRIMARY,col_varchar_key); Using where
                                                                      1 row in set (0.00 sec)

                incorrect result set:

                9 7 s
                17 1 r
                18 9 v
                20 5 r

                expected result set:

                9 7 s
                10 1 j
                17 1 r
                18 9 v
                20 5 r

                bzr version-info
                revision-id: igor@askmonty.org-20110908162131-y1ddcj1sfxzlayah
                date: 2011-09-08 09:21:31 -0700
                build-date: 2011-09-09 10:07:16 +0300
                revno: 3180
                branch-nick: maria-5.3

                Show
                philipstoev Philip Stoev added a comment - Re: Wrong result with index_merge / sort_union and LEFT JOIN A test case without a join: CREATE TABLE t1 ( a int NOT NULL , b int, c varchar(32), KEY (c,b), PRIMARY KEY (a)) ; INSERT INTO t1 VALUES (9,7,'s'),(10,1,'j'),(16,1,NULL),(17,1,'r'),(18,9,'v'),(19,1,NULL),(20,5,'r'); SELECT * FROM t1 WHERE a = 0 AND c LIKE 'l' AND ( a = 134 OR b = 157 ) OR c < 'j' OR c > 'bj' ; explain: 1. row *************************** id: 1 select_type: SIMPLE table: t1 type: index_merge possible_keys: PRIMARY,col_varchar_key key: PRIMARY,col_varchar_key key_len: 4,35 ref: NULL rows: 6 Extra: Using sort_union(PRIMARY,col_varchar_key); Using where 1 row in set (0.00 sec) incorrect result set: 9 7 s 17 1 r 18 9 v 20 5 r expected result set: 9 7 s 10 1 j 17 1 r 18 9 v 20 5 r bzr version-info revision-id: igor@askmonty.org-20110908162131-y1ddcj1sfxzlayah date: 2011-09-08 09:21:31 -0700 build-date: 2011-09-09 10:07:16 +0300 revno: 3180 branch-nick: maria-5.3
                Hide
                Permalink
                ratzpo Rasmus Johansson added a comment -

                Launchpad bug id: 823301

                Show
                ratzpo Rasmus Johansson added a comment - Launchpad bug id: 823301

                  People

                  • Assignee:
                    igor Igor Babaev
                    Reporter:
                    philipstoev Philip Stoev
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    0 Start watching this issue

                    Dates

                    • Created:
                      Updated:
                      Resolved: