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

addition between datetime and interval is not compatible with Mysql #9727

Closed
qw4990 opened this issue Mar 14, 2019 · 2 comments · Fixed by #10329
Closed

addition between datetime and interval is not compatible with Mysql #9727

qw4990 opened this issue Mar 14, 2019 · 2 comments · Fixed by #10329
Labels
component/expression good first issue Denotes an issue ready for a new contributor, according to the "help wanted" guidelines. help wanted Denotes an issue that needs help from a contributor. Must meet "help wanted" guidelines. type/bug The issue is confirmed as a bug.

Comments

@qw4990
Copy link
Contributor

qw4990 commented Mar 14, 2019

Bug Report

Please answer these questions before submitting your issue. Thanks!

  1. What did you do?
    If possible, provide a recipe for reproducing the error.
SELECT "1900-01-01 00:00:00" + INTERVAL "100000000:214748364700" MINUTE_SECOND;
SELECT "1900-01-01 00:00:00" + INTERVAL 1<<37 SECOND;
SELECT "1900-01-01 00:00:00" + INTERVAL 1<<31 MINUTE;
SELECT "1900-01-01 00:00:00" + INTERVAL 1<<38 SECOND;
SELECT "1900-01-01 00:00:00" + INTERVAL 1<<33 MINUTE;
SELECT "1900-01-01 00:00:00" + INTERVAL 1<<30 HOUR;
SELECT "1900-01-01 00:00:00" + INTERVAL "1000000000:214748364700" MINUTE_SECOND;
  1. What did you expect to see?
    In Mysql
mysql> SELECT "1900-01-01 00:00:00" + INTERVAL "100000000:214748364700" MINUTE_SECOND;
+-------------------------------------------------------------------------+
| "1900-01-01 00:00:00" + INTERVAL "100000000:214748364700" MINUTE_SECOND |
+-------------------------------------------------------------------------+
| 8895-03-27 22:11:40                                                     |
+-------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT "1900-01-01 00:00:00" + INTERVAL 1<<37 SECOND;
+-----------------------------------------------+
| "1900-01-01 00:00:00" + INTERVAL 1<<37 SECOND |
+-----------------------------------------------+
| 6255-04-08 15:04:32                           |
+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT "1900-01-01 00:00:00" + INTERVAL 1<<31 MINUTE;
+-----------------------------------------------+
| "1900-01-01 00:00:00" + INTERVAL 1<<31 MINUTE |
+-----------------------------------------------+
| 5983-01-24 02:08:00                           |
+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT "1900-01-01 00:00:00" + INTERVAL 1<<38 SECOND;
+-----------------------------------------------+
| "1900-01-01 00:00:00" + INTERVAL 1<<38 SECOND |
+-----------------------------------------------+
| NULL                                          |
+-----------------------------------------------+
1 row in set, 1 warning (0.01 sec)

mysql> SELECT "1900-01-01 00:00:00" + INTERVAL 1<<33 MINUTE;
+-----------------------------------------------+
| "1900-01-01 00:00:00" + INTERVAL 1<<33 MINUTE |
+-----------------------------------------------+
| NULL                                          |
+-----------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT "1900-01-01 00:00:00" + INTERVAL 1<<30 HOUR;
+---------------------------------------------+
| "1900-01-01 00:00:00" + INTERVAL 1<<30 HOUR |
+---------------------------------------------+
| NULL                                        |
+---------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT "1900-01-01 00:00:00" + INTERVAL "1000000000:214748364700" MINUTE_SECOND;
+--------------------------------------------------------------------------+
| "1900-01-01 00:00:00" + INTERVAL "1000000000:214748364700" MINUTE_SECOND |
+--------------------------------------------------------------------------+
| NULL                                                                     |
+--------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
  1. What did you see instead?
    In TiDB
mysql> SELECT "1900-01-01 00:00:00" + INTERVAL "100000000:214748364700" MINUTE_SECOND;
+-------------------------------------------------------------------------+
| "1900-01-01 00:00:00" + INTERVAL "100000000:214748364700" MINUTE_SECOND |
+-------------------------------------------------------------------------+
| 1607-09-22 00:12:43.145224                                              |
+-------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT "1900-01-01 00:00:00" + INTERVAL 1<<37 SECOND;
+-----------------------------------------------+
| "1900-01-01 00:00:00" + INTERVAL 1<<37 SECOND |
+-----------------------------------------------+
| 1607-09-22 00:12:43.145224                    |
+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT "1900-01-01 00:00:00" + INTERVAL 1<<31 MINUTE;
+-----------------------------------------------+
| "1900-01-01 00:00:00" + INTERVAL 1<<31 MINUTE |
+-----------------------------------------------+
| 1891-03-09 05:06:04.033139                    |
+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT "1900-01-01 00:00:00" + INTERVAL 1<<38 SECOND;
+-----------------------------------------------+
| "1900-01-01 00:00:00" + INTERVAL 1<<38 SECOND |
+-----------------------------------------------+
| 1607-09-22 00:12:43.145224                    |
+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT "1900-01-01 00:00:00" + INTERVAL 1<<33 MINUTE;
+-----------------------------------------------+
| "1900-01-01 00:00:00" + INTERVAL 1<<33 MINUTE |
+-----------------------------------------------+
| 1864-09-26 20:24:16.132555                    |
+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT "1900-01-01 00:00:00" + INTERVAL 1<<30 HOUR;
+---------------------------------------------+
| "1900-01-01 00:00:00" + INTERVAL 1<<30 HOUR |
+---------------------------------------------+
| 1635-07-15 09:02:00.994161                  |
+---------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT "1900-01-01 00:00:00" + INTERVAL "1000000000:214748364700" MINUTE_SECOND;
+--------------------------------------------------------------------------+
| "1900-01-01 00:00:00" + INTERVAL "1000000000:214748364700" MINUTE_SECOND |
+--------------------------------------------------------------------------+
| 1607-09-22 00:12:43.145224                                               |
+--------------------------------------------------------------------------+
1 row in set (0.00 sec)
  1. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                                                     |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v3.0.0-beta-212-g0681b8125
Git Commit Hash: 0681b8125080045ee0d4747ce61923164c6f2dd1
Git Branch: master
UTC Build Time: 2019-03-14 04:39:19
GoVersion: go version go1.12 darwin/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
@qw4990 qw4990 mentioned this issue Mar 14, 2019
Closed
30 tasks
@qw4990 qw4990 added type/bug The issue is confirmed as a bug. help wanted Denotes an issue that needs help from a contributor. Must meet "help wanted" guidelines. good first issue Denotes an issue ready for a new contributor, according to the "help wanted" guidelines. component/expression labels Mar 14, 2019
@erjiaqing
Copy link
Contributor

This is because

tidb/expression/builtin_time.go

Line 2717 in 29e9a41

duration := time.Duration(dur)

However, in golang

A Duration represents the elapsed time between two instants as an int64 nanosecond count. The representation limits the largest representable duration to approximately 290 years.

1 << 37 second = 137438953472 second ~= 4355 years.

@erjiaqing
Copy link
Contributor

Also

SELECT "1900-01-01 00:00:00" + INTERVAL 1.123456789e4 SECOND;

expected:

1900-01-01 03:07:14.567890

but get

1900-01-01 03:07:15

@erjiaqing erjiaqing mentioned this issue Apr 30, 2019
Merged
@qw4990 qw4990 mentioned this issue May 7, 2019
Merged
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
component/expression good first issue Denotes an issue ready for a new contributor, according to the "help wanted" guidelines. help wanted Denotes an issue that needs help from a contributor. Must meet "help wanted" guidelines. type/bug The issue is confirmed as a bug.
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

expression: addition between datetime and interval is not compatible with Mysql erjiaqing/tidb
2 participants
@erjiaqing @qw4990