Better SQL expansion of negation, particularly with ground #172

New issue

Open

opened 2020-08-06 16:56:08 +00:00 by gburd · 0 comments

gburd commented

2020-08-06 16:56:08 +00:00

(Migrated from github.com)

[:find ?x :where [?x _ ?v] (not [(ground 5) ?v])]

expands to

SELECT DISTINCT `all_datoms00`.e AS `?x` FROM `all_datoms` AS `all_datoms00`
WHERE NOT EXISTS (SELECT 1 WHERE `all_datoms00`.v = 5)

which is terrible. A not expression should, where possible, be inverted into a conditional expression instead of being a NOT EXISTS:

SELECT DISTINCT `all_datoms00`.e AS `?x` FROM `all_datoms` AS `all_datoms00`
WHERE NOT `all_datoms00`.v = 5

or, indeed, a negated operator:

SELECT DISTINCT `all_datoms00`.e AS `?x` FROM `all_datoms` AS `all_datoms00`
WHERE `all_datoms00`.v <> 5

This gets even worse when there's more than one value: we get

SELECT DISTINCT `datoms00`.e AS `?x` FROM `datoms` AS `datoms00`
WHERE `datoms00`.a = 7 AND NOT EXISTS
  (SELECT 1 FROM
   (SELECT 0 AS `?v` WHERE 0 UNION ALL VALUES (28), (29)) AS `c00`
    WHERE `datoms00`.v = `c00`.`?v`)

when we could generate

SELECT DISTINCT `datoms00`.e AS `?x` FROM `datoms` AS `datoms00`
WHERE `datoms00`.a = 7 AND `datoms00`.v NOT IN (28, 29)

```edn [:find ?x :where [?x _ ?v] (not [(ground 5) ?v])] ``` expands to ```sql SELECT DISTINCT `all_datoms00`.e AS `?x` FROM `all_datoms` AS `all_datoms00` WHERE NOT EXISTS (SELECT 1 WHERE `all_datoms00`.v = 5) ``` which is terrible. A `not` expression should, where possible, be inverted into a conditional expression instead of being a `NOT EXISTS`: ```sql SELECT DISTINCT `all_datoms00`.e AS `?x` FROM `all_datoms` AS `all_datoms00` WHERE NOT `all_datoms00`.v = 5 ``` or, indeed, a negated operator: ```sql SELECT DISTINCT `all_datoms00`.e AS `?x` FROM `all_datoms` AS `all_datoms00` WHERE `all_datoms00`.v <> 5 ``` This gets even worse when there's more than one value: we get ```sql SELECT DISTINCT `datoms00`.e AS `?x` FROM `datoms` AS `datoms00` WHERE `datoms00`.a = 7 AND NOT EXISTS (SELECT 1 FROM (SELECT 0 AS `?v` WHERE 0 UNION ALL VALUES (28), (29)) AS `c00` WHERE `datoms00`.v = `c00`.`?v`) ``` when we could generate ```sql SELECT DISTINCT `datoms00`.e AS `?x` FROM `datoms` AS `datoms00` WHERE `datoms00`.a = 7 AND `datoms00`.v NOT IN (28, 29) ```