Can't cast text to date/time types

[bgrainger]

The following conversions succeed with MySQL Community Edition 8.0.31:

select cast('12:34:56' as time);
select cast('2023-01-07 12:34:56' as datetime);
select cast('2023-01-07' as date);
select cast('2023-01-07 12:34:56' as date);
select cast('2023-01-07 12:34:56' as time);

They fail with PlanetScale 8.0.23. (Interestingly, I can't reproduce a failure from MySQL Workbench, but I can from mysql CLI.)

mysql> select cast('12:34:56' as TIME);
ERROR 1105 (HY000): Unsupported type conversion: TIME
mysql> select cast('2023-01-07 12:34:56' as datetime);
ERROR 1105 (HY000): Unsupported type conversion: DATETIME
mysql> select cast('2023-01-07' as date);
ERROR 1105 (HY000): Unsupported type conversion: DATE
mysql> select cast('2023-01-07 12:34:56' as date);
ERROR 1105 (HY000): Unsupported type conversion: DATE
mysql> select cast('2023-01-07 12:34:56' as time);
ERROR 1105 (HY000): Unsupported type conversion: TIME

[orware]

Hello @bgrainger,

In some aspects this is a similar situation to what's been shared in discussions#369, but it's a bit more obvious in this case.

I am seeing the CAST() function listed over in the following Vitess Issue#9647.

I would have been under the assumption that this particular function already worked, but based on your tests and some I have also completed I can see those same errors you've run into too.

However, your note about things able to work from MySQL Workbench has led me to a clue and essentially a pseudo-workaround that you can experiment with a little further.

Essentially, the main difference I saw in MySQL Workbench was that it added an extra LIMIT/OFFSET at the end:

So I tried that out in our web console and simply adding in a LIMIT seems to allow things to work:

And the reason for the issue in the first case may be similar to discussions#369 in that one may be going through the Vitess parser, but adding in the LIMIT clause may force the query to be sent directly to MySQL for processing and avoids the issue in the second case.

I'll be collecting some details here from both of your discussions to create some internal tracking issues for further investigation, but the above workaround may be a good temporary option that could be used in the meanwhile.