MySQL Bugs: #116438: Timestamp literals have deceptive behavior in operations

Bug #116438	Timestamp literals have deceptive behavior in operations
Submitted:	21 Oct 2024 19:06	Modified:	23 Oct 2024 9:58
Reporter:	Bob Terrell	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	between, literal, timestamp

Description:
Timestamps and timestamp literals do not behave predictably with various operations. Consider the statements below.

How to repeat:
Create the table:

CREATE TABLE `time` (`stamp` TIMESTAMP NOT NULL ) ENGINE = InnoDB;

Insert a value:

INSERT INTO time VALUES ('2024-10-01 12:00:00+00:00')

See the value exists:

SELECT * FROM `time`
stamp
2024-10-01 08:00:00

Note the above is in EDT.

Now let's try to select it:

SELECT * FROM `time` WHERE stamp = '2024-10-01 12:00:00+00:00'
This returns our row.

SELECT * FROM `time` WHERE stamp > '2024-10-01 11:00:00+00:00'
also returns the row.

SELECT * FROM `time` WHERE stamp < '2024-10-01 13:00:00+00:00'
also returns the row.

However, when using BETWEEN:

SELECT * FROM `time` WHERE stamp BETWEEN '2024-10-01 11:00:00+00:00' AND '2024-10-01 13:00:00+00:00'

the row is NOT returned.

Finally, let's check our logic:

SELECT 
stamp,
stamp = '2024-10-01 12:00:00+00:00' AS eq,
stamp > '2024-10-01 11:00:00+00:00' AS gt,
stamp < '2024-10-01 13:00:00+00:00' AS lt,
stamp BETWEEN '2024-10-01 11:00:00+00:00' AND '2024-10-01 13:00:00+00:00' AS bt
FROM `time`

stamp: 2024-10-01 08:00:00
eq: 0
gt: 0
lt: 1
bt: 0

What?

Suggested fix:
This unintuitive behavior where a value is not equal to a value but also is equal to a value and is both greater than and not greater than a lower value seems to stem from the fact that the values coerce to strings instead of timestamps, given the choice.

It would be more logical and consistent for all operations involving a timestamp and string to attempt to convert the string to a timestamp first, and either throw a warning or convert the other way if converting to timestamp doesn't work.

Hi Mr. Terrel,

Thank you for your bug report.

However, it is not a bug.

If you have read the relevant chapter in our Reference Manual:

https://dev.mysql.com/doc/refman/8.0/en/datetime.html

you would have seen that syntax that you have used is not supported. 

Simply, you can not add arithmetic operation in the constant. You have to use INTERVAL, as explained in the mentioned Manual.

Not a bug.

It sure looks supported in section 11.1.3, Date and Time Literals:

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-literals.html

To be clear, I understand that it changed the displayed timezone. If you insist that the behavior in the SELECT clause is intended, fine. But how about that BETWEEN?

Hi Mr. Terrell,

What you want to achieve can be accomplished with our CAST() functionality ......

We changed your queries and we got the following results:

stamp
2024-10-01 12:00:00
stamp
2024-10-01 12:00:00
stamp
2024-10-01 12:00:00
stamp
2024-10-01 12:00:00
stamp
2024-10-01 12:00:00
stamp	                     eq	gt	lt	bt
2024-10-01 12:00:00	1	1	1	1

Not a bug.

Hi Mr. Terrell,

Regarding your last comment, those were INSERT commands not SELECT's. '+' can also be used as a delimiter, but that was not your intention.

For SELECTs you have to use CAST() functionality.

First: I get different values on 9.1.0 on Linux.

Second: For unsupported / unexpected behavior the database should probably return an error or a warning. I think the "Not a Bug" might be right, but could also be changed in a request for this to add errors/warnings.

mysql-9.1.0> CREATE TABLE `time` (`stamp` TIMESTAMP NOT NULL ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql-9.1.0> INSERT INTO time VALUES ('2024-10-01 12:00:00+00:00');
Query OK, 1 row affected (0.01 sec)

mysql-9.1.0> SELECT * FROM `time` WHERE stamp = '2024-10-01 12:00:00+00:00';
+---------------------+
| stamp               |
+---------------------+
| 2024-10-01 14:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql-9.1.0> SELECT * FROM `time` WHERE stamp > '2024-10-01 12:00:00+00:00';
Empty set (0.00 sec)

mysql-9.1.0> SELECT * FROM `time` WHERE stamp < '2024-10-01 12:00:00+00:00';
Empty set (0.00 sec)

mysql-9.1.0> SELECT * FROM `time` WHERE stamp BETWEEN '2024-10-01 11:00:00+00:00' AND '2024-10-01 13:00:00+00:00';
Empty set (0.00 sec)

mysql-9.1.0> SELECT 
    -> stamp,
    -> stamp = '2024-10-01 12:00:00+00:00' AS eq,
    -> stamp > '2024-10-01 11:00:00+00:00' AS gt,
    -> stamp < '2024-10-01 13:00:00+00:00' AS lt,
    -> stamp BETWEEN '2024-10-01 11:00:00+00:00' AND '2024-10-01 13:00:00+00:00' AS bt
    -> FROM `time`;
+---------------------+----+----+----+----+
| stamp               | eq | gt | lt | bt |
+---------------------+----+----+----+----+
| 2024-10-01 14:00:00 |  0 |  1 |  0 |  0 |
+---------------------+----+----+----+----+
1 row in set (0.00 sec)

Hi All,

This is now a fully verified bug.

There are several reasons for it.

First of all,  a BETWEEN b AND c  is equivalent to  a >= b AND a <= c.

In this case, it translates to these two statements being identical:

SELECT * FROM time  WHERE stamp BETWEEN '2024-10-01 11:00:00+00:00' AND '2024-10-01 13:00:00+00:00';

SELECT * FROM time  WHERE stamp >= '2024-10-01 11:00:00+00:00' AND        stamp <= '2024-10-01 13:00:00+00:00';

But the results are not the same.

Second reason is that there are no errors and warnings ....

Third reason is that an expression including AT TIME ZONE '+00:00' like this one:

SELECT cast( `tstamp` AT TIME ZONE '+00:00' AS DATETIME ) from table1;

is acceptable in SELECT list, but it is not acceptable in the WHERE clause.

This is now a verified bug report for version 8.0 and higher.