From: Tommi P. <tom...@ec...> - 2008-06-16 11:59:13
|
> We recently found very starnge join problems in views with > FB2.X, I'll try to find my coworkers testcase with (or > soemthing we have used wrongly wirh > FB1.x) SQL scripts... Here is my Coworkers description of this, hopefully it is self explanatory, bucause I am not that good at SQL :) -Tommi Prami- ******************************************************************** I made a following query using view called view_customer_8: SELECT ADDRESS,BACKOFFICEID,BUSINESSID,COMMENT,CONTRACTORID, CONTRACTORSHORTNAME,CURRENTCONTRACTORID,CUSTOMERGROUPID, CUSTOMERGROUPNAME,CUSTOMERID,C_NAME,DISTRICTID,DISTRICTNAME, EMAIL,FIRSTNAME,INUSE,LASTNAME,LINEOFBUSINESSID,MAINCUSTOMERID, MOBILEPHONE,NAME,PHONE,PHONE2,POSTNUMBER,POSTOFFICE FROM VIEW_CUSTOMER_8 WHERE (INUSE = 1) AND (UPPER(NAME COLLATE FI_FI) LIKE 'I%') ORDER BY NAME ASC ******************************************************************** /* View: VIEW_CUSTOMER_8, Owner: SYSDBA */ CREATE VIEW "VIEW_CUSTOMER_8" ( "DISTRICTNAME", "CONTRACTORID", "CUSTOMERID", "MAINCUSTOMERID", "BUSINESSID", "LINEOFBUSINESSID", "CUSTOMERGROUPID", "INUSE", "BACKOFFICEID", "FIRSTNAME", "LASTNAME", "NAME", "ADDRESS", "POSTNUMBER", "POSTOFFICE", "DISTRICTID", "PHONE", "PHONE2", "MOBILEPHONE", "EMAIL", "COMMENT", "CUSTOMERGROUPNAME", "CONTRACTORSHORTNAME", "CURRENTCONTRACTORID", "C_NAME" ) AS SELECT DISTINCT D.NAME AS DISTRICTNAME, CT.CONTRACTORID, CT.CUSTOMERID, CT.MAINCUSTOMERID, CT.BUSINESSID, CT.LINEOFBUSINESSID, CT.CUSTOMERGROUPID, CT.INUSE, CT.BACKOFFICEID, CT.FIRSTNAME, CT.LASTNAME, CT.NAME, CT.ADDRESS, CT.POSTNUMBER, CT.POSTOFFICE, CT.DISTRICTID, CT.PHONE, CT.PHONE2, CT.MOBILEPHONE, CT.EMAIL, CT.COMMENT, CG.NAME AS CUSTOMERGROUPNAME, C.SHORTNAME AS CONTRACTORSHORTNAME, CA.CURRENTCONTRACTORID, C.NAME AS C_NAME FROM CUSTOMER CT LEFT OUTER JOIN CONTRACT CRT ON CT.CONTRACTORID = CRT.CONTRACTORID AND (CT.CUSTOMERID = CRT.INVOICECUSTOMERID OR CT.CUSTOMERID = CRT.CUSTOMERID) AND CRT.INUSE = CT.INUSE LEFT OUTER JOIN CONTRACTORROUTE CR ON CRT.CONTRACTORID = CR.CONTRACTORID AND CRT.CONTRACTID = CR.CONTRACTID AND CRT.INUSE = CR.INUSE LEFT OUTER JOIN CONTRACTAREA CA ON CA.CONTRACTORID = CR.CONTRACTORID AND CA.CONTRACTAREAID = CR.CONTRACTAREAID LEFT OUTER JOIN CUSTOMERGROUP CG ON CT.CUSTOMERGROUPID = CG.CUSTOMERGROUPID LEFT OUTER JOIN CONTRACTOR C ON CT.CONTRACTORID = C.CONTRACTORID LEFT OUTER JOIN DISTRICT D ON CT.DISTRICTID = D.DISTRICTID ; Plan is PLAN SORT (SORT (JOIN (JOIN (JOIN (JOIN (SORT (JOIN (VIEW_CUSTOMER_8 CT INDEX (IX_CUSTOMER), VIEW_CUSTOMER_8 CRT INDEX (FK_CUSTOMERINV_CONTRACT, FK_CUSTOMER_CONTRACT)), VIEW_CUSTOMER_8 CR INDEX (FK_CONTRACT_CNRTORROUTE)), VIEW_CUSTOMER_8 CA INDEX (PK_CONTRACTAREA)), VIEW_CUSTOMER_8 CG INDEX (PK_CUSTOMERGROUPID)), VIEW_CUSTOMER_8 C INDEX (PK_CONTRACTOR)), VIEW_CUSTOMER_8 D INDEX (PK_DISTRICT)))) And resulting data set is not ordered by name but when I made following change to the view_customer_8 /* View: VIEW_CUSTOMER_8, Owner: SYSDBA */ CREATE VIEW "VIEW_CUSTOMER_8" ( "DISTRICTNAME", "CONTRACTORID", "CUSTOMERID", "MAINCUSTOMERID", "BUSINESSID", "LINEOFBUSINESSID", "CUSTOMERGROUPID", "INUSE", "BACKOFFICEID", "FIRSTNAME", "LASTNAME", "NAME", "ADDRESS", "POSTNUMBER", "POSTOFFICE", "DISTRICTID", "PHONE", "PHONE2", "MOBILEPHONE", "EMAIL", "COMMENT", "CUSTOMERGROUPNAME", "CONTRACTORSHORTNAME", "CURRENTCONTRACTORID", "C_NAME" ) AS SELECT DISTINCT D.NAME AS DISTRICTNAME, CT.CONTRACTORID, CT.CUSTOMERID, CT.MAINCUSTOMERID, CT.BUSINESSID, CT.LINEOFBUSINESSID, CT.CUSTOMERGROUPID, CT.INUSE, CT.BACKOFFICEID, CT.FIRSTNAME, CT.LASTNAME, CT.NAME, CT.ADDRESS, CT.POSTNUMBER, CT.POSTOFFICE, CT.DISTRICTID, CT.PHONE, CT.PHONE2, CT.MOBILEPHONE, CT.EMAIL, CT.COMMENT, CG.NAME AS CUSTOMERGROUPNAME, C.SHORTNAME AS CONTRACTORSHORTNAME, CA.CURRENTCONTRACTORID, C.NAME AS C_NAME FROM CUSTOMER CT LEFT OUTER JOIN CONTRACT CRT ON CT.CONTRACTORID = CRT.CONTRACTORID AND (CT.CUSTOMERID = CRT.INVOICECUSTOMERID OR CT.CUSTOMERID = CRT.CUSTOMERID) AND CRT.INUSE = CT.INUSE LEFT OUTER JOIN CONTRACTORROUTE CR ON CRT.CONTRACTORID = CR.CONTRACTORID AND CRT.CONTRACTID = CR.CONTRACTID AND CRT.INUSE = CR.INUSE LEFT OUTER JOIN CONTRACTAREA CA ON CA.CONTRACTORID = CR.CONTRACTORID AND CA.CONTRACTAREAID = CR.CONTRACTAREAID LEFT OUTER JOIN CUSTOMERGROUP CG ON CT.CUSTOMERGROUPID = CG.CUSTOMERGROUPID INNER JOIN CONTRACTOR C ON CT.CONTRACTORID = C.CONTRACTORID /* old line was LEFT OUTER JOIN CONTRACTOR C ON CT.CONTRACTORID = C.CONTRACTORID */ LEFT OUTER JOIN DISTRICT D ON CT.COUNTRYID= D.COUNTRYID AND CT.DISTRICTID = D.DISTRICTID /* old line was LEFT OUTER JOIN DISTRICT D ON CT.DISTRICTID = D.DISTRICTID */ ; plan is PLAN SORT (SORT (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (VIEW_CUSTOMER_8 CT INDEX (IX_CUSTOMER), VIEW_CUSTOMER_8 CRT INDEX (FK_CUSTOMERINV_CONTRACT, FK_CUSTOMER_CONTRACT)), VIEW_CUSTOMER_8 CR INDEX (FK_CONTRACT_CNRTORROUTE)), VIEW_CUSTOMER_8 CA INDEX (PK_CONTRACTAREA)), VIEW_CUSTOMER_8 CG INDEX (PK_CUSTOMERGROUPID)), VIEW_CUSTOMER_8 C INDEX (PK_CONTRACTOR)), VIEW_CUSTOMER_8 D INDEX (PK_DISTRICT)))) And resulting data set is ordered by name. Another problem that I could not resolve: I use following query: SELECT ADDRESS,BACKOFFICEID,COMMENT,CONTRACTID,CONTRACTORID, CONTRACTORSHORTNAME,COORDINATEDATE,COORDINATEQUALITY, COORDINATE_X,COORDINATE_Y,COORDINATE_Z,CRTT_NAME, CURRENTCONTRACTORID,CUST_FIRSTNAME,CUST_LASTNAME,CUST_NAME, C_NAME,DISTANCE,DISTANCEUNITID,DISTRICTID,DISTRICTNAME,INUSE, INVCUST_FIRSTNAME,INVCUST_LASTNAME,INVCUST_NAME,LOADINGPOINTID, MAPDATA,NAME,PROPERTYID,ROUTEID,ROUTEPOINTGROUPID, ROUTEPOINTGROUPNAME,ROUTEPOINTID,VEHICLEGROUPID FROM VIEW_ROUTEPOINT_11_NEW WHERE (INUSE = 1) AND (UPPER(ADDRESS COLLATE FI_FI) LIKE 'R%') ORDER BY ADDRESS ASC With following view: /* View: VIEW_ROUTEPOINT_11_NEW, Owner: SYSDBA */ CREATE VIEW "VIEW_ROUTEPOINT_11_NEW" ( "ROUTEPOINTID", "CONTRACTORID", "ADDRESS", "NAME", "DISTRICTNAME", "DISTRICTID", "INUSE", "ROUTEPOINTGROUPID", "ROUTEPOINTGROUPNAME", "BACKOFFICEID", "CONTRACTORSHORTNAME", "COORDINATEQUALITY", "CURRENTCONTRACTORID", "COORDINATE_X", "COORDINATE_Y", "MAPDATA", "COMMENT", "CONTRACTID", "LOADINGPOINTID", "VEHICLEGROUPID", "DISTANCE", "DISTANCEUNITID", "COORDINATE_Z", "COORDINATEDATE", "PROPERTYID", "CRTT_NAME", "C_NAME", "CUST_FIRSTNAME", "CUST_LASTNAME", "CUST_NAME", "INVCUST_FIRSTNAME", "INVCUST_LASTNAME", "INVCUST_NAME", "ROUTEID" ) AS SELECT DISTINCT RP.ROUTEPOINTID, RP.CONTRACTORID, RP.ADDRESS, RP.NAME, DIST.NAME AS DISTRICTNAME, RP.DISTRICTID, RP.INUSE, RP.ROUTEPOINTGROUPID, RPG.NAME AS ROUTEPOINTGROUPNAME, RP.BACKOFFICEID, C.SHORTNAME AS CONTRACTORSHORTNAME, RP.COORDINATEQUALITY, CA.CURRENTCONTRACTORID, RP.COORDINATE_X, RP.COORDINATE_Y, RP.MAPDATA, RP.COMMENT, RP.CONTRACTID, RP.LOADINGPOINTID, RP.VEHICLEGROUPID, RP.DISTANCE, RP.DISTANCEUNITID, RP.COORDINATE_Z, RP.COORDINATEDATE, RP.PROPERTYID, CRTT.NAME AS CRTT_NAME, C.NAME AS C_NAME, CUST.FIRSTNAME AS CUST_FIRSTNAME, CUST.LASTNAME AS CUST_LASTNAME, CUST.NAME AS CUST_NAME, INVCUST.FIRSTNAME AS INVCUST_FIRSTNAME, INVCUST.LASTNAME AS INVCUST_LASTNAME, INVCUST.NAME AS INVCUST_NAME, CR.ROUTEID FROM ROUTEPOINT RP INNER JOIN CONTRACTOR C ON RP.CONTRACTORID = C.CONTRACTORID LEFT OUTER JOIN CONTRACTORROUTE CR ON RP.CONTRACTORID = CR.CONTRACTORID AND RP.ROUTEPOINTID = CR.ROUTEPOINTID AND RP.INUSE = CR.INUSE INNER JOIN CONTRACTAREA CA ON CR.CONTRACTORID = CA.CONTRACTORID AND CR.CONTRACTAREAID = CA.CONTRACTAREAID LEFT OUTER JOIN ROUTEPOINTGROUP RPG ON RP.ROUTEPOINTGROUPID = RPG.ROUTEPOINTGROUPID LEFT OUTER JOIN DISTRICT DIST ON RP.COUNTRYID = DIST.COUNTRYID AND RP.DISTRICTID = DIST.DISTRICTID LEFT OUTER JOIN CONTRACT CRT ON RP.CONTRACTORID = CRT.CONTRACTORID AND RP.CONTRACTID = CRT.CONTRACTID LEFT OUTER JOIN CONTRACTTYPE CRTT ON CRT.CONTRACTTYPEID = CRTT.CONTRACTTYPEID LEFT OUTER JOIN CUSTOMER CUST ON CRT.CONTRACTORID = CUST.CONTRACTORID AND CRT.CUSTOMERID = CUST.CUSTOMERID LEFT OUTER JOIN CUSTOMER INVCUST ON CRT.CONTRACTORID = INVCUST.CONTRACTORID AND CRT.INVOICECUSTOMERID = INVCUST.CUSTOMERID ; I get correct sorting, not correct amount of rows because it makes also LEFT OUTER JOIN CONTRACTAREA CA into INNER JOIN, meaning it drops every routepoint that doesn't have contractorroute, and I don't want that. On the other hand, with following view: /* View: VIEW_ROUTEPOINT_11_NEW, Owner: SYSDBA */ CREATE VIEW "VIEW_ROUTEPOINT_11_NEW" ( "ROUTEPOINTID", "CONTRACTORID", "ADDRESS", "NAME", "DISTRICTNAME", "DISTRICTID", "INUSE", "ROUTEPOINTGROUPID", "ROUTEPOINTGROUPNAME", "BACKOFFICEID", "CONTRACTORSHORTNAME", "COORDINATEQUALITY", "CURRENTCONTRACTORID", "COORDINATE_X", "COORDINATE_Y", "MAPDATA", "COMMENT", "CONTRACTID", "LOADINGPOINTID", "VEHICLEGROUPID", "DISTANCE", "DISTANCEUNITID", "COORDINATE_Z", "COORDINATEDATE", "PROPERTYID", "CRTT_NAME", "C_NAME", "CUST_FIRSTNAME", "CUST_LASTNAME", "CUST_NAME", "INVCUST_FIRSTNAME", "INVCUST_LASTNAME", "INVCUST_NAME", "ROUTEID" ) AS SELECT DISTINCT RP.ROUTEPOINTID, RP.CONTRACTORID, RP.ADDRESS, RP.NAME, DIST.NAME AS DISTRICTNAME, RP.DISTRICTID, RP.INUSE, RP.ROUTEPOINTGROUPID, RPG.NAME AS ROUTEPOINTGROUPNAME, RP.BACKOFFICEID, C.SHORTNAME AS CONTRACTORSHORTNAME, RP.COORDINATEQUALITY, CA.CURRENTCONTRACTORID, RP.COORDINATE_X, RP.COORDINATE_Y, RP.MAPDATA, RP.COMMENT, RP.CONTRACTID, RP.LOADINGPOINTID, RP.VEHICLEGROUPID, RP.DISTANCE, RP.DISTANCEUNITID, RP.COORDINATE_Z, RP.COORDINATEDATE, RP.PROPERTYID, CRTT.NAME AS CRTT_NAME, C.NAME AS C_NAME, CUST.FIRSTNAME AS CUST_FIRSTNAME, CUST.LASTNAME AS CUST_LASTNAME, CUST.NAME AS CUST_NAME, INVCUST.FIRSTNAME AS INVCUST_FIRSTNAME, INVCUST.LASTNAME AS INVCUST_LASTNAME, INVCUST.NAME AS INVCUST_NAME, CR.ROUTEID FROM ROUTEPOINT RP INNER JOIN CONTRACTOR C ON RP.CONTRACTORID = C.CONTRACTORID LEFT OUTER JOIN CONTRACTORROUTE CR ON RP.CONTRACTORID = CR.CONTRACTORID AND RP.ROUTEPOINTID = CR.ROUTEPOINTID AND RP.INUSE = CR.INUSE LEFT OUTER JOIN CONTRACTAREA CA ON CR.CONTRACTORID = CA.CONTRACTORID AND CR.CONTRACTAREAID = CA.CONTRACTAREAID LEFT OUTER JOIN ROUTEPOINTGROUP RPG ON RP.ROUTEPOINTGROUPID = RPG.ROUTEPOINTGROUPID LEFT OUTER JOIN DISTRICT DIST ON RP.COUNTRYID = DIST.COUNTRYID AND RP.DISTRICTID = DIST.DISTRICTID LEFT OUTER JOIN CONTRACT CRT ON RP.CONTRACTORID = CRT.CONTRACTORID AND RP.CONTRACTID = CRT.CONTRACTID LEFT OUTER JOIN CONTRACTTYPE CRTT ON CRT.CONTRACTTYPEID = CRTT.CONTRACTTYPEID LEFT OUTER JOIN CUSTOMER CUST ON CRT.CONTRACTORID = CUST.CONTRACTORID AND CRT.CUSTOMERID = CUST.CUSTOMERID LEFT OUTER JOIN CUSTOMER INVCUST ON CRT.CONTRACTORID = INVCUST.CONTRACTORID AND CRT.INVOICECUSTOMERID = INVCUST.CUSTOMERID ; I get correct amount of rows, but sorting doesn't work. |