[
https://issues.apache.org/jira/browse/DERBY-5933?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13461188#comment-13461188
]
Bryan Pendleton commented on DERBY-5933:
----------------------------------------
I think it must be related to the outer join. If you remove the outer join,
the optimizer tosses a SortResultSet as the top-most result set in the
query plan, giving the right results:
select a1
from
a inner join b on a3 = b1
inner join d on a2 = d1
where b1 = 1
order by a1;
A1
-----------
1
1
2
3 rows selected
And if you populate table C with some matching values, and
then just change "left outer" to "inner" in the query, it again
gives the correct results:
insert into c values (3), (4), (2);
select a1
from
a inner join b on a3 = b1
inner join c on a4 = c1
inner join d on a2 = d1
where b1 = 1
order by a1;
A1
-----------
1
1
2
3 rows selected
But even with the data present in C, the OUTER JOIN query doesn't perform the
sort.
Actually, if you run repro.sql with -Dderby.language.logQueryPlan=true, you can
see
that if the OUTER JOIN is missing, the optimizer chooses a tree full of table
scans,
nested loop joins, and an outer-most SortResultSet at the end, while with the
OUTER
JOIN in place, the query plan is entirely comprised of HashJoin nodes.
That is, the two query plans are wildly different, just by changing "left
outer" to "inner"
in the query.
Not sure if any of this helps, just wanted to share it.
> SQL sorting error
> -----------------
>
> Key: DERBY-5933
> URL: https://issues.apache.org/jira/browse/DERBY-5933
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.0.2.1, 10.1.1.0, 10.2.1.6, 10.3.1.4, 10.4.1.3,
> 10.5.1.1, 10.6.1.0, 10.7.1.1, 10.8.1.2, 10.9.1.0
> Environment: Windows 7 Netbeans JDBC GUI
> Reporter: Vlasov Igor
> Labels: derby_triage10_10
> Attachments: Helpdesk.zip, repro.sql, right_sorting.png,
> wrong_sorting.png
>
>
> Hello
> I have a simple database with 100 records.
> I am running a SQL query from Netbeans GUI though JDBC
> This query was generated by Hibernate ORM.
> In certain circumstances the result rowset is not sorting.
> When I use condition morefld2_.mf_id in (5) the result is unsortable.
> When I use condition morefld2_.mf_id in (5,0) the result is sorting properly.
>
>
--
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
