db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-1633) Regression: The fields of views are not being calculated properly since 10.1.2.4
Date Tue, 15 Aug 2006 22:59:14 GMT
    [ http://issues.apache.org/jira/browse/DERBY-1633?page=comments#action_12428256 ] 
            
Rick Hillegas commented on DERBY-1633:
--------------------------------------

Thanks for the extensive analysis, Army! This has helped me to understand some of the issues.
At this point I'm afraid that I don't have a very firm grasp of the high level problem and
I hope that you will teach me more. It is clear that you have studied this tricky piece of
code. Please bear with me.

I lost the thread of your explanation when I got to OBS#2 in DERBY-1633_v2.html. I did not
understand this statement: "When a predicate is pushed to a UNION that appears in a Select
list, the predicate will be "remapped" so that the column references point to their source
result columns." I did not grasp how the problem was caused by the UNION. Why does a UNION
require this mapping?

Instead, it seemed to me that the remapping was required by what was underneath the UNION.
In this case, it is a Permuting Node (that's a term I just made up), that is, an operator
which changes column order. In this case, the Permuting Node is a view, although I suppose
it could also be a subquery.

It seems to me that positional remapping is required for other predicates that we try to push
down, even if no UNION is involved. For instance, I think we need positional remapping in
the following cases. And this brings me to the heart of my confusion: Why does predicate pushdown
work in the following cases--or does it? If it does work, how is it that UNIONs break the
logic? Here are some cases that come to mind:

select *
from 
  t1,
  ( select b as x, a as y from t2) v2
where t1.a = v2.y

select *
from ( select b as x, a as y from t2) v2
where v2.y = 1



> Regression: The fields of views are not being calculated properly since 10.1.2.4
> --------------------------------------------------------------------------------
>
>                 Key: DERBY-1633
>                 URL: http://issues.apache.org/jira/browse/DERBY-1633
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.1.3.0, 10.1.3.1
>         Environment: 2.8 GHZ dual PIV on Windows XP SP2, 2 GB memory
>            Reporter: Prasenjit Sarkar
>         Assigned To: A B
>             Fix For: 10.2.0.0
>
>         Attachments: d1633_repro.sql, d1633_v1_reviewOnly.patch, d1633_v2.patch, DERBY-1633_v1.html,
DERBY-1633_v2.html
>
>
> Database can be assumed to be same as in Derby - 1205 Jira issue
> SELECT PORT1.PORT_ID FROM T_RES_PORT PORT1, T_VIEW_ENTITY2PORT ENTITY2PORT WHERE ENTITY2PORT.PORT_ID
= PORT1.PORT_ID
> This works fine in 10.1.2.1 but fails thereafter complaining that Comparison between
INTEGER and CHAR is not supported
> for some reason, it thinks one of the PORT_ID columns is a character, when in reality
both are integers.
> 		SELECT DISTINCT 
> 		    ZONE.ZONE_ID ZONE_ID, 
> 			 PORT2ZONE.ZONE_MEMBER_ID  
> 		FROM  
> 			 T_RES_ZONE ZONE left outer join T_VIEW_PORT2ZONE PORT2ZONE on  
> 			 ZONE.ZONE_ID = PORT2ZONE.ZONE_ID   ,  T_RES_FABRIC FABRIC 
> In this query, it is complaining that one of the columns is a VARCHAR and cannot be compared
to INTEGER, when clearly this is not the case...
> Same issue

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

Mime
View raw message