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] Updated: (DERBY-4695) Internal assignment of tablenumer, columnnumber looks wrong in query tree, although no ill effects are seen.
Date Tue, 15 Jun 2010 17:59:24 GMT

     [ https://issues.apache.org/jira/browse/DERBY-4695?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Dag H. Wanvik updated DERBY-4695:
---------------------------------

    Description: 
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***


  was:
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 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***



> 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