SQL usability issue: unclear exception when a key of a wrong type is used

[dmagda]

Sergi, Vovan,

One of Ignite users struggled with an SQL issue and asked me to help him troubleshooting it. The root of the issue was trivial. The guy used a custom object as a key's type but passed the key of “int” type to DML queries.

Honestly, I could spot it only after looking into the code because the exception generated by both H2 and Ignite was useless.

Caused by: org.h2.jdbc.JdbcSQLException: Hexadecimal string with odd number of characters: "100"; SQL statement:
SELECT
TABLE._KEY,
TABLE.ID,
TABLE.NAME,
TABLE.COUNTRY
FROM TABLE(_KEY OTHER=(?1,), ID BIGINT=(?2,), NAME VARCHAR=(?3,), COUNTRY VARCHAR=(?4,)) [90003-195

Let’s improve the usability here. The ticket is ready:
https://issues.apache.org/jira/browse/IGNITE-5250 <https://issues.apache.org/jira/browse/IGNITE-5250>

Do you think add some hint in the nearest release?

—
Denis

[Dmitriy Setrakyan]

Vova, Sergi,

Any chance we can provide a proper error message in the exception?

D.

On Wed, May 17, 2017 at 8:50 PM, Denis Magda <[hidden email]> wrote:

> Sergi, Vovan,
>
> One of Ignite users struggled with an SQL issue and asked me to help him
> troubleshooting it. The root of the issue was trivial. The guy used a
> custom object as a key's type but passed the key of “int” type to DML
> queries.
>
> Honestly, I could spot it only after looking into the code because the
> exception generated by both H2 and Ignite was useless.
>
> Caused by: org.h2.jdbc.JdbcSQLException: Hexadecimal string with odd
> number of characters: "100"; SQL statement:
> SELECT
> TABLE._KEY,
> TABLE.ID,
> TABLE.NAME,
> TABLE.COUNTRY
> FROM TABLE(_KEY OTHER=(?1,), ID BIGINT=(?2,), NAME VARCHAR=(?3,), COUNTRY
> VARCHAR=(?4,)) [90003-195
>
> Let’s improve the usability here. The ticket is ready:
> https://issues.apache.org/jira/browse/IGNITE-5250 <
> https://issues.apache.org/jira/browse/IGNITE-5250>
>
> Do you think add some hint in the nearest release?
>
> —
> Denis

[Pavel Tupitsyn]

Denis, good point, I have also seen this confusing error a number of times.

And even if there is a clear description of an error, it is buried deep
into the "caused by".
For example, if I misprint a table name, this is what I get:

class IgniteCheckedException: Failed to parse query: ...
Caused by: CacheException: IgniteSQLException: Failed to parse query: ...
Caused by: IgniteSQLException: Failed to parse query: ...
Caused by: JdbcSQLException: Table "ORGANIZATION1" not found; SQL
statement: ...

Ideally user should see the real problem right away in the top level
exception.

Thoughts?



On Thu, May 18, 2017 at 9:18 AM, Dmitriy Setrakyan <[hidden email]>
wrote:

> Vova, Sergi,
>
> Any chance we can provide a proper error message in the exception?
>
> D.
>
> On Wed, May 17, 2017 at 8:50 PM, Denis Magda <[hidden email]> wrote:
>
> > Sergi, Vovan,
> >
> > One of Ignite users struggled with an SQL issue and asked me to help him
> > troubleshooting it. The root of the issue was trivial. The guy used a
> > custom object as a key's type but passed the key of “int” type to DML
> > queries.
> >
> > Honestly, I could spot it only after looking into the code because the
> > exception generated by both H2 and Ignite was useless.
> >
> > Caused by: org.h2.jdbc.JdbcSQLException: Hexadecimal string with odd
> > number of characters: "100"; SQL statement:
> > SELECT
> > TABLE._KEY,
> > TABLE.ID,
> > TABLE.NAME,
> > TABLE.COUNTRY
> > FROM TABLE(_KEY OTHER=(?1,), ID BIGINT=(?2,), NAME VARCHAR=(?3,), COUNTRY
> > VARCHAR=(?4,)) [90003-195
> >
> > Let’s improve the usability here. The ticket is ready:
> > https://issues.apache.org/jira/browse/IGNITE-5250 <
> > https://issues.apache.org/jira/browse/IGNITE-5250>
> >
> > Do you think add some hint in the nearest release?
> >
> > —
> > Denis
>