[ 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
