db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta A. Satoor (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-3926) Incorrect ORDER BY caused by index
Date Thu, 23 Apr 2009 20:13:30 GMT

    [ https://issues.apache.org/jira/browse/DERBY-3926?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12702085#action_12702085
] 

Mamta A. Satoor commented on DERBY-3926:
----------------------------------------

wisconsin test was showing diffs after I commented out the code in OrderByList (code through
504-533). The diffs were for 7 queries and for those 7 queries, now the plan picked does a
sorting (prior to my changes, the sorting was getting avoided). wisconsin test only does query
plan dump, it does not check the actual data returned for those queries. For the 7 queries
that changed, I added a check to dump the data returned from those queries. Rerunning wisconsin
with and without my code changes atleast confirms that the data returned because of the additional
sorting node has not been affected. All of these 7 queries involved multiple tables in the
FROM list and they had ORDER BY clause. (I have included the 7 queries below for reference).

I went through all the queries in wisconsin test and see that there are still quite a few
queries (even the ones with more than one table in the FROM list and have ORDER BY) that do
not have sorting node on top of their query plan because of the commenting of the code. One
of such query example is
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	 where TENKTUP1.unique1 = TENKTUP2.unique1
	 and TENKTUP2.unique1 < 6000
	 order by TENKTUP1.unique1';


BTW, the 7 queries that have changed their plans so that they now require sorting are as follows(Note
that the comment for the query 2 "says that sort avoidance with joins and order by on columns
in different tables". Well, with other change in the code, we are not avoiding sort anymore)
1)
ij> -- one row from joining table
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	 where TENKTUP1.unique2 = TENKTUP2.unique2
	 and TENKTUP2.unique1 = 0
	 order by TENKTUP1.unique1';

2)
ij> -- Sort avoidance with joins and order by on columns in different tables
--
-- order on joining columns
get cursor c as
	'select * from TENKTUP1, TENKTUP2
	 where TENKTUP1.unique1 = TENKTUP2.unique1
	 order by TENKTUP1.unique1, TENKTUP2.unique1';

3)
ij> get cursor c as
	'select * from TENKTUP1, TENKTUP2
	 where TENKTUP1.unique1 = TENKTUP2.unique1
	 and TENKTUP1.unique2 = 0
	 and TENKTUP2.unique2 = 0
	 order by TENKTUP1.unique1, TENKTUP2.unique1';

4)
ij> get cursor c as
	'select * from TENKTUP1, TENKTUP2
	 where TENKTUP1.unique1 = TENKTUP2.unique1
	 and TENKTUP1.unique2 < 6000
	 and TENKTUP2.unique2 = 0
	 order by TENKTUP1.unique1, TENKTUP2.unique1';

5)
ij> get cursor c as
	'select * from TENKTUP1, TENKTUP2
	 where TENKTUP1.unique1 = TENKTUP2.unique1
	 and TENKTUP1.unique2 < 6000
	 and TENKTUP2.unique2 = 0
	 order by TENKTUP1.unique2, TENKTUP2.unique2';

6)
ij> get cursor c as
	'select * from TENKTUP1, TENKTUP2, ONEKTUP
	 where TENKTUP1.unique1 = TENKTUP2.unique1
	 and ONEKTUP.unique1 = TENKTUP1.unique1
	 and TENKTUP1.unique2 = 0
	 and TENKTUP2.unique2 = 0
	 order by TENKTUP1.unique1, TENKTUP2.unique1';

7)
ij> get cursor c as
	'select * from TENKTUP1, TENKTUP2, ONEKTUP
	 where TENKTUP1.unique2 = TENKTUP2.unique2
	 and ONEKTUP.unique2 = TENKTUP1.unique2
	 and TENKTUP1.unique2 = 0
	 and TENKTUP2.unique2 = 0
	 order by TENKTUP1.unique1, TENKTUP2.unique1';



> Incorrect ORDER BY caused by index
> ----------------------------------
>
>                 Key: DERBY-3926
>                 URL: https://issues.apache.org/jira/browse/DERBY-3926
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.1.3.3, 10.2.3.0, 10.3.3.1, 10.4.2.0
>            Reporter: Tars Joris
>            Assignee: Mamta A. Satoor
>         Attachments: derby-reproduce.zip
>
>
> I think I found a bug in Derby that is triggered by an index on a large column: VARCHAR(1024).
I know it  is generally not a good idea to have an index on such a large column.
> I have a table (table2) with a column "value", my query orders on this column but the
result is not sorted. It is sorted if I remove the index on that column.
> The output of the attached script is as follows (results should be ordered on the middle
column):
> ID                  |VALUE        |VALUE
> ----------------------------------------------
> 2147483653          |000002       |21857
> 2147483654          |000003       |21857
> 4294967297          |000001       |21857
> While I would expect:
> ID                  |VALUE        |VALUE
> ----------------------------------------------
> 4294967297          |000001       |21857
> 2147483653          |000002       |21857
> 2147483654          |000003       |21857
> This is the definition:
> CREATE TABLE table1 (id BIGINT NOT NULL, PRIMARY KEY(id));
> CREATE INDEX key1 ON table1(id);
> CREATE TABLE table2 (id BIGINT NOT NULL, name VARCHAR(40) NOT NULL, value VARCHAR(1024),
PRIMARY KEY(id, name));
> CREATE UNIQUE INDEX key2 ON table2(id, name);
> CREATE INDEX key3 ON table2(value);
> This is the query:
> SELECT table1.id, m0.value, m1.value
> FROM table1, table2 m0, table2 m1
> WHERE table1.id=m0.id
> AND m0.name='PageSequenceId'
> AND table1.id=m1.id
> AND m1.name='PostComponentId'
> AND m1.value='21857'
> ORDER BY m0.value;
> The bug can be reproduced by just executing the attached script with the ij-tool.
> Note that the result of the query becomes correct when enough data is changed. This prevented
me from creating a smaller example.
> See the attached file "derby-reproduce.zip" for sysinfo, derby.log and script.sql.
> Michael Segel pointed out:
> "It looks like its hitting the index ordering on id,name from table 2 and is ignoring
the order by clause."

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message