#1292 data exception: string data, right truncation in CASE

[Ruslan Vaskov]

Hi!
I have two string columns with some 'compressed' data in following format: <tagNum>=<value><delimiter><tagNum>=<value><delimiter><tagNum>=<value><delimiter>...
Use-case is two extract some value by tag number that may by in any of two columns.
Below is an example where time is retrieved from date value.
Here delimiter is char 0x1, not displayed in browser unfortunately. I've attached the same in file just in case.

CREATE TABLE TEST_TABLE (Col_1 varchar(2147483647), Col_2 varchar(2147483647))

INSERT INTO TEST_TABLE VALUES ('23=foo
52=20130329 10:48:51
67=bar98=fake
', '105=baz
256=qux
')

SELECT
CASE WHEN SUBSTRING('
' || Col_1 || Col_2
FROM POSITION('
52=' IN '
' || Col_1 || Col_2)+4+17
FOR 1)
='.'
THEN SUBSTRING('
' || Col_1 || Col_2
FROM POSITION('
52=' IN '
' || Col_1 || Col_2)+4+9
FOR 12)
ELSE SUBSTRING('
' || Col_1 || Col_2
FROM POSITION('
52=' IN '
' || Col_1 || Col_2)+4+9
FOR 8)
END
FROM TEST_TABLE

Following exception occurs:
Error code: -3401
SQL state: 22001
Stack trace of root exception:
java.sql.SQLDataException: data exception: string data, right truncation
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.fetchResult(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.executeUpdate(Unknown Source)

I'd not be wondered if such exception occurred when columns of this size (Integer.MAX) are concatenated, even though size of their content is small.
But concatenation is executed successfully.
All these expressions are executed successfully individually, but not in this CASE together.
Other two weird things I noticed playing:
1. If in WHERE right argument in comparison is 0x1, then query is executed successfully
2. If ELSE returns any explicit value instead of SUBSTRING function, query would is executed. I repeat, this SUBSTRING itself is executable successfully.

And two related questions with our permission:
1. Is it planned two support kind of VARCHAR(MAX) format to avoid explicit huge sizes defined?
2. Is it planned to implement POSITION in CLOB?

Thanks in advance!

[Ruslan Vaskov]

[Ruslan Vaskov]

Clarification on weird thing #1:
If in WHERE right argument in = is 0x1, then query is executed successfully.
<> fails for 0x1, so I can't interchange expressions in THEN and ELSE.

[Fred Toussi]

Thanks. This has been fixed and committed. The next snapshot jar will include the changes.

With version 2.3.0 you can use Unicode strings with escapes. You can use VARCHAR(1G) or VARCHAR(2000M) instead of a full value.

You can store your data as CLOB and cast as VARCHAR before using ||. If you use || or other forms of concatenation on a CLOB, a temporary clob is created which is not efficient at all.

We will probably support POSITION with CLOB in a future version.

CREATE TABLE TEST_TABLE (Col_1 varchar(2147483647), Col_2 varchar(2147483647))

INSERT INTO TEST_TABLE VALUES (U&'23=foo\000152=20130329 10:48:51\000167=bar98=fake\0001', U&'105=baz\0001256=qux\0001')
SELECT
CASE WHEN SUBSTRING(U&'\0001' || Col_1 || Col_2
FROM POSITION(U&'\000152=' IN U&'\0001' || Col_1 || Col_2)+4+17
FOR 1)
='.'
THEN SUBSTRING(U&'\0001' || Col_1 || Col_2
FROM POSITION(U&'\000152=' IN U&'\0001' || Col_1 || Col_2)+4+9
FOR 12)
ELSE SUBSTRING(U&'\0001' || Col_1 || Col_2
FROM POSITION(U&'\000152=' IN U&'\0001' || Col_1 || Col_2)+4+9
FOR 8)
END
FROM TEST_TABLE