db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Phil Virgo <pwvi...@outlook.com>
Subject Derby sql bug
Date Fri, 05 Oct 2012 13:58:00 GMT
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!
 		 	   		  
Mime
View raw message