Bug with Jet OLEDB provider and DECIMAL subquery?

[Jamie Collins]

Consider this test table with a DECIMAL data column:

CurrentProject.Connection.Execute _
"CREATE TABLE Test (" & _
"key_col INTEGER NOT NULL UNIQUE," & _
" data_col DECIMAL(19, 4) NOT NULL);"

CurrentProject.Connection.Execute _
"INSERT INTO Test VALUES (1, 1);"
CurrentProject.Connection.Execute _
"INSERT INTO Test VALUES (2, 2);"
CurrentProject.Connection.Execute _
"INSERT INTO Test VALUES (3, 3);"

Consider this SQL to return a repeating column total on every row of
the resultset (although the resultset is limited to one row by the
search condition):

sSQL = _
"SELECT T1.key_col, T1.data_col," & _
" (SELECT SUM(T2.data_col)" & _
" FROM Test AS T2) AS total_data_col" & _
" FROM Test AS T1 WHERE T1.key_col = 1;"

First, DAO provides the correct result for total_data_col:

? CurrentDb.OpenRecordset(sSQL)(2)
6

Now for the wrong answers from ADO:

? CurrentProject.Connection.Execute(sSQL)(2)

Well, the first thing to note is that most of the time I get an error,
"Invalid procedure call or argument." but a small proportion of the
time I get the value

114850184919423154316574819

Hmm, a little overestimated, methinks <g>.

OK, using another method to get the field's value:

? CurrentProject.Connection.Execute(sSQL).GetRows()(2, 0)
1

Oh, dear. This is the value in the field but it is incorrect. I'm not
quite sure what is going on here but it seems the result of the
subquery is being replaced by the value of T1.data_col.
If the data type is changed (CURRENCY. FLOAT, etc) the behaviour is
correct. If the SUM keyword is changed to COUNT the behaviour is
correct.

The above works fine in a Access2007 query object's SQL view while in
ANSI-92 mode but the same (wrong) behaviour against CurrentProject. I
think therefore the source of the bug is the Jet 4.0 OLEDB provider but
again I'm merely guessing.

Workarounds are simple enough e.g.

sSQL = _
"SELECT T1.key_col, T1.data_col, DT1.total_data_col" & _
" FROM Test AS T1, (SELECT SUM(T2.data_col)" & _
" AS total_data_col FROM Test AS T2) AS DT1" & _
" WHERE T1.key_col = 1;"

Jamie.

--