1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
use {crate::*, gluesql_core::translate::TranslateError};
test_case!(order_by, async move {
run!(
r#"
CREATE TABLE Test (
id INTEGER,
num INTEGER,
name TEXT NULL,
rate FLOAT NULL
)"#
);
run!(
r#"
INSERT INTO Test (id, num, name, rate)
VALUES
(1, 2, "Hello", 3.0),
(1, 9, NULL, NULL),
(3, 4, "World", 1.0),
(4, 7, "Thursday", NULL);
"#
);
use gluesql_core::prelude::Value::*;
test!(
Ok(select!(
id | num
I64 | I64;
1 2;
1 9;
3 4;
4 7
)),
"SELECT id, num FROM Test"
);
macro_rules! s {
($v: literal) => {
Str($v.to_owned())
};
}
test!(
Ok(select_with_null!(
id | num | name;
I64(1) I64(2) s!("Hello");
I64(3) I64(4) s!("World");
I64(1) I64(9) Null;
I64(4) I64(7) s!("Thursday")
)),
"SELECT id, num, name FROM Test ORDER BY id + num ASC"
);
test!(
Ok(select_with_null!(
id | num | name;
I64(1) I64(9) Null;
I64(4) I64(7) s!("Thursday");
I64(3) I64(4) s!("World");
I64(1) I64(2) s!("Hello")
)),
"SELECT id, num, name FROM Test ORDER BY num DESC"
);
test!(
Ok(select_with_null!(
id | num | name;
I64(1) I64(2) s!("Hello");
I64(4) I64(7) s!("Thursday");
I64(3) I64(4) s!("World");
I64(1) I64(9) Null
)),
"SELECT id, num, name FROM Test ORDER BY name"
);
test!(
Ok(select_with_null!(
id | num | name;
I64(1) I64(9) Null;
I64(3) I64(4) s!("World");
I64(4) I64(7) s!("Thursday");
I64(1) I64(2) s!("Hello")
)),
"SELECT id, num, name FROM Test ORDER BY name DESC"
);
test!(
Ok(select_with_null!(
id | num | name | rate;
I64(4) I64(7) s!("Thursday") Null;
I64(1) I64(9) Null Null;
I64(1) I64(2) s!("Hello") F64(3.0);
I64(3) I64(4) s!("World") F64(1.0)
)),
"SELECT id, num, name, rate FROM Test ORDER BY rate DESC, id DESC"
);
test!(
Ok(select!(
id | num
I64 | I64;
1 9;
1 2;
3 4;
4 7
)),
"SELECT id, num FROM Test ORDER BY id ASC, num DESC"
);
test!(
Ok(select!(
id | num
I64 | I64;
1 9;
1 2;
3 4;
4 7
)),
"
SELECT id, num FROM Test
ORDER BY
(SELECT id FROM Test t2 WHERE Test.id = t2.id LIMIT 1) ASC,
num DESC
"
);
test!(
Ok(select!(
id | num
I64 | I64;
1 9;
1 2;
3 4;
4 7
)),
"
SELECT id, num FROM Test
ORDER BY
(SELECT t2.id FROM Test t2
WHERE Test.id = t2.id
ORDER BY (Test.id + t2.id) LIMIT 1
) ASC,
num DESC;
"
);
test!(
Err(TranslateError::OrderByNullsFirstOrLastNotSupported.into()),
"SELECT * FROM Test ORDER BY id NULLS FIRST"
);
});
