Bug in DB2 8.2

[N. Shamsundar]

I made up this trivial example to show up a bug that I encountered in a more complex
application.

When a subquery is used as an inline table which is sorted by one column, the first
nn lines of the result chosen and then sorted by another column, DB2 incorrectly
complains about the number of columns in the result of the subquery and the alias
name for the resulting table. If, however, a dummy column is included in the alias
list, DB2 is content, but the name of the dummy column is not shown in the output!

N. Shamsundar
University of Houston
__________________________________________________ ________________________
create table student
(sno int not null,
lname char(10),
fmname char(10),
strt char(10),
city char(10),
st char(2),
zip char(5),
email char(20)
);

insert into student values
(123,'Adams','John','Via Appia','Roma','IT','12-56','c*****@senate.it'),
(452,'Hun','Atilla','Main St','Jakarta','ID','Ab-DE','s*******@jakr.com');

select * from
(
select lname,fmname,strt,city, st,zip,email
from student s
order by sno desc
fetch first 41 rows only
)
as tbl(Lname,FMname,Strt,City,State,Zip,EMail)
order by lname;

SQL0158N The number of columns specified for "TBL" is not the same as the
number of columns in the result table. SQLSTATE=42811

select * from
(
select lname,fmname,strt,city, st,zip,email
from student s
order by sno desc
fetch first 41 rows only
)
as tbl(Lname,FMname,Strt,City,State,Zip,EMail,xxx)
order by lname;

LNAME FMNAME STRT CITY STATE ZIP EMAIL
---------- ---------- ---------- ---------- ----- ----- --------------------
Adams John Via Appia Roma IT 12-56 ca****@senate.it
Hun Atilla Main St Jakarta ID Ab-DE su******@jakr.com

2 record(s) selected.

S:\ug>db2level
DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL08022" with
level identifier "03030106".
Informational tokens are "DB2 v8.1.9.700", "s050422", "WR21350", and FixPak
"9".
Product is installed at "E:\Applications\DB2".

[Mark Yudkin]

You won't get anywhere reporting a bug here. Open a PMR.

"N. Shamsundar" <sh************@uh.edu> wrote in message
news:dd***********@masala.cc.uh.edu...

I made up this trivial example to show up a bug that I encountered in a
more complex application.

When a subquery is used as an inline table which is sorted by one column,
the first nn lines of the result chosen and then sorted by another column,
DB2 incorrectly complains about the number of columns in the result of the
subquery and the alias name for the resulting table. If, however, a dummy
column is included in the alias list, DB2 is content, but the name of the
dummy column is not shown in the output!

N. Shamsundar
University of Houston
__________________________________________________ ________________________
create table student
(sno int not null,
lname char(10),
fmname char(10),
strt char(10),
city char(10),
st char(2),
zip char(5),
email char(20)
);

insert into student values
(123,'Adams','John','Via Appia','Roma','IT','12-56','c*****@senate.it'),
(452,'Hun','Atilla','Main
St','Jakarta','ID','Ab-DE','s*******@jakr.com');

select * from
(
select lname,fmname,strt,city, st,zip,email
from student s
order by sno desc
fetch first 41 rows only
)
as tbl(Lname,FMname,Strt,City,State,Zip,EMail)
order by lname;

SQL0158N The number of columns specified for "TBL" is not the same as the
number of columns in the result table. SQLSTATE=42811

select * from
(
select lname,fmname,strt,city, st,zip,email
from student s
order by sno desc
fetch first 41 rows only
)
as tbl(Lname,FMname,Strt,City,State,Zip,EMail,xxx)
order by lname;

LNAME FMNAME STRT CITY STATE ZIP EMAIL
---------- ---------- ---------- ---------- ----- ----- --------------------
Adams John Via Appia Roma IT 12-56 ca****@senate.it
Hun Atilla Main St Jakarta ID Ab-DE su******@jakr.com

2 record(s) selected.

S:\ug>db2level
DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL08022"
with
level identifier "03030106".
Informational tokens are "DB2 v8.1.9.700", "s050422", "WR21350", and
FixPak
"9".
Product is installed at "E:\Applications\DB2".

[Christian Maslen]

I'm not sure whether you're looking for a work around or just pointing
out a bug. But if you want a work around:
select * from
(
select ROW_NUMBER() OVER (order by sno desc),
lname, fmname, strt, city, st, zip, email
from student s
)
as tbl(Row_Num, Lname, FMname, Strt, City, State, Zip, EMail)
where Row_Num <= 41
order by lname;

Christian Maslen

[Serge Rielau]

N. Shamsundar wrote:

I made up this trivial example to show up a bug that I encountered in a
more complex application.

When a subquery is used as an inline table which is sorted by one
column, the first nn lines of the result chosen and then sorted by
another column, DB2 incorrectly complains about the number of columns in
the result of the subquery and the alias name for the resulting table.
If, however, a dummy column is included in the alias list, DB2 is
content, but the name of the dummy column is not shown in the output!

<smip>

Please report the bug here:
http://www.developer.ibm.com/us/en/u...olars/support/

Thank you for reporting
Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab