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.

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

incorrect tableDual plan caused by comparing year with big value #50235

Closed
wjhuang2016 opened this issue Jan 9, 2024 · 2 comments · Fixed by #53395
Closed

incorrect tableDual plan caused by comparing year with big value #50235

wjhuang2016 opened this issue Jan 9, 2024 · 2 comments · Fixed by #53395

Comments

@wjhuang2016
Copy link
Member

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE `tlff7fd271` (
  `col_45` bit(3) NOT NULL DEFAULT b'101',
  `col_46` year(4) NOT NULL DEFAULT '2016',
  `col_47` double NOT NULL DEFAULT '2508.393214016021',
  `col_48` timestamp NOT NULL DEFAULT '2025-11-27 00:00:00',
  KEY `idx_15` (`col_45`,`col_47`),
  PRIMARY KEY (`col_46`,`col_45`,`col_48`) /*T![clustered_index] NONCLUSTERED */,
  KEY `idx_17` (`col_45`,`col_46`,`col_47`),
  UNIQUE KEY `idx_18` (`col_45`,`col_48`,`col_46`)
);
desc SELECT `tlff7fd271`.`col_46` AS `r0` FROM `tlff7fd271` where `col_46` <= 16212511333665770580;

2. What did you expect to see? (Required)

TableScan or IndexScan

3. What did you see instead (Required)

mysql> desc SELECT `tlff7fd271`.`col_46` AS `r0` FROM `tlff7fd271` where `col_46` <= 16212511333665770580;
+-------------+---------+------+---------------+---------------+
| id          | estRows | task | access object | operator info |
+-------------+---------+------+---------------+---------------+
| TableDual_5 | 0.00    | root |               | rows:0        |
+-------------+---------+------+---------------+---------------+
1 row in set (0.00 sec)

4. What is your TiDB version? (Required)

182efd8

@kennedy8312
Copy link

Seeing the same issue on v5.4.0 as well.

@qw4990
Copy link
Contributor

qw4990 commented May 20, 2024

The minimum reproducible case:

mysql> create table tt (c year(4) NOT NULL DEFAULT '2016', primary key(c));
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show warnings;
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                   |
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1681 | The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column. |
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from tt where c < 16212511333665770580;
Empty set (0.00 sec)

mysql> explain select * from tt where c < 16212511333665770580;
+-------------+---------+------+---------------+---------------+
| id          | estRows | task | access object | operator info |
+-------------+---------+------+---------------+---------------+
| TableDual_5 | 0.00    | root |               | rows:0        |
+-------------+---------+------+---------------+---------------+
1 row in set (0.00 sec)

mysql> insert into tt values (2016);
Query OK, 1 row affected (0.00 sec)

mysql>  select * from tt where c < 16212511333665770580;
Empty set (0.00 sec)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment