Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Support casting in LIMIT clauses for compatibility with postgres_fdw (12+) #13835

Closed
hlcianfagna opened this issue Mar 21, 2023 · 1 comment · Fixed by #13954
Closed

Support casting in LIMIT clauses for compatibility with postgres_fdw (12+) #13835

hlcianfagna opened this issue Mar 21, 2023 · 1 comment · Fixed by #13954
Assignees
@matriv
Labels
complexity: 1-3 feature: sql: grammar SQL Grammar enhancements

Comments

@hlcianfagna
Copy link
Contributor

Problem Statement

In certain circumstances (requested in the Community) it can be useful to use a FOREIGN DATA WRAPPER in a PostgreSQL instance to have FOREIGN TABLEs from a CrateDB instance.

Starting on PostgreSQL 12, postgres_fdw will pass LIMIT clauses with a cast like:

LIMIT 1::bigint

This is currently not supported by CrateDB and it fails with:

mismatched input '::' expecting {<EOF>, ';'}

Possible Solutions

Use PostgreSQL 11.19 which is still supported.

Considered Alternatives

There are workarounds when the client/input query can be controlled, but in the case mentioned in the Community there was a 3rd party tool involved.
@hlcianfagna hlcianfagna changed the title Support casting in LIMIT clauses for compatibility for postgres_fdw (12+) Support casting in LIMIT clauses for compatibility with postgres_fdw (12+) Mar 21, 2023
@matriv matriv self-assigned this Apr 5, 2023
matriv added a commit that referenced this issue Apr 11, 2023
@matriv
Add CAST support for LIMIT and OFFSET
e2da17c 
Allow both `CAST(<value> AS <datatype>)` and <value>::<datatype>``
expressions. Invalid datatypes would throw errors during symbol
normalization.

Closes: #13835
@matriv matriv mentioned this issue Apr 11, 2023
Merged
matriv added a commit that referenced this issue Apr 11, 2023
@matriv
Add CAST support for LIMIT and OFFSET
a6ff655 
Allow both `CAST(<value> AS <datatype>)` and <value>::<datatype>``
expressions. Invalid datatypes would throw errors during symbol
normalization.

Closes: #13835
matriv added a commit that referenced this issue Apr 11, 2023
@matriv
Add CAST support for LIMIT and OFFSET
25e154a 
Allow both `CAST(<value> AS <datatype>)` and `<value>::<datatype>` expressions.
Invalid datatypes would throw errors during either from the `ExpressionAnalyzer`
if the resulting type of the cast cannot be converted to an integer,
or during symbol normalization, if the actual runtime value cannot be converted
to an integer.

Closes: #13835
@mergify mergify bot closed this as completed in #13954 Apr 11, 2023
mergify bot pushed a commit that referenced this issue Apr 11, 2023
@matriv @mergify
Add CAST support for LIMIT and OFFSET
5b73ac0 
Allow both `CAST(<value> AS <datatype>)` and `<value>::<datatype>` expressions.
Invalid datatypes would throw errors during either from the `ExpressionAnalyzer`
if the resulting type of the cast cannot be converted to an integer,
or during symbol normalization, if the actual runtime value cannot be converted
to an integer.

Closes: #13835
@matriv
Copy link
Contributor

matriv commented Apr 11, 2023

@hlcianfagna Thx for reporting this improvement, It's been implemented and will be included in the next release.

@amotl amotl mentioned this issue Feb 12, 2024
Open
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@matriv matriv

Labels
complexity: 1-3 feature: sql: grammar SQL Grammar enhancements
Projects
No open projects
CrateDB 5.4
Status: Done
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

Add CAST support for LIMIT and OFFSET crate/crate
3 participants
@mfussenegger @matriv @hlcianfagna