db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@oracle.com>
Subject Re: Derby sql bug
Date Tue, 09 Oct 2012 14:00:14 GMT
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!
>


Mime
View raw message