[MDEV-30302] Value changed caused by DISTINCT and WITH [10.5.11,10.11.1] Created: 2022-12-25  Updated: 2023-11-28

Status: Confirmed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.5.11, 10.11.1, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10
Fix Version/s: 10.5, 10.6

Type: Bug Priority: Major
Reporter: qaqcatz Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: distinct-optimization
Environment:

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)



 Comments   
Comment by Alice Sherepa [ 2022-12-29 ]

Thank you!
I repeated on the current 10.5-10.11:

MariaDB [test]> explain extended SELECT * FROM (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)) dt; 
+------+--------------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
 
| id   | select_type        | table      | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
 
+------+--------------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
 
|    1 | PRIMARY            | <derived2> | ALL  | NULL          | NULL | NULL    | NULL | 2    |   100.00 |                 |
 
|    2 | DERIVED            | t          | ALL  | NULL          | NULL | NULL    | NULL | 1    |   100.00 | Using temporary |
 
|    4 | DEPENDENT SUBQUERY | t          | ALL  | NULL          | NULL | NULL    | NULL | 1    |   100.00 |                 |
 
+------+--------------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
 
3 rows in set, 7 warnings (0,001 sec)
 
 
 
Warning (Code 1292): Truncated incorrect datetime value: '1111111111111111111111111111111111111111111111111111111111111101'
 
Warning (Code 1292): Truncated incorrect datetime value: '1111111111111111111111111111111111111111111111111111111111111101'
 
Warning (Code 1292): Truncated incorrect datetime value: '1111111111111111111111111111111111111111111111111111111111111101'
 
Warning (Code 1292): Truncated incorrect datetime value: '1111111111111111111111111111111111111111111111111111111111111101'
 
Warning (Code 1292): Truncated incorrect datetime value: '1111111111111111111111111111111111111111111111111111111111111101'
 
Warning (Code 1292): Truncated incorrect DECIMAL value: '1111-11-18 11:11:11'
 
Note (Code 1003): /* select#1 */ select `dt`.`f1` AS `f1` from (/* select#2 */ select distinct cast(~coercibility(`test`.`t`.`c1`) as char charset binary) - unix_timestamp('2011-12-22 14:22:02') AS `f1` from `test`.`t` where 1) `dt`
 
MariaDB [test]>  SELECT * FROM (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)) dt; 
+--------------------+
 
| f1                 |
 
+--------------------+
 
| 100000000000000000 |
 
+--------------------+
 
1 row in set, 6 warnings (0,001 sec)
 
 
 
Warning (Code 1292): Truncated incorrect datetime value: '1111111111111111111111111111111111111111111111111111111111111101'
 
Warning (Code 1292): Truncated incorrect datetime value: '1111111111111111111111111111111111111111111111111111111111111101'
 
Warning (Code 1292): Truncated incorrect datetime value: '1111111111111111111111111111111111111111111111111111111111111101'
 
Warning (Code 1292): Truncated incorrect datetime value: '1111111111111111111111111111111111111111111111111111111111111101'
 
Warning (Code 1292): Truncated incorrect datetime value: '1111111111111111111111111111111111111111111111111111111111111101'
 
Warning (Code 1292): Truncated incorrect DECIMAL value: '1111-11-18 11:11:11'

Generated at Thu Feb 08 10:15:14 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.