Repository: gluesql/gluesql
Pull request #56 (2020-09-03 18:37:21)

Add support for BETWEEN and NOT BETWEEN
Add support for `BETWEEN` queries

```sql
SELECT * FROM users WHERE age BETWEEN 20 AND 30 
```

```sql
SELECT * FROM users WHERE age NOT BETWEEN 20 AND 30 
```


Patch:
diff --git a/src/executor/filter.rs b/src/executor/filter.rs
index d565a45c..15d7da56 100644
--- a/src/executor/filter.rs
+++ b/src/executor/filter.rs
@@ -154,6 +154,17 @@ fn check_expr<'a, T: 'static + Debug>(
                 .next()
                 .unwrap_or(Ok(negated))
         }
+        Expr::Between {
+            expr,
+            negated,
+            low,
+            high,
+        } => {
+            let negated = *negated;
+            let target = evaluate(expr)?;
+
+            Ok(negated ^ (evaluate(low)? <= target && target <= evaluate(high)?))
+        }
         _ => Err(FilterError::Unimplemented.into()),
     }
 }


Test patch:
diff --git a/src/tests/filter.rs b/src/tests/filter.rs
new file mode 100644
index 00000000..44852a02
--- /dev/null
+++ b/src/tests/filter.rs
@@ -0,0 +1,61 @@
+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)");
+
+    use Value::*;
+
+    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 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);
+
+    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 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);
+}
diff --git a/src/tests/mod.rs b/src/tests/mod.rs
index f94afa3c..efec1f49 100644
--- a/src/tests/mod.rs
+++ b/src/tests/mod.rs
@@ -4,6 +4,7 @@ pub mod basic;
 pub mod blend;
 pub mod drop_table;
 pub mod error;
+pub mod filter;
 pub mod join;
 pub mod migrate;
 pub mod nested_select;
@@ -48,5 +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);
     };
 }