Issue Details (XML | Word | Printable)

Key: JDBC-560
Type: Bug Bug
Status: Resolved Resolved
Resolution: Fixed
Priority: Minor Minor
Assignee: Mark Rotteveel
Reporter: Attila Molnár
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
Jaybird JCA/JDBC Driver

FBDatabaseMetaData.Clause build poor performing SQL

Created: 30/Oct/18 07:28 AM   Updated: 17/Nov/18 01:13 PM
Component/s: None
Affects Version/s: Jaybird 2.2.13, Jaybird 2.2.14, Jaybird 3.0.5
Fix Version/s: Jaybird 4

Issue Links:
Relate
 


 Description  « Hide
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!

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Mark Rotteveel added a comment - 30/Oct/18 10:13 AM
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.

Mark Rotteveel added a comment - 03/Nov/18 01:11 PM
Implemented. As an additional optimization, metadata patterns ending in % without other unescaped wildcards will be matched using STARTING WITH.