bug: the content of the result sets mismatch

[hanyisong]

Search before asking

• I had searched in the issues and found no similar issues.

Version

8.0.26-v0.8.23-nightly-cbaedce-simd(rust-1.64.0-nightly-2022-09-02T16:39:41.032000394Z)

What's Wrong?

the content of the result sets mismatch

How to Reproduce?

DROP DATABASE IF EXISTS databend4;
CREATE DATABASE databend4;
USE databend4;
CREATE TABLE t0(c0FLOAT FLOAT NULL DEFAULT(0.3604859709739685), c1BOOLEAN BOOL NOT NULL);
INSERT INTO t0(c0float, c1boolean) VALUES (0.8634665608406067, false);
INSERT INTO t0(c0float, c1boolean) VALUES (0.4523228406906128, true);
INSERT INTO t0(c0float) VALUES (3.40282347e+38), (0.30710574984550476);
INSERT INTO t0(c1boolean) VALUES (false), (false);
INSERT INTO t0(c0float, c1boolean) VALUES (0.313891738653183, true), (0.9063047766685486, false);

MySQL [databend4]> SELECT DISTINCT t0.c1boolean, t0.c0float FROM t0;
+-----------+--------------+
| c1boolean | c0float      |
+-----------+--------------+
|         0 |    0.9063048 |
|         1 |   0.45232284 |
|         0 | 3.4028235e38 |
|         0 |   0.30710575 |
|         1 |   0.31389174 |
|         0 |   0.86346656 |
|         0 |   0.36048597 |
+-----------+--------------+
7 rows in set (0.008 sec)

MySQL [databend4]> SELECT t0.c1boolean, t0.c0float FROM t0 WHERE NULL UNION SELECT t0.c1boolean, t0.c0float FROM t0 WHERE (NOT NULL) UNION SELECT t0.c1boolean, t0.c0float FROM t0 WHERE ((NULL) IS NULL);
+-----------+---------------+
| c1boolean | c0float       |
+-----------+---------------+
|         1 |  5.819015e-39 |
|         1 |  5.822949e-39 |
|         1 | 1.1709024e-38 |
|         1 | 3.5063643e-38 |
|         1 |  5.750206e-39 |
|         1 | 3.5012793e-38 |
|         1 |  5.760011e-39 |
+-----------+---------------+
7 rows in set (0.011 sec)

Are you willing to submit PR?

• Yes I am willing to submit a PR!

[hanyisong]

@BohuTANG @sundy-li

[sundy-li]

Union schema mismatch, cc @xudong963

[xudong963]

The issue was fixed in main branch

mysql> SELECT t0.c1boolean, t0.c0float FROM t0 WHERE NULL UNION SELECT t0.c1boolean, t0.c0float FROM t0 WHERE (NOT NULL) UNION SELECT t0.c1boolean, t0.c0float FROM t0 WHERE ((NULL) IS NULL);
+-----------+--------------+
| c1boolean | c0float      |
+-----------+--------------+
|         0 |   0.36048597 |
|         1 |   0.45232284 |
|         0 |    0.9063048 |
|         0 |   0.30710575 |
|         0 | 3.4028235e38 |
|         1 |   0.31389174 |
|         0 |   0.86346656 |
+-----------+--------------+
7 rows in set (0.06 sec)
Read 8 rows, 42.00 B in 0.016 sec., 486.22 rows/sec., 2.49 KiB/sec.

mysql> SELECT DISTINCT t0.c1boolean, t0.c0float FROM t0;
+-----------+--------------+
| c1boolean | c0float      |
+-----------+--------------+
|         0 |   0.36048597 |
|         1 |   0.45232284 |
|         0 |    0.9063048 |
|         0 |   0.30710575 |
|         0 | 3.4028235e38 |
|         1 |   0.31389174 |
|         0 |   0.86346656 |
+-----------+--------------+
7 rows in set (0.04 sec)
Read 8 rows, 42.00 B in 0.008 sec., 1.02 thousand rows/sec., 5.23 KiB/sec.

mysql>

[xudong963]

https://github.com/sqlancer/sqlancer/pull/568/files#r985058302 cc @hanyisong