The following query:

select
 floor((PV.rowID-1)/3)+1 as rowCellID,
 floor((PV.columnID-1)/3)+1 as columnCellID,
 PV.cellValue,
 count(*) as occurrences
from PossibleValuesStage2 as PV
group by
 floor((PV.rowID-1)/3)+1,
 floor((PV.columnID-1)/3)+1,
 PV.cellValue
order by rowCellID, columnCellID, cellValue

returns 27 groups, which is incorrect. The expected result is 81 groups.

The following query (which is invalid SQL):

select
 floor((PV.rowID-1)/3)+1 as rowCellID,
 floor((PV.columnID-1)/3)+1 as columnCellID,
 PV.cellValue,
 count(*) as occurrences
from PossibleValuesStage2 as PV
group by rowCellID, columnCellID, cellValue
order by rowCellID, columnCellID, cellValue

returns the correct result - 81 groups.

Ole Willy Tuv

Ole,

<< returns 27 groups, which is incorrect. The expected result is 81 groups.
>>

In the interest of speed, could you send me the table that you're using ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<< In the interest of speed, could you send me the table that you're using ?
>>

The SQL to create the database is enclosed.

1. Create and execute the sp_Sudoko_EDB procedure

2. Execute the first query:

select
 floor((PV.rowID-1)/3)+1 as rowCellID,
 floor((PV.columnID-1)/3)+1 as columnCellID,
 PV.cellValue,
 count(*) as occurrences
from PossibleValuesStage2 as PV
group by
 floor((PV.rowID-1)/3)+1,
 floor((PV.columnID-1)/3)+1,
 PV.cellValue
order by rowCellID, columnCellID, cellValue

Result: 27 rows

3. Execute the second query:

select
 floor((PV.rowID-1)/3)+1 as rowCellID,
 floor((PV.columnID-1)/3)+1 as columnCellID,
 PV.cellValue,
 count(*) as occurrences
from PossibleValuesStage2 as PV
group by rowCellID, columnCellID, cellValue
order by rowCellID, columnCellID, cellValue

Result: 81 rows

Ole Willy Tuv

Ole,

<< The SQL to create the database is enclosed. >>

Holy sh*t, that's a pretty wild SP.

The issue is fixed now.  If you run the query with an execution plan, you'll
see the problem - the GROUP BY is matching up the wrong select expression.
EDB tries to use an existing SELECT column expression for the grouping if it
can find one, otherwise it uses an internal grouping column.

BTW, the truncation and range errors will most likely be in 1.02 b2.  I
figured out a way to isolate them to the column assignments, which is what I
was looking for.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<< BTW, the truncation and range errors will most likely be in 1.02 b2.  I
figured out a way to isolate them to the column assignments, which is what I
was looking for. >>

I take it you mean all types of assigment targets, i.e. columns, SQL
parameters and local variables ?

Ole Willy Tuv

Tim,

<< Holy sh*t, that's a pretty wild SP. >>

I didn't figure out the Sudoko algorithms myself

I've translated the EDB version from a Transact-SQL script that was posted
on the Internet some time ago. I think it's a good test-case for
testing/debugging the PSM implementation in EDB.

Ole Willy Tuv

Ole,

<< I take it you mean all types of assigment targets, i.e. columns, SQL
parameters and local variables ? >>

No, just columns initially.  Parameters and variables are more complicated
because they can literally grow in EDB.  You can define a parameter or
variable as just VARCHAR in EDB - it doesn't require a length.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<< Parameters and variables are more complicated because they can literally
grow in EDB.  You can define a parameter or variable as just VARCHAR in
EDB - it doesn't require a length. >>

Are you saying that the length of a character string declaration is not
respected ?

CHAR/VARCHAR without a length specification normally means CHAR/VARCHAR with
length 1.

Ole Willy Tuv

Ole,

<< Are you saying that the length of a character string declaration is not
respected ? >>

No, I'm saying that specifically in the case of parameters and variables in
routines, EDB allows you to specify a VARCHAR without a specific length.
IOW, it can grow as required.  If you set a length, then EDB will respect
the length.

Think of a situation where you're appending a bunch of strings to one
VARCHAR variable.  It is very difficult, if not impossible, to know what the
resultant length should be ahead of time.  EDB solves this problem by
allowing you to simply not specify a length, which means that the VARCHAR
can be anywhere from 0 to High(Integer) in size.

<< CHAR/VARCHAR without a length specification normally means CHAR/VARCHAR
with length 1. >>

Actually, I think that is only for CHAR.  VARCHAR without a length is
invalid as a type specification (but legal in EDB for parameters and
variables due to the above).

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<< Actually, I think that is only for CHAR.  VARCHAR without a length is
invalid as a type specification (but legal in EDB for parameters and
variables due to the above). >>

No, the default length of 1 (if length is not specified) is the same for
both CHAR and VARCHAR.

Ole Willy Tuv