Could the Order By clause change the query results?
I discovered that the addition of order by changes the values of yes/no empty-null cells (because of non match row in right table) to negative values!
I built tables specifically to isolate the problem, and the description is here:
Two tables, one named MasterTable and one named ChildTable.
Let's talk about a case where the table ChildTable is completely empty, and the MasterTable contains one row. Look at the following query:
SELECT
MasterTable.ID,
MasterTable.FirstName,
ChildTable.ID,
ChildTable.IsActive,
IIF(ChildTable.IsActive IS NULL, 'IS NULL', 'IS NOT NULL') As [TestNull]
FROM MasterTable LEFT JOIN ChildTable
ON MasterTable.ID = ChildTable.ID_MASTER
There should be one row with the three fields of MaterTable, and with two empty fields for the ChildTable (ID, IsActive), and last Column with 'IS NULL' text.
And this is indeed what happens:
| MasterTable.ID | FirstName | ChildTable.ID | IsActive | TestNull |
|---|---|---|---|---|
| 1 | david | null | null (ok!) | IS NULL (ok!) |
The strange behaviour:
Now I add one line to the query:
ORDER BY MasterTable.ID
Here is the result via C# (the Dapper ORM, but also a raw OleDbCommand):
| MasterTable.ID | FirstName | ChildTable.ID | IsActive | TestNull |
|---|---|---|---|---|
| 1 | david | null | False (!!??) | IS NOT NULL (!!??) |
(It should be noted that, in Microsoft Access, the results are normal, and in any case the result is null and IS NULL respectively).
I don't need a solution (after I caught the problem!), because it is possible to condition on another non-Boolean field that is always set to empty.
But am I not missing something, and is this bug known and could it be fixed?
