Skip to content

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 [CORE5062] #5349

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
Closed
firebird-automations opened this issue Jan 1, 2016 · 9 comments
Closed

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 [CORE5062] #5349

firebird-automations opened this issue Jan 1, 2016 · 9 comments
Assignees
@asfernandes
Labels
affect-version: 2.5.4 affect-version: 2.5.5 affect-version: 3.0 Beta 2 affect-version: 3.0 RC1 fix-version: 2.5.6 fix-version: 3.0 RC2 priority: major qa: done with caveats type: bug

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @mrotteveel

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.

Commits: a5c4a29 e2c8f22 3ab690b dcdec26 210920d 964f91b 30e729d 7638ad5 FirebirdSQL/jaybird@fe64e29 FirebirdSQL/fbt-repository@feab8d0 FirebirdSQL/fbt-repository@cc54cec FirebirdSQL/fbt-repository@1689fb2 FirebirdSQL/fbt-repository@17ca9ae FirebirdSQL/fbt-repository@a561042 FirebirdSQL/fbt-repository@bf6b9c8

====== Test Details ======

Could not reproduce ticket issue on x86.
Checked on: WI-V2.5.4.26856, WI-V3.0.0.31948 (Python = 2.7 x86, fdb = 1.5).
@firebird-automations
Copy link
Collaborator Author

Commented by: @mrotteveel

Test case is available in Branch_2_2: FirebirdSQL/jaybird@fe64e29

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

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

resolution: Fixed [ 1 ]

Fix Version: 3.0 RC2 [ 10048 ]

Fix Version: 2.5.6 [ 10721 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

> The error does not occur if the uuid column does not have an index (. . .),
> nor when the connection character set is a single byte character set.

Can't catch error using parametrized ES and running script in ISQL with "-ch utf8" switch for connect:

recreate table test_uuid (
uuid char(16) character set octets collate octets,
datavalue integer,
constraint test_uuid_pk primary key(uuid) using index test_uuid_pk
);
commit;

set term ^;
execute block as
declare s varchar(128) = 'select datavalue from test_uuid u where u.uuid = char_to_uuid( ? )';
declare n int = 3;
declare v int;
declare p varchar(256) character set utf8;
begin
p = _utf8 '57F2B8C7-E1D8-4B61-9086-C66D1794F2D9';
while (n > 0) do
begin
--execute statement ( s ) ( uuid_to_char(gen_uuid()) ) into v; -- no error
--execute statement ( s ) ( p ) into v; -- no error
--execute statement ( s ) ( _utf8 '57F2B8C7-E1D8-4B61-9086-C66D1794F2D9' ) into v; -- no error
n = n - 1;
end
end
^
set term ;^
quit;

What I've missed ?
Checked on: WI-V3.0.0.32256, WI-V3.0.0.32266; OS: Win XP.

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

EXECUTE STATEMENT AFAIR uses varchar descriptors even for char columns.

I think you can have some success (failure) with this internal command:

-----------
set bulk_insert INSERT INTO t_uuid values (?, 1);
--
('57F2B8C7-E1D8-4B61-9086-C66D1794F2D9')
stop
-----------

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

ASF> I think you can have some success (failure) with this internal command

I've tried, on WI-V3.0.0.32266.

C:\MIX\firebird\fb30>isql /3333:e30
Database: /3333:e30, User: SYSDBA
SQL> recreate table test_uuid (
CON> uuid char(16) character set octets collate octets,
CON> datavalue integer,
CON> constraint test_uuid_pk primary key(uuid) using index test_uuid_pk
CON> );
SQL> commit;
SQL> set bulk_insert INSERT INTO test_uuid values (?, 1);
BULK> ('57F2B8C7-E1D8-4B61-9086-C66D1794F2D9')
String too long
BULK> stop
SQL> set list on;
SQL> select uuid, uuid_to_char(uuid), datavalue from test_uuid;

UUID 35374632423843372D453144382D3442
UUID_TO_CHAR 35374632-4238-4337-2D45-3144382D3442
DATAVALUE 1

Exception "String too long" is expected: I gave 'human readable' string with length = 36 rather than required argument with type = 'character set octets'.

But: why this argument (with invalid length) successfully passed ?

Another attempt, with enclosing parameter into CHAR_TO_UUID() function (tried on official RC-1, i.e. build 32136):

C:\MIX\firebird\fb30tmp>isql /3309:e30
Database: /3309:e30, User: SYSDBA
SQL> recreate table test_uuid (
CON> uuid char(16) character set octets collate octets,
CON> datavalue integer,
CON> constraint test_uuid_pk primary key(uuid) using index test_uuid_pk
CON> );
SQL> commit;
SQL> set bulk_insert INSERT INTO test_uuid values ( char_to_uuid(?), 1);
BULK> ('57F2B8C7-E1D8-4B61-9086-C66D1794F2D9')
BULK> stop
SQL> set list on;
SQL> select uuid, uuid_to_char(uuid), datavalue from test_uuid;

UUID 57F2B8C7E1D84B619086C66D1794F2D9
UUID_TO_CHAR 57F2B8C7-E1D8-4B61-9086-C66D1794F2D9
DATAVALUE 1

No exception occured though it should be here.

Also, no exception will be in case of trying in Python.
Code (file='http://c5062.py'):

import fdb
print fdb.__version__
conn = fdb.connect(dsn='localhost/3309:e30', user='SYSDBA', password='masterke')
tx=conn.trans()
xcur=tx.cursor()
xcmd1=xcur.prep( "select datavalue from test_uuid where uuid = char_to_uuid(?)" )
xcur.execute(xcmd1, ['57F2B8C7-E1D8-4B61-9086-C66D1794F2D9'])
xcur.execute(xcmd1, ['DEB35589B5CB4F0492455F83021D7C1A'])
xcur.close()
tx.commit()
conn.close()

Run: python http://c5062.py
Output: 1.4.3 (and thats all; no error will appear on console).

Trace for 1st statement that inserts '57F2B8C7-E1D8-4B61-9086-C66D1794F2D9' - NO errors:

2016-01-05T18:06:45.3120 (876:00C33418) PREPARE_STATEMENT
e30 (ATT_263, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
C:\Python27\python.exe:2212
(TRA_323, READ_COMMITTED | REC_VERSION | WAIT | READ_WRITE)

Statement 26:
-------------------------------------------------------------------------------
select datavalue from test_uuid where uuid = char_to_uuid(?)
6 ms

2016-01-05T18:06:45.3120 (876:00C33418) EXECUTE_STATEMENT_START
e30 (ATT_263, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
C:\Python27\python.exe:2212
(TRA_323, READ_COMMITTED | REC_VERSION | WAIT | READ_WRITE)

Statement 26:
-------------------------------------------------------------------------------
select datavalue from test_uuid where uuid = char_to_uuid(?)
param0 = varchar(36), "57F2B8C7-E1D8-4B61-9086-C66D1794F2D9"

2016-01-05T18:06:45.3120 (876:00C33418) EXECUTE_STATEMENT_FINISH
e30 (ATT_263, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
C:\Python27\python.exe:2212
(TRA_323, READ_COMMITTED | REC_VERSION | WAIT | READ_WRITE)

Statement 26:
-------------------------------------------------------------------------------
select datavalue from test_uuid where uuid = char_to_uuid(?)
param0 = varchar(36), "57F2B8C7-E1D8-4B61-9086-C66D1794F2D9"
0 records fetched
0 ms, 1 read(s), 2 fetch(es)

Trace for 2nd statement, of course, shows runtime exception:

2016-01-05T18:06:45.3120 (876:00C33418) ERROR AT JStatement::openCursor
e30 (ATT_263, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
C:\Python27\python.exe:2212
335544606 : expression evaluation not supported
335544948 : Human readable UUID argument for CHAR_TO_UUID must be of exact length 36

-- but it was only in the trace and not in Python output.

@firebird-automations
Copy link
Collaborator Author

Commented by: @sim1984

In my opinion this fix something broken. Create a database with UTF-8 encoding. We are trying run any query in IBExpert, for example,

select count (*)
from rdb$database

Then we obtain an error.

The trace shows the following

Statement 63:
-------------------------------------------------- -----------------------------
select RDB$CONSTRAINT_NAME
from RDB$RELATION_CONSTRAINTS
where RDB$INDEX_NAME =?

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN (RDB $ RELATION_CONSTRAINTS INDEX (RDB $ INDEX_43))
0 ms

2016-01-05T19: 22: 46.7730 (10380: 000000003A100040) ERROR AT JStatement :: openCursor
test (ATT_19, SYSDBA: NONE, UTF8, TCPv6 ::: 1)
C: \ IBExpert \ IBExpert.exe: 240
335544569: Dynamic SQL Error
335544321: arithmetic exception, numeric overflow, or string truncation
335544914: string right truncation
335545033: expected length 124, actual 3

Apparently IBExpert can not perform some internal queries for metadata extraction. Previously, this did not happen.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: No test => Done with caveats

Test Details: Could not reproduce ticket issue on x86.
Checked on: WI-V2.5.4.26856, WI-V3.0.0.31948 (Python = 2.7 x86, fdb = 1.5).

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

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

@asfernandes asfernandes

Labels
affect-version: 2.5.4 affect-version: 2.5.5 affect-version: 3.0 Beta 2 affect-version: 3.0 RC1 fix-version: 2.5.6 fix-version: 3.0 RC2 priority: major qa: done with caveats type: bug
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@asfernandes @firebird-automations