CHAR_TO_UUID on column with index throws expression evaluation not supported
Human readable UUID argument for CHAR_TO_UUID must be of exact length 36
-----------------------------------------------------------------------------------------------------------------------------------------------------
Key: CORE-5062
URL: http://tracker.firebirdsql.org/browse/CORE-5062
Project: Firebird Core
Issue Type: Bug
Affects Versions: 2.5.5, 3.0 RC1, 3.0 Beta 2, 2.5.4
Environment: Firebird-2.5.4.26856-0_x64, Firebird-2.5.5.26952-0_x64,
Firebird-3.0.0.32257-0_x64 (Windows 10) with Jaybird 2.2.9.
Reporter: Mark Rotteveel
Assuming a table:
create table t_uuid (
uuid CHAR(16) CHARACTER SET OCTETS PRIMARY KEY,
datavalue integer
)
Using connection character set UTF8 and executing the query SELECT datavalue
from t_uuid where uuid = char_to_uuid(?) with a UUID (eg
57F2B8C7-E1D8-4B61-9086-C66D1794F2D9)
will throw GDS Exception. 335544606. expression evaluation not supported Human
readable UUID argument for CHAR_TO_UUID must be of exact length 36
See also
http://stackoverflow.com/questions/34532398/firebird-char-to-uuid-failed-with-gds-exception-335544606
The error does not occur if the uuid column does not have an index (that is: it
is not a primary, unique or foreign key, or has no normal index), nor when the
connection character set is a single byte character set. The bind description
received by Jaybird is the same for the working and failing case.
This error has been been introduced in 2.5.4, it is not reproducible with
Firebird-2.5.3.26780-0_x64 or earlier.
This is reproducible using Jaybird (note: run from the TestFBPreparedStatement
class in Jaybird Branch_2_2) with:
@Test
public void testCharToUuid() throws Exception {
executeCreateTable(con, "create table t_uuid ("
+ " uuid CHAR(16) CHARACTER SET OCTETS PRIMARY KEY,"
+ " datavalue integer"
+ ")");
//executeDDL(con, "create index ix_uuid on t_uuid (uuid)");
con.close();
Properties props = getDefaultPropertiesForConnection();
props.setProperty("lc_ctype", "UTF8");
con = DriverManager.getConnection(getUrl(), props);
PreparedStatement pstmt = con.prepareStatement("SELECT datavalue from
t_uuid where uuid = char_to_uuid(?)");
//PreparedStatement pstmt = con.prepareStatement("SELECT datavalue from
t_uuid where uuid = char_to_uuid(cast(? as char(36) character set utf8))");
try {
System.out.println(((FirebirdPreparedStatement)
pstmt).getExecutionPlan());
pstmt.setString(1, "57F2B8C7-E1D8-4B61-9086-C66D1794F2D9");
//pstmt.setBytes(1,
"57F2B8C7-E1D8-4B61-9086-C66D1794F2D9".getBytes(StandardCharsets.US_ASCII));
ResultSet rs = pstmt.executeQuery();
assertFalse(rs.next());
} finally {
pstmt.close();
}
}
Workaround is to explicitly cast the parameter to a character set like UTF8 or
ASCII. Note that from the perspective of Jaybird there is no difference in the
received bind description.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
