Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 7 of 7 total |
UNION problem/bug |
Mon, Apr 9 2007 8:03 AM | Permanent Link |
"Ole Willy Tuv" | /* BlockedValuesStage2 */
-- Identify cell values blocked because they exist in a column pair select PV.rowID, PV.columnID, PV.cellValue from PossibleValues as PV inner join TwinValues_DT1 as DT on PV.columnID = DT.columnID and PV.cellValue in (DT.value1, DT.value2) and PV.rowID not in (DT.rowID1, DT.rowID2) union -- Identify cell values blocked because they exist in a row pair select PV.rowID, PV.columnID, PV.cellValue from PossibleValues as PV inner join TwinValues_DT2 as DT on PV.rowID = DT.rowID and PV.cellValue in (DT.value1, DT.value2) and PV.columnID not in (DT.columnID1, DT.columnID2) union -- Identify cells from the original blocked query. select rowID, columnID, cellValue from BlockedValues The query returns 243 rows, while the correct result is 296 rows. When executing each part of the union separately, I'm getting the expected results: Query 1 returns 0 rows Query 2 returns 0 rows query 3 returns 296 rows Ole Willy Tuv |
Mon, Apr 9 2007 8:19 AM | Permanent Link |
"Ole Willy Tuv" | << The query returns 243 rows, while the correct result is 296 rows. >>
If I change the order of query expressions, such that the query returning a non-empty result comes first: /* BlockedValuesStage2 */ -- Identify cells from the original blocked query. select rowID, columnID, cellValue from BlockedValues union -- Identify cell values blocked because they exist in a column pair select PV.rowID, PV.columnID, PV.cellValue from PossibleValues as PV inner join TwinValues_DT1 as DT on PV.columnID = DT.columnID and PV.cellValue in (DT.value1, DT.value2) and PV.rowID not in (DT.rowID1, DT.rowID2) union -- Identify cell values blocked because they exist in a row pair select PV.rowID, PV.columnID, PV.cellValue from PossibleValues as PV inner join TwinValues_DT2 as DT on PV.rowID = DT.rowID and PV.cellValue in (DT.value1, DT.value2) and PV.columnID not in (DT.columnID1, DT.columnID2) then the union returns the expected 296 rows. Ole Willy Tuv |
Mon, Apr 9 2007 8:52 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Ole,
<< When executing each part of the union separately, I'm getting the expected results: >> Could you please send me the tables that you're using ? Thanks, -- Tim Young Elevate Software www.elevatesoft.com |
Mon, Apr 9 2007 9:22 PM | Permanent Link |
"Ole Willy Tuv" | Tim,
<< Could you please send me the tables that you're using ? >> It's the Sudoko database I just posted in another reply. You can reproduce the issue as follows: 1. Execute the sp_default_Sudoko_puzzle procedure 2. Execute the first union query: -- Identify cell values blocked because they exist in a column pair select PV.rowID, PV.columnID, PV.cellValue from PossibleValues as PV inner join TwinValues_DT1 as DT on PV.columnID = DT.columnID and PV.cellValue in (DT.value1, DT.value2) and PV.rowID not in (DT.rowID1, DT.rowID2) union -- Identify cell values blocked because they exist in a row pair select PV.rowID, PV.columnID, PV.cellValue from PossibleValues as PV inner join TwinValues_DT2 as DT on PV.rowID = DT.rowID and PV.cellValue in (DT.value1, DT.value2) and PV.columnID not in (DT.columnID1, DT.columnID2) union -- Identify cells from the original blocked query. select rowID, columnID, cellValue from BlockedValues Result: 243 rows 3. Execute the second union query -- Identify cells from the original blocked query. select rowID, columnID, cellValue from BlockedValues union -- Identify cell values blocked because they exist in a column pair select PV.rowID, PV.columnID, PV.cellValue from PossibleValues as PV inner join TwinValues_DT1 as DT on PV.columnID = DT.columnID and PV.cellValue in (DT.value1, DT.value2) and PV.rowID not in (DT.rowID1, DT.rowID2) union -- Identify cell values blocked because they exist in a row pair select PV.rowID, PV.columnID, PV.cellValue from PossibleValues as PV inner join TwinValues_DT2 as DT on PV.rowID = DT.rowID and PV.cellValue in (DT.value1, DT.value2) and PV.columnID not in (DT.columnID1, DT.columnID2) Result: 296 rows Ole Willy Tuv |
Tue, Apr 10 2007 9:59 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Ole,
<< It's the Sudoko database I just posted in another reply. You can reproduce the issue as follows: 1. Execute the sp_default_Sudoko_puzzle procedure >> Hah, I just spent almost 5 minutes trying to figure out why you had a Boolean parameter to this procedure. The issue is already resolved - it was the same problem that Ralf Bertoldi reported in relation to querying views that include joins that can be optimized by re-ordering them. The query on the view ends up artificially limiting the number of rows that are returned. -- Tim Young Elevate Software www.elevatesoft.com |
Wed, Apr 11 2007 9:06 AM | Permanent Link |
"Ole Willy Tuv" | Tim,
<< Hah, I just spent almost 5 minutes trying to figure out why you had a Boolean parameter to this procedure. >> LOL. I guess you've fixed the problem with calling a procedure with an empty parameter list in 1.02 build 2, such that I can remove the dummy procedure parameters ? Ole Willy Tuv |
Wed, Apr 11 2007 3:30 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Ole,
<< LOL. I guess you've fixed the problem with calling a procedure with an empty parameter list in 1.02 build 2, such that I can remove the dummy procedure parameters ? >> Yes, it is fixed. -- Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |