Bugs item #2629960, was opened at 2009-02-23 13:16
Message generated for change (Settings changed) made by stmane
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=482468&aid=2629960&group_id=56967
Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: SQL/Core
Group: SQL CVS Head
>Status: Deleted
>Resolution: Duplicate
Priority: 5
Private: Yes
Submitted By: Jens Oehlschlägel (jens_oehl)
Assigned to: Niels Nes (nielsnes)
Summary: View and subquery don't give same results
Initial Comment:
-- this statement gives 150000 rows
SELECT count(*) FROM (
SELECT postingYear, postingMonth, DT_Country, DT_Customer_Attrib1,
DT_Customer_Group, DT_Customer_Attrib2, DT_Customer_Name, DT_Region,
DT_Product_Attrib2, DT_Product_Line, DT_PRICE_CATEG, DT_CTR_DURATION_CATEG,
DT_VOLUME, DT_D00, DT_D01, DT_D02, DT_D03, DT_D04, DT_D05, TYPETRANSAC
FROM voc.q_SourceOverview
WHERE (DT_UOM='kg')
AND (DT_CURR='EUR')
AND (( (PostingYear=2005)) or ( (PostingYear=2006)))
) sub
;
-- since q_SourceOverview is defined to be same as q_local_Source_currency
-- we also get 150000 rows
SELECT count(*) FROM (
SELECT postingYear, postingMonth, DT_Country, DT_Customer_Attrib1,
DT_Customer_Group, DT_Customer_Attrib2, DT_Customer_Name, DT_Region,
DT_Product_Attrib2, DT_Product_Line, DT_PRICE_CATEG, DT_CTR_DURATION_CATEG,
DT_VOLUME, DT_D00, DT_D01, DT_D02, DT_D03, DT_D04, DT_D05, TYPETRANSAC
FROM voc.q_local_Source_currency
WHERE (DT_UOM='kg')
AND (DT_CURR='EUR')
AND (( (PostingYear=2005)) or ( (PostingYear=2006)))
) sub
;
-- now replacing the viewname with the vew definition in the a subquery
-- gives only 35883 rows !??
SELECT count(*) FROM (
SELECT postingYear, postingMonth, DT_Country, DT_Customer_Attrib1,
DT_Customer_Group, DT_Customer_Attrib2, DT_Customer_Name, DT_Region,
DT_Product_Attrib2, DT_Product_Line, DT_PRICE_CATEG, DT_CTR_DURATION_CATEG,
DT_VOLUME, DT_D00, DT_D01, DT_D02, DT_D03, DT_D04, DT_D05, TYPETRANSAC
FROM (
SELECT
T_BASE_SALES.DT_Region,
T_BASE_SALES.DT_Country,
T_BASE_SALES.DT_PG_Region,
T_BASE_SALES.DT_PG_Customer,
T_BASE_SALES.DT_PG_Key_product,
T_BASE_SALES.DT_PG_zipcode,
T_BASE_SALES.DT_Customer_Number,
T_BASE_SALES.DT_Customer_NameCode,
T_BASE_SALES.DT_Customer_Name,
T_BASE_SALES.DT_Customer_Attrib1,
T_BASE_SALES.DT_Customer_Attrib2Code,
T_BASE_SALES.DT_Customer_Attrib2,
T_BASE_SALES.DT_Customer_Attrib3,
T_BASE_SALES.DT_Product_Line,
T_BASE_SALES.DT_Product_Attrib1,
T_BASE_SALES.DT_Product_Attrib2,
T_BASE_SALES.DT_Product_Attrib3,
T_BASE_SALES.DT_Product_Name,
T_BASE_SALES.DT_Customer_Group,
T_BASE_SALES.DT_Product_Number,
T_BASE_SALES.DT_Product_Attrib1_Number,
T_BASE_SALES.ImportedDate,
T_BASE_SALES.PostingDate,
T_BASE_SALES.PostingYear,
T_BASE_SALES.PostingSemi,
T_BASE_SALES.PostingMonth,
T_BASE_SALES.PostingWeek,
T_BASE_SALES.PostingQuarter,
T_BASE_SALES.InputIndex,
T_BASE_SALES.ImportedTypeTransac,
T_BASE_SALES.TypeTransac,
T_BASE_SALES.PostingDate_Text,
T_BASE_SALES.DT_D00*TBL_EXCHANGE_RATES.Exchrate AS DT_D00,
T_BASE_SALES.DT_D01*TBL_EXCHANGE_RATES.Exchrate AS DT_D01,
T_BASE_SALES.DT_D02*TBL_EXCHANGE_RATES.Exchrate AS DT_D02,
T_BASE_SALES.DT_D03*TBL_EXCHANGE_RATES.Exchrate AS DT_D03,
T_BASE_SALES.DT_D04*TBL_EXCHANGE_RATES.Exchrate AS DT_D04,
T_BASE_SALES.DT_D05*TBL_EXCHANGE_RATES.Exchrate AS DT_D05,
T_BASE_SALES.DT_VOLUME*xRate AS DT_VOLUME,
T_BASE_SALES.DT_C00*TBL_EXCHANGE_RATES.Exchrate AS DT_C00,
T_BASE_SALES.DT_C01_01*TBL_EXCHANGE_RATES.Exchrate AS DT_C01_01,
T_BASE_SALES.DT_C02_01*TBL_EXCHANGE_RATES.Exchrate AS DT_C02_01,
T_BASE_SALES.DT_C03_01*TBL_EXCHANGE_RATES.Exchrate AS DT_C03_01,
T_BASE_SALES.DT_C04_01*TBL_EXCHANGE_RATES.Exchrate AS DT_C04_01,
T_BASE_SALES.DT_C05_01*TBL_EXCHANGE_RATES.Exchrate AS DT_C05_01,
T_BASE_SALES.DT_D01_01*TBL_EXCHANGE_RATES.Exchrate AS DT_D01_01,
T_BASE_SALES.DT_D01_02*TBL_EXCHANGE_RATES.Exchrate AS DT_D01_02,
T_BASE_SALES.DT_D01_03*TBL_EXCHANGE_RATES.Exchrate AS DT_D01_03,
T_BASE_SALES.DT_D01_04*TBL_EXCHANGE_RATES.Exchrate AS DT_D01_04,
T_BASE_SALES.DT_D02_01*TBL_EXCHANGE_RATES.Exchrate AS DT_D02_01,
T_BASE_SALES.DT_D02_02*TBL_EXCHANGE_RATES.Exchrate AS DT_D02_02,
T_BASE_SALES.DT_D02_03*TBL_EXCHANGE_RATES.Exchrate AS DT_D02_03,
T_BASE_SALES.DT_D02_04*TBL_EXCHANGE_RATES.Exchrate AS DT_D02_04,
T_BASE_SALES.DT_D02_05*TBL_EXCHANGE_RATES.Exchrate AS DT_D02_05,
T_BASE_SALES.DT_D02_06*TBL_EXCHANGE_RATES.Exchrate AS DT_D02_06,
T_BASE_SALES.DT_D02_90*TBL_EXCHANGE_RATES.Exchrate AS DT_D02_90,
T_BASE_SALES.DT_D02_91*TBL_EXCHANGE_RATES.Exchrate AS DT_D02_91,
T_BASE_SALES.DT_D03_01*TBL_EXCHANGE_RATES.Exchrate AS DT_D03_01,
T_BASE_SALES.DT_D04_01*TBL_EXCHANGE_RATES.Exchrate AS DT_D04_01,
T_BASE_SALES.DT_D05_01*TBL_EXCHANGE_RATES.Exchrate AS DT_D05_01,
T_BASE_SALES.DT_D05_02*TBL_EXCHANGE_RATES.Exchrate AS DT_D05_02,
T_BASE_SALES.DT_D05_03*TBL_EXCHANGE_RATES.Exchrate AS DT_D05_03,
T_BASE_SALES.DT_Sales*TBL_EXCHANGE_RATES.Exchrate AS DT_Sales,
T_BASE_SALES.DT_Revenues*TBL_EXCHANGE_RATES.Exchrate AS DT_Revenues,
T_BASE_SALES.DT_PRICE_CATEG,
T_BASE_SALES.DT_PRICE_CLASS,
T_BASE_SALES.DT_CTR_DURATION_CATEG,
T_BASE_SALES.DT_ShareFix,
T_BASE_SALES.DT_ShareVar,
T_BASE_SALES.DT_PLANT,
TBL_UOM.UnitUOM AS DT_UOM,
T_BASE_SALES.DT_ORIG_CURR,
T_BASE_SALES.DT_ORIG_XRATE,
TBL_EXCHANGE_RATES.DT_CURR
FROM voc.TBL_UOM, voc.T_BASE_SALES INNER JOIN voc.TBL_EXCHANGE_RATES
ON (T_BASE_SALES.PostingYear=TBL_EXCHANGE_RATES."year")
AND (T_BASE_SALES.PostingMonth=TBL_EXCHANGE_RATES.MonthNumber)
) sub1
WHERE (DT_UOM='kg')
AND (DT_CURR='EUR')
AND (( (PostingYear=2005)) or ( (PostingYear=2006)))
) sub
;
----------------------------------------------------------------------
>Comment By: Stefan Manegold (stmane)
Date: 2009-02-23 15:09
Message:
accidental duplicate of
[ 2629897 ] View and subquery don't give same results
https://sourceforge.net/tracker/index.php?func=detail&aid=2629897&group_id=56967&atid=482468
----------------------------------------------------------------------
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=482468&aid=2629960&group_id=56967
------------------------------------------------------------------------------
Open Source Business Conference (OSBC), March 24-25, 2009, San Francisco, CA
-OSBC tackles the biggest issue in open source: Open Sourcing the Enterprise
-Strategies to boost innovation and cut costs with open source participation
-Receive a $600 discount off the registration fee with the source code: SFAD
http://p.sf.net/sfu/XcvMzF8H
_______________________________________________
Monetdb-bugs mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/monetdb-bugs
