[DERBY-4032] Record not found in some SQL

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Cannot Reproduce
• Affects Version/s: 10.3.2.1
• Component/s: SQL
• Environment: Windows XP
• Urgency: Normal
• Bug behavior facts: Data corruption, Seen in production, Wrong query result
• Assignee: Unassigned
• Reporter: Chip Hartney
• Created: 28/Jan/09 16:01
• Updated: 24/Mar/10 16:24
• Resolved: 24/Mar/10 16:24

Description

Per discussion at http://www.nabble.com/Record-not-found-in-some-SQL---Bug--td21700110.html...

I have a "Product" table with a "Num" column that contains a record that is only accessible by some SQL and not others. I have tested this by JDBC access from my Java app as well was from IJ directly.

ij> select "Num", length("Num") as "Len" from app."Product" where "Num" like 'HG1549%';
Num |Len
----------------------------
HG15490 |7
HG15493 |7
HG15497 |7 <== Found as expected
HG15499 |7
4 rows selected

ij> select "Num" from app."Product" where "Num" = 'HG15490';
Num
----------------
HG15490 <== Found as expected
1 row selected

ij> select "Num" from app."Product" where "Num" = 'HG15493';
Num
----------------
HG15493 <== Found as expected
1 row selected

ij> select "Num" from app."Product" where "Num" = 'HG15499';
Num
----------------
HG15499 <== Found as expected
1 row selected

ij> select "Num" from app."Product" where "Num" = 'HG15497';
Num
----------------
0 rows selected <== Not found!!!

What could possibly hide the 'HG15497' record from the last SELECT?

And it's not just a matter of equality versus inequality...as the following SQL does return the record:
SELECT I."STYLE" FROM TEMP."ZJVINV2" AS I INNER JOIN APP."Product" AS P ON I."STYLE" = P."Num" WHERE I."STYLE" = 'HG15497';