Skip to content
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.

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

JayBird throws DataTruncation or produces empty results when using Hibernate and the search text reachs the column length [CORE1217] #1641

Closed
firebird-automations opened this issue Sep 13, 2006 · 13 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Jose M. Rus (josemrus)

Attachments:
testcase-jdbc90.zip

Votes: 2

'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.

@firebird-automations
Copy link
Collaborator Author

Commented by: Jose M. Rus (josemrus)

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

@firebird-automations
Copy link
Collaborator Author

Modified by: Jose M. Rus (josemrus)

Attachment: testcase-jdbc90.zip [ 10126 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Roman Rokytskyy (rrokytskyy)

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: Roman Rokytskyy (rrokytskyy)

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

resolution: Fixed [ 1 ]

Fix Version: Jaybird 2.1 [ 10050 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Jose M. Rus (josemrus)

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?

@firebird-automations
Copy link
Collaborator Author

Commented by: Roman Rokytskyy (rrokytskyy)

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: Roman Rokytskyy (rrokytskyy)

Project: Jaybird JCA/JDBC Driver [ 10002 ] => Firebird Core [ 10000 ]

Version: 2.0.1 [ 10090 ]

Key: JDBC90 => CORE1217

Version: Jaybird 2.1 [ 10050 ] =>

Version: Jaybird 2.0.1 [ 10051 ] =>

assignee: Roman Rokytskyy [ rrokytskyy ] =>

@firebird-automations
Copy link
Collaborator Author

Commented by: Roman Rokytskyy (rrokytskyy)

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

resolution: Fixed [ 1 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Fix Version: Jaybird 2.1 [ 10050 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Workflow: jira [ 11203 ] => Firebird [ 14340 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Sylvain Dusart (sdusart)

Hi,

any news about the paramater management problem in the engine ?

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

Regards,

Sylvain

@mrotteveel
Copy link
Member

Duplicate (which IMHO describes the problem better): #1361

@mrotteveel mrotteveel closed this as not planned Won't fix, can't repro, duplicate, stale Apr 8, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants