The sql and queries below demonstrate a BUG in derby. I don't have thetime to
learn the official reporting process, but someone may beinterested.
I was modifying a Java application that uses an Oracle DB and to run onan
embedded javaDB and getting different results in Deby. Derby gets itwrong.
Below are 2 versions of the same queries both run in deby showingwith the 2
results and they do not that do not add up!
The difference is that I have divided a long winded case statement into3 parts
and gotten a different result for the whole query than for theparts it is
composed of. The version when I split it into 3 partsreturns the correct answer
- the 1st version returns the wrong answer.
The versions are converted one to another only by changing which linesof the
case statement are commented out changing from a single statementwith 3 clauses
which are "ANDED" together and/or the same 3 clauses independently.
C:\a\rhymis\db\derby>java -jar %DERBY_HOME%\lib\derbyrun.jar
sysinfo------------------ Java Information ------------------Java Version:
1.6.0_35Java Vendor: Sun Microsystems Inc.Java home: C:\Program
Files\Java\jre6Java classpath: c:\Program Files\Sun\JavaDB\lib\derbyrun.jarOS
name: Windows 7OS architecture: x86OS version: 6.1Java user name:
pvirgoJava user home: do not have corparate authorization to show thisJava
user dir: C:\a\rhymis\db\derbyjava.specification.name: Java Platform API
Specificationjava.specification.version: 1.6java.runtime.version:
1.6.0_35-b10--------- Derby Information --------JRE - JDBC: Java SE 6 - JDBC
4.0[C:\Program Files\Sun\JavaDB\lib\derby.jar] 10.6.2.1 - (999685)[C:\Program
Files\Sun\JavaDB\lib\derbytools.jar] 10.6.2.1 - (999685)[C:\Program
Files\Sun\JavaDB\lib\derbynet.jar] 10.6.2.1 - (999685)[C:\Program
Files\Sun\JavaDB\lib\derbyclient.jar] 10.6.2.1 -
(999685)-----------------------------------------------------------------------
Locale Information -----------------Current Locale : [English/United States
[en_US]]Found support for locale: [cs] version: 10.6.2.1 -
(999685)Found support for locale: [de_DE] version: 10.6.2.1 -
(999685)Found support for locale: [es] version: 10.6.2.1 -
(999685)Found support for locale: [fr] version: 10.6.2.1 -
(999685)Found support for locale: [hu] version: 10.6.2.1 -
(999685)Found support for locale: [it] version: 10.6.2.1 -
(999685)Found support for locale: [ja_JP] version: 10.6.2.1 -
(999685)Found support for locale: [ko_KR] version: 10.6.2.1 -
(999685)Found support for locale: [pl] version: 10.6.2.1 -
(999685)Found support for locale: [pt_BR] version: 10.6.2.1 -
(999685)Found support for locale: [ru] version: 10.6.2.1 -
(999685)Found support for locale: [zh_CN] version: 10.6.2.1 -
(999685)Found support for locale: [zh_TW] version: 10.6.2.1 -
(999685)------------------------------------------------------
==========================================================This query shows the
incorrect result in the column
"ok"==========================================================
C:\a\rhymis\db\derby>
SELECT CASE WHEN ((g.bbb_req=0) or ((g.bbb_req=1) and ((be.now_entr >0)
or (bx.now_exit >0)))) -- then 'bbb' else 'bbbNO' end as bbbok,
and-- CASE WHEN ((g.sss_req=0) or ((g.sss_req=1) and
(s.now_contacts >0)))-- then 'sss' else 'sssNO' end as sssok, and
-- CASE WHEN ((g.ttt_req=0) or ((g.ttt_req=1) and ((te.now_entr >0)
or (tx.now_exit >0)))) -- then 'ttt' else 'tttNO' end as tttok,then 'Yes'
else 'No' end as ok, g.cid, -- bbb_req, be.now_entr as bne, be.then_entr
as bte,-- bx.now_exit as bnx, bx.then_exit as btx,-- sss_req,
s.now_contacts as snc, s.then_contacts as stc,-- ttt_req, te.now_entr as tne,
te.then_entr as tte,-- tx.now_exit as tnx, tx.then_exit as ttx,
'notes' as notes FROM comply_grants g LEFT OUTER JOIN comply_bbb_entr be on
g.cid=be.cid LEFT OUTER JOIN comply_bbb_exit bx on g.cid=bx.cid LEFT OUTER JOIN
comply_sss s on g.cid=s.cid LEFT OUTER JOIN comply_ttt_entr te on g.cid=te.cid
LEFT OUTER JOIN comply_ttt_exit tx on g.cid=tx.cid where g.cid like ('01MA%')
ORDER by 1, 2, 3;
RESULTS (minus hundreds of errors about null values - even though only 8 rows
satisfied the Where clause)
WARNING 01003: Null.No |01MA0008|notesNo |01MA0010|notesNo |01MA0018|notesNo
|01MA0019|notesNo |01MA0021|notesNo |01MA0024|notesYes|01MA0004|notes
8 rows selectedij> --WARNING 01003: Null values were eli
==========================================================This query shows the
correct results in the columns bbbok,sssok, and tttok. When they are all bbb,
sss,and ttt the OK column above should also have been
ok.==========================================================
SELECT CASE WHEN ((g.bbb_req=0) or ((g.bbb_req=1) and ((be.now_entr >0)
or (bx.now_exit >0)))) then 'bbb' else 'bbbNO' end as bbbok,
-- and CASE WHEN ((g.sss_req=0) or ((g.sss_req=1) and
(s.now_contacts >0))) then 'sss' else 'sssNO' end as sssok,-- and CASE
WHEN ((g.ttt_req=0) or ((g.ttt_req=1) and ((te.now_entr >0) or
(tx.now_exit >0)))) then 'ttt' else 'tttNO' end as tttok,-- then
'Yes' else 'No' end as ok, g.cid, -- bbb_req, be.now_entr as bne,
be.then_entr as bte,-- bx.now_exit as bnx, bx.then_exit as btx,--
sss_req, s.now_contacts as snc, s.then_contacts as stc,-- ttt_req,
te.now_entr as tne, te.then_entr as tte,-- tx.now_exit as tnx, tx.then_exit
as ttx, 'notes' as notes FROM comply_grants g LEFT OUTER JOIN
comply_bbb_entr be on g.cid=be.cid LEFT OUTER JOIN comply_bbb_exit bx on
g.cid=bx.cid LEFT OUTER JOIN comply_sss s on g.cid=s.cid LEFT OUTER JOIN
comply_ttt_entr te on g.cid=te.cid LEFT OUTER JOIN comply_ttt_exit tx on
g.cid=tx.cid where g.cid like ('01MA%') ORDER by 1, 2, 3;
RESULTS (minus hundreds of errors about null values - even though only 8 rows
satisfied the Where clause)
WARNING 01003: Null values were eliminate.
bbb |sss |ttt |01MA0018|notesbbb |sss |ttt |01MA0008|notesbbb |sss |ttt
|01MA0004|notesbbb |sssNO|ttt |01MA0021|notesbbbNO|sss |ttt
|01MA0024|notesbbbNO|sssNO|ttt |01MA0010|notesbbbNO|sssNO|tttNO|01MA0002|notes
8 rows selectedij> --WARNING 01003: Null values were eli
The first three rows above should all have be "Ok" in the first query!
