Issue Details (XML | Word | Printable)

Key: CORE-1217
Type: Bug Bug
Status: Reopened Reopened
Priority: Major Major
Assignee: Unassigned
Reporter: Jose M. Rus
Votes: 2
Watchers: 2
Operations

If you were logged in you would be able to see more operations.
Firebird Core

JayBird throws DataTruncation or produces empty results when using Hibernate and the search text reachs the column length

Created: 13/Sep/06 07:17 AM   Updated: 09/Aug/10 08:32 AM
Component/s: None
Affects Version/s: 2.0.1
Fix Version/s: None

File Attachments: 1. Zip Archive testcase-jdbc90.zip (35 kB)

Environment:
Firebird 1.5.3 together with JayBird 2.0.1/2.1.0 (embedded)
and Hibernate 3.1.3.


 Description  « Hide
'Like' queries throw DataTruncation exceptions or produce empty results when the search text reachs the column length or the column length -1.

Consider a VARCHAR field and a simple Hibernate Criteria query like:
    Restrictions.like("fieldName", "%" + filter + "%")
or
    Restrictions.like("fieldName", filter, MatchMode.ANYWHERE)

When the parameter value has the column length or the column length -1 the query breaks.

With JayBird 2.1.0 it produces an empty result when there should be matches, and with JayBird 2.0.1 it just throws a DataTruncation
exception.



 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Jose M. Rus added a comment - 13/Sep/06 07:23 AM
I include a testcase with Person.java (POJO), Person.hbm.xml (Hibernate mapping), PersonHandler (testcase) and test.fdb (empty database).

The Person table has a column for storing the name with up to 10 characters. PersonHandler stores a Person and builds a very simple query for this column, using a parameter of 9 characters.

With JayBird 2.1.0 the results is empty when there should be an element. With JayBird 2.0.1 it throws the Exception:

13-sep-2006 16:51:52 org.hibernate.type.NullableType nullSafeSet
INFO: could not bind value '%abcdefghi%' to parameter: 1; Data truncation
13-sep-2006 16:51:52 org.hibernate.util.JDBCExceptionReporter logExceptions
ADVERTENCIA: SQL Error: 0, SQLState: 01004
13-sep-2006 16:51:52 org.hibernate.util.JDBCExceptionReporter logExceptions
GRAVE: Data truncation
Exception in thread "main" org.hibernate.exception.GenericJDBCException: could not execute query
        at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
        at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
        at org.hibernate.loader.Loader.doList(Loader.java:2148)
        at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
        at org.hibernate.loader.Loader.list(Loader.java:2024)
        at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:94)
        at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1533)
        at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
        at app.PersonHandler.main(PersonHandler.java:65)
Caused by: java.sql.DataTruncation: Data truncation
        at org.firebirdsql.jdbc.field.FBWorkaroundStringField.setString(FBWorkaroundStringField.java:95)
        at org.firebirdsql.jdbc.AbstractPreparedStatement.setString(AbstractPreparedStatement.java:383)
        at org.hibernate.type.StringType.set(StringType.java:26)
        at org.hibernate.type.NullableType.nullSafeSet(NullableType.java:83)
        at org.hibernate.type.NullableType.nullSafeSet(NullableType.java:65)
        at org.hibernate.loader.Loader.bindPositionalParameters(Loader.java:1514)
        at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1576)
        at org.hibernate.loader.Loader.doQuery(Loader.java:661)
        at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
        at org.hibernate.loader.Loader.doList(Loader.java:2145)
        ... 6 more
Java Result: 1


Roman Rokytskyy added a comment - 15/Sep/06 11:11 AM
The situation with Jaybird 2.0 is clear - it did not allow the parameter values that were longer in byte representation than the length of the field in bytes. Reason was that all Firebirds incl. 1.5.x crashed when too long string was passed (that did not happen though when the string was longer for 2 bytes).

We have added additional check to the code that does not throw the DataTruncation error when the string length in bytes in longer than max. field length and starts and/or ends with '%' character.

Now to the issue. Tests show that data are correctly passed to the engine. The issue seems to be there. I have forwarded the bug description to the core engine team and I am waiting for reply. Till then the issue is considered resolved.

Jose M. Rus added a comment - 19/Sep/06 09:45 PM
I've opened the database attached in the previous testcase with an SQL client (SQuirreL) and launched the query:
     SELECT * FROM PERSONS WHERE NAME LIKE '%abcdefghij%'

And it correctly founds records (1 entry in the example) when the search term reachs the column length. In the connection I've used JayBird 2.1.0 embedded. So it's the same scenario than in the testcase but with a direct SQL connection instead of Hibernate. Maybe the problem is more related to the Hibernate dialect for Firebird?

In eitheir case, if the problem is in the engine shouldn't have it a registered issued for tracking purposes?


Roman Rokytskyy added a comment - 20/Sep/06 10:58 AM
The query without parameters does not produce errors. The issue with the engine is not yet reproduced, so no tracker entry had been created. It also happens regardless of the connection type, but please be sure, it's not forgotten.

Roman Rokytskyy added a comment - 20/Apr/07 04:19 AM
Steps to reproduce it without Hibernate

    CREATE TABLE Persons (
      PERSON_ID integer not null,
      NAME varchar(10),
      SURNAME varchar(20),
      primary key (PERSON_ID)
    );

    INSERT INTO Persons VALUES(1, 'abcdefghij', '');

Now the query

    SELECT * FROM Persons WHERE name like '%abcdefghi%'

produces correct results, however the query with a paremeter (SELECT * FROM Persons WHERE name like ?) has issues:

- parameter value of %abcdefghi% finds nothing
- paremeter value of %bcdefghij% produces correct results.

Looks like the parameter is truncated somewhere in the engine to the length of column.

Sylvain Dusart added a comment - 09/Aug/10 08:32 AM
Hi,

any news about the paramater management problem in the engine ?

I attached a test case with pure JDBC code in CORE-3099 (instead of adding it here, my mistake).

Regards,

Sylvain