Possible Sybase bug causes AssertionFailure in QueryByExampleTest

Description

Test QueryByExampleTest.testJunctionNotExpressionQBE fails due to a possible bug in Sybase 15. The same test passes for Sybase 12.5. The test expects two records as a result for a query, but it gets 3. After some debugging with the query that Hibernate generated, I end up with these two simpler ones:

select id from Componentizable where not (name like ? and subName1 like ?)

select id from Componentizable where not (name like 'hibernate' and subName1 like 'ope%')

The first one returns three records, while the second returns two. The interesting part is that if I replace the first parameter in the first query by a hardcoded value ("hibernate"), it still returns 3 records. That means that the problem is probably with the binding of JDBC parameters containing a wildcard % .

Environment

sybase 15

Activity

Show:
Strong Liu
November 17, 2009, 11:27 AM
Steve Ebersole
November 17, 2009, 5:21 PM

Actually I am wondering if the test case itself might have to do with null comparisons. The test basically builds a disjunction predicate like:

( OR^ (ex) (NOT ex) )

So... give me everything that matches either (1) ex or (2) NOT ex. In theory that should be everything. However ANSI SQL says that any comparision involving NULLs should always evaluate to UNKNOWN. So even though there are a total of 3 records in the db only 2 should match because of that null-comparison rule. Both Sybase and SQL Server treat null comparisons in a manner other than what is described by ANSI SQL. Specifically I think they say here that "NULL LIKE 'something%'" evaluates to FALSE and therefore the negation (NOT) of that evaluates to TRUE.

You say that this is a different server (15 versus 12.5). Was this 15 server set up with the option "set ansinull on"? I'll follow up with Red Hat QE who own these boxes to verify.

As for your experienced discrepency between a literal match-string and a parameterized match-string, dunno. That sure sounds like I bug in the driver or database. I'll look after I follow up with QE

Strong Liu
November 18, 2009, 1:02 PM

from juca(JBPAPP-3069):
I have "sa" rights in the database, so, I can change it. I tried to find the command to set it for all connections for a given database, but I couldn't yet. Anyway, I added the below line to the test (locally) and it passes, confirming that the "ansinull" option is indeed the "guilty" in this case:

s.connection().createStatement().execute("set ansinull on");

This line shouldn't go into the test case, I added it only locally to verify if this option affects the test.

Steve Ebersole
March 21, 2011, 7:05 PM

Bulk closing stale resolved issues

Strong Liu
August 29, 2011, 4:57 PM

add "set ansinull on" in hibernate-parent/pom.xml
ANSI NULL mode – “val = NULL” is unknown when “val” is NULL
The ANSI rule for the where and on clauses return rows that are true, and rejects rows that are both false and unknown.

Assignee

Steve Ebersole

Reporter

Strong Liu

Fix versions

None

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

Priority

Major
Configure