[
https://issues.apache.org/jira/browse/DERBY-3997?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Knut Anders Hatlen updated DERBY-3997:
--------------------------------------
Derby Info: (was: [Patch Available])
Fix Version/s: 10.5.0.0
Thanks for looking at the patch, Bryan, and for the extra test cases.
I added some variations of your test cases and a test case for dynamic columns
(see below) and committed revision 730188. I plan to back-port the fix to 10.4
so I'm leaving the issue open.
-- DERBY-3997: Elimination of ORDER BY clause because all the columns
-- to order by were known to be constant, made extra columns appear in
-- the result.
create table d3997(x int, y int, z int);
-- These queries used to have two result columns, but should only have one
select 1 from d3997 where x=1 order by x;
select y from d3997 where x=1 order by x;
-- Used to have three columns, should only have two
select y,z from d3997 where x=1 order by x;
-- Used to have three columns, should only have one
select x from d3997 where y=1 and z=1 order by y,z;
-- Dynamic parameters are also constants (expect one column)
execute 'select x from d3997 where y=? order by y' using 'values 1';
-- Order by columns should not be removed from the result here
select * from d3997 where x=1 order by x;
select x,y,z from d3997 where x=1 order by x;
select x,y,z from d3997 where x=1 and y=1 order by x,y;
-- Order by should not be eliminated here (not constant values). Insert some
-- data in reverse order to verify that the results are sorted.
insert into d3997 values (9,8,7),(6,5,4),(3,2,1);
select * from d3997 where y<>2 order by y;
select z from d3997 where y>2 order by y;
drop table d3997;
> ORDER BY causes column to be returned
> -------------------------------------
>
> Key: DERBY-3997
> URL: https://issues.apache.org/jira/browse/DERBY-3997
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.4.1.3
> Environment: Mac OS 10.4, JDK 1,6
> Reporter: geoff hendrey
> Assignee: Knut Anders Hatlen
> Fix For: 10.5.0.0
>
> Attachments: d3997.diff, d3997.stat
>
>
> The ORDER BY is causing the ordered column to be retrieved even though it is
> not part of the SELECT clause. Here is a script to create a table, insert a
> row, and perform the select:
> CREATE TABLE "REVIEWS"."GEOFF__REVIEWS__REVIEW"
> (
> PK INTEGER PRIMARY KEY not null,
> numstars BIGINT,
> body VARCHAR(32672),
> title VARCHAR(32672),
> authoremail VARCHAR(32672)
> );
> INSERT INTO "REVIEWS"."GEOFF__REVIEWS__REVIEW"
> (PK,numstars,body,title,authoremail) VALUES (0 /*not
> nullable*/,0,'s','s','s');
> SELECT "review"."numstars"
> FROM
> "GEOFF__REVIEWS__REVIEW" AS "review"
> WHERE
> "review"."PK" = 1
> ORDER BY
> "review".PK
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
