MySQL Bugs: #47052: wrong result, Subquery with VIEW containing BETWEEN

Bug #47052	wrong result, Subquery with VIEW containing BETWEEN
Submitted:	1 Sep 2009 19:22	Modified:	7 Dec 2009 14:40
Reporter:	Matthias Leich	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	6.0	OS:	Any
Assigned to:	Tor Didriksen	CPU Architecture:	Any
Tags:	optimizer_switch, semijoin, subquery

Description:
Problem found when running RQG tests:
-------------------------------------

--source include/master-slave.inc

--disable_warnings
DROP TABLE IF EXISTS t0, t1, t2;
DROP VIEW  IF EXISTS v1, v2;
--enable_warnings

CREATE TABLE t0 (f1 INTEGER, PRIMARY KEY (f1)); INSERT INTO t0 VALUES (1);
CREATE TABLE t1 LIKE t0;                        INSERT INTO t1 VALUES (1);
CREATE TABLE t2 LIKE t0;                        INSERT INTO t2 VALUES (1);

CREATE VIEW v1 AS SELECT * FROM t1 WHERE f1 BETWEEN 8 AND 0;
CREATE VIEW v2 AS SELECT * FROM t2 WHERE f1 BETWEEN 8 AND 0;
# Both VIEWs "are" empty, which is correct.
SELECT * FROM v1;

# Why do I get
#    ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
# here? The view is "empty" = nothing has to be inserted!
--error ER_DUP_ENTRY
INSERT INTO v1 SELECT * FROM t0 WHERE f1 IN ( SELECT f1 FROM v1 );
# I get the same wrong effect if I do not use "v1" two times.
--error ER_DUP_ENTRY
INSERT INTO v1 SELECT * FROM t0 WHERE f1 IN ( SELECT f1 FROM v2 );
# Fortunately the current content of v1 is correct (empty).
SELECT * FROM v1;
# Replication stays easy in sync.
--sync_slave_with_master

PREPARE st1 FROM "INSERT INTO v2 SELECT * FROM t0 WHERE f1 IN ( SELECT f1 FROM v2 )";
# Here the server response is ok.
EXECUTE st1;
# The current content of v2 is correct (empty).
SELECT * FROM v2;
# Here the replication breaks with "slave SQL thread failure" which
# is not surprising.
# The prepared statement stuff was replaced by a non prepared statement
# which gets the wrong response by the server.
--sync_slave_with_master

DROP TABLE t0, t1, t2;
DROP VIEW v1, v2;

This bug (the problem with the INSERT) occurs in
mysql-next-bugfixing (5.4) only.
The test itself fails on the final "--sync_slave_with_master"
in 5.0, 5.1 and 5.4. But I am no replication expert and
so it could be that I used wrong server options or similar.

My environment:
- mysql-next-bugfixing 2009-08-31
  mysql-5.1-bugteam 2009-08-28
  mysql-5.0-bugteam 2009-08-28
- ./BUILD/compile-pentium64-debug-max
- Linux OpenSuSE 11.0 (64 Bit)

How to repeat:
See above

Thank you for the report.

Verified as described.

The optimized test:
-------------------
DROP TABLE IF EXISTS t1;
DROP VIEW  IF EXISTS v1;
--enable_warnings
CREATE TABLE t1 (f1 INTEGER, PRIMARY KEY (f1)); INSERT INTO t1 VALUES (1);
CREATE VIEW v1 AS SELECT f1 FROM t1 WHERE f1 BETWEEN 8 AND 0;

--echo # Variant 1: Direct content of future SUBQUERY
let $empty_result1= SELECT f1 FROM v1;
eval $empty_result1;
--echo # Variant 2: Content of SUBQUERY with VIEW statement expanded
let $empty_result2= SELECT f1 FROM ( SELECT f1 FROM t1 WHERE f1 BETWEEN 8 AND 0 ) AS v1;
eval $empty_result2;
--echo # Variant 3: Content of SUBQUERY with VIEW statement expanded and some manual optimization
let $empty_result3= SELECT f1 FROM t1 AS v1 WHERE f1 BETWEEN 8 AND 0;
eval $empty_result3;

--echo # Use the variants from above as Subquery. We must get in all cases an empty result.
eval SELECT f1 FROM t1 WHERE f1 IN ( $empty_result1 );
eval SELECT f1 FROM t1 WHERE f1 IN ( $empty_result2 );
eval SELECT f1 FROM t1 WHERE f1 IN ( $empty_result3 );

--echo # So Variant 2 gives a wrong result, when running in non prepared statement mode.
--echo # Let's check what happens in case of prepared statement.
eval PREPARE st1 FROM "SELECT f1 FROM t1 WHERE f1 IN ( $empty_result2 )";
EXECUTE st1;

--echo # Cleanup
DROP TABLE t1;
DROP VIEW v1;

Protocol of the optimized test:
-------------------------------
CREATE TABLE t1 (f1 INTEGER, PRIMARY KEY (f1));
INSERT INTO t1 VALUES (1);
CREATE VIEW v1 AS SELECT f1 FROM t1 WHERE f1 BETWEEN 8 AND 0;
# Variant 1: Direct content of future SUBQUERY
SELECT f1 FROM v1;
f1
# Variant 2: Content of SUBQUERY with VIEW statement expanded
SELECT f1 FROM ( SELECT f1 FROM t1 WHERE f1 BETWEEN 8 AND 0 ) AS v1;
f1
# Variant 3: Content of SUBQUERY with VIEW statement expanded and some manual optimization
SELECT f1 FROM t1 AS v1 WHERE f1 BETWEEN 8 AND 0;
f1
# Use the variants from above as Subquery. We must get in all cases an empty result.
SELECT f1 FROM t1 WHERE f1 IN ( SELECT f1 FROM v1 );
f1
1        <--- THIS IS WRONG
SELECT f1 FROM t1 WHERE f1 IN ( SELECT f1 FROM ( SELECT f1 FROM t1 WHERE f1 BETWEEN 8 AND 0 ) AS v1 );
f1
SELECT f1 FROM t1 WHERE f1 IN ( SELECT f1 FROM t1 AS v1 WHERE f1 BETWEEN 8 AND 0 );
f1
# So Variant 2 gives a wrong result, when running in non prepared statement mode.
# Let's check what happens in case of prepared statement.
PREPARE st1 FROM "SELECT f1 FROM t1 WHERE f1 IN ( SELECT f1 FROM ( SELECT f1 FROM t1 WHERE f1 BETWEEN 8 AND 0 ) AS v1 )";
EXECUTE st1;
f1

With the following optimizer switch setting:

set @@optimizer_switch='default,semijoin=off';

the INSERT query inserts zero rows into view v1, as it should.

Hence marking this bug with 'semijoin'.

I have not tested this with replication turned on, though,

This is the same as 
Bug#49097 subquery with view generates wrong result with non-prepared statement 

(which is awaiting review)