db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bryan Pendleton (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-5933) SQL sorting error
Date Sat, 22 Sep 2012 15:19:07 GMT

    [ 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

Mime
View raw message