Repository: gluesql/gluesql
Pull request #60 (2020-09-05 08:27:33)

Add support for EXISTS and NOT EXISTS
SELECT foo FROM TableA `WHERE EXISTS`(
  SELECT * FROM TableB WHERE(TableA.foo = TableB.bar)
)

SELECT foo FROM TableA `WHERE NOT EXISTS`(
  SELECT * FROM TableB WHERE(TableA.foo = TableB.bar)
)


Patch:
diff --git a/src/executor/filter.rs b/src/executor/filter.rs
index 15d7da56..71e66f33 100644
--- a/src/executor/filter.rs
+++ b/src/executor/filter.rs
@@ -165,6 +165,7 @@ fn check_expr<'a, T: 'static + Debug>(
 
             Ok(negated ^ (evaluate(low)? <= target && target <= evaluate(high)?))
         }
+        Expr::Exists(query) => Ok(select(storage, query, filter_context)?.next().is_some()),
         _ => Err(FilterError::Unimplemented.into()),
     }
 }


Test patch:
diff --git a/src/tests/filter.rs b/src/tests/filter.rs
index 44852a02..64b78361 100644
--- a/src/tests/filter.rs
+++ b/src/tests/filter.rs
@@ -1,61 +1,68 @@
 use crate::*;
 
-pub fn between(mut tester: impl tests::Tester) {
-    tester.run_and_print(
-        r#"
-CREATE TABLE Test (
-    id INTEGER,
-    name TEXT,
-    strength FLOAT
-)"#,
-    );
-    tester.run_and_print("INSERT INTO Test (id, name, strength) VALUES (1, \"Amelia\", 10.10)");
-    tester.run_and_print("INSERT INTO Test (id, name, strength) VALUES (2, \"Doll\", 20.20)");
-    tester.run_and_print("INSERT INTO Test (id, name, strength) VALUES (3, \"Gascoigne\", 30.30)");
-    tester.run_and_print("INSERT INTO Test (id, name, strength) VALUES (4, \"Gehrman\", 40.40)");
-    tester.run_and_print("INSERT INTO Test (id, name, strength) VALUES (5, \"Maria\", 50.50)");
+pub fn filter(mut tester: impl tests::Tester) {
+    let create_sqls = [
+        "
+        CREATE TABLE Boss (
+            id INTEGER,
+            name TEXT,
+            strength FLOAT
+        );",
+        "
+        CREATE TABLE Hunter (
+            id INTEGER,
+            name TEXT
+        );",
+    ];
 
-    use Value::*;
+    create_sqls.iter().for_each(|sql| tester.run_and_print(sql));
 
-    let found = tester
-        .run("SELECT id, name FROM Test WHERE id BETWEEN 2 AND 4")
-        .expect("select");
-    let expected = select!(
-        I64 Str;
-        2   "Doll".to_owned();
-        3   "Gascoigne".to_owned();
-        4   "Gehrman".to_owned()
-    );
-    assert_eq!(expected, found);
+    let insert_sqls = [
+        "INSERT INTO Boss (id, name, strength) VALUES (1, \"Amelia\", 10.10);",
+        "INSERT INTO Boss (id, name, strength) VALUES (2, \"Doll\", 20.20);",
+        "INSERT INTO Boss (id, name, strength) VALUES (3, \"Gascoigne\", 30.30);",
+        "INSERT INTO Boss (id, name, strength) VALUES (4, \"Gehrman\", 40.40);",
+        "INSERT INTO Boss (id, name, strength) VALUES (5, \"Maria\", 50.50);",
+        "INSERT INTO Hunter (id, name) VALUES (1, \"Gascoigne\");",
+        "INSERT INTO Hunter (id, name) VALUES (2, \"Gehrman\");",
+        "INSERT INTO Hunter (id, name) VALUES (3, \"Maria\");",
+    ];
 
-    let found = tester
-        .run("SELECT id, name FROM Test WHERE name BETWEEN 'Doll' AND 'Gehrman'")
-        .expect("select");
-    let expected = select!(
-        I64 Str;
-        2   "Doll".to_owned();
-        3   "Gascoigne".to_owned();
-        4   "Gehrman".to_owned()
-    );
-    assert_eq!(expected, found);
+    insert_sqls.iter().for_each(|sql| tester.run_and_print(sql));
 
-    let found = tester
-        .run("SELECT name FROM Test WHERE name NOT BETWEEN 'Doll' AND 'Gehrman'")
-        .expect("select");
-    let expected = select!(
-        Str;
-        "Amelia".to_owned();
-        "Maria".to_owned()
-    );
-    assert_eq!(expected, found);
+    let select_sqls = [
+        (3, "SELECT id, name FROM Boss WHERE id BETWEEN 2 AND 4"),
+        (
+            3,
+            "SELECT id, name FROM Boss WHERE name BETWEEN 'Doll' AND 'Gehrman'",
+        ),
+        (
+            2,
+            "SELECT name FROM Boss WHERE name NOT BETWEEN 'Doll' AND 'Gehrman'",
+        ),
+        (
+            2,
+            "SELECT strength, name FROM Boss WHERE name NOT BETWEEN 'Doll' AND 'Gehrman'",
+        ),
+        (
+            3,
+            "SELECT name 
+             FROM Boss 
+             WHERE EXISTS (
+                SELECT * FROM Hunter WHERE Hunter.name = Boss.name
+             )",
+        ),
+        (
+            2,
+            "SELECT name 
+             FROM Boss 
+             WHERE NOT EXISTS (
+                SELECT * FROM Hunter WHERE Hunter.name = Boss.name
+             )",
+        ),
+    ];
 
-    let found = tester
-        .run("SELECT strength, name FROM Test WHERE name NOT BETWEEN 'Doll' AND 'Gehrman'")
-        .expect("select");
-    let expected = select!(
-        F64   Str;
-        10.10 "Amelia".to_owned();
-        50.50 "Maria".to_owned()
-    );
-    assert_eq!(expected, found);
+    select_sqls
+        .iter()
+        .for_each(|(num, sql)| tester.test_rows(sql, *num));
 }
diff --git a/src/tests/mod.rs b/src/tests/mod.rs
index efec1f49..511ffeaa 100644
--- a/src/tests/mod.rs
+++ b/src/tests/mod.rs
@@ -49,6 +49,6 @@ macro_rules! generate_tests {
         glue!(ordering, ordering::ordering);
         glue!(sql_types, sql_types::sql_types);
         glue!(synthesize, synthesize::synthesize);
-        glue!(between, filter::between);
+        glue!(filter, filter::filter);
     };
 }