Some Eclipse Foundation services are deprecated, or will be soon. Please ensure you've read this important communication.

Bug 331642

Summary:	Query output diferse when using Parameters
Product:	z_Archived	Reporter:	Martijn Cremer <martijn.cremer>
Component:	BIRT	Assignee:	Birt-Data-inbox <Birt-Data-inbox>
Status:	RESOLVED FIXED	QA Contact:	Hao Zhou <hao.zhou>
Severity:	normal
Priority:	P3	CC:	bluesoldier, lzhu
Version:	unspecified
Target Milestone:	---
Hardware:	PC
OS:	All
Whiteboard:

Description Martijn Cremer

2010-12-02 05:23:08 EST

Build Identifier: v20100915-1750


While using a query on a Oracle 10 environment I test a query i get difrent results when I introduce the parameter.

Example query:

SELECT MAX(DECODE(ROWNUM,1, valu)) var 1,
       MAX(DECODE(ROWNUM,2, valu)) var2,
       MAX(DECODE(ROWNUM,3, valu)) var3,
       (SELECT * FROM GLOBAL_NAME) AS db_name
  FROM (SELECT mpar.mpar_modu_module,
               mpar.mpar_modu_numversie,
               mpar.mpar_parm_cod,
               NVL(runp.runp_wrd,mpar.mpar_defaultwaarde) valu
          FROM sy_moduleparameters mpar,
               sy_runparameters runp
         WHERE mpar.mpar_modu_module = runp.runp_mpar_modu_module
           AND mpar.mpar_modu_numversie = runp.runp_mpar_modu_numversie
           AND mpar.mpar_parm_cod = runp.runp_mpar_parm_cod
           AND runp.runp_mpar_modu_module = ‘static parameter’
           AND runp.runp_mpar_modu_numversie = 1
           AND mpar.mpar_parm_cod IN ('var1',' var2',' var3'))
 GROUP BY TRUNC(MOD(rownum,9)/9)

this gives a result like:

var 1 | var 2 | var 3| DB_Name |
--------------------------------
  A   |   B   |   C  | DB_Name |

This is the rusult I get when I do not use a parameter or when I run it trough my sql develper etc.

But when I introduce a bouded parameter to the query like:

  FROM (SELECT mpar.mpar_modu_module,
               mpar.mpar_modu_numversie,
               mpar.mpar_parm_cod,
               NVL(runp.runp_wrd,mpar.mpar_defaultwaarde) valu
          FROM sy_moduleparameters mpar,
               sy_runparameters runp
         WHERE mpar.mpar_modu_module = runp.runp_mpar_modu_module
           AND mpar.mpar_modu_numversie = runp.runp_mpar_modu_numversie
           AND mpar.mpar_parm_cod = runp.runp_mpar_parm_cod
           AND runp.runp_mpar_modu_module = ?
           AND runp.runp_mpar_modu_numversie = 1
           AND mpar.mpar_parm_cod IN ('var1',' var2',' var3'))
 GROUP BY TRUNC(MOD(rownum,9)/9)

this gives a result like:

var 1 | var 2 | var 3| DB_Name |
--------------------------------
  C   |   A   |   B  | DB_Name |

This is only when I use a ? in the query. Som how introducing a ? to the query BIRT seems to change how the query is parsed to the database.

Reproducible: Always

Comment 1 Xiaoying Gu

2011-03-23 06:05:35 EDT

Can not reproduce this issue with BIRT 2.6.1 all-in-one with Oracle 11g.

I tried with a similar SQL sentense:

select MAX(DECODE(ROWNUM,1, value)) var1,
       MAX(DECODE(ROWNUM,2, value)) var2,
       MAX(DECODE(ROWNUM,3, value)) var3
from ( select C_NATIONKEY value
from customer
where C_CUSTKEY < ?)
group by TRUNC(MOD(rownum,9)/9)

The output value remains same, no matter use parameter or not in query text.

Comment 2 Martijn Cremer

2011-03-23 06:18:01 EDT

The only difrence I can see is the Oracle variant and the your using a < instead of the =.

We ended working around this problem by adding a order by in the inner query. Yet if I remove this it still changes the result set somtimes.

Comment 3 Xiaoying Gu

2011-08-25 02:06:38 EDT

I can't reproduce this issue with latest birt in Oracle 11g.
Could you provide more detail information about how to reproduce it?

Comment 4 Xiaoying Gu

2011-08-28 23:00:48 EDT

Please see comment3