Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Wrong results with NOT BETWEEN + EXTRACT #31937

Open
espresso98 opened this issue Jan 24, 2022 · 2 comments
Open

Wrong results with NOT BETWEEN + EXTRACT #31937

espresso98 opened this issue Jan 24, 2022 · 2 comments
Labels
severity/moderate sig/execution SIG execution type/bug The issue is confirmed as a bug.

Comments

@espresso98
Copy link
Collaborator

Bug Report

This issues is related to tidb/issues/30359 and tidb/issues/31600.

1. Minimal reproduce step

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
  col_time_6_not_null_key TIME(6) NOT NULL,
  col_time_5_key TIME(5) DEFAULT NULL,
  col_timestamp TIMESTAMP DEFAULT NULL,
  col_time_key TIME  DEFAULT NULL,
  col_datetime_key DATETIME DEFAULT NULL,
  col_time_not_null TIME NOT NULL,
  pk TIMESTAMP(4) NOT NULL PRIMARY KEY
);
SET sql_mode=''; -- for zero date
INSERT INTO t1 VALUES
('03:07:29.013169',NULL,'0000-00-00 00:00:00','18:29:54','2000-06-10 19:41:30',
  '13:56:51','2011-09-07 23:08:20.8362'),('00:20:06.000000','08:25:11.04580',
  '2009-06-16 04:33:32','00:20:04','2004-08-23 21:05:10','00:20:06',
  '2011-09-07 23:08:22.8362'),('00:20:03.000000','00:20:07.00000',
  '2007-05-28 13:16:25','00:26:33','0000-00-00 00:00:00','01:04:48',
  '2011-09-07 23:08:24.8362'),('19:48:23.009935','00:20:07.00000',
  '2009-04-09 13:29:15','00:20:01',NULL,'21:43:24','2011-09-07 23:08:26.8362'),
('00:20:01.000000','22:27:30.00296','2009-03-21 23:00:46',NULL,'2003-01-23 14:57:31',
 '00:20:09','2011-09-07 23:08:28.8362'),('23:09:37.056340','07:01:38.05720',
 '2006-01-25 15:25:59','00:20:05',NULL,'05:10:32','2011-09-07 23:08:19.8362'),
('09:44:10.025082','00:20:07.00000','2009-01-06 18:48:29','00:20:02',
 '0000-00-00 00:00:00','00:20:00','2011-09-07 23:08:21.8362'),
('23:02:50.013380','22:48:12.05831',NULL,NULL,'2003-11-18 04:32:18','00:20:06',
 '2011-09-07 23:08:23.8362'),('00:20:01.000000','16:19:55.00007',
 '0000-00-00 00:00:00','07:55:21','0000-00-00 00:00:00','14:48:29',
 '2011-09-07 23:08:25.8362'),('06:00:36.034953','00:20:01.00000','2007-03-15 21:00:00',
 '00:20:01',NULL,'23:29:59','2011-09-07 23:08:27.8362');
SET sql_mode=DEFAULT;

SELECT
 pk,
 EXTRACT(YEAR_MONTH FROM '0000-00-00 00:00:00') as expr1,
 COALESCE (col_time_6_not_null_key, col_datetime_key) as expr2
FROM t1 
WHERE col_time_not_null
  NOT BETWEEN EXTRACT(YEAR_MONTH FROM '0000-00-00 00:00:00')
  AND COALESCE (col_time_6_not_null_key, col_datetime_key);
SHOW WARNINGS;

SELECT pk, col_time_not_null, col_time_6_not_null_key, col_datetime_key
FROM t1
WHERE col_time_not_null NOT BETWEEN NULL AND COALESCE (col_time_6_not_null_key, col_datetime_key);
DROP TABLE t1;

2. What did you expect to see?

In MySQL8.0

mysql> SELECT
    ->  pk,
    ->  EXTRACT(YEAR_MONTH FROM '0000-00-00 00:00:00') as expr1,
    ->  COALESCE (col_time_6_not_null_key, col_datetime_key) as expr2
    -> FROM t1 
    -> WHERE col_time_not_null
    ->   NOT BETWEEN EXTRACT(YEAR_MONTH FROM '0000-00-00 00:00:00')
    ->   AND COALESCE (col_time_6_not_null_key, col_datetime_key);
+--------------------------+-------+----------------------------+
| pk                       | expr1 | expr2                      |
+--------------------------+-------+----------------------------+
| 2011-09-07 23:08:20.8362 |  NULL | 2022-01-24 03:07:29.013169 |
| 2011-09-07 23:08:24.8362 |  NULL | 2022-01-24 00:20:03.000000 |
| 2011-09-07 23:08:25.8362 |  NULL | 2022-01-24 00:20:01.000000 |
| 2011-09-07 23:08:26.8362 |  NULL | 2022-01-24 19:48:23.009935 |
| 2011-09-07 23:08:27.8362 |  NULL | 2022-01-24 06:00:36.034953 |
| 2011-09-07 23:08:28.8362 |  NULL | 2022-01-24 00:20:01.000000 |
+--------------------------+-------+----------------------------+
6 rows in set, 6 warnings (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+-------------------------------------------------+
| Level   | Code | Message                                         |
+---------+------+-------------------------------------------------+
| Warning | 1292 | Incorrect datetime value: '0000-00-00 00:00:00' |
| Warning | 1292 | Incorrect datetime value: '0000-00-00 00:00:00' |
| Warning | 1292 | Incorrect datetime value: '0000-00-00 00:00:00' |
| Warning | 1292 | Incorrect datetime value: '0000-00-00 00:00:00' |
| Warning | 1292 | Incorrect datetime value: '0000-00-00 00:00:00' |
| Warning | 1292 | Incorrect datetime value: '0000-00-00 00:00:00' |
+---------+------+-------------------------------------------------+
6 rows in set (0.00 sec)

mysql> SELECT pk, col_time_not_null, col_time_6_not_null_key, col_datetime_key
    -> FROM t1
    -> WHERE col_time_not_null NOT BETWEEN NULL AND COALESCE (col_time_6_not_null_key, col_datetime_key);
+--------------------------+-------------------+-------------------------+---------------------+
| pk                       | col_time_not_null | col_time_6_not_null_key | col_datetime_key    |
+--------------------------+-------------------+-------------------------+---------------------+
| 2011-09-07 23:08:19.8362 | 05:10:32          | 23:09:37.056340         | NULL                |
| 2011-09-07 23:08:21.8362 | 00:20:00          | 09:44:10.025082         | 0000-00-00 00:00:00 |
| 2011-09-07 23:08:22.8362 | 00:20:06          | 00:20:06.000000         | 2004-08-23 21:05:10 |
| 2011-09-07 23:08:23.8362 | 00:20:06          | 23:02:50.013380         | 2003-11-18 04:32:18 |
+--------------------------+-------------------+-------------------------+---------------------+
4 rows in set (0.00 sec)

3. What did you see instead

tidb>  SELECT
    ->  pk,
    ->  EXTRACT(YEAR_MONTH FROM '0000-00-00 00:00:00') as expr1,
    ->  COALESCE (col_time_6_not_null_key, col_datetime_key) as expr2
    -> FROM t1 
    -> WHERE col_time_not_null
    ->   NOT BETWEEN EXTRACT(YEAR_MONTH FROM '0000-00-00 00:00:00')
    ->   AND COALESCE (col_time_6_not_null_key, col_datetime_key);
Empty set, 2 warnings (0.00 sec)

tidb>  SHOW WARNINGS;
+---------+------+--------------------------------------------------------+
| Level   | Code | Message                                                |
+---------+------+--------------------------------------------------------+
| Warning | 1292 | Incorrect datetime value: '0000-00-00 00:00:00.000000' |
| Warning | 1292 | Incorrect datetime value: '0000-00-00 00:00:00.000000' |
+---------+------+--------------------------------------------------------+
2 rows in set (0.00 sec)

tidb>  SELECT pk, col_time_not_null, col_time_6_not_null_key, col_datetime_key
    -> FROM t1
    -> WHERE col_time_not_null NOT BETWEEN NULL AND COALESCE (col_time_6_not_null_key, col_datetime_key);
+--------------------------+-------------------+-------------------------+---------------------+
| pk                       | col_time_not_null | col_time_6_not_null_key | col_datetime_key    |
+--------------------------+-------------------+-------------------------+---------------------+
| 2011-09-07 23:08:20.8362 | 13:56:51          | 03:07:29.013169         | 2000-06-10 19:41:30 |
| 2011-09-07 23:08:24.8362 | 01:04:48          | 00:20:03.000000         | 0000-00-00 00:00:00 |
| 2011-09-07 23:08:26.8362 | 21:43:24          | 19:48:23.009935         | NULL                |
| 2011-09-07 23:08:28.8362 | 00:20:09          | 00:20:01.000000         | 2003-01-23 14:57:31 |
| 2011-09-07 23:08:25.8362 | 14:48:29          | 00:20:01.000000         | 0000-00-00 00:00:00 |
| 2011-09-07 23:08:27.8362 | 23:29:59          | 06:00:36.034953         | NULL                |
+--------------------------+-------------------+-------------------------+---------------------+
6 rows in set (0.00 sec)

4. What is your TiDB version?

tidb_version(): Release Version: v5.5.0-alpha-154-gc589ee547
Edition: Community
Git Commit Hash: c589ee5471e05430f7f888190780a27fddb9ce7a
Git Branch: master
UTC Build Time: 2022-01-19 21:56:30
GoVersion: go1.17.2
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
@espresso98 espresso98 added the type/bug The issue is confirmed as a bug. label Jan 24, 2022
@morgo
Copy link
Contributor

morgo commented Jan 24, 2022 •
edited
Loading

It looks like there might be a bug in MySQL:

mysql [localhost:8028] {msandbox} (test) > SELECT pk, col_time_not_null, col_time_6_not_null_key, col_datetime_key FROM t1 WHERE col_time_not_null NOT BETWEEN NULL AND col_time_6_not_null_key;
+--------------------------+-------------------+-------------------------+---------------------+
| pk                       | col_time_not_null | col_time_6_not_null_key | col_datetime_key    |
+--------------------------+-------------------+-------------------------+---------------------+
| 2011-09-07 23:08:20.8362 | 13:56:51          | 03:07:29.013169         | 2000-06-10 19:41:30 |
| 2011-09-07 23:08:24.8362 | 01:04:48          | 00:20:03.000000         | 0000-00-00 00:00:00 |
| 2011-09-07 23:08:25.8362 | 14:48:29          | 00:20:01.000000         | 0000-00-00 00:00:00 |
| 2011-09-07 23:08:26.8362 | 21:43:24          | 19:48:23.009935         | NULL                |
| 2011-09-07 23:08:27.8362 | 23:29:59          | 06:00:36.034953         | NULL                |
| 2011-09-07 23:08:28.8362 | 00:20:09          | 00:20:01.000000         | 2003-01-23 14:57:31 |
+--------------------------+-------------------+-------------------------+---------------------+
6 rows in set (0.00 sec)

mysql [localhost:8028] {msandbox} (test) > SELECT pk, col_time_not_null, col_time_6_not_null_key, col_datetime_key FROM t1 WHERE col_time_not_null NOT BETWEEN NULL AND COALESCE (col_time_6_not_null_key, col_datetime_key);
+--------------------------+-------------------+-------------------------+---------------------+
| pk                       | col_time_not_null | col_time_6_not_null_key | col_datetime_key    |
+--------------------------+-------------------+-------------------------+---------------------+
| 2011-09-07 23:08:19.8362 | 05:10:32          | 23:09:37.056340         | NULL                |
| 2011-09-07 23:08:21.8362 | 00:20:00          | 09:44:10.025082         | 0000-00-00 00:00:00 |
| 2011-09-07 23:08:22.8362 | 00:20:06          | 00:20:06.000000         | 2004-08-23 21:05:10 |
| 2011-09-07 23:08:23.8362 | 00:20:06          | 23:02:50.013380         | 2003-11-18 04:32:18 |
+--------------------------+-------------------+-------------------------+---------------------+
4 rows in set (0.00 sec)

Because the first column in the COALESCE function is always not null, the two should return the same results.

Edit: Filed as https://bugs.mysql.com/bug.php?id=106267

@morgo morgo changed the title temp Wrong results with NOT NETWEEN + EXTRACT Jan 24, 2022
@morgo morgo changed the title Wrong results with NOT NETWEEN + EXTRACT Wrong results with NOT BETWEEN + EXTRACT Jan 24, 2022
@morgo
Copy link
Contributor

morgo commented Jan 24, 2022

There is both a bug in MySQL and a bug in TiDB. This is what I think is a bug in TiDB; the EXTRACT expression returns NULL, but if I substitute NULL into the results it returns differently:

tidb> SELECT  pk,  EXTRACT(YEAR_MONTH FROM '0000-00-00 00:00:00') as expr1,  COALESCE (col_time_6_not_null_key, col_datetime_key) as expr2 FROM t1  WHERE col_time_not_null   NOT BETWEEN NULL AND COALESCE (col_time_6_not_null_key, col_datetime_key);
+--------------------------+-------+----------------------------+
| pk                       | expr1 | expr2                      |
+--------------------------+-------+----------------------------+
| 2011-09-07 23:08:20.8362 |  NULL | 2022-01-24 03:07:29.013169 |
| 2011-09-07 23:08:24.8362 |  NULL | 2022-01-24 00:20:03.000000 |
| 2011-09-07 23:08:26.8362 |  NULL | 2022-01-24 19:48:23.009935 |
| 2011-09-07 23:08:28.8362 |  NULL | 2022-01-24 00:20:01.000000 |
| 2011-09-07 23:08:25.8362 |  NULL | 2022-01-24 00:20:01.000000 |
| 2011-09-07 23:08:27.8362 |  NULL | 2022-01-24 06:00:36.034953 |
+--------------------------+-------+----------------------------+
6 rows in set, 1 warning (0.00 sec)

tidb> SELECT  pk,  EXTRACT(YEAR_MONTH FROM '0000-00-00 00:00:00') as expr1,  COALESCE (col_time_6_not_null_key, col_datetime_key) as expr2 FROM t1  WHERE col_time_not_null   NOT BETWEEN EXTRACT(YEAR_MONTH FROM '0000-00-00 00:00:00')   AND COALESCE (col_time_6_not_null_key, col_datetime_key);
Empty set, 2 warnings (0.03 sec)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
severity/moderate sig/execution SIG execution type/bug The issue is confirmed as a bug.
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@morgo @espresso98 @seiya-annie