[Bug]: unsupport "select now() - INTERVAL 1 DAY"

yangj1211 commented 4 months ago

Is there an existing issue for the same bug?

[X] I have checked the existing issues.

Branch Name

main

Commit ID

newest

Other Environment Information

- Hardware parameters:
- OS type:
- Others:

Actual Behavior

mo:

Expected Behavior

mysql：

Steps to Reproduce

select now() - INTERVAL 1 DAY;

Additional information

No response

aronchanisme commented 4 months ago

As per the doc here, https://docs.matrixorigin.cn/en/1.2.1/MatrixOne/Reference/Operators/interval/

The INTERVAL values are used mainly for date and time calculations. The INTERVAL in expressions represents a temporal interval.

Temporal intervals are used for certain functions, such as DATE_ADD() and DATE_SUB().

Temporal arithmetic also can be performed in expressions using INTERVAL together with the + or - operator:

date + INTERVAL expr unit date - INTERVAL expr unit

So these sqls should be legal as it is in mysql, while in fact it isn't in mo.

select now() - INTERVAL 1 DAY;
select now() + INTERVAL 1 DAY;

Reproducible in mo on both main and 1.2-dev branches, such as 1.2-dev

github@test0:/data/mo/1.2-dev/matrixone$ mo_ctl connect
2024-07-11 16:42:31.259 UTC+0800    [INFO]    Checking connectivity
2024-07-11 16:42:31.336 UTC+0800    [INFO]    Ok, connecting for user ... 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6705
Server version: 8.0.30-MatrixOne-v58497 MatrixOne

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select now();
+----------------------------+
| now()                      |
+----------------------------+
| 2024-07-11 16:42:32.714317 |
+----------------------------+
1 row in set (0.00 sec)

mysql> select now() - INTERVAL 1 DAY;
ERROR 20203 (HY000): invalid argument operator -, bad value [TIMESTAMP INTERVAL]
mysql> select ( now() - INTERVAL 1 DAY);
ERROR 20203 (HY000): invalid argument operator -, bad value [TIMESTAMP INTERVAL]
mysql> SELECT DATE_SUB(now(),INTERVAL 1 DAY);
+-----------------------------------+
| DATE_SUB(now(), INTERVAL(1, day)) |
+-----------------------------------+
| 2024-07-10 16:44:05.059131        |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_ADD(now(),INTERVAL 1 DAY);
+-----------------------------------+
| DATE_ADD(now(), INTERVAL(1, day)) |
+-----------------------------------+
| 2024-07-12 16:44:23.642578        |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> select now() + INTERVAL 1 DAY;
ERROR 20203 (HY000): invalid argument operator +, bad value [TIMESTAMP INTERVAL]
mysql> select ( now() + INTERVAL 1 DAY );
ERROR 20203 (HY000): invalid argument operator +, bad value [TIMESTAMP INTERVAL]
mysql> select git_version();
+---------------+
| git_version() |
+---------------+
| 58b49ccf7     |
+---------------+
1 row in set (0.00 sec)

Expected behavior in mysql

root@szpc-10-111-1-11:~# mysql80 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.31 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>  select now() + INTERVAL 1 DAY;
+------------------------+
| now() + INTERVAL 1 DAY |
+------------------------+
| 2024-07-12 08:45:22    |
+------------------------+
1 row in set (0.00 sec)

mysql>  select now() - INTERVAL 1 DAY;
+------------------------+
| now() - INTERVAL 1 DAY |
+------------------------+
| 2024-07-10 08:45:28    |
+------------------------+
1 row in set (0.00 sec)

mysql>  select ( now() - INTERVAL 1 DAY );
+----------------------------+
| ( now() - INTERVAL 1 DAY ) |
+----------------------------+
| 2024-07-10 08:45:49        |
+----------------------------+
1 row in set (0.00 sec)

mysql>  select ( now() + INTERVAL 1 DAY );
+----------------------------+
| ( now() + INTERVAL 1 DAY ) |
+----------------------------+
| 2024-07-12 08:45:58        |
+----------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_ADD(now(),INTERVAL 1 DAY);
+--------------------------------+
| DATE_ADD(now(),INTERVAL 1 DAY) |
+--------------------------------+
| 2024-07-12 08:46:09            |
+--------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_SUB(now(),INTERVAL 1 DAY);
+--------------------------------+
| DATE_SUB(now(),INTERVAL 1 DAY) |
+--------------------------------+
| 2024-07-10 08:46:16            |
+--------------------------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.31    |
+-----------+
1 row in set (0.00 sec)

Temporary Workaround

SELECT DATE_SUB(now(),INTERVAL 1 DAY);
SELECT DATE_ADD(now(),INTERVAL 1 DAY);

aronchanisme commented 4 months ago

@daviszhen Hi Davis, could you pls kindly help take a look? Thx.

daviszhen commented 4 months ago

@YANGGMM

YANGGMM commented 2 months ago

fixed，plz test @aressu1985

Ariznawlll commented 2 months ago

测试case：
select now() - INTERVAL 1 DAY;
select now() + INTERVAL 1 DAY;
SELECT DATE_SUB(now(),INTERVAL 1 DAY);
SELECT DATE_ADD(now(),INTERVAL 1 DAY);
select now() + INTERVAL 1 DAY;
select ( now() + INTERVAL 1 DAY );

main commit: 0492a8113 测试结果：

mysql> select now();
+----------------------------+
| now()                      |
+----------------------------+
| 2024-08-28 10:41:47.715465 |
+----------------------------+
1 row in set (0.00 sec)

mysql>  select now() - INTERVAL 1 DAY;
+----------------------------+
| now() - INTERVAL(1, day)   |
+----------------------------+
| 2024-08-27 10:41:51.141910 |
+----------------------------+
1 row in set (0.00 sec)

mysql> select now() + INTERVAL 1 DAY;
+----------------------------+
| now() + INTERVAL(1, day)   |
+----------------------------+
| 2024-08-29 10:41:58.563137 |
+----------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_SUB(now(),INTERVAL 1 DAY);
+-----------------------------------+
| DATE_SUB(now(), INTERVAL(1, day)) |
+-----------------------------------+
| 2024-08-27 10:42:02.811293        |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_ADD(now(),INTERVAL 1 DAY);
+-----------------------------------+
| DATE_ADD(now(), INTERVAL(1, day)) |
+-----------------------------------+
| 2024-08-29 10:42:07.787641        |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> select now() + INTERVAL 1 DAY;
+----------------------------+
| now() + INTERVAL(1, day)   |
+----------------------------+
| 2024-08-29 10:42:14.364427 |
+----------------------------+
1 row in set (0.00 sec)

mysql> select ( now() + INTERVAL 1 DAY );
+----------------------------+
| now() + INTERVAL(1, day)   |
+----------------------------+
| 2024-08-29 10:42:17.894798 |
+----------------------------+
1 row in set (0.00 sec)

main测试通过

matrixorigin / matrixone