Duplicate results with semijoin=on and materialization=off

[Timour Katchaounov]

When executing the test case for mysql BUG#45191 in subselect_sj.test
with 'semijoin=on,materialization=off', the last two queries produce wrong
result with duplicate rows.

Test case copied from subselect_sj.test, the only difference is the explicit
optimizer_switch:

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);

set @@optimizer_switch='semijoin=on,materialization=off';

SELECT t0.int_key
FROM t0
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
);

/*
Produces:
+---------+
| int_key |
+---------+
| 9 |
| 9 |
| 7 |
| 7 |
+---------+

Should be:
+---------+
| int_key |
+---------+
| 9 |
| 7 |
+---------+

*/

[Sergey Petrunia (sergefp)]

EXPLAIN:

+--+-----------+-----+-----+-------------+-----------+-------+----+----+-----------------------------------------------------------------+
|id|select_type|table|type |possible_keys|key |key_len|ref |rows|Extra |
+--+-----------+-----+-----+-------------+-----------+-------+----+----+-----------------------------------------------------------------+
| 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) |
+--+-----------+-----+-----+-------------+-----------+-------+----+----+-----------------------------------------------------------------+
3 rows in set (0.00 sec)

[Sergey Petrunia (sergefp)]

Not repeatable anymore, mysql-test/r/subselect_sj{,_jcl6}.result have correct result of two records. Closing as Invalid.