3
$\begingroup$

Suppose I have an SQLite Database with a table name myTable. Then running the following query:

EntityValue[FilteredEntityClass[
    "myTable",
    EntityFunction[entries,And[
        If[entries["field1"]<1,
            entries["field2"]<2,
            entries["field3"]<3
        ]
    ]]
],{"field1","field2","field3"}]

produces the following compiled SQL query:

SELECT
CAST("myTable_T1131"."field1" AS INTEGER) AS "field1",
CAST("myTable_T1131"."field2" AS INTEGER) AS "field2",
CAST("myTable_T1131"."field3" AS INTEGER) AS "field3"
FROM "myTable" AS "myTable_T1131"
WHERE 
"myTable_T1131"."field1" < 1 AND
"myTable_T1131"."field2" < 2 OR
"myTable_T1131"."field3" < 3

The WHERE condition is wrong it should be

("myTable_T1131"."field1" < 1 AND "myTable_T1131"."field2" < 2) OR
(NOT("myTable_T1131"."field1" < 1) AND "myTable_T1131"."field3" < 3)

You can verify that by converting the If statement into boolean expression:

BooleanConvert@If[a,b,c]

gives

(a && b) || (!a && c)

!a is missing from the compiled SQL. A similar issue happens with Which.

The following command can be used to see the compiled SQL query:

Databases`Database`$DBQueryLogger=Echo;

Is this a bug?

$\endgroup$
5
  • 1
    $\begingroup$ Indeed looks like a bug, although I would have to look a little closer. Let me know if you are interested in a quick fix / patch that can be done on the top level. If you are impatient, the If case can be fixed by calling Get["GeneralUtilities`"]; GeneralUtilities`PrintDefinitions[Databases`Database`DBExprToAST], then scroll the resulting notebook to the rule DBExprToAST[If[cond_, true_, false_]] := DBExprToAST @ Which[cond,true, True, false];, replace there True by !cond, and press SHIFT + ENTER. After that the code is patched for this session. For Which I will have to look. $\endgroup$ Aug 16, 2023 at 20:47
  • 1
    $\begingroup$ @LeonidShifrin i am not in a hurry but i caught this when i was running a complicated nested query and was getting incorrect output. After drilling down through several expressions I noticed the If and Which statements were not getting translated to SQL correctly. If you can fix it via paclet update for everyone without me having to do anything that would be nice. Also please do it carefully, i love the entity framework and have come to rely on it so much. I don’t want it to break. This Framework need a lot more love from you guys. $\endgroup$
    – user13892
    Aug 16, 2023 at 22:42
  • 1
    $\begingroup$ For now I am working around this problem by changing my statements from If[a,b,c] to If[a,b,!a&&c]. $\endgroup$
    – user13892
    Aug 16, 2023 at 22:56
  • 1
    $\begingroup$ @LeonidShifrin looking forward to your work on Database backed Dataset. I hope you guys can make your Query language work on SQL backends! $\endgroup$
    – user13892
    Aug 16, 2023 at 22:59
  • $\begingroup$ Thanks for all the wishes. No worries, we wouldn't want to break anything :). We wrote this query compiler and spent too mush effort on this framework to ruin it now. We probably won't issue a paclet update just for this bug, but it will be filed and processed, and we should get it fixed for the next release. In the mean time, I will try to not forget to create a top-level patch and post it here - for those who need this sooner. $\endgroup$ Aug 17, 2023 at 0:05

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.