Please ensure that the defect Project is correct, the Abstract
is descriptive and follows all standards set out by your
organization, team and sub-team, and the Build Level and
Environment fields in the Testing tab are complete. Also,
please include information about the Install image used if that
is what was used for testing).
1. Problem description
(This section should include a description of the failure you
are opening this defect for, including all symptoms you think
would be interesting to Development and failing routine name
and trace point (if available), SQLCODE/failure during the
operation, and testcase name (if applicable). If you have been
able to isolate the fault yourself, a detailed explanation of
the fault and its location should be included here.)
Incorrect result when using A VIEW or WITH clause containing a
FETCH FIRST n ROWS clause Multiple references to that VIEW or
WITH clause from when using ANSI join syntax;
2. Operating system and level:
ENV: AIX
DB21085I This instance or install (instance name, where
applicable:
"mdsinst1") uses "64" bits and DB2 code release "SQL10054" with
level
identifier "0605010E".
Informational tokens are "DB2 v10.5.0.4", "special_32990",
"IP23616_32990", and
Fix Pack "4".
Product is installed at "/opt/IBM/db2/V10.5.4_sp32990".
3. Client, server, and gateway information
a. Client information, if applicable
Project:
Build Level:
Environment (including Platform and bitness):
b. Gateway information, if applicable
Project:
Build Level:
Environment (including Platform and bitness):
c. Server information, if applicable
Project:
Build Level:
Environment (including Platform and bitness):
4. System setup
(For example, if your database is partitioned, this is where
this information should be included. Provide clearcase view
name, and contents of db2nodes.cfg file, including catalog,
coordinator node information. Also which machine name has which
node number(s) for DPF.)
5. Diagnostic information
(This section should include applicable db2diag.log entries,
stack traces, lineofcode tool output, and formatted dump files.
Also include any other FODC or PD tool output that helped you or
will help Development diagnose the failure you're opening this
defect for. Include the location of all files not attached but
referenced by this defect.)
6. How to reproduce the problem
(Include the name of the bucket and snippet of existing or
planned test unit that exposed the failure described above. A
"repro" should be as small as possible. Also, include the degree
of difficulty of the repro. A repro is Easy if it can be done
on demand, Medium if it can be done after several attempts, and
Hard if it is not possible.)
Problem can be reproduce with following SQL:
=============
create table temp1(tpp_val date,desc varchar(20));
create table temp2(tpp_val date,dsb_nbr varchar(20),usr_nbr
varchar(20),chk_flag char(1));
create table temp3(tpp_val date,dsb_nbr varchar(20),usr_nbr
varchar(20));
create table temp4(tpp_val date,spp_cod varchar(3),spp_val
varchar(3));
insert into temp1 select (date('2014-01-01')+row_number()
over(order by tableid) days),substr(tabname,1,20) from
syscat.tables;
insert into temp2 select (date('2014-01-01')+row_number()
over(order by tableid) days),char(row_number() over(order by
tableid)),char(row_number() over(order by tableid)),'Y' from
syscat.tables;
insert into temp3 select (date('2014-01-01')+row_number()
over(order by tableid) days),char(row_number() over(order by
tableid)),char(row_number() over(order by tableid)) from
syscat.tables;
insert into temp4 select (date('2014-01-01')+row_number()
over(order by tableid) days),char(row_number() over(order by
tableid)),char(row_number() over(order by tableid)) from
syscat.tables;
with tmp_tpp(tpp_val) as (select distinct tpp_val from temp1
where tpp_val <='2014-11-30' order by tpp_val desc fetch first
33 rows only),tmp_rptchksts(tpp_val,chk_flg)
as (select a.tpp_val,'Y' from temp2 a inner join tmp_tpp t on
a.tpp_val=t.tpp_val
left join temp3 b on a.dsb_nbr=b.dsb_nbr ),tmp_dta as (select
a.tpp_val,spp_cod,spp_val from temp4 a inner join tmp_rptchksts
r on a.tpp_val=r.tpp_val where
a.spp_cod is not null and a.spp_val is not null) select
a.tpp_val as rowseq,b.spp_cod from tmp_tpp a left join tmp_dta b
on a.tpp_val=b.tpp_val order by rowseq asc with ur;
=====================
7. Did anything in particular seem to cause this problem?
8. Is this problem a regression? If so, please provide details
on the last successful build level similar to (3) above. |
