gluesql_test_suite/
like_ilike.rs

1use {
2    crate::*,
3    bigdecimal::BigDecimal,
4    gluesql_core::{
5        data::Literal,
6        error::{LiteralError, ValueError},
7        prelude::Value::{self, Bool},
8    },
9    std::{borrow::Cow, str::FromStr},
10};
11
12test_case!(like_ilike, {
13    let g = get_tester!();
14
15    g.named_test(
16        "basic usage - LIKE and ILIKE",
17        "
18            VALUES
19                ('abc' LIKE '%c'),
20                ('abc' NOT LIKE '_c'),
21                ('abc' LIKE '_b_'),
22                ('HELLO' ILIKE '%el%'),
23                ('HELLO' NOT ILIKE '_ELLE');
24        ",
25        Ok(select!(column1 Bool; true; true; true; true; true)),
26    )
27    .await;
28
29    g.run(
30        "
31        CREATE TABLE Item (
32            id INTEGER,
33            name TEXT
34        );
35    ",
36    )
37    .await;
38    g.run(
39        "
40        INSERT INTO Item (id, name) VALUES
41            (1,    'Amelia'),
42            (2,      'Doll'),
43            (3, 'Gascoigne'),
44            (4,   'Gehrman'),
45            (5,     'Maria');
46    ",
47    )
48    .await;
49
50    let test_cases = [
51        (2, "SELECT name FROM Item WHERE name LIKE '_a%'"),
52        (2, "SELECT name FROM Item WHERE name LIKE '%r%'"),
53        (2, "SELECT name FROM Item WHERE SUBSTR(name, 1) LIKE '%a'"),
54        (0, "SELECT name FROM Item WHERE 'name' LIKE SUBSTR('%a', 1)"),
55        (
56            2,
57            "SELECT name FROM Item WHERE SUBSTR(name, 1) LIKE SUBSTR('%a', 1)",
58        ),
59        (
60            2,
61            "SELECT name FROM Item WHERE SUBSTR(name, 1) LIKE SUBSTR('%a', 1)",
62        ),
63        (
64            2,
65            "SELECT name FROM Item WHERE LOWER(name) LIKE SUBSTR('%a', 1)",
66        ),
67        (
68            2,
69            "SELECT name FROM Item WHERE SUBSTR(name, 1) LIKE '%' || LOWER('A')",
70        ),
71        (5, "SELECT name FROM Item WHERE name LIKE '%%'"),
72        (0, "SELECT name FROM Item WHERE name LIKE 'g%'"),
73        (2, "SELECT name FROM Item WHERE name ILIKE '_A%'"),
74        (2, "SELECT name FROM Item WHERE name ILIKE 'g%'"),
75        (5, "SELECT name FROM Item WHERE name ILIKE '%%'"),
76        (1, "SELECT name FROM Item WHERE name NOT LIKE '%a%'"),
77        (1, "SELECT name FROM Item WHERE name NOT ILIKE '%A%'"),
78        (5, "SELECT name FROM Item WHERE 'ABC' LIKE '_B_'"),
79        (5, "SELECT name FROM Item WHERE 'abc' ILIKE '_B_'"),
80        (5, "SELECT name FROM Item WHERE 'ABC' ILIKE '_B_'"),
81    ];
82
83    for (num, sql) in test_cases {
84        g.count(sql, num).await;
85    }
86
87    let error_sqls = [
88        (
89            "SELECT name FROM Item WHERE 'ABC' LIKE 10",
90            LiteralError::LikeOnNonString {
91                base: format!("{:?}", Literal::Text(Cow::Owned("ABC".to_owned()))),
92                pattern: format!(
93                    "{:?}",
94                    Literal::Number(Cow::Owned(BigDecimal::from_str("10").unwrap()))
95                ),
96                case_sensitive: true,
97            }
98            .into(),
99        ),
100        (
101            "SELECT name FROM Item WHERE True ILIKE '_B_'",
102            LiteralError::LikeOnNonString {
103                base: format!("{:?}", Literal::Boolean(true)),
104                pattern: format!("{:?}", Literal::Text(Cow::Owned("_B_".to_owned()))),
105                case_sensitive: false,
106            }
107            .into(),
108        ),
109        (
110            "SELECT name FROM Item WHERE name = 'Amelia' AND name LIKE 10",
111            ValueError::LikeOnNonString {
112                base: Value::Str("Amelia".to_owned()),
113                pattern: Value::I64(10),
114                case_sensitive: true,
115            }
116            .into(),
117        ),
118        (
119            "SELECT name FROM Item WHERE name = 'Amelia' AND name ILIKE 10",
120            ValueError::LikeOnNonString {
121                base: Value::Str("Amelia".to_owned()),
122                pattern: Value::I64(10),
123                case_sensitive: false,
124            }
125            .into(),
126        ),
127    ];
128
129    for (sql, error) in error_sqls {
130        g.test(sql, Err(error)).await;
131    }
132});
gluesql_test_suite/like_ilike.rs

gluesql_test_suite/
like_ilike.rs
