1use {
2 crate::*,
3 gluesql_core::{error::ValueError, prelude::Value::*},
4};
5
6test_case!(nullable, {
7 let g = get_tester!();
8
9 g.run(
10 "
11 CREATE TABLE Test (
12 id INTEGER NULL,
13 num INTEGER NOT NULL,
14 name TEXT
15 )
16 ",
17 )
18 .await;
19 g.run(
20 "
21 INSERT INTO Test (id, num, name) VALUES
22 (NULL, 2, 'Hello'),
23 ( 1, 9, 'World'),
24 ( 3, 4, 'Great');
25 ",
26 )
27 .await;
28
29 let test_cases = [
30 (
31 "SELECT id, num, name FROM Test",
32 select_with_null!(
33 id | num | name;
34 Null I64(2) Str("Hello".to_owned());
35 I64(1) I64(9) Str("World".to_owned());
36 I64(3) I64(4) Str("Great".to_owned())
37 ),
38 ),
39 (
40 "SELECT id, num FROM Test WHERE id IS NULL AND name = 'Hello'",
41 select_with_null!(
42 id | num;
43 Null I64(2)
44 ),
45 ),
46 (
47 "SELECT id, num FROM Test WHERE id IS NULL",
48 select_with_null!(
49 id | num;
50 Null I64(2)
51 ),
52 ),
53 (
54 "SELECT name FROM Test WHERE SUBSTR(name, 1) IS NULL",
55 select!(
56 name;
57 ),
58 ),
59 (
60 "SELECT id, num FROM Test WHERE id IS NOT NULL",
61 select_with_null!(
62 id | num;
63 I64(1) I64(9);
64 I64(3) I64(4)
65 ),
66 ),
67 (
68 "SELECT id, num FROM Test WHERE id + 1 IS NULL",
69 select_with_null!(
70 id | num;
71 Null I64(2)
72 ),
73 ),
74 (
75 "SELECT id, num FROM Test WHERE id + 1 IS NOT NULL",
76 select_with_null!(
77 id | num;
78 I64(1) I64(9);
79 I64(3) I64(4)
80 ),
81 ),
82 (
83 "SELECT id, num FROM Test WHERE 100 IS NULL",
84 select!(id | num),
85 ),
86 (
87 "SELECT id, num FROM Test WHERE 100 IS NOT NULL",
88 select_with_null!(
89 id | num;
90 Null I64(2);
91 I64(1) I64(9);
92 I64(3) I64(4)
93 ),
94 ),
95 (
96 "SELECT id, num FROM Test WHERE 8 + 3 IS NULL",
97 select!(id | num),
98 ),
99 (
100 "SELECT id, num FROM Test WHERE 8 + 3 IS NOT NULL",
101 select_with_null!(
102 id | num;
103 Null I64(2);
104 I64(1) I64(9);
105 I64(3) I64(4)
106 ),
107 ),
108 (
109 "SELECT id, num FROM Test WHERE NULL IS NULL",
110 select_with_null!(
111 id | num;
112 Null I64(2);
113 I64(1) I64(9);
114 I64(3) I64(4)
115 ),
116 ),
117 (
118 "SELECT id, num FROM Test WHERE NULL IS NOT NULL",
119 select!(id | num),
120 ),
121 (
122 "SELECT id, num FROM Test WHERE (NULL + id) IS NULL;",
123 select_with_null!(
124 id | num;
125 Null I64(2);
126 I64(1) I64(9);
127 I64(3) I64(4)
128 ),
129 ),
130 (
131 "SELECT id, num FROM Test WHERE (NULL + NULL) IS NULL;",
132 select_with_null!(
133 id | num;
134 Null I64(2);
135 I64(1) I64(9);
136 I64(3) I64(4)
137 ),
138 ),
139 (
140 "SELECT id, num FROM Test WHERE 'NULL' IS NULL",
141 select!(id | num),
142 ),
143 (
144 "SELECT id, num FROM Test WHERE 'NULL' IS NOT NULL",
145 select_with_null!(
146 id | num;
147 Null I64(2);
148 I64(1) I64(9);
149 I64(3) I64(4)
150 ),
151 ),
152 (
153 "SELECT id, num FROM Test WHERE (NULL + id) IS NULL;",
154 select_with_null!(
155 id | num;
156 Null I64(2);
157 I64(1) I64(9);
158 I64(3) I64(4)
159 ),
160 ),
161 (
162 "SELECT id, num FROM Test WHERE id + 1 IS NULL;",
163 select_with_null!(
164 id | num;
165 Null I64(2)
166 ),
167 ),
168 (
169 "SELECT id, num FROM Test WHERE 1 + id IS NULL;",
170 select_with_null!(
171 id | num;
172 Null I64(2)
173 ),
174 ),
175 (
176 "SELECT id, num FROM Test WHERE id - 1 IS NULL;",
177 select_with_null!(
178 id | num;
179 Null I64(2)
180 ),
181 ),
182 (
183 "SELECT id, num FROM Test WHERE 1 - id IS NULL;",
184 select_with_null!(
185 id | num;
186 Null I64(2)
187 ),
188 ),
189 (
190 "SELECT id, num FROM Test WHERE id * 1 IS NULL;",
191 select_with_null!(
192 id | num;
193 Null I64(2)
194 ),
195 ),
196 (
197 "SELECT id, num FROM Test WHERE 1 * id IS NULL;",
198 select_with_null!(
199 id | num;
200 Null I64(2)
201 ),
202 ),
203 (
204 "SELECT id, num FROM Test WHERE id / 1 IS NULL;",
205 select_with_null!(
206 id | num;
207 Null I64(2)
208 ),
209 ),
210 (
211 "SELECT id, num FROM Test WHERE 1 / id IS NULL;",
212 select_with_null!(
213 id | num;
214 Null I64(2)
215 ),
216 ),
217 (
218 "SELECT id + 1, 1 + id, id - 1, 1 - id, id * 1, 1 * id, id / 1, 1 / id FROM Test WHERE id IS NULL;",
219 select_with_null!(
220 "id + 1" | "1 + id" | "id - 1" | "1 - id" | "id * 1" | "1 * id" | "id / 1" | "1 / id";
221 Null Null Null Null Null Null Null Null
222 ),
223 ),
224 ];
225
226 for (sql, expected) in test_cases {
227 g.test(sql, Ok(expected)).await;
228 }
229
230 g.run("UPDATE Test SET id = 2").await;
231
232 let test_cases = [
233 ("SELECT id FROM Test", Ok(select!(id I64; 2; 2; 2))),
234 (
235 "SELECT id, num FROM Test",
236 Ok(select!(
237 id | num
238 I64 | I64;
239 2 2;
240 2 9;
241 2 4
242 )),
243 ),
244 (
245 "INSERT INTO Test VALUES (1, NULL, 'ok')",
246 Err(ValueError::NullValueOnNotNullField.into()),
247 ),
248 ];
249
250 for (sql, expected) in test_cases {
251 g.test(sql, expected).await;
252 }
253});
254
255test_case!(nullable_text, {
256 let g = get_tester!();
257
258 g.run(
259 "
260 CREATE TABLE Foo (
261 id INTEGER,
262 name TEXT NULL
263 );
264 ",
265 )
266 .await;
267
268 g.run("INSERT INTO Foo (id, name) VALUES (1, 'Hello'), (2, Null);")
269 .await;
270});
271
272test_case!(nullable_implicit_insert, {
273 let g = get_tester!();
274
275 g.run(
276 "
277 CREATE TABLE Foo (
278 id INTEGER,
279 name TEXT NULL
280 );
281 ",
282 )
283 .await;
284
285 g.run("INSERT INTO Foo (id) VALUES (1)").await;
286 g.test(
287 "SELECT id, name FROM Foo",
288 Ok(select_with_null!(
289 id | name;
290 I64(1) Null
291 )),
292 )
293 .await;
294});