Skip to content

Backport to release-14: Fix parsing of CAST() statements #10512 and #10514#10517

Merged
GuptaManan100 merged 2 commits intovitessio:release-14.0from
planetscale:fix-cast-parsing-bp-14
Jun 16, 2022
Merged

Backport to release-14: Fix parsing of CAST() statements #10512 and #10514#10517
GuptaManan100 merged 2 commits intovitessio:release-14.0from
planetscale:fix-cast-parsing-bp-14

Conversation

@GuptaManan100
Copy link
Copy Markdown
Contributor

@GuptaManan100 GuptaManan100 commented Jun 16, 2022

Description

This is a backport of the PRs #10512 and #10514 for release-14. These PRs fix the parsing of CAST function when used in a CREATE TABLE statement.

Related Issue(s)

Checklist

  • "Backport me!" label has been added if this change should be backported
  • Tests were added or are not required
  • Documentation was added or is not required

Deployment Notes

dbussink and others added 2 commits June 16, 2022 12:14
@dbussink @vmg @GuptaManan100
Fix parsing of CAST() statements (vitessio#10512)
bdeceb5 
* Fix parsing of CAST() statements

CAST() was treated as an alias for CONVERT() but with slightly different
syntax.

This is also described in the documentation at
https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html,
specifically:

With CAST(expr AS type syntax, the CAST() function takes an expression of
any type and produces a result value of the specified type. This operation
may also be expressed as CONVERT(expr, type), which is equivalent. If expr
is NULL, CAST() returns NULL.

This is wrong sadly. CAST() is not equivalent to CONVERT(), specifically
in the context of a CREATE TABLE. For JSON keys, the ARRAY attribute is
possible on a CAST(), but that is not accepted for a CONVERT().

The difference in parsing also shows in MySQL:

```
mysql> select convert(json_keys(c), char(64) array) from t;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'array) from t' at line 1
mysql> select cast(json_keys(c) as char(64) array) from t;
ERROR 1235 (42000): This version of MySQL doesn't yet support 'Use of CAST( .. AS .. ARRAY) outside of functional index in CREATE(non-SELECT)/ALTER TABLE or in general expressions'
```

Here the first statement can't be parsed at all. The second is properly
parsed, but ARRAY is not allowed in the context of CAST() in this
situation (and only in a CREATE TABLE).

This means we should really treat these as two separate expressions and
don't store them both in the same structure. The change here creates a
separate CAST structure, removes the ARRAY option from CONVERT and
updates the grammar and all tests accordingly.

Signed-off-by: Dirkjan Bussink <d.bussink@gmail.com>

* Handle new cast expression in evalengine and planbuilder

Signed-off-by: Dirkjan Bussink <d.bussink@gmail.com>

* evalengine: do not duplicate CAST/CONVERT translation

Signed-off-by: Vicent Marti <vmg@strn.cat>

Co-authored-by: Vicent Marti <vmg@strn.cat>
Signed-off-by: Manan Gupta <manan@planetscale.com>
@dbussink @GuptaManan100
Add back unary single column expression check (vitessio#10514)
ae65d6e 
This was accidentally removed in
vitessio#10512 but it shouldn't have
been.

Signed-off-by: Dirkjan Bussink <d.bussink@gmail.com>
@github-actions
Copy link
Copy Markdown
Contributor

github-actions bot commented Jun 16, 2022

Review Checklist

Hello reviewers! 👋 Please follow this checklist when reviewing this Pull Request.

General

  • Ensure that the Pull Request has the correct release notes label. release notes none should only be used for PRs that are so trivial that they need not be included.
  • If a new flag is being introduced, review whether it is really needed. The flag names should be clear and intuitive (as far as possible), and the flag's help should be descriptive.
  • If a workflow is added or modified, each items in Jobs should be named in order to mark it as required. If the workflow should be required, the GitHub Admin should be notified.

Bug fixes

  • There should be at least one unit or end-to-end test.
  • The Pull Request description should either include a link to an issue that describes the bug OR an actual description of the bug and how to reproduce, along with a description of the fix.

Non-trivial changes

  • There should be some code comments as to why things are implemented the way they are.

New/Existing features

  • Should be documented, either by modifying the existing documentation or creating new documentation.
  • New features should have a link to a feature request issue or an RFC that documents the use cases, corner cases and test cases.

Backward compatibility

  • Protobuf changes should be wire-compatible.
  • Changes to _vt tables and RPCs need to be backward compatible.
  • vtctl command output order should be stable and awk-able.

@GuptaManan100 GuptaManan100 force-pushed the fix-cast-parsing-bp-14 branch from d900091 to ae65d6e Compare June 16, 2022 08:07
@GuptaManan100 GuptaManan100 merged commit d761547 into vitessio:release-14.0 Jun 16, 2022
@GuptaManan100 GuptaManan100 deleted the fix-cast-parsing-bp-14 branch June 16, 2022 11:03
@skywhat
Copy link
Copy Markdown

skywhat commented Jul 23, 2022

I have tried to parse cast(user_id as int) or cast(user_id as bigint), it's failed and looks like it's not supported. could you help take a look? Thanks

@dbussink
Copy link
Copy Markdown
Member

dbussink commented Jul 23, 2022

I have tried to parse cast(user_id as int) or cast(user_id as bigint), it's failed and looks like it's not supported. could you help take a look? Thanks

@skywhat A quick test shows that MySQL does not support this syntax either:

mysql> select cast(1 as bigint);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'bigint)' at line 1

So Vitess would not support this either as it aims to be compatible with MySQL. If you do have a query that is parsed on MySQL but is not on Vitess, please open a new issue with the exact query so we can look at why it isn't working then.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

@dbussink dbussink dbussink approved these changes

@harshit-gangal harshit-gangal Awaiting requested review from harshit-gangal harshit-gangal is a code owner

@systay systay Awaiting requested review from systay systay is a code owner

@frouioui frouioui Awaiting requested review from frouioui frouioui is a code owner

@deepthi deepthi Awaiting requested review from deepthi

@rohit-nayak-ps rohit-nayak-ps Awaiting requested review from rohit-nayak-ps

@mattlord mattlord Awaiting requested review from mattlord

@ajm188 ajm188 Awaiting requested review from ajm188

@doeg doeg Awaiting requested review from doeg

@notfelineit notfelineit Awaiting requested review from notfelineit

Assignees

No one assigned

Labels

Backport This is a backport Component: Query Serving Type: Bug

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

3 participants

@GuptaManan100 @skywhat @dbussink