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?
Ifcase can be fixed by callingGet["GeneralUtilities`"]; GeneralUtilities`PrintDefinitions[Databases`Database`DBExprToAST], then scroll the resulting notebook to the ruleDBExprToAST[If[cond_, true_, false_]] := DBExprToAST @ Which[cond,true, True, false];, replace thereTrueby!cond, and press SHIFT + ENTER. After that the code is patched for this session. ForWhichI will have to look. $\endgroup$