Bug #93418 SELECT with IN clause get wrong result set

• Submitted: 30 Nov 2018 1:07
• Modified: 30 Nov 2018 4:58
• Reporter: Song Libing
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.7, 5.7.24
• OS: Any
• CPU Architecture: Any

[30 Nov 2018 1:07] Song Libing

Description:
below select generates wrong result set.
===
SELECT a.* FROM (SELECT f1() AS c1, c2 FROM t1) AS a
  WHERE c1 IN (SELECT c1 FROM t2);

look at the explain of it:
1  SIMPLE  t2  FirstMatch
1  SIMPLE  t1  Using where; Using join buffer (Block Nested Loop)

the subquery is optimized by using semijoin and t2 will be the first table with FirstMatch. As I understand, FirstMatch cannot be used on first table.

The full test case is in repeat section.

How to repeat:
CREATE TABLE t1(c1 VARCHAR(10), c2 INT);
INSERT INTO t1 VALUES('value2',2), ('value1',1);

CREATE TABLE t2(c1 VARCHAR(10));

# value2 is before values
INSERT INTO t2 VALUES('value2');
INSERT INTO t2 VALUES('value1');
SELECT * FROM t2;

--delimiter |
CREATE FUNCTION f1()
RETURNS VARCHAR(10)
BEGIN
  RETURN 'value1';
end |
--delimiter ;

select @@optimizer_switch;
#set optimizer_switch='firstmatch=off';
SELECT a.* FROM (SELECT f1() AS c1, c2 FROM t1) AS a
  WHERE c1 IN (SELECT c1 FROM t2);

EXPLAIN
  SELECT a.* FROM (SELECT f1() AS c1, c2 FROM t1) AS a
    WHERE c1 IN (SELECT c1 FROM t2);

SELECT a.* FROM (SELECT c1, c2 FROM t1) AS a
  WHERE c1 IN (SELECT c1 FROM t2);

EXPLAIN
  SELECT a.* FROM (SELECT c1, c2 FROM t1) AS a
    WHERE c1 IN (SELECT c1 FROM t2);

DELETE FROM t2;
# 'value1' is before 'value2'
INSERT INTO t2 VALUES('value1');
INSERT INTO t2 VALUES('value2');
SELECT * FROM t2;

SELECT a.* FROM (SELECT f1() AS c1, c2 FROM t1) AS a
  WHERE c1 IN (SELECT c1 FROM t2);

EXPLAIN
  SELECT a.* FROM (SELECT f1() AS c1, c2 FROM t1) AS a
    WHERE c1 IN (SELECT c1 FROM t2);

[30 Nov 2018 4:58] MySQL Verification Team

Hello Libing,

Thank you for the report and test case.

regards,
Umesh