[
https://issues.apache.org/jira/browse/DERBY-6148?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Dag H. Wanvik closed DERBY-6148.
--------------------------------
> Wrong sort elimination when using permuted join order
> -----------------------------------------------------
>
> Key: DERBY-6148
> URL: https://issues.apache.org/jira/browse/DERBY-6148
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.3.3.0,
> 10.4.1.3, 10.4.2.0, 10.5.1.1, 10.5.2.0, 10.5.3.0, 10.6.1.0, 10.6.2.1,
> 10.7.1.1, 10.8.1.2, 10.8.2.2, 10.8.3.0, 10.9.1.0, 10.10.1.1
> Environment: Windows 7 64-bit, Ubuntu 32-bit; JRE 1.7.0_11+
> Reporter: John English
> Assignee: Dag H. Wanvik
> Fix For: 10.8.3.1, 10.9.2.2, 10.10.1.2, 10.11.0.0
>
> Attachments: derby-6148-1.diff, derby-6148-1.status,
> derby-6148-3.diff, derby-6148-3.status, preprocessed1.txt, preprocessed2.txt,
> q1.txt, q2.txt, queryplans.txt
>
>
> I have a query that looks like this:
> SELECT tests.id,tests.item,title FROM tests,item_usage
> WHERE username=? AND user_role>?
> AND item_usage.item=tests.item
> ORDER BY tests.item,title
> The result ordering is by item code followed by title, but the item codes are
> listed in the order in which they appear in the ITEMS table where they are
> the primary key rather than in ascending order as expected. If however I
> change the ORDER BY clause to sort by item_usage.item rather than tests.item,
> it works correctly, even though the two values are the same!
> The same thing happens in another unrelated query involving item_usage, and
> the same workaround cures it.
> The relevant tables are defined like so:
> CREATE TABLE item_usage (
> username VARCHAR(15) NOT NULL,
> item VARCHAR(15) NOT NULL,
> value SMALLINT DEFAULT 0,
> CONSTRAINT item_usage_pk PRIMARY KEY (username,item),
> CONSTRAINT item_usage_1 FOREIGN KEY (username)
> REFERENCES users(username)
> ON DELETE CASCADE,
> CONSTRAINT item_usage_2 FOREIGN KEY (item)
> REFERENCES items(item)
> ON DELETE CASCADE,
> CONSTRAINT item_usage_3 CHECK (value BETWEEN 0 AND 4)
> );
> CREATE TABLE tests (
> id INTEGER GENERATED ALWAYS AS IDENTITY,
> item VARCHAR(15) NOT NULL,
> title VARCHAR(255) NOT NULL,
> disp SMALLINT NOT NULL DEFAULT 0,
> starttime TIMESTAMP DEFAULT NULL,
> endtime TIMESTAMP DEFAULT NULL,
> offsetx INTEGER NOT NULL DEFAULT 0,
> offsety INTEGER NOT NULL DEFAULT 0,
> rate INTEGER NOT NULL DEFAULT 0,
> duration INTEGER NOT NULL DEFAULT 0,
> calibrate INTEGER NOT NULL DEFAULT 0,
> deadline TIMESTAMP DEFAULT NULL,
> stepsize INTEGER NOT NULL DEFAULT 0,
> interval INTEGER NOT NULL DEFAULT 0,
> stand CHAR(1) DEFAULT NULL,
> hidden CHAR(1) DEFAULT NULL,
> repeated CHAR(1) DEFAULT NULL,
> private CHAR(1) DEFAULT NULL,
> sequential CHAR(1) DEFAULT NULL,
> final CHAR(1) DEFAULT NULL,
> notes CLOB DEFAULT NULL,
> testxml CLOB NOT NULL,
> author VARCHAR(15) NOT NULL,
> time TIMESTAMP NOT NULL,
> CONSTRAINT tests_pk PRIMARY KEY (id),
> CONSTRAINT tests_1 UNIQUE (item, title),
> CONSTRAINT tests_2 FOREIGN KEY (item)
> REFERENCES items(item)
> ON DELETE CASCADE,
> CONSTRAINT tests_3 CHECK (disp BETWEEN 0 AND 100),
> CONSTRAINT tests_4 CHECK (rate BETWEEN 0 AND 100),
> CONSTRAINT tests_5 CHECK (stepsize BETWEEN 0 AND 100)
> );
> If I run the query manually I get this, as expected:
> ID ITEM TITLE
> 37 60001 Test 1
> 42 60001 Test 2
> 51 60001 Test 3
> 17 61303 Test 2a
> 16 61303 Test 2b
> 7 7205731 Test 2a
> 8 7205731 Test 2b
> Now, this is actually part of a web app that should turn this into a list of
> options in a <select> item using the following code:
> while (query.next()) {
> println("<option value='" + query.getInt("id") + "'>"
> + encode(query.getString("item") + ": "
> + query.getString("title")) + "</option>");
> }
> What I actually get is this:
> <option value="17">61303: Test 2a</option>
> <option value="16">61303: Test 2b</option>
> <option value="7">7205731: Test 2a</option>
> <option value="8">7205731: Test 2b</option>
> <option value="37">60001: Test 1</option>
> <option value="42">60001: Test 2</option>
> <option value="51">60001: Test 3</option>
> The results are sorted by item then by title, but the item order is the order
> in which they were originally inserted into the items table (where the item
> and item description are stored, referenced by item_usage.item) rather than
> by item code.
> I've tried to reproduce this behaviour in a simple example, but without
> success. I have logged the query plans for both versions; the log output is
> as follows, with the INCORRECT query (using ORDER BY tests.item) followed
> later by the CORRECT query (using ORDER BY item_usage.item):
> (moved queryplans to attachment; see attachment queryplans.txt -dagw)
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
