On 10/5/12 6:58 AM, Phil Virgo wrote:
The sql and queries below demonstrate a BUG in derby. I don't have the
time to learn the official reporting process, but someone may be
interested.
Thanks, Phil. Could you provide an ij script which demonstrates the
problem? That would include at least a CREATE TABLE statement to declare
the table, an INSERT statement to put enough data into the table to
trigger the problem, and then the SELECTs which demonstrate the bug.
Having a reproducible script will make it more likely that the bug will
be addressed in one of the upcoming maintenance releases (10.8.3 and
10.9.2).
Thanks,
-Rick
I was modifying a Java application that uses an Oracle DB and to run on
an embedded javaDB and getting different results in Deby. Derby gets it
wrong.
Below are 2 versions of the same queries both run in deby showing
with the 2 results and they do not that do not add up!
The difference is that I have divided a long winded case statement into
3 parts and gotten a different result for the whole query than for the
parts it is composed of. The version when I split it into 3 parts
returns the correct answer - the 1st version returns the wrong answer.
The versions are converted one to another only by changing which lines
of the case statement are commented out changing from a single statement
with 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_35
Java Vendor: Sun Microsystems Inc.
Java home: C:\Program Files\Java\jre6
Java classpath: c:\Program Files\Sun\JavaDB\lib\derbyrun.jar
OS name: Windows 7
OS architecture: x86
OS version: 6.1
Java user name: pvirgo
Java user home: do not have corparate authorization to show this
Java user dir: C:\a\rhymis\db\derby
java.specification.name: Java Platform API Specification
java.specification.version: 1.6
java.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|notes
No |01MA0010|notes
No |01MA0018|notes
No |01MA0019|notes
No |01MA0021|notes
No |01MA0024|notes
Yes|01MA0004|notes
8 rows selected
ij> --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|notes
bbb |sss |ttt |01MA0008|notes
bbb |sss |ttt |01MA0004|notes
bbb |sssNO|ttt |01MA0021|notes
bbbNO|sss |ttt |01MA0024|notes
bbbNO|sssNO|ttt |01MA0010|notes
bbbNO|sssNO|tttNO|01MA0002|notes
8 rows selected
ij> --WARNING 01003: Null values were eli
The first three rows above should all have be "Ok" in the first query!
