Uncorrect calculation of Select with case statement
---------------------------------------------------
Key: CORE-3565
URL: http://tracker.firebirdsql.org/browse/CORE-3565
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 2.0.6, 2.1.3, 2.1.2, 2.0.5
Environment: Windows
Reporter: Dmitry Lendel
Query
select
Rem.iddepot,
Rem.idcommodity,
case :qq1
when 1 then Rem.costprice
else null end as f1,
case :qq2
when 1 then Rem.saleprice
else null end as f2,
case :qq3
when 1 then Rem.usebydate
else null end as f3,
case :qq4
when 1 then Rem.idcolor
else null end as f4,
case :qq5
when 1 then Rem.barcode
else null end as f5,
case :qq6
when 1 then Rem.param1
else null end as f6,
case :qq7
when 1 then Rem.param2
else null end as f7,
Sum(Rem.QUANTITY)
from remainder Rem
where IDDate<=:LIDDate
group by 1,2,3,4,5,6,7,8,9
having Sum(Rem.QUANTITY)>0
returns uncorrect result if fields idcolor or usebydate have null and non null
values
for example 0,0,null,1,0,0 and so on
if to change query with code
case :qq3
when 1 then coalesce(Rem.usebydate,current_date )
else null end as f3,
case :qq4
when 1 then coalesce(Rem.idcolor,0)
else null end as f4,
result will correct
I can send database with example.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Got Input? Slashdot Needs You.
Take our quick survey online. Come on, we don't ask for help often.
Plus, you'll get a chance to win $100 to spend on ThinkGeek.
http://p.sf.net/sfu/slashdot-survey
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
