MySQL Bugs: #114373: Incorrect result after transformation from "IN (SELECT)" to "materialization"

Bug #114373	Incorrect result after transformation from "IN (SELECT)" to "materialization"
Submitted:	15 Mar 16:32	Modified:	20 Mar 2:33
Reporter:	John Jove	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I have two cases 1 and 2, in which case 1 and case 2 are designed to return the same result. However, the case 2 return a different result.

I investigate the optimization used in evaluating the query in case 1 and case 2.  Case 2 did a transformation from "IN (SELECT)" to "materialization", while case 1 not. I will attach the obtain optimization trace for case 1 and case2.

How to repeat:
I connect to the server through the JDBC, and start two connection to execute the case 1 and case 2, separately.
Since the optimization is cost-based, you may have to repeat the above process.

-- case 1
DROP DATABASE IF EXISTS database1;
CREATE DATABASE database1;
USE database1;
CREATE TABLE IF NOT EXISTS t1 (c1 DOUBLE);
CREATE UNIQUE INDEX i0 USING BTREE ON t1(c1);
INSERT INTO t1 (c1) VALUES (-5);
INSERT INTO t1 (c1) VALUES (103);
INSERT INTO t1 (c1) VALUES (-2.8174631679674573E307);
SELECT ca4 AS ca1 FROM (SELECT c1 AS ca4 FROM t1) AS ta1 WHERE (ca4 >= SOME (SELECT c1 FROM t1)) NOT IN (SELECT ca12 AS ca11 FROM (SELECT c1 AS ca12 FROM t1) AS ta3 WHERE ((- ca12) ^ ca12) BETWEEN 1 AND 2); -- 3

-- case 2
DROP DATABASE IF EXISTS database2;
CREATE DATABASE database2;
USE database2;
CREATE TABLE `t1` (  `c1` double DEFAULT NULL,  UNIQUE KEY `i0` (`c1`) USING BTREE);
INSERT INTO t1 (c1) VALUES (-5);
INSERT INTO t1 (c1) VALUES (103);
INSERT INTO t1 (c1) VALUES (-2.8174631679674573E307);
SELECT ca4 AS ca1 FROM (SELECT c1 AS ca4 FROM t1) AS ta1 WHERE (ca4 >= SOME (SELECT c1 FROM t1)) NOT IN (SELECT ca12 AS ca11 FROM (SELECT c1 AS ca12 FROM t1) AS ta3 WHERE ((- ca12) ^ ca12) BETWEEN 1 AND 2); -- 0

Moreover, after removing the index, an error was returned, in which the message is "ERROR 1690 (22003): BIGINT value is out of range in '-(`database1`.`t1`.`c1`)'".

Hi Mr. Jove,

Thank you for your bug report.

However, this is not a code bug.

You made two mistakes in your queries.

First of all, DOUBLE numeric type can use only 15.5 significant digits and not 16. If you remove one digit in your fractional part, both queries return the same result. 

Next, bitwise XOR operator can be used only for integer and string types (if those are convertible to integers). hence, this is not a bug either.

However, this is not documented at all.  Hence, this is a documentation bug.

Hence, we used "SHOW WARNINGS" after each operation and no warning have been issued on the too long DOUBLE value, nor on the usage of bitwise XOR operation on the DOUBLE columns.

Hence, this is first of all a documentation bug, but also a minor code bug.

Verified for 8.0 and higher.

Thank you for the detailed explanation.

Hi,

You are truly welcome.