MySQL Bugs: #106352: Binary resultset for `SELECT ?` with date/time gives wrong type for the column

Bug #106352	Binary resultset for `SELECT ?` with date/time gives wrong type for the column
Submitted:	2 Feb 2022 0:00	Modified:	9 Feb 2022 19:00
Reporter:	Austin Bonander	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S3 (Non-critical)
Version:	8.0.28, 5.7.37	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The recently released 8.0.28 version of MySQL server has broken our type encoding/decoding tests for the DATE, TIME, DATETIME, and TIMESTAMP types in SQLx, a SQL client library for Rust: https://github.com/launchbadge/sqlx/issues/1664

I believe this is related to the changes to the handling of these types as mentioned in the release notes: https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-28.html#mysqld-8-0-28-data-types

Essentially, we prepare and execute a statement `SELECT ({0} <=> ?), {0}, ?` where `{0}` is a literal value of the SQL type we're testing, formatted directly into the SQL, and `?` is that same value as represented in Rust, bound as an argument in the binary protocol.

We then assert that the first column is non-zero (we map that to `true`), and that, when decoded from the binary protocol to their Rust equivalents, the second and third columns are also equal. As part of the decoding, it asserts that the column type given in the Resultset matches the expected type.

This test for TIME, DATE, DATETIME and TIMESTAMP started failing upon the release of 8.0.28. The test seems to think that the third column in the above query is type `VARCHAR`: https://github.com/launchbadge/sqlx/runs/4889500905?check_suite_focus=true

This test spontaneously started failing, as it was previously passing with no changes to the MySQL driver code in between the passing test and the failing test. The only thing that changed was that MySQL server 8.0.28 was released and the Docker image we use for testing was updated to it.

When I investigated using Wireshark, I did find that, while the COM_STMT_EXECUTE told MySQL the correct type for the bind parameter, the Binary Resultset response always indicates that the type is `MYSQL_TYPE_VAR_STRING`, which we interpret as the SQL type `VARCHAR`. I will attach Wireshark capture files for each type your inspection.

This only seems to affect the date/time types, however, which lends further credence to my hypothesis that this is a regression in 8.0.28 caused by changes to the handling of those types.

How to repeat:
Spin up MySQL server version 8.0.28. For our testing, we use the `mysql:8` image from Docker hub, which is tagged at 8.0.28.

Prepare and execute `SELECT ?` with a DATE, TIME, DATETIME or TIMESTAMP as the bound argument and observe the response in the binary protocol. The ColumnDefinition message for the column in the Binary Resultset response following the COM_STMT_EXECUTE will describe the column type as MYSQL_TYPE_VAR_STRING instead of MYSQL_TYPE_[TIME, DATE, DATETIME, TIMESTAMP].

Wireshark capture: DATE

Attachment: mysql-8.0.28-regression-date.pcapng (application/x-pcapng, text), 6.00 KiB.

Wireshark capture: TIME

Attachment: mysql-8.0.28-regression-time.pcapng (application/x-pcapng, text), 5.38 KiB.

Wireshark capture: DATETIME

Attachment: mysql-8.0.28-regression-datetime.pcapng (application/x-pcapng, text), 5.41 KiB.

Wireshark capture: TIMESTAMP

Attachment: mysql-8.0.28-regression-timestamp.pcapng (application/x-pcapng, text), 5.48 KiB.

Hello Austin Bonander,

Thank you for the report and feedback.
Imho this is seen in even earlier versions(5.6,5.7,8.0).

regards,
Umesh

Hi Austin,
can you please add the actual SQL statements for the various temporal data types?

The problem you are facing is most likely related to now detecting parameter types from the context, in accordance with the SQL standard. It probably means that some CAST clauses must be inserted in the SQL statements.

Roy:

SQLx is telling MySQL what the type of that bind argument is, e.g. MYSQL_TYPE_TIMESTAMP, and passing it in the binary encoding. There shouldn't be any inference necessary.

Additionally, MySQL appears to be returning returning the value as a string instead of in the binary encoding as expected, which explains why the type is MYSQL_TYPE_VAR_STRING.

The same test passes for all the other types we cover, and passes for these types with 8.0.27. It is almost certainly a regression introduced in 8.0.28.

I can simplify the query to just `SELECT ?` and reproduce the issue. I will attach the Wireshark capture for the simplified test if it makes the issue clearer. 

For simplicity, it's just going to be the capture from testing with TIMESTAMP, but I do want to reiterate that this also affects DATE, TIME and DATETIME.

Wireshark capture: TIMESTAMP by itself

Attachment: mysql-8.0.28-regression-timestamp-simplified.pcapng (application/x-pcapng, text), 5.54 KiB.

MySQL will always derive a data type for dynamic parameters based on the context.
However, for a query like "SELECT ?", there is no context to look into,
so by default, the type VARCHAR is assumed. On execution, we check whether the
actual type is compatible with the derived type, and if these are incompatible,
an implicit repreparation is performed, where the parameter type is reassigned
according to the actual type. This is assumed to happen here, but doesn't.

It is however always good to avoid such repreparations, this is why we recommend
to rewrite the query with an explicit cast, such as

  SELECT CAST(? AS DATETIME);

Anyway, we'll fix this regression as soon as possible.

Posted by developer:
 
Fixed as of the upcoming MySQL 8.0.29 release, and here's the proposed changelog entry from the documentation team:

A prepared and executed query like ‘SELECT ?’ with a DATE, TIME,
DATETIME or TIMESTAMP as the bound argument could return the wrong column
type. The issue is fixed by restoring the previous logic that reprepared
such queries to give each dynamic parameter its literal value.

Thank you for the bug report.