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

LP:702374 - wrong query result in subselect_sj.test

        Details

        • Type: Bug
        • Status: Closed
        • Resolution: Not a Bug
        • Affects Version/s: None
        • Fix Version/s: None
        • Component/s: None
        • Labels:

          Description

          The testcase for "Bug#46550 Azalea returning duplicate results for some IN subqueries" in subselect_sj.test produces wrong results:

          CREATE TABLE t0 (
          int_key int(11) DEFAULT NULL,
          varchar_key varchar(1) DEFAULT NULL,
          varchar_nokey varchar(1) DEFAULT NULL,
          KEY int_key (int_key),
          KEY varchar_key (varchar_key,int_key)
          );

          INSERT INTO t0 VALUES
          (1,'m','m'),
          (40,'h','h'),
          (1,'r','r'),
          (1,'h','h'),
          (9,'x','x'),
          (NULL,'q','q'),
          (NULL,'k','k'),
          (7,'l','l'),
          (182,'k','k'),
          (202,'a','a'),
          (7,'x','x'),
          (6,'j','j'),
          (119,'z','z'),
          (4,'d','d'),
          (5,'h','h'),
          (1,'u','u'),
          (3,'q','q'),
          (7,'a','a'),
          (3,'e','e'),
          (6,'l','l');

          CREATE TABLE t1 (
          int_key int(11) DEFAULT NULL,
          varchar_key varchar(1) DEFAULT NULL,
          varchar_nokey varchar(1) DEFAULT NULL,
          KEY int_key (int_key),
          KEY varchar_key (varchar_key,int_key)
          );
          INSERT INTO t1 VALUES (7,NULL,NULL),(4,'x','x');

          CREATE TABLE t2 (
          int_key int(11) DEFAULT NULL,
          varchar_key varchar(1) DEFAULT NULL,
          varchar_nokey varchar(1) DEFAULT NULL,
          KEY int_key (int_key),
          KEY varchar_key (varchar_key,int_key)
          );
          INSERT INTO t2 VALUES (123,NULL,NULL);

          MariaDB [j6]> SELECT t0.int_key FROM t0, t2 WHERE t0.varchar_nokey IN (SELECT t1_1 .varchar_key FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key);
          ---------

          int_key

          ---------

          9
          9
          7
          7

          ---------
          4 rows in set (0.00 sec)

          The correct result is

          { (9), (7)}

          , i.e. we get duplicates.

            Gliffy Diagrams

              Attachments

                Activity

                Ascending order - Click to sort in descending order
                Hide
                Permalink
                psergey Sergei Petrunia added a comment -

                Re: wrong query result with semi-join + FirstMatch (subselect_sj.test)
                The EXPLAIN:

                MariaDB [j6]> explain SELECT t0.int_key FROM t0, t2 WHERE t0.varchar_nokey IN ( SELECT t1_1 .varchar_key FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key );
                ----------------------------------------------------------------------------------------------------------------------------------+

                id select_type table type possible_keys key key_len ref rows Extra

                ----------------------------------------------------------------------------------------------------------------------------------+

                1 PRIMARY t2 system NULL NULL NULL NULL 1  
                1 PRIMARY t1_1 index varchar_key varchar_key 9 NULL 2 Using where; Using index; LooseScan
                1 PRIMARY t1_2 index NULL int_key 5 NULL 2 Using index; FirstMatch(t1_1); Using join buffer (flat, BNL join)
                1 PRIMARY t0 ALL NULL NULL NULL NULL 20 Using where; Using join buffer (flat, BNL join)

                ----------------------------------------------------------------------------------------------------------------------------------+

                Show
                psergey Sergei Petrunia added a comment - Re: wrong query result with semi-join + FirstMatch (subselect_sj.test) The EXPLAIN: MariaDB [j6] > explain SELECT t0.int_key FROM t0, t2 WHERE t0.varchar_nokey IN ( SELECT t1_1 .varchar_key FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key ); --- ----------- ----- ------ ------------- ----------- ------- ---- ---- ------------------------------------------------------------------+ id select_type table type possible_keys key key_len ref rows Extra --- ----------- ----- ------ ------------- ----------- ------- ---- ---- ------------------------------------------------------------------+ 1 PRIMARY t2 system NULL NULL NULL NULL 1   1 PRIMARY t1_1 index varchar_key varchar_key 9 NULL 2 Using where; Using index; LooseScan 1 PRIMARY t1_2 index NULL int_key 5 NULL 2 Using index; FirstMatch(t1_1); Using join buffer (flat, BNL join) 1 PRIMARY t0 ALL NULL NULL NULL NULL 20 Using where; Using join buffer (flat, BNL join) --- ----------- ----- ------ ------------- ----------- ------- ---- ---- ------------------------------------------------------------------+
                Hide
                Permalink
                psergey Sergei Petrunia added a comment -

                Re: wrong query result in subselect_sj.test
                Currently, the testcase has correct results.

                Show
                psergey Sergei Petrunia added a comment - Re: wrong query result in subselect_sj.test Currently, the testcase has correct results.
                Hide
                Permalink
                ratzpo Rasmus Johansson added a comment -

                Launchpad bug id: 702374

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

                  People

                  • Assignee:
                    psergey Sergei Petrunia
                    Reporter:
                    psergey Sergei Petrunia
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    0 Start watching this issue

                    Dates

                    • Created:
                      Updated:
                      Resolved: