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

dateAdd function bug #27573

Closed
mengxin9014 opened this issue Aug 25, 2021 · 4 comments · Fixed by #28133
Closed

dateAdd function bug #27573

mengxin9014 opened this issue Aug 25, 2021 · 4 comments · Fixed by #28133
Labels
affects-5.3 This bug affects 5.3.x versions. severity/major sig/execution SIG execution type/bug The issue is confirmed as a bug.

Comments

@mengxin9014
Copy link
Contributor

Bug Report

1. Minimal reproduce step (Required)

drop table t;
create table t(a char(32));
insert into t values('20129');
select a, DATE_ADD(a, INTERVAL 1 day ) from t ; 

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

+------------+------------------------------+
| a          | DATE_ADD(a, INTERVAL 1 day ) |
+------------+------------------------------+
| 20129      | 2020-12-10                   |
+------------+------------------------------+

3. What did you see instead (Required)

+------------+------------------------------+
| a          | DATE_ADD(a, INTERVAL 1 day ) |
+------------+------------------------------+
| 20129      | 2020-12-10 00:00:00          |
+------------+------------------------------+

4. What is your TiDB version? (Required)

v5.1.1

@mengxin9014 mengxin9014 added the type/bug The issue is confirmed as a bug. label Aug 25, 2021
@aytrack
Copy link
Contributor

aytrack commented Aug 25, 2021

The return value depends on the arguments:
DATE if the date argument is a DATE value and your calculations involve only YEAR, MONTH, and DAY parts (that is, no time parts).
DATETIME` if the first argument is a DATETIME (or TIMESTAMP) value, or if the first argument is a DATE and the unit value uses HOURS, MINUTES, or SECONDS.
String otherwise.

The returning type is wrong.

@aytrack
Copy link
Contributor

aytrack commented Aug 26, 2021

more cases, seems not only the type problem

mysql> select * from t where DATE_ADD(a, INTERVAL 1 day )  = 20201210;
Empty set (0.05 sec)

mysql> select * from t where DATE_ADD(a, INTERVAL 1 day )  = cast(20201210 as char);
+-------+
| a     |
+-------+
| 20129 |
+-------+
1 row in set (0.05 sec)

mysql> select * from t where DATE_ADD(a, INTERVAL 1 day )  = cast(20201210 as date);
+-------+
| a     |
+-------+
| 20129 |
+-------+
1 row in set (0.04 sec)

mysql> select DATE_ADD('20129', INTERVAL 1 day )  = 20201210;
+------------------------------------------------+
| DATE_ADD('20129', INTERVAL 1 day )  = 20201210 |
+------------------------------------------------+
|                                              0 |
+------------------------------------------------+
1 row in set (0.04 sec)

mysql> select DATE_ADD('201209', INTERVAL 1 day )  = 20201210;
+-------------------------------------------------+
| DATE_ADD('201209', INTERVAL 1 day )  = 20201210 |
+-------------------------------------------------+
|                                               1 |
+-------------------------------------------------+
1 row in set (0.04 sec)


mysql> select * from t where DATE_ADD(a, INTERVAL 1 day )  = '20201210';
+-------+
| a     |
+-------+
| 20129 |
+-------+
1 row in set (0.05 sec)

mysql> select * from t where DATE_ADD(a, INTERVAL 1 day )  = '2020-12-10';
+-------+
| a     |
+-------+
| 20129 |
+-------+
1 row in set (0.05 sec)



mysql> select * from t where DATE_ADD(a, INTERVAL 1 day )  = cast('2020-12-10' as date);
+-------+
| a     |
+-------+
| 20129 |
+-------+
1 row in set (0.04 sec)

mysql> select * from t where DATE_ADD(a, INTERVAL 1 day )  = cast('20201210' as date);
+-------+
| a     |
+-------+
| 20129 |
+-------+
1 row in set (0.04 sec)

@aytrack
Copy link
Contributor

aytrack commented Aug 26, 2021

degrade to major because most common usages was correct

@github-actions
Copy link

Please check whether the issue should be labeled with 'affects-x.y' or 'fixes-x.y.z', and then remove 'needs-more-info' label.

rebelice pushed a commit to TiInterstellar/tidb that referenced this issue Jan 5, 2022
* topsql: make topsql enable only be controlled by pub/sub sink (pingcap#31209)

* ddl: support batch create table  (pingcap#28763)

* executor: fix data race in IndexMergeReaderExec (pingcap#31230)

close pingcap#31229

* server: filter the EOF error for normal closed at handshake  (pingcap#31081)

close pingcap#31063

* expression: change date add function return type (pingcap#28133)

close pingcap#27573

* support create interval partition

Signed-off-by: crazycs520 <crazycs520@gmail.com>

* support create interval partition (support int/timestamp partition key)

Signed-off-by: crazycs520 <crazycs520@gmail.com>

* parser: support alter table partitions move engine statement

Signed-off-by: crazycs520 <crazycs520@gmail.com>

* support ddl operation

Signed-off-by: crazycs520 <crazycs520@gmail.com>

* support interval partition manager

Signed-off-by: crazycs520 <crazycs520@gmail.com>

* support interval partition manager handle job framwork

Signed-off-by: crazycs520 <crazycs520@gmail.com>

* support auto create interval partition when insert meet no partition suitable error

Signed-off-by: crazycs520 <crazycs520@gmail.com>

* fix bug

Signed-off-by: crazycs520 <crazycs520@gmail.com>

* fix cancel job and load old job then continue to do

Signed-off-by: crazycs520 <crazycs520@gmail.com>

* make partition readonly work(not allow to insert/update/delete)

Signed-off-by: crazycs520 <crazycs520@gmail.com>

* add begin,end time in tables

Signed-off-by: crazycs520 <crazycs520@gmail.com>

* tiny fix for auto create interval partition in concurrent case

Signed-off-by: crazycs520 <crazycs520@gmail.com>

* init

Signed-off-by: crazycs520 <crazycs520@gmail.com>

* init

Signed-off-by: crazycs520 <crazycs520@gmail.com>

* todo: remove flag

Signed-off-by: crazycs520 <crazycs520@gmail.com>

* fix dumpling

Signed-off-by: crazycs520 <crazycs520@gmail.com>

* remove data in aws s3 when drop/truncate table/partition

Signed-off-by: crazycs520 <crazycs520@gmail.com>

* make hello world work

Signed-off-by: crazycs520 <crazycs520@gmail.com>

* remove debug info

Signed-off-by: crazycs520 <crazycs520@gmail.com>

Co-authored-by: xhe <xw897002528@gmail.com>
Co-authored-by: guo-shaoge <shaoge1994@163.com>
Co-authored-by: knull-cn <hu__haifeng@163.com>
Co-authored-by: Meng Xin <tregoldmeng@gmail.com>
@Defined2014 Defined2014 added the affects-5.3 This bug affects 5.3.x versions. label Nov 4, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
affects-5.3 This bug affects 5.3.x versions. severity/major sig/execution SIG execution type/bug The issue is confirmed as a bug.
Projects
None yet
3 participants