Bug #118090 Wrong inner join result with the subquery contain -0
Submitted: 29 Apr 17:33 Modified: 7 May 11:53
Reporter: Zhaokun Xiang Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:9.3 OS:MacOS
Assigned to: CPU Architecture:Any

[29 Apr 17:33] Zhaokun Xiang 
Description:
Hi, MySQL developers,

I perform the following cases which just contain simple schema and queries, and I meet some unexpected inner join result. Please see the below cases. This bug can only be reproduced on the 9.3 version.

```
CREATE TABLE t0(c0 INT);
CREATE TABLE t3(c0 INT);

INSERT INTO t0(c0) VALUES(1);
INSERT INTO t3(c0) VALUES(1);
INSERT INTO t3(c0) VALUES(1);

SELECT *
FROM t0 JOIN (SELECT (-(+ (''))) AS col0
                       FROM t3
              LIMIT 2) as subQuery1
ON true;
-- c0, col0
--  1, -0
--  1, -0

SELECT *
FROM t0 JOIN (SELECT (-(+ (''))) AS col0
                       FROM t3
              LIMIT 2) as subQuery1
ON true
where (!(subQuery1.col0));
-- c0, col0
-- 1, -0
-- wrong result, should be same as preceding query

# additionally, remove limit 2 will enable them to produce correct result
SELECT *
FROM t0 JOIN (SELECT (-(+ (''))) AS col0
              FROM t3) as subQuery1
             ON true;
-- c0, col0
--  1, -0
--  1, -0

SELECT *
FROM t0 JOIN (SELECT (-(+ (''))) AS col0
              FROM t3) as subQuery1
             ON true
where (!(subQuery1.col0));
-- c0, col0
--  1, -0
--  1, -0

```

How to repeat:
```
CREATE TABLE t0(c0 INT);
CREATE TABLE t3(c0 INT);

INSERT INTO t0(c0) VALUES(1);
INSERT INTO t3(c0) VALUES(1);
INSERT INTO t3(c0) VALUES(1);

SELECT *
FROM t0 JOIN (SELECT (-(+ (''))) AS col0
                       FROM t3
              LIMIT 2) as subQuery1
ON true;
-- c0, col0
--  1, -0
--  1, -0

SELECT *
FROM t0 JOIN (SELECT (-(+ (''))) AS col0
                       FROM t3
              LIMIT 2) as subQuery1
ON true
where (!(subQuery1.col0));
-- c0, col0
-- 1, -0
-- wrong result, should be same as preceding query

```
[30 Apr 7:03] MySQL Verification Team 
Hello Zhaokun Xiang,

Thank you for the report and feedback.
I quickly tried on 9.3.0 build but not seeing any issues i.e.  it is working as expected. Am I missing something here? Thank you.

-- Using the binary tarball build (mysql-9.3.0-linux-glibc2.17-x86_64.tar)

/export/home/tmp/ushastry/work/binaries/mysql-9.3.0/mysql-test]$ ./mtr --nocheck-testcases bug118090
Logging: ./mtr  --nocheck-testcases bug118090
MySQL Version 9.3.0
Checking supported features
Using 'all' suites
Collecting tests
Checking leftover processes
Removing old var directory
Creating var directory '/export/home/tmp/ushastry/work/binaries/mysql-9.3.0/mysql-test/var'
Installing system database
Using parallel: 1
ports_per_thread:30

==============================================================================
                  TEST NAME                       RESULT  TIME (ms) COMMENT
------------------------------------------------------------------------------
CREATE TABLE t0(c0 INT);
CREATE TABLE t3(c0 INT);
INSERT INTO t0(c0) VALUES(1);
INSERT INTO t3(c0) VALUES(1);
INSERT INTO t3(c0) VALUES(1);
SELECT *
FROM t0 JOIN (SELECT (-(+ (''))) AS col0
FROM t3
LIMIT 2) as subQuery1
ON true;
c0	col0
1	-0
1	-0
SELECT *
FROM t0 JOIN (SELECT (-(+ (''))) AS col0
FROM t3
LIMIT 2) as subQuery1
ON true
where (!(subQuery1.col0));
c0	col0
1	-0
1	-0
Warnings:
Warning	1287	'!' is deprecated and will be removed in a future release. Please use NOT instead
SELECT *
FROM t0 JOIN (SELECT (-(+ (''))) AS col0
FROM t3
LIMIT 2) as subQuery1
ON true
where (NOT (subQuery1.col0));
c0	col0
1	-0
1	-0
[ 50%] main.bug118090                            [ pass ]     60
[100%] shutdown_report                           [ pass ]

regards,
Umesh
[30 Apr 7:40] Zhaokun Xiang 
Actually, I use the mysql homebrew version. The version is Server version: 9.3.0 Homebrew. Please try:

brew install mysql

Additionally, I print the brew info mysql, and it is the result.
```
➜  ~ brew info mysql
==> mysql: stable 9.3.0 (bottled)
```
I have double checked it. In this homebrew version, this bug can be reproduced.
[30 Apr 8:03] MySQL Verification Team 
Thank you for the details, let me try and get back to you if anything needed.

regards,
Umesh
[30 Apr 10:22] Zhaokun Xiang 
Thanks, I’d appreciate it if you could quickly analyze it
[7 May 10:20] Zhaokun Xiang 
Dear MySQL developers,

If you have any difficulties in reproducing this bug, I can provide some help. I will appreciate your effort in analyzing and reproducing this bug.
[7 May 11:53] MySQL Verification Team 
Hello Zhaokun Xiang,

I installed MySQL Server 9.3.0 using brew but still not seeing the issue (even with binary tarball build).  Joining the details shortly.

-- 

umshastr@umshastr-mac ~ % mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 9.3.0 Homebrew

Copyright (c) 2000, 2025, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.005 sec)

mysql> use test
Database changed
mysql> CREATE TABLE t0(c0 INT);
Query OK, 0 rows affected (0.011 sec)

mysql> CREATE TABLE t3(c0 INT);
Query OK, 0 rows affected (0.007 sec)

mysql>
mysql> INSERT INTO t0(c0) VALUES(1);
Query OK, 1 row affected (0.006 sec)

mysql> INSERT INTO t3(c0) VALUES(1);
Query OK, 1 row affected (0.001 sec)

mysql> INSERT INTO t3(c0) VALUES(1);
Query OK, 1 row affected (0.002 sec)

mysql> SELECT *
    -> FROM t0 JOIN (SELECT (-(+ (''))) AS col0
    ->                        FROM t3
    ->               LIMIT 2) as subQuery1
    -> ON true;
+------+------+
| c0   | col0 |
+------+------+
|    1 |   -0 |
|    1 |   -0 |
+------+------+
2 rows in set (0.001 sec)

mysql> SELECT *
    -> FROM t0 JOIN (SELECT (-(+ (''))) AS col0
    ->                        FROM t3
    ->               LIMIT 2) as subQuery1
    -> ON true
    -> where (!(subQuery1.col0));
+------+------+
| c0   | col0 |
+------+------+
|    1 |   -0 |
|    1 |   -0 |
+------+------+
2 rows in set, 1 warning (0.001 sec)

mysql>

-----

--  No issues on Official build (using Linux - Generic (glibc 2.17)
(x86, 64-bit), TAR)

/export/home/tmp/ushastry/work/binaries/mysql-9.3.0]$ bin/mysql -uroot
-S/tmp/mysql.sock --local-infile
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 9.3.0 MySQL Community Server - GPL

Copyright (c) 2000, 2025, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input
statement.

mysql> create database test;
Query OK, 1 row affected (0.005 sec)

mysql> use test
Database changed
mysql> CREATE TABLE t0(c0 INT);
Query OK, 0 rows affected (0.014 sec)

mysql> CREATE TABLE t3(c0 INT);
Query OK, 0 rows affected (0.012 sec)

mysql>
mysql> INSERT INTO t0(c0) VALUES(1);
Query OK, 1 row affected (0.004 sec)

mysql> INSERT INTO t3(c0) VALUES(1);
Query OK, 1 row affected (0.002 sec)

mysql> INSERT INTO t3(c0) VALUES(1);
Query OK, 1 row affected (0.002 sec)

mysql>
mysql> SELECT *
    -> FROM t0 JOIN (SELECT (-(+ (''))) AS col0
    ->                        FROM t3
    ->               LIMIT 2) as subQuery1
    -> ON true;
+------+------+
| c0   | col0 |
+------+------+
|    1 |   -0 |
|    1 |   -0 |
+------+------+
2 rows in set (0.001 sec)

mysql> SELECT *
    -> FROM t0 JOIN (SELECT (-(+ (''))) AS col0
    ->                        FROM t3
    ->               LIMIT 2) as subQuery1
    -> ON true
    -> where (!(subQuery1.col0));
+------+------+
| c0   | col0 |
+------+------+
|    1 |   -0 |
|    1 |   -0 |
+------+------+
2 rows in set, 1 warning (0.001 sec)

regards,
Umesh
[7 May 11:55] MySQL Verification Team 
Test results

Attachment: 118090.results (application/octet-stream, text), 11.32 KiB.