limbo/testing/where.test

#!/usr/bin/env tclsh

set testdir [file dirname $argv0]
source $testdir/tester.tcl

do_execsql_test_small where-is-null {
    select count(*) from demo where value is null;
} {2}

do_execsql_test_small where-equals-null {
    select count(*) from demo where value = null;
} {0}

do_execsql_test_small where-is-not-null {
    select count(*) from demo where value is not null;
} {3}

do_execsql_test_small where-not-equal-null {
    select count(*) from demo where value != null;
} {0}

do_execsql_test_small where-is-a-with-nulls {
    select count(*) from demo where value is 'A';
} {1}

do_execsql_test_small where-equals-a-with-nulls {
    select count(*) from demo where value == 'A';
} {1}

do_execsql_test_small where-is-not-a-with-nulls {
    select count(*) from demo where value is not 'A';
} {4}

do_execsql_test_small where-not-equals-a-with-nulls {
    select count(*) from demo where value != 'A';
} {2}

do_execsql_test_small where-is-null-combined {
    select * from demo where value is null or id = 3;
} {2|
3|B
4|
}

do_execsql_test_small where-is-not-null-combined {
    select * from demo where value is not null or id = 2;
} {1|A
2|
3|B
5|C
}

do_execsql_test where-clause-eq {
    select last_name from users where id = 2000;
} {Rodriguez}

do_execsql_test where-clause-eq-string {
    select count(1) from users where last_name = 'Rodriguez';
} {61}

do_execsql_test where-clause-isnull {
    select count(1) from users where last_name isnull;
} {0}

do_execsql_test where-clause-isnull-or-false {
    select count(1) from users where null isnull or 1 != 1;
} {10000}

do_execsql_test where-clause-notnull {
    select count(1) from users where last_name not null;
} {10000}

do_execsql_test where-clause-notnull-or-false {
    select count(1) from users where last_name not null or 1 != 1;
} {10000}

do_execsql_test where-clause-ne {
    select count(1) from users where id != 2000;
} {9999}

do_execsql_test where-clause-gt {
    select count(1) from users where id > 2000;
} {8000}

do_execsql_test where-clause-gte {
    select count(1) from users where id >= 2000;
} {8001}

do_execsql_test where-clause-lt {
    select count(1) from users where id < 2000;
} {1999}

do_execsql_test where-clause-lte {
    select count(1) from users where id <= 2000;
} {2000}

do_execsql_test where-clause-unary-true {
    select count(1) from users where 1;
} {10000}

# not correct? should be 0?
do_execsql_test where-clause-unary-false {
    select count(1) from users where 0;
} {0}

do_execsql_test where-clause-no-table-constant-condition-true {
    select 1 where 1;
} {1}

do_execsql_test where-clause-no-table-constant-condition-identifier-true {
    select 1 where true;
} {1}

do_execsql_test where-clause-no-table-constant-condition-true-2 {
    select 1 where '1';
} {1}

do_execsql_test where-clause-no-table-constant-condition-true-3 {
    select 1 where 6.66;
} {1}

do_execsql_test where-clause-no-table-constant-condition-true-4 {
    select 1 where '6.66';
} {1}

do_execsql_test where-clause-no-table-constant-condition-true-5 {
    select 1 where -1;
} {1}

do_execsql_test where-clause-no-table-constant-condition-true-6 {
    select 1 where '-1';
} {1}

do_execsql_test where-clause-no-table-constant-condition-false {
    select 1 where 0;
} {}

do_execsql_test where-clause-no-table-constant-condition-identifier-false {
    select 1 where false;
} {}

do_execsql_test where-clause-no-table-constant-condition-false-2 {
    select 1 where '0';
} {}

do_execsql_test where-clause-no-table-constant-condition-false-3 {
    select 1 where 0.0;
} {}

do_execsql_test where-clause-no-table-constant-condition-false-4 {
    select 1 where '0.0';
} {}

do_execsql_test where-clause-no-table-constant-condition-false-5 {
    select 1 where -0.0;
} {}

do_execsql_test where-clause-no-table-constant-condition-false-6 {
    select 1 where '-0.0';
} {}

do_execsql_test where-clause-no-table-constant-condition-false-7 {
    select 1 where 'hamburger';
} {}

if {[info exists ::env(SQLITE_EXEC)] && ($::env(SQLITE_EXEC) eq "scripts/limbo-sqlite3-index-experimental" || $::env(SQLITE_EXEC) eq "sqlite3")} {
    # this test functions as an assertion that the index on users.age is being used, since the results are ordered by age without an order by.
    do_execsql_test select-where-and {
        select first_name, age from users where first_name = 'Jamie' and age > 80
    } {Jamie|87
    Jamie|88
    Jamie|88
    Jamie|92
    Jamie|94
    Jamie|99
    }
    do_execsql_test select-where-or {
        select first_name, age from users where first_name = 'Jamie' and age > 80
    } {Jamie|87
    Jamie|88
    Jamie|88
    Jamie|92
    Jamie|94
    Jamie|99
    }
} else {
    # this test functions as an assertion that the index on users.age is being used, since the results are ordered by age without an order by.
    do_execsql_test select-where-and {
        select first_name, age from users where first_name = 'Jamie' and age > 80
    } {Jamie|94
    Jamie|88
    Jamie|99
    Jamie|92
    Jamie|87
    Jamie|88
    }
    do_execsql_test select-where-or {
        select first_name, age from users where first_name = 'Jamie' and age > 80
    } {Jamie|94
    Jamie|88
    Jamie|99
    Jamie|92
    Jamie|87
    Jamie|88
    }

}


do_execsql_test select-where-and-or {
    select first_name, age from users where first_name = 'Jamie' or age = 1 and age = 2
} {Jamie|94
Jamie|88
Jamie|31
Jamie|26
Jamie|71
Jamie|50
Jamie|28
Jamie|46
Jamie|17
Jamie|64
Jamie|76
Jamie|99
Jamie|92
Jamie|47
Jamie|27
Jamie|54
Jamie|47
Jamie|15
Jamie|12
Jamie|71
Jamie|87
Jamie|34
Jamie|88
Jamie|41
Jamie|73
}

do_execsql_test where-float-int {
    select * from products where price > 50 and name != 'hat';
} {2|cap|82.0
5|sweatshirt|74.0
6|shorts|70.0
7|jeans|78.0
8|sneakers|82.0
11|accessories|81.0}

do_execsql_test where-multiple-and {
    select * from products where price > 50 and name != 'sweatshirt' and price < 75;
} {6|shorts|70.0}

do_execsql_test where-multiple-or {
    select * from products where price > 75 or name = 'shirt' or name = 'coat';
} {1|hat|79.0
2|cap|82.0
3|shirt|18.0
7|jeans|78.0
8|sneakers|82.0
10|coat|33.0
11|accessories|81.0}

do_execsql_test where_in_list {
    select * from products where name in ('hat', 'sweatshirt', 'shorts');
} {1|hat|79.0
5|sweatshirt|74.0
6|shorts|70.0}

do_execsql_test where_not_in_list {
    select * from products where name not in ('hat', 'sweatshirt', 'shorts');
} {2|cap|82.0
3|shirt|18.0
4|sweater|25.0
7|jeans|78.0
8|sneakers|82.0
9|boots|1.0
10|coat|33.0
11|accessories|81.0}

do_execsql_test where_in_list_or_another_list {
    select * from products where name in ('hat', 'sweatshirt', 'shorts') or price in (81.0, 82.0);
} {1|hat|79.0
2|cap|82.0
5|sweatshirt|74.0
6|shorts|70.0
8|sneakers|82.0
11|accessories|81.0}

do_execsql_test where_not_in_list_and_not_in_another_list {
    select * from products where name not in ('hat', 'sweatshirt', 'shorts') and price not in (81.0, 82.0, 78.0, 1.0, 33.0);
} {3|shirt|18.0
4|sweater|25.0}

do_execsql_test where_in_list_or_not_in_another_list {
    select * from products where name in ('hat', 'sweatshirt', 'shorts') or price not in (82.0, 18.0, 78.0, 33.0, 81.0);
} {1|hat|79.0
4|sweater|25.0
5|sweatshirt|74.0
6|shorts|70.0
9|boots|1.0}

do_execsql_test where_in_empty_list {
    select * from products where name in ();
} {}

do_execsql_test where_not_in_empty_list {
    select * from products where name not in ();
} {1|hat|79.0
2|cap|82.0
3|shirt|18.0
4|sweater|25.0
5|sweatshirt|74.0
6|shorts|70.0
7|jeans|78.0
8|sneakers|82.0
9|boots|1.0
10|coat|33.0
11|accessories|81.0}

do_execsql_test where_name_in_list_and_price_gt_70_or_name_exactly_boots {
    select * from products where name in ('hat', 'sweatshirt', 'shorts') and price > 70 or name = 'boots';
} {1|hat|79.0
5|sweatshirt|74.0
9|boots|1.0}

do_execsql_test where_name_in_list_or_price_gt_70_and_name_like_shirt {
    select * from products where name in ('hat', 'shorts') or price > 70 and name like '%shirt%';
} {1|hat|79.0
5|sweatshirt|74.0
6|shorts|70.0}

do_execsql_test where_name_not_in_list_or_name_eq_shirt {
    select * from products where name not in ('shirt', 'boots') or name = 'shirt';
} {1|hat|79.0
2|cap|82.0
3|shirt|18.0
4|sweater|25.0
5|sweatshirt|74.0
6|shorts|70.0
7|jeans|78.0
8|sneakers|82.0
10|coat|33.0
11|accessories|81.0}

do_execsql_test where_multiple {
    select id, first_name, age from users where id = 5 and age < 50;
} {5|Edward|15}

do_execsql_test where_multiple_flipped {
    select id, first_name, age from users where age < 50 and id = 5;
} {5|Edward|15}

do_execsql_test where-parentheses-and {
    select id, name from products where (id = 5 and name = 'sweatshirt') and (id = 5 and name = 'sweatshirt') ORDER BY id;
} {5|sweatshirt}

do_execsql_test where-nested-parentheses {
    select id, name from products where ((id = 5 and name = 'sweatshirt') or (id = 1 and name = 'hat')) ORDER BY id;
} {1|hat
5|sweatshirt}

do_execsql_test where-complex-parentheses {
    select id, name from products where ((id = 5 and name = 'sweatshirt') or (id = 1 and name = 'hat')) and (name = 'sweatshirt' or name = 'hat') ORDER BY id;
} {1|hat
5|sweatshirt}

# regression test for primary key index behavior
do_execsql_test where-id-index-seek-regression-test {
    select id from users where id > 9995;
} {9996
9997
9998
9999
10000}

do_execsql_test where-id-index-seek-regression-test-opposite {
    select id from users where 9999 < id;
    select id from users where 10000 <= id;
    select id from users where 2 > id;
    select id from users where 1 >= id;
} {10000
10000
1
1}

do_execsql_test where-id-index-seek-regression-test-2 {
    select count(1) from users where id > 0;
} {10000}

# regression test for secondary index (users.age) behavior
do_execsql_test where-age-index-seek-regression-test {
    select age from users where age >= 100 limit 20;
} {100
100
100
100
100
100
100
100
100
100
100
100
100
100
100
100
100
100
100
100}

do_execsql_test where-age-index-seek-regression-test-2 {
    select count(1) from users where age > 0;
} {10000}

if {[info exists ::env(SQLITE_EXEC)] && ($::env(SQLITE_EXEC) eq "scripts/limbo-sqlite3-index-experimental" || $::env(SQLITE_EXEC) eq "sqlite3")} {
    do_execsql_test where-age-index-seek-regression-test-3 {
        select age from users where age > 90 limit 1;
    } {91}
} else {
    do_execsql_test where-age-index-seek-regression-test-3 {
        select age from users where age > 90 limit 1;
    } {94}

}

do_execsql_test where-simple-between {
    SELECT * FROM products WHERE price BETWEEN 70 AND 100;
} {1|hat|79.0
2|cap|82.0
5|sweatshirt|74.0
6|shorts|70.0
7|jeans|78.0
8|sneakers|82.0
11|accessories|81.0}

do_execsql_test between-price-range-with-names {
    SELECT * FROM products 
    WHERE (price BETWEEN 70 AND 100) 
    AND (name = 'sweatshirt' OR name = 'sneakers');
} {5|sweatshirt|74.0
8|sneakers|82.0}

do_execsql_test where-between-true-and-2 {
    select id from users where id between true and 2;
} {1
2}

do_execsql_test nested-parens-conditionals-or-and-or {
    SELECT count(*) FROM users WHERE ((age > 25 OR age < 18) AND (city = 'Boston' OR state = 'MA'));
} {146}

do_execsql_test nested-parens-conditionals-and-or-and {
    SELECT * FROM users WHERE (((age > 18 AND city = 'New Mario') OR age = 92) AND city = 'Lake Paul');
} {{9989|Timothy|Harrison|woodsmichael@example.net|+1-447-830-5123|782 Wright Harbors|Lake Paul|ID|52330|92}}


do_execsql_test nested-parens-conditionals-and-double-or {
  SELECT * FROM users WHERE ((age > 30 OR age < 20) AND (state = 'NY' OR state = 'CA')) AND first_name glob 'An*' order by id;
} {{1738|Angelica|Pena|jacksonjonathan@example.net|(867)536-1578x039|663 Jacqueline Estate Apt. 652|Clairehaven|NY|64172|74
1811|Andrew|Mckee|jchen@example.net|359.939.9548|19809 Blair Junction Apt. 438|New Lawrencefort|NY|26240|42
3773|Andrew|Peterson|cscott@example.com|(405)410-4972x90408|90513 Munoz Radial Apt. 786|Travisfurt|CA|52951|43
3875|Anthony|Cordova|ocross@example.org|+1-356-999-4070x557|77081 Aguilar Turnpike|Michaelfurt|CA|73353|37
4909|Andrew|Carson|michelle31@example.net|823.423.1516|78514 Luke Springs|Lake Crystal|CA|49481|74
5498|Anna|Hall|elizabethheath@example.org|9778473725|5803 Taylor Tunnel|New Nicholaston|NY|21825|14
6340|Angela|Freeman|juankelly@example.net|501.372.4720|3912 Ricardo Mission|West Nancyville|NY|60823|34
8171|Andrea|Lee|dgarrison@example.com|001-594-430-0646|452 Anthony Stravenue|Sandraville|CA|28572|12
9110|Anthony|Barrett|steven05@example.net|(562)928-9177x8454|86166 Foster Inlet Apt. 284|North Jeffreyburgh|CA|80147|97
9279|Annette|Lynn|joanne37@example.com|(272)700-7181|2676 Laura Points Apt. 683|Tristanville|NY|48646|91}}

# Regression test for nested parens + OR + AND. This returned 0 rows before the fix.
# It should always return 1 row because it is true for id = 6.
do_execsql_test nested-parens-and-inside-or-regression-test {
    SELECT count(1) FROM users 
    WHERE (
        (
            (
                (id != 5) 
                AND 
                (id = 5 OR TRUE)
            ) 
            OR FALSE
        ) 
        AND 
        (id = 6 OR FALSE)
    );
} {1}

# Regression tests for binary conditional jump comparisons where one operand is null
# Test behavior of binary comparisons (=,>,<,>=,<=,!=) when one operand is NULL
# Each test has 3 variants:
# 1. Simple comparison with NULL (should return empty)
# 2. Comparison with NULL OR id=1 (should return Jamie)
# 3. Comparison with NULL AND id=1 (should return empty)
foreach {operator} {
    =
    >  
    <
    >=
    <=
    !=
} {
    # Simple NULL comparison
    do_execsql_test where-binary-one-operand-null-$operator "select * from users where first_name $operator NULL" {}

    # NULL comparison OR id=1 
    do_execsql_test where-binary-one-operand-null-or-$operator "select first_name from users where first_name $operator NULL OR id = 1" {Jamie}

    # NULL comparison AND id=1
    do_execsql_test where-binary-one-operand-null-and-$operator "select first_name from users where first_name $operator NULL AND id = 1" {}
}

# Test literals in WHERE clause
do_execsql_test where-literal-string {
    select count(*) from users where 'yes';
} {0}

# FIXME: should return 0
#do_execsql_test where-literal-number {
#    select count(*) from users where x'DEADBEEF';
#} {0}

# Test CAST in WHERE clause
do_execsql_test where-cast-string-to-int {
    select count(*) from users where cast('1' as integer);
} {10000}

do_execsql_test where-cast-float-to-int {
    select count(*) from users where cast('0' as integer);
} {0}

# Test FunctionCall in WHERE clause
do_execsql_test where-function-length {
    select count(*) from users where length(first_name);
} {10000}

# Test CASE in WHERE clause
do_execsql_test where-case-simple {
    select count(*) from users where 
    case when age > 0 then 1 else 0 end;
} {10000}

do_execsql_test where-case-searched {
    select count(*) from users where 
    case age 
        when 0 then 0
        else 1
    end;
} {10000}

# Test unary operators in WHERE clause
do_execsql_test where-unary-not {
    select count(*) from users where not (id = 1);
} {9999}

do_execsql_test where-unary-plus {
    select count(*) from users where +1;
} {10000}

do_execsql_test where-unary-minus {
    select count(*) from users where -1;
} {10000}

do_execsql_test where-unary-bitnot {
    select count(*) from users where ~1;
} {10000}

# Test binary math operators in WHERE clause
do_execsql_test where-binary-add {
    select count(*) from users where 1 + 1;
} {10000}

do_execsql_test where-binary-subtract {
    select count(*) from users where 2 - 1;
} {10000}

do_execsql_test where-binary-multiply {
    select count(*) from users where 2 * 1;
} {10000}

do_execsql_test where-binary-divide {
    select count(*) from users where 2 / 2;
} {10000}

do_execsql_test where-binary-modulo {
    select count(*) from users where 3 % 2;
} {10000}

do_execsql_test where-binary-shift-left {
    select count(*) from users where 1 << 1;
} {10000}

do_execsql_test where-binary-shift-right {
    select count(*) from users where 2 >> 1;
} {10000}

do_execsql_test where-binary-bitwise-and {
    select count(*) from users where 3 & 1;
} {10000}

do_execsql_test where-binary-bitwise-or {
    select count(*) from users where 2 | 1;
} {10000}

do_execsql_test where-constant-condition-no-tables {
    select 1 where 1 IS NULL;
} {}

do_execsql_test where-constant-condition-no-tables-2 {
    select 1 where 1 IS NOT NULL;
} {1}

# We had a bug where NULL was incorrectly used as a seek key, returning all rows (because NULL < everything in index keys)
do_execsql_test where-null-comparison-index-seek-regression-test {
    select age from users where age > NULL;
} {}

# We had a bug where Limbo tried to use an index when there was a WHERE term like 't.x = t.x'
do_execsql_test where-self-referential-regression {
  select count(1) from users where id = id;
} {10000}