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 Tue, 15 Jun 2010 20:03:23 GMT

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

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

My primary interest in persuing this issue is that the fix for DERBY-4679 now relies
on (tablenumber, columnnumber) for the remapping in 
ColumnReference#remapColumnReferencesToExpressions, only falling back to map via
column name strings when at the BaseColumnNode level.

Theoretically, if assignment of (tablenumber, columnnumber) is sometimes wrong, as seen in
this
issue, one could end up with a wrong remap. Actually what happens for the query under investigation
here
is that given the wrong column pair (1,6),  

     ftRC = rcl.getResultColumn(
                    tableNumberBeforeFlattening,
                    columnNumberBeforeFlattening)

fails to find such a column and falls back to the column string method used solely prior to
the fix for DERBY-4679.

If correctly bound to (1,2), the test in the beginning of ColumnReference#remapColumnReferencesToExpressions:

   /* Nothing to do if we are not pointing to a redundant RC */
   if (! source.isRedundant())
   {
      return this;
   }

hits in since the column reference is already bound correctly. The fact that the wrong pair
exists is masked by the saving grace of the column name based lookup and thus corrected. This
is pure luck, though, another combination of numbers could
have made the (tn, cn) based lookup find the wrong column.

This is also why this problem has not been seen in practice, perhaps.

In summary, the presence of the wrong (tn,cn) assignment is a fact, so there is a small chance
that the fix for DERBY-4679 could break existing working queries (which work only due to luck,
as shown above). Therefore, I would like to fix this issue
before back-porting the fix of DERBY-4679 to the 10.6 branch. 


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