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.

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

FBDatabaseMetaData.Clause build poor performing SQL [JDBC560] #593

Closed
firebird-automations opened this issue Oct 30, 2018 · 7 comments
Closed

Comments

@firebird-automations
Copy link

Submitted by: Attila Molnár (e_pluribus_unum)

Is related to JDBC467

Hi Mark!

FBDatabaseMetaData.Clause builds this : "CAST(" + columnName + " AS VARCHAR(" + (OBJECT_NAME_LENGTH + 10) + ")) = ? and ";
Because of the CAST() Firebird does not use index in this case, it reads tables NATURAL.
What is the reason to this strange condition? Index using SQL should be build : columnName + " = ? and ". If you want support special cases, please detect it and use a dedicated SQL for that.

Thank You!

Commits: 326c788

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

Parameters in Firebird are restricted in length by the column or expression they are compared against, this trick is done to allow wider values by a margin of 10 so people don't get a string right truncation error when using a slightly longer name than supported by Firebird. Older versions of Firebird do not allow casting the parameter, so we cast the column expression instead. Given all supported versions do support casting parameters, I'll consider changing this to cast the parameter instead.

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

Fix Version: Jaybird 4 [ 10441 ]

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

Version: Jaybird 3.0.5 [ 10873 ]

Version: Jaybird 2.2.14 [ 10820 ]

Version: Jaybird 2.2.13 [ 10805 ]

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

Implemented. As an additional optimization, metadata patterns ending in % without other unescaped wildcards will be matched using STARTING WITH.

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

Link: This issue is related to JDBC467 [ JDBC467 ]

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

status: Resolved [ 5 ] => Closed [ 6 ]

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