/* 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

<< 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

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

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

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

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

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