Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.5.11, 10.11.1, 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL)
-
ubuntu 18.04
Description
Description:
In theory, the result of sql2(DISTINCT) ⊆ the result of sql1
WITH `MYWITH` AS (SELECT (BINARY f2-UNIX_TIMESTAMP('2011-12-22 14:22:02')) AS f1 FROM (SELECT (~COERCIBILITY(c1)) AS f2 FROM t) AS t1 WHERE (DATE_ADD(BIN(f2), INTERVAL 1 WEEK) NOT IN (SELECT 1 FROM t)) AND (f2 BETWEEN '1' AND f2)) SELECT * FROM `MYWITH`; -- sql1 |
WITH `MYWITH` AS (SELECT DISTINCT (BINARY f2-UNIX_TIMESTAMP('2011-12-22 14:22:02')) AS f1 FROM (SELECT (~COERCIBILITY(c1)) AS f2 FROM t) AS t1 WHERE (DATE_ADD(BIN(f2), INTERVAL 1 WEEK) NOT IN (SELECT 1 FROM t)) AND (f2 BETWEEN '1' AND f2)) SELECT * FROM `MYWITH`; -- sql2 |
However, the value `18446744072384987000` changed to `100000000000000000` after adding DISTINCT, seems like a logical bug:
mysql> select version(); |
+-----------------------------------------+ |
| version() |
|
+-----------------------------------------+ |
| 10.11.1-MariaDB-1:10.11.1+maria~ubu2204 |
|
+-----------------------------------------+ |
1 row in set (0.00 sec) |
|
|
mysql> WITH `MYWITH` AS (SELECT (BINARY f2-UNIX_TIMESTAMP('2011-12-22 14:22:02')) AS f1 FROM (SELECT (~COERCIBILITY(c1)) AS f2 FROM t) AS t1 WHERE (DATE_ADD(BIN(f2), INTERVAL 1 WEEK) NOT IN (SELECT 1 FROM t)) AND (f2 BETWEEN '1' AND f2)) SELECT * FROM `MYWITH`; -- sql1 |
+----------------------+ |
| f1 |
|
+----------------------+ |
| 18446744072384987000 |
|
+----------------------+ |
1 row in set, 6 warnings (0.00 sec) |
|
|
mysql> WITH `MYWITH` AS (SELECT DISTINCT (BINARY f2-UNIX_TIMESTAMP('2011-12-22 14:22:02')) AS f1 FROM (SELECT (~COERCIBILITY(c1)) AS f2 FROM t) AS t1 WHERE (DATE_ADD(BIN(f2), INTERVAL 1 WEEK) NOT IN (SELECT 1 FROM t)) AND (f2 BETWEEN '1' AND f2)) SELECT * FROM `MYWITH`; -- sql2 |
+--------------------+ |
| f1 |
|
+--------------------+ |
| 100000000000000000 |
|
+--------------------+ |
1 row in set, 6 warnings (0.00 sec) |
How to repeat:
drop table if exists t; |
CREATE TABLE t (c1 CHAR(20)); |
INSERT INTO t VALUES ('0'); |
|
|
WITH `MYWITH` AS (SELECT (BINARY f2-UNIX_TIMESTAMP('2011-12-22 14:22:02')) AS f1 FROM (SELECT (~COERCIBILITY(c1)) AS f2 FROM t) AS t1 WHERE (DATE_ADD(BIN(f2), INTERVAL 1 WEEK) NOT IN (SELECT 1 FROM t)) AND (f2 BETWEEN '1' AND f2)) SELECT * FROM `MYWITH`; -- sql1 |
WITH `MYWITH` AS (SELECT DISTINCT (BINARY f2-UNIX_TIMESTAMP('2011-12-22 14:22:02')) AS f1 FROM (SELECT (~COERCIBILITY(c1)) AS f2 FROM t) AS t1 WHERE (DATE_ADD(BIN(f2), INTERVAL 1 WEEK) NOT IN (SELECT 1 FROM t)) AND (f2 BETWEEN '1' AND f2)) SELECT * FROM `MYWITH`; -- sql2 |
Hope these can be helpful for your debugging:
1. The bug cannot be reproduced after removing WITH `MYWITH` AS ... SELECT * FROM `MYWITH`.
2. We look for the first version of the bug in all docker images (with format x.x.x) of https://hub.docker.com/_/mariadb/tags
We found that the bug first occurred in mariadb:10.5.11, it cannot be reproduced in mariadb:10.5.10:
MariaDB [TEST]> select version(); |
+---------------------------------------+ |
| version() |
|
+---------------------------------------+ |
| 10.5.11-MariaDB-1:10.5.11+maria~focal |
|
+---------------------------------------+ |
1 row in set (0.000 sec) |
|
|
MariaDB [TEST]> WITH `MYWITH` AS (SELECT (BINARY f2-UNIX_TIMESTAMP('2011-12-22 14:22:02')) AS f1 FROM (SELECT (~COERCIBILITY(c1)) AS f2 FROM t) AS t1 WHERE (DATE_ADD(BIN(f2), INTERVAL 1 WEEK) NOT IN (SELECT 1 FROM t)) AND (f2 BETWEEN '1' AND f2)) SELECT * FROM `MYWITH`; -- sql1 |
+----------------------+ |
| f1 |
|
+----------------------+ |
| 18446744072384987000 |
|
+----------------------+ |
1 row in set, 6 warnings (0.001 sec) |
|
|
MariaDB [TEST]> WITH `MYWITH` AS (SELECT DISTINCT (BINARY f2-UNIX_TIMESTAMP('2011-12-22 14:22:02')) AS f1 FROM (SELECT (~COERCIBILITY(c1)) AS f2 FROM t) AS t1 WHERE (DATE_ADD(BIN(f2), INTERVAL 1 WEEK) NOT IN (SELECT 1 FROM t)) AND (f2 BETWEEN '1' AND f2)) SELECT * FROM `MYWITH`; -- sql2 |
+--------------------+ |
| f1 |
|
+--------------------+ |
| 100000000000000000 |
|
+--------------------+ |
1 row in set, 6 warnings (0.001 sec) |
|
|
MariaDB [TEST]> select version(); |
+---------------------------------------+ |
| version() |
|
+---------------------------------------+ |
| 10.5.10-MariaDB-1:10.5.10+maria~focal |
|
+---------------------------------------+ |
1 row in set (0.000 sec) |
|
|
MariaDB [TEST]> WITH `MYWITH` AS (SELECT (BINARY f2-UNIX_TIMESTAMP('2011-12-22 14:22:02')) AS f1 FROM (SELECT (~COERCIBILITY(c1)) AS f2 FROM t) AS t1 WHERE (DATE_ADD(BIN(f2), INTERVAL 1 WEEK) NOT IN (SELECT 1 FROM t)) AND (f2 BETWEEN '1' AND f2)) SELECT * FROM `MYWITH`; -- sql1 |
Empty set, 4 warnings (0.001 sec) |
|
|
MariaDB [TEST]> WITH `MYWITH` AS (SELECT DISTINCT (BINARY f2-UNIX_TIMESTAMP('2011-12-22 14:22:02')) AS f1 FROM (SELECT (~COERCIBILITY(c1)) AS f2 FROM t) AS t1 WHERE (DATE_ADD(BIN(f2), INTERVAL 1 WEEK) NOT IN (SELECT 1 FROM t)) AND (f2 BETWEEN '1' AND f2)) SELECT * FROM `MYWITH`; -- sql2 |
Empty set, 4 warnings (0.001 sec) |