db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dag H. Wanvik (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-4695) Internal assignment of tablenumer, columnnumber looks wrong in query tree, although no ill effects are seen.
Date Thu, 17 Jun 2010 15:40:24 GMT

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

Dag H. Wanvik commented on DERBY-4695:
--------------------------------------

Performing the rebinding block only for HalfOuterJoinNode (subclass of JoinNode), makes the
outer joins pass again, and removes the inconsistent binding seen in the original query of
this issue and JoinTest still passes.

It is still unclear to me why outer join should need this rebinding code, though, but not
inner joins (at least those we test in JoinTest).




> Internal assignment of tablenumer, columnnumber looks wrong in query tree, although no
ill effects are seen.
> ------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-4695
>                 URL: https://issues.apache.org/jira/browse/DERBY-4695
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.7.0.0
>            Reporter: Dag H. Wanvik
>            Assignee: Dag H. Wanvik
>            Priority: Minor
>         Attachments: derby.log
>
>
> While looking into derby-4679, I also looked at the query in
> derby-2526 to validate that my changes also worked for that variant of
> the problem.
> During inspection of the query trees after the bind phase, I found one
> instance in which the pair (tablenumber, columnnumber) of a column
> reference was wrong. Although it did not seem to impact the query
> result, I note it here as as something we should probably investiate
> as it may be a symptom an underlying problem, or a potential for
> future problems.
> The query looks like this:
> select b3.* from b3 join bvw on (b3.c8 = bvw.c5) join b4 on (bvw.c1 = b4.c7) where b4.c4
= 42"
> and the underlying DDL is this:
> create table b2 (c1 int, c2 int, c3 char(1), c4 int, c5 int, c6 int);
> create table b4 (c7 int, c4 int, c6 int);
> create table b3 (c8 int, c9 int, c5 int, c6 int);
> create table b (c1 int, c2 int, c3 char(1), c4 int, c5 int, c6 int);
> create view bvw (c5, c1 ,c2 ,c3 ,c4) as
> select c5, c1 ,c2 ,c3 ,c4 from b2 union
> select c5, c1 ,c2 ,c3 ,c4 from b;
> create view bvw2 (c1 ,c2 ,c3 ,c4 ,c5) as
> After the bind phase, the join clause "bvw.c1 = b4.c7" has the
> following entry for the column reference bvw.C1:
>           
>           tableNumber: 1
>           columnNumber: 6
> The problem is that the node with tablenumber 1 is bvw, which is the
> view with the subquery for the union, which has only 5 resulting
> columns, so 6 must be wrong. Although both the view participant tables
> (b, b2) both have six column, the view does not. In any case, C1 is
> column 2 in the view and column 2 in the two union selects from both b
> and b2.
> C1 is however, column 6 of the join node resulting from "select b3.*
> from b3 join bvw on (b3.c8 = bvw.c5)", but the correct table number for
> that would be 5, not 1.
> So, it would seem the table number has been bound to the bvw view's
> result set, but the column number has been bound to the innermost join
> node's result set. This looks worrying to me.
> See derby.log attached for the full dump of the query tree after the
> bind phase.
> sourceResultSet:                                    
>     org.apache.derby.impl.sql.compile.FromSubquery@12789d2
>     correlation Name: BVW
>     null
>     tableNumber 1 <------------------------------------------- Note!
>     level 0
>     resultSetNumber: 0
>     referencedTableMap: null
>     statementResultSet: false
>     resultColumns:                                      
>         org.apache.derby.impl.sql.compile.ResultColumnList@c943d1
>         indexRow: false
>         orderBySelect: 0
>         [0]:                                        
>         org.apache.derby.impl.sql.compile.ResultColumn@d3c6a3
>         ***truncated***
>         [1]:                                        
>         org.apache.derby.impl.sql.compile.ResultColumn@18352d8
>         exposedName: C1
>         name: C1
>         tableName: null
>         isDefaultColumn: false
>         wasDefaultColumn: false
>         isNameGenerated: false
>         sourceTableName: B2
>         type: INTEGER
>         columnDescriptor: null
>         isGenerated: false
>         isGeneratedForUnmatchedColumnInInsert: false
>         isGroupingColumn: false
>         isReferenced: true
>         isRedundant: false
>         virtualColumnId: 2
>         resultSetNumber: -1
>         dataTypeServices: INTEGER
>         expression:                                         
>             org.apache.derby.impl.sql.compile.VirtualColumnNode@b40ec4
>             dataTypeServices: null
>             sourceColumn:                                               
>                 org.apache.derby.impl.sql.compile.ResultColumn@1d95da8
>                 ***truncated***
>             sourceResultSet:                                                
>                 org.apache.derby.impl.sql.compile.UnionNode@14d7745
>                 ***truncated***
>         [2]:                                        
>         org.apache.derby.impl.sql.compile.ResultColumn@13576a2
>         exposedName: C2
>         name: C2
>         tableName: null
>         isDefaultColumn: false
>         wasDefaultColumn: false
>         isNameGenerated: false
>         sourceTableName: B2
>         type: INTEGER
>         columnDescriptor: null
>         isGenerated: false
>         isGeneratedForUnmatchedColumnInInsert: false
>         isGroupingColumn: false
>         isReferenced: true
>         isRedundant: false
>         virtualColumnId: 3
>         resultSetNumber: -1
>         dataTypeServices: INTEGER
>         expression:                                         
>             org.apache.derby.impl.sql.compile.VirtualColumnNode@ff8c74
>             dataTypeServices: null
>             sourceColumn:                                               
>                 org.apache.derby.impl.sql.compile.ResultColumn@61736e
>                 ***truncated***
>             sourceResultSet:                                                
>                 org.apache.derby.impl.sql.compile.UnionNode@14d7745
>                 ***truncated***
>         [3]:                                        
>         org.apache.derby.impl.sql.compile.ResultColumn@15e2ccd
>         exposedName: C3
>         name: C3
>         tableName: null
>         isDefaultColumn: false
>         wasDefaultColumn: false
>         isNameGenerated: false
>         sourceTableName: B2
>         type: CHAR(1)
>         columnDescriptor: null
>         isGenerated: false
>         isGeneratedForUnmatchedColumnInInsert: false
>         isGroupingColumn: false
>         isReferenced: true
>         isRedundant: false
>         virtualColumnId: 4
>         resultSetNumber: -1
>         dataTypeServices: CHAR(1)
>         expression:                                         
>             org.apache.derby.impl.sql.compile.VirtualColumnNode@1cf7491
>             dataTypeServices: null
>             sourceColumn:                                               
>                 org.apache.derby.impl.sql.compile.ResultColumn@11946c2
>                 ***truncated***
>             sourceResultSet:                                                
>                 org.apache.derby.impl.sql.compile.UnionNode@14d7745
>                 ***truncated***
>         [4]:    <----------------------------------------- highest column number is
5 (index is zero-based)
>         org.apache.derby.impl.sql.compile.ResultColumn@edf730
>         exposedName: C4
>         name: C4
>         tableName: null
>         isDefaultColumn: false
>         wasDefaultColumn: false
>         isNameGenerated: false
>         sourceTableName: B2
>         type: INTEGER
>         columnDescriptor: null
>         isGenerated: false
>         isGeneratedForUnmatchedColumnInInsert: false
>         isGroupingColumn: false
>         isReferenced: true
>         isRedundant: false
>         virtualColumnId: 5
>         resultSetNumber: -1
>         dataTypeServices: INTEGER
>         expression:                                         
>             org.apache.derby.impl.sql.compile.VirtualColumnNode@ff94b1
>             dataTypeServices: null
>             sourceColumn:                                               
>                 org.apache.derby.impl.sql.compile.ResultColumn@17a4989
>                 ***truncated***
>             sourceResultSet:                                                
>                 org.apache.derby.impl.sql.compile.UnionNode@14d7745
>                 ***truncated***
>     subquery:                                       
>         org.apache.derby.impl.sql.compile.UnionNode@14d7745
>         ***truncated***

-- 
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