[
https://issues.apache.org/jira/browse/DERBY-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Lily Wei updated DERBY-4331:
----------------------------
Attachment: orderby4331.zip
notorderby4331.zip
Taking out Author table from previous query still not ordering in trunk but
works 10.5 (783167)
ij> SELECT CS.ID
FROM --DERBY-PROPERTIES joinOrder=FIXED
REPOSITORIES R --DERBY-PROPERTIES constraint=REPOSITORIES_PATH
, FILES F --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH
, FILECHANGES FC --DERBY-PROPERTIES constraint=FILECHANGES_FILE_CHANGESET
, CHANGESETS CS --DERBY-PROPERTIES constraint=CHANGESETS_PRIMARY_ID
WHERE
R.PATH = '/var/tmp/source5923202038296723704opengrok/mercurial' AND
F.REPOSITORY = R.ID AND
CS.REPOSITORY = R.ID AND
CS.ID = FC.CHANGESET AND
F.ID = FC.FILE AND
EXISTS (
SELECT 1
FROM FILES F2 --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH
WHERE
F2.ID = FC.FILE)
ORDER BY CS.ID DESC;
ID
-----------
1
2
3
2
2
3
If take out of 'CS.ID = FC.CHANGESET' from where cause constraint, the query
will perform order by on both trunk and 10.5(783167)
ij> SELECT CS.ID
FROM --DERBY-PROPERTIES joinOrder=FIXED
REPOSITORIES R --DERBY-PROPERTIES constraint=REPOSITORIES_PATH
, FILES F --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH
, FILECHANGES FC --DERBY-PROPERTIES constraint=FILECHANGES_FILE_CHANGESET
, CHANGESETS CS --DERBY-PROPERTIES constraint=CHANGESETS_PRIMARY_ID
WHERE
R.PATH = '/var/tmp/source5923202038296723704opengrok/mercurial' AND
F.REPOSITORY = R.ID AND
CS.REPOSITORY = R.ID AND
F.ID = FC.FILE AND
EXISTS (
SELECT 1
FROM FILES F2 --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH
WHERE
F2.ID = FC.FILE)
ORDER BY CS.ID DESC;
ID
-----------
3
3
3
3
3
3
2
2
2
2
2
2
1
1
1
1
1
1
18 rows selected
I cannot really tell what is the difference between the two path. However, the
value -1 for Tree height on the first query seems odd to me.
The derby.log for the first query is on notorderby4331.zip and the second query
is on orderby4331.zip.
(The below capture from derby.log on notorderby4331.zip)
User supplied optimizer
overrides on REPOSITORIES are { index=SQL090803170751640 }
Index Scan ResultSet for
REPOSITORIES using constraint REPOSITORIES_PATH at read committed isolation
level using share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 1
Rows filtered = 0
Fetch Size = 1
constructor time
(milliseconds) = 0
open time
(milliseconds) = 0
next time
(milliseconds) = 0
close time
(milliseconds) = 0
next time in
milliseconds/row = 0
scan information:
Bit set of columns
fetched=All
Number of columns
fetched=2
Number of deleted rows
visited=0
Number of pages
visited=1
Number of rows
qualified=1
Number of rows visited=1
Scan type=btree
Tree height=-1
<<<==== -1 is an odd value
start position:
>= on first 1 column(s).
(The below capture from derby.log on orderby4331.zip)
User supplied optimizer
overrides on REPOSITORIES are { index=SQL090803170751640 }
Index Scan ResultSet
for REPOSITORIES using constraint REPOSITORIES_PATH at read committed isolation
level using share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 1
Rows filtered = 0
Fetch Size = 1
constructor
time (milliseconds) = 0
open time
(milliseconds) = 0
next time
(milliseconds) = 0
close time
(milliseconds) = 0
next time in
milliseconds/row = 0
scan information:
Bit set of
columns fetched=All
Number of
columns fetched=2
Number of
deleted rows visited=0
Number of pages
visited=1
Number of rows
qualified=1
Number of rows
visited=1
Scan type=btree
Tree height=1
<<<==== not -1 value
start position:
>= on first 1 column(s).
Hope this info is helpful. Thanks, Lily
> Join returns results in wrong order
> -----------------------------------
>
> Key: DERBY-4331
> URL: https://issues.apache.org/jira/browse/DERBY-4331
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.5.2.0, 10.6.0.0
> Reporter: Knut Anders Hatlen
> Attachments: notorderby4331.zip, orderby4331.zip, repro.sql,
> repro2.sql, repro2_qryplan.txt
>
>
> In Derby 10.5.2.0, the query below started returning results in wrong order
> (note the ORDER BY clause). Derby 10.5.1.1 gives the expected ordering.
> ij> SELECT CS.ID
> FROM
> CHANGESETS CS, FILECHANGES FC, REPOSITORIES R, FILES F, AUTHORS A
> WHERE
> R.PATH = '/var/tmp/source5923202038296723704opengrok/mercurial' AND
> F.REPOSITORY = R.ID AND A.REPOSITORY = R.ID AND
> CS.REPOSITORY = R.ID AND CS.ID = FC.CHANGESET AND F.ID = FC.FILE AND
> A.ID = CS.AUTHOR AND
> EXISTS (
> SELECT 1
> FROM FILES F2
> WHERE
> F2.ID = FC.FILE AND F2.REPOSITORY = R.ID AND
> F2.PATH LIKE '/%' ESCAPE '#')
> ORDER BY CS.ID DESC;
> ID
> -----------
> 1
> 2
> 3
> 2
> 2
> 3
> 6 rows selected
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
