modules/sql-engine/src/integrationTest/sql/group2/sqlite/select2/select2_results.test

statement ok
CREATE TABLE t1(a INTEGER, b INTEGER, c INTEGER, d INTEGER, e INTEGER)

statement ok
INSERT INTO t1(e,c,b,d,a) VALUES(NULL,102,NULL,101,104)

statement ok
INSERT INTO t1(a,c,d,e,b) VALUES(107,106,108,109,105)

statement ok
INSERT INTO t1(e,d,b,a,c) VALUES(110,114,112,NULL,113)

statement ok
INSERT INTO t1(d,c,e,a,b) VALUES(116,119,117,115,NULL)

statement ok
INSERT INTO t1(c,d,b,e,a) VALUES(123,122,124,NULL,121)

statement ok
INSERT INTO t1(a,d,b,e,c) VALUES(127,128,129,126,125)

statement ok
INSERT INTO t1(e,c,a,d,b) VALUES(132,134,131,133,130)

statement ok
INSERT INTO t1(a,d,b,e,c) VALUES(138,136,139,135,137)

statement ok
INSERT INTO t1(e,c,d,a,b) VALUES(144,141,140,142,143)

statement ok
INSERT INTO t1(b,a,e,d,c) VALUES(145,149,146,NULL,147)

statement ok
INSERT INTO t1(b,c,a,d,e) VALUES(151,150,153,NULL,NULL)

statement ok
INSERT INTO t1(c,e,a,d,b) VALUES(155,157,159,NULL,158)

statement ok
INSERT INTO t1(c,b,a,d,e) VALUES(161,160,163,164,162)

statement ok
INSERT INTO t1(b,d,a,e,c) VALUES(167,NULL,168,165,166)

statement ok
INSERT INTO t1(d,b,c,e,a) VALUES(171,170,172,173,174)

statement ok
INSERT INTO t1(e,c,a,d,b) VALUES(177,176,179,NULL,175)

statement ok
INSERT INTO t1(b,e,a,d,c) VALUES(181,180,182,183,184)

statement ok
INSERT INTO t1(c,a,b,e,d) VALUES(187,188,186,189,185)

statement ok
INSERT INTO t1(d,b,c,e,a) VALUES(190,194,193,192,191)

statement ok
INSERT INTO t1(a,e,b,d,c) VALUES(199,197,198,196,195)

statement ok
INSERT INTO t1(b,c,d,a,e) VALUES(NULL,202,203,201,204)

statement ok
INSERT INTO t1(c,e,a,b,d) VALUES(208,NULL,NULL,206,207)

statement ok
INSERT INTO t1(c,e,a,d,b) VALUES(214,210,213,212,211)

statement ok
INSERT INTO t1(b,c,a,d,e) VALUES(218,215,216,217,219)

statement ok
INSERT INTO t1(b,e,d,a,c) VALUES(223,221,222,220,224)

statement ok
INSERT INTO t1(d,e,b,a,c) VALUES(226,227,228,229,225)

statement ok
INSERT INTO t1(a,c,b,e,d) VALUES(234,231,232,230,233)

statement ok
INSERT INTO t1(e,b,a,c,d) VALUES(237,236,239,NULL,238)

statement ok
INSERT INTO t1(e,c,b,a,d) VALUES(NULL,244,240,243,NULL)

statement ok
INSERT INTO t1(e,d,c,b,a) VALUES(246,248,247,249,245)

query I rowsort
SELECT a+b*2
  FROM t1
 WHERE (a>b-2 AND a<b+2)
   AND b IS NOT NULL
   AND a>b
----
391
475
502
544
595
685

query IIII rowsort
SELECT a+b*2+c*3+d*4+e*5,
       abs(a),
       d,
       CASE WHEN a<b-3 THEN 111 WHEN a<=b THEN 222
        WHEN a<b+3 THEN 333 ELSE 444 END
  FROM t1
 WHERE e+d BETWEEN a+b-10 AND c+130
   AND (e>c OR e<d)
----
1612
107
108
333
1902
127
128
222

query IIIIIII rowsort
SELECT a-b,
       b-c,
       abs(a),
       abs(b-c),
       CASE a+1 WHEN b THEN 111 WHEN c THEN 222
        WHEN d THEN 333  WHEN e THEN 444 ELSE 555 END,
       b,
       d-e
  FROM t1
 WHERE b IS NOT NULL
   AND e+d BETWEEN a+b-10 AND c+130
   AND c BETWEEN b-2 AND d+2
----
2
-1
107
1
333
105
-1

query II rowsort
SELECT a+b*2+c*3+d*4,
       CASE WHEN c>(SELECT avg(c) FROM t1) THEN a*2 ELSE b*10 END
  FROM t1
 WHERE e+d BETWEEN a+b-10 AND c+130
   AND EXISTS(SELECT 1 FROM t1 AS x WHERE x.b<t1.b)
----
1272
1290

query IIIIIII rowsort
SELECT CASE WHEN c>(SELECT avg(c) FROM t1) THEN a*2 ELSE b*10 END,
       abs(a),
       CASE a+1 WHEN b THEN 111 WHEN c THEN 222
        WHEN d THEN 333  WHEN e THEN 444 ELSE 555 END,
       a+b*2,
       a,
       d-e,
       d
  FROM t1
 WHERE a IS NULL
   AND d>e
----
1120
NULL
555
NULL
NULL
4
114

query I rowsort
SELECT d
  FROM t1
 WHERE c>d
   AND (c<=d-2 OR c>=d+2)
   AND (e>c OR e<d)
----
185
212
222

query I rowsort
SELECT CASE a+1 WHEN b THEN 111 WHEN c THEN 222
        WHEN d THEN 333  WHEN e THEN 444 ELSE 555 END
  FROM t1
 WHERE a IS NULL
----
555
555

query II rowsort
SELECT b-c,
       c
  FROM t1
 WHERE (e>a AND e<b)
----
-1
224
1
193
2
247

query III rowsort
SELECT a+b*2+c*3+d*4+e*5,
       a+b*2+c*3,
       c
  FROM t1
 WHERE b>c
   AND a>b
   AND e+d BETWEEN a+b-10 AND c+130
----

query I rowsort
SELECT b-c
  FROM t1
 WHERE a IS NULL
   AND (e>c OR e<d)
----
-1

query II rowsort
SELECT c-d,
       CASE WHEN a<b-3 THEN 111 WHEN a<=b THEN 222
        WHEN a<b+3 THEN 333 ELSE 444 END
  FROM t1
 WHERE c BETWEEN b-2 AND d+2
   AND e+d BETWEEN a+b-10 AND c+130
   AND (c<=d-2 OR c>=d+2)
----
-2
333

query III rowsort
SELECT a+b*2+c*3,
       d-e,
       (a+b+c+d+e)/5
  FROM t1
 WHERE coalesce(a,b,c,d,e)<>0
   AND (e>c OR e<d)
   AND e+d BETWEEN a+b-10 AND c+130
----
635
-1
107
760
2
127

query I rowsort
SELECT abs(b-c)
  FROM t1
 WHERE b IS NOT NULL
   AND e+d BETWEEN a+b-10 AND c+130
----
1
4

query IIIII rowsort
SELECT (a+b+c+d+e)/5,
       c-d,
       CASE a+1 WHEN b THEN 111 WHEN c THEN 222
        WHEN d THEN 333  WHEN e THEN 444 ELSE 555 END,
       a+b*2+c*3,
       d-e
  FROM t1
 WHERE a>b
   AND (e>c OR e<d)
   AND a IS NULL
----

query IIIII rowsort
SELECT a+b*2+c*3,
       a+b*2+c*3+d*4+e*5,
       d,
       CASE WHEN c>(SELECT avg(c) FROM t1) THEN a*2 ELSE b*10 END,
       abs(b-c)
  FROM t1
 WHERE c BETWEEN b-2 AND d+2
   AND a IS NULL
   AND d>e
----
NULL
NULL
114
1120
1

query IIII rowsort
SELECT a+b*2+c*3+d*4,
       CASE WHEN a<b-3 THEN 111 WHEN a<=b THEN 222
        WHEN a<b+3 THEN 333 ELSE 444 END,
       a-b,
       c-d
  FROM t1
 WHERE (a>b-2 AND a<b+2)
   AND (c<=d-2 OR c>=d+2)
----

query III rowsort
SELECT abs(b-c),
       (a+b+c+d+e)/5,
       d
  FROM t1
 WHERE (e>a AND e<b)
   AND a IS NULL
----

query I rowsort
SELECT (SELECT count(*) FROM t1 AS x WHERE x.b<t1.b)
  FROM t1
 WHERE (a>b-2 AND a<b+2)
   AND d>e
   AND b>c
----
5

query I rowsort
SELECT abs(b-c)
  FROM t1
 WHERE (e>a AND e<b)
   AND c>d
----
1
1

query III rowsort
SELECT d,
       abs(a),
       (SELECT count(*) FROM t1 AS x WHERE x.b<t1.b)
  FROM t1
 WHERE a IS NULL
   AND a>b
----

query IIIII rowsort
SELECT a+b*2+c*3+d*4+e*5,
       CASE WHEN a<b-3 THEN 111 WHEN a<=b THEN 222
        WHEN a<b+3 THEN 333 ELSE 444 END,
       CASE WHEN c>(SELECT avg(c) FROM t1) THEN a*2 ELSE b*10 END,
       b,
       abs(b-c)
  FROM t1
 WHERE (c<=d-2 OR c>=d+2)
   AND c>d
   AND a IS NULL
----

query IIII rowsort
SELECT abs(b-c),
       (a+b+c+d+e)/5,
       b-c,
       a+b*2
  FROM t1
 WHERE e+d BETWEEN a+b-10 AND c+130
   AND d NOT BETWEEN 110 AND 150
----
1
107
-1
317

query I rowsort
SELECT c
  FROM t1
 WHERE EXISTS(SELECT 1 FROM t1 AS x WHERE x.b<t1.b)
   AND (c<=d-2 OR c>=d+2)
   AND (e>c OR e<d)
----
125
161
187
214
215
224
231

query I rowsort
SELECT d-e
  FROM t1
 WHERE (e>a AND e<b)
----
-2
1
2

query III rowsort
SELECT d-e,
       d,
       CASE WHEN a<b-3 THEN 111 WHEN a<=b THEN 222
        WHEN a<b+3 THEN 333 ELSE 444 END
  FROM t1
 WHERE EXISTS(SELECT 1 FROM t1 AS x WHERE x.b<t1.b)
   AND (e>a AND e<b)
   AND (a>b-2 AND a<b+2)
----

query IIII rowsort
SELECT d-e,
       d,
       c,
       b-c
  FROM t1
 WHERE a IS NULL
   AND b>c
----

query I rowsort
SELECT a+b*2
  FROM t1
 WHERE (c<=d-2 OR c>=d+2)
   AND e+d BETWEEN a+b-10 AND c+130
----
317
385

query III rowsort
SELECT a-b,
       a+b*2+c*3+d*4+e*5,
       a+b*2
  FROM t1
 WHERE c>d
   AND b>c
   AND (c<=d-2 OR c>=d+2)
----
-3
2878
579

query IIIIII rowsort
SELECT CASE a+1 WHEN b THEN 111 WHEN c THEN 222
        WHEN d THEN 333  WHEN e THEN 444 ELSE 555 END,
       CASE WHEN a<b-3 THEN 111 WHEN a<=b THEN 222
        WHEN a<b+3 THEN 333 ELSE 444 END,
       d-e,
       a+b*2+c*3+d*4,
       a+b*2+c*3,
       a-b
  FROM t1
 WHERE d>e
   AND a>b
   AND b>c
----
555
333
3
2323
1391
2

query II rowsort
SELECT a,
       c
  FROM t1
 WHERE c BETWEEN b-2 AND d+2
   AND a IS NULL
   AND (a>b-2 AND a<b+2)
----

query I rowsort
SELECT a
  FROM t1
 WHERE b IS NOT NULL
   AND d>e
   AND e+d BETWEEN a+b-10 AND c+130
----
127

query I rowsort
SELECT a-b
  FROM t1
 WHERE (e>a AND e<b)
----
-3
-3
-4

query I rowsort
SELECT CASE WHEN a<b-3 THEN 111 WHEN a<=b THEN 222
        WHEN a<b+3 THEN 333 ELSE 444 END
  FROM t1
 WHERE d>e
   AND a>b
   AND a IS NULL
----

query I rowsort
SELECT a+b*2
  FROM t1
 WHERE (e>a AND e<b)
----
579
666
743

query I rowsort
SELECT e
  FROM t1
 WHERE c>d
   AND coalesce(a,b,c,d,e)<>0
   AND a>b
----
132
173
180
189
210

query III rowsort
SELECT d-e,
       b-c,
       c
  FROM t1
 WHERE e+d BETWEEN a+b-10 AND c+130
   AND a>b
   AND a IS NULL
----

query II rowsort
SELECT (a+b+c+d+e)/5,
       a+b*2
  FROM t1
 WHERE e+d BETWEEN a+b-10 AND c+130
----
107
317
127
385

query I rowsort
SELECT (a+b+c+d+e)/5
  FROM t1
 WHERE c BETWEEN b-2 AND d+2
   AND a IS NULL
   AND d NOT BETWEEN 110 AND 150
----
NULL

query II rowsort
SELECT CASE a+1 WHEN b THEN 111 WHEN c THEN 222
        WHEN d THEN 333  WHEN e THEN 444 ELSE 555 END,
       abs(b-c)
  FROM t1
 WHERE a IS NULL
----
555
1
555
2

query IIIIIII rowsort
SELECT a+b*2+c*3,
       (a+b+c+d+e)/5,
       b-c,
       e,
       abs(b-c),
       c-d,
       b
  FROM t1
 WHERE e+d BETWEEN a+b-10 AND c+130
   AND (e>c OR e<d)
   AND b>c
----
760
127
4
126
4
-3
129

query I rowsort
SELECT b
  FROM t1
 WHERE EXISTS(SELECT 1 FROM t1 AS x WHERE x.b<t1.b)
   AND a IS NULL
----
112
206

query I rowsort
SELECT CASE WHEN c>(SELECT avg(c) FROM t1) THEN a*2 ELSE b*10 END
  FROM t1
 WHERE (e>a AND e<b)
----
382
440
490

query I rowsort
SELECT d
  FROM t1
 WHERE (a>b-2 AND a<b+2)
----
133
136
140
183
196
226
NULL
NULL

query IIIIIII rowsort
SELECT a+b*2+c*3,
       abs(a),
       c,
       a+b*2+c*3+d*4,
       e,
       a,
       a+b*2+c*3+d*4+e*5
  FROM t1
 WHERE a IS NULL
   AND coalesce(a,b,c,d,e)<>0
   AND (c<=d-2 OR c>=d+2)
----

query IIII rowsort
SELECT a,
       c-d,
       b,
       a+b*2+c*3+d*4+e*5
  FROM t1
 WHERE a>b
   AND (c<=d-2 OR c>=d+2)
   AND b>c
----
234
-2
232
3473

query I rowsort
SELECT b
  FROM t1
 WHERE e+d BETWEEN a+b-10 AND c+130
   AND a>b
----
105

query III rowsort
SELECT d,
       c,
       a-b
  FROM t1
 WHERE a IS NULL
----
114
113
NULL
207
208
NULL

query IIIII rowsort
SELECT b,
       b-c,
       (SELECT count(*) FROM t1 AS x WHERE x.b<t1.b),
       c-d,
       a+b*2+c*3+d*4
  FROM t1
 WHERE a IS NULL
   AND (e>c OR e<d)
   AND d NOT BETWEEN 110 AND 150
----

query III rowsort
SELECT (a+b+c+d+e)/5,
       CASE WHEN a<b-3 THEN 111 WHEN a<=b THEN 222
        WHEN a<b+3 THEN 333 ELSE 444 END,
       CASE WHEN c>(SELECT avg(c) FROM t1) THEN a*2 ELSE b*10 END
  FROM t1
 WHERE e+d BETWEEN a+b-10 AND c+130
   AND b IS NOT NULL
   AND (e>a AND e<b)
----

query III rowsort
SELECT b-c,
       a-b,
       d-e
  FROM t1
 WHERE b IS NOT NULL
   AND (e>a AND e<b)
   AND (c<=d-2 OR c>=d+2)
----
-1
-3
1
1
-3
-2

query I rowsort
SELECT abs(b-c)
  FROM t1
 WHERE a IS NULL
   AND e+d BETWEEN a+b-10 AND c+130
----

query I rowsort
SELECT (a+b+c+d+e)/5
  FROM t1
 WHERE coalesce(a,b,c,d,e)<>0
   AND (e>a AND e<b)
   AND b IS NOT NULL
----
192
222
247

query II rowsort
SELECT b,
       (a+b+c+d+e)/5
  FROM t1
 WHERE e+d BETWEEN a+b-10 AND c+130
----
105
107
129
127

query II rowsort
SELECT CASE a+1 WHEN b THEN 111 WHEN c THEN 222
        WHEN d THEN 333  WHEN e THEN 444 ELSE 555 END,
       b
  FROM t1
 WHERE e+d BETWEEN a+b-10 AND c+130
----
333
105
333
129

query II rowsort
SELECT c,
       e
  FROM t1
 WHERE e+d BETWEEN a+b-10 AND c+130
   AND d NOT BETWEEN 110 AND 150
----
106
109

query II rowsort
SELECT d-e,
       (a+b+c+d+e)/5
  FROM t1
 WHERE e+d BETWEEN a+b-10 AND c+130
   AND (c<=d-2 OR c>=d+2)
----
-1
107
2
127

query III rowsort
SELECT abs(a),
       abs(b-c),
       a
  FROM t1
 WHERE e+d BETWEEN a+b-10 AND c+130
----
107
1
107
127
4
127

query IIIII rowsort
SELECT a+b*2,
       b-c,
       c,
       b,
       CASE WHEN c>(SELECT avg(c) FROM t1) THEN a*2 ELSE b*10 END
  FROM t1
 WHERE e+d BETWEEN a+b-10 AND c+130
   AND c>d
   AND b>c
----

query II rowsort
SELECT a+b*2+c*3+d*4+e*5,
       a-b
  FROM t1
 WHERE e+d BETWEEN a+b-10 AND c+130
----
1612
2
1902
-2

query II rowsort
SELECT a+b*2,
       a-b
  FROM t1
 WHERE a>b
   AND d>e
   AND (a>b-2 AND a<b+2)
----
391
1
544
1

query I rowsort
SELECT a+b*2+c*3
  FROM t1
 WHERE a IS NULL
----
NULL
NULL

query II rowsort
SELECT CASE WHEN c>(SELECT avg(c) FROM t1) THEN a*2 ELSE b*10 END,
       b
  FROM t1
 WHERE a IS NULL
----
1120
112
NULL
206

query I rowsort
SELECT a+b*2+c*3+d*4
  FROM t1
 WHERE (a>b-2 AND a<b+2)
----
1325
1371
1411
1828
1964
2264
NULL
NULL

query I rowsort
SELECT a+b*2
  FROM t1
 WHERE (a>b-2 AND a<b+2)
----
391
416
428
475
502
544
595
685

query I rowsort
SELECT d-e
  FROM t1
 WHERE c>d
   AND d NOT BETWEEN 110 AND 150
----
-2
-2
-4
1
2
3
NULL
NULL

query IIIII rowsort
SELECT a+b*2+c*3+d*4+e*5,
       c,
       CASE WHEN c>(SELECT avg(c) FROM t1) THEN a*2 ELSE b*10 END,
       c-d,
       a+b*2+c*3+d*4
  FROM t1
 WHERE a IS NULL
   AND c>d
   AND (e>a AND e<b)
----

query II rowsort
SELECT a,
       c
  FROM t1
 WHERE d>e
   AND b>c
----
127
125
138
137
234
231
245
247

query IIII rowsort
SELECT CASE WHEN a<b-3 THEN 111 WHEN a<=b THEN 222
        WHEN a<b+3 THEN 333 ELSE 444 END,
       a+b*2+c*3+d*4+e*5,
       d,
       e
  FROM t1
 WHERE a IS NULL
----
444
NULL
114
110
444
NULL
207
NULL

query I rowsort
SELECT abs(a)
  FROM t1
 WHERE d>e
   AND a IS NULL
----
NULL

query III rowsort
SELECT a+b*2,
       abs(b-c),
       a
  FROM t1
 WHERE a IS NULL
   AND (e>c OR e<d)
   AND c>d
----

query I rowsort
SELECT d-e
  FROM t1
 WHERE c BETWEEN b-2 AND d+2
   AND b>c
----
-4
1
2
3
NULL

query I rowsort
SELECT c
  FROM t1
 WHERE a IS NULL
   AND (c<=d-2 OR c>=d+2)
   AND c>d
----

query II rowsort
SELECT a+b*2+c*3+d*4+e*5,
       e
  FROM t1
 WHERE (e>a AND e<b)
   AND (e>c OR e<d)
----
3331
221
3706
246

query II rowsort
SELECT c,
       a+b*2+c*3+d*4
  FROM t1
 WHERE d>e
   AND b IS NOT NULL
   AND (e>a AND e<b)
----
224
2226
247
2476

query IIII rowsort
SELECT a+b*2+c*3+d*4,
       a+b*2+c*3,
       CASE a+1 WHEN b THEN 111 WHEN c THEN 222
        WHEN d THEN 333  WHEN e THEN 444 ELSE 555 END,
       b
  FROM t1
 WHERE EXISTS(SELECT 1 FROM t1 AS x WHERE x.b<t1.b)
   AND a>b
   AND e+d BETWEEN a+b-10 AND c+130
----

query I rowsort
SELECT a+b*2+c*3+d*4+e*5
  FROM t1
 WHERE (a>b-2 AND a<b+2)
----
1985
2046
2131
2728
2949
3399
NULL
NULL

query I rowsort
SELECT a+b*2+c*3+d*4
  FROM t1
 WHERE (e>c OR e<d)
   AND (a>b-2 AND a<b+2)
----
1325
1371
1411
1828
1964
2264
NULL

query I rowsort
SELECT abs(a)
  FROM t1
 WHERE EXISTS(SELECT 1 FROM t1 AS x WHERE x.b<t1.b)
   AND coalesce(a,b,c,d,e)<>0
   AND a IS NULL
----
NULL
NULL

query I rowsort
SELECT a+b*2
  FROM t1
 WHERE a>b
   AND coalesce(a,b,c,d,e)<>0
   AND a IS NULL
----

query IIIII rowsort
SELECT b-c,
       abs(b-c),
       d-e,
       a+b*2,
       c-d
  FROM t1
 WHERE a>b
   AND (e>a AND e<b)
----

query I rowsort
SELECT d-e
  FROM t1
 WHERE (e>a AND e<b)
----
-2
1
2

query IIIII rowsort
SELECT a+b*2+c*3,
       c,
       a,
       CASE a+1 WHEN b THEN 111 WHEN c THEN 222
        WHEN d THEN 333  WHEN e THEN 444 ELSE 555 END,
       a-b
  FROM t1
 WHERE (c<=d-2 OR c>=d+2)
   AND (a>b-2 AND a<b+2)
----

query III rowsort
SELECT d-e,
       a+b*2+c*3+d*4+e*5,
       a
  FROM t1
 WHERE (e>c OR e<d)
   AND a IS NULL
   AND a>b
----

query I rowsort
SELECT b-c
  FROM t1
 WHERE e+d BETWEEN a+b-10 AND c+130
----
-1
4

query I rowsort
SELECT (a+b+c+d+e)/5
  FROM t1
 WHERE e+d BETWEEN a+b-10 AND c+130
----
107
127

query I rowsort
SELECT a-b
  FROM t1
 WHERE a>b
   AND c BETWEEN b-2 AND d+2
   AND (e>c OR e<d)
----
1
1
2
2
2
2
3
4

query I rowsort
SELECT CASE WHEN c>(SELECT avg(c) FROM t1) THEN a*2 ELSE b*10 END
  FROM t1
 WHERE (a>b-2 AND a<b+2)
   AND (e>c OR e<d)
----
1300
1390
1430
1580
364
398
458

query I rowsort
SELECT d-e
  FROM t1
 WHERE coalesce(a,b,c,d,e)<>0
   AND e+d BETWEEN a+b-10 AND c+130
   AND a IS NULL
----

query IIIIIII rowsort
SELECT e,
       b-c,
       a+b*2,
       (SELECT count(*) FROM t1 AS x WHERE x.b<t1.b),
       d-e,
       CASE a+1 WHEN b THEN 111 WHEN c THEN 222
        WHEN d THEN 333  WHEN e THEN 444 ELSE 555 END,
       (a+b+c+d+e)/5
  FROM t1
 WHERE a>b
   AND e+d BETWEEN a+b-10 AND c+130
   AND d>e
----

query IIII rowsort
SELECT abs(a),
       c,
       (a+b+c+d+e)/5,
       CASE WHEN a<b-3 THEN 111 WHEN a<=b THEN 222
        WHEN a<b+3 THEN 333 ELSE 444 END
  FROM t1
 WHERE a IS NULL
----
NULL
113
NULL
444
NULL
208
NULL
444

query II rowsort
SELECT (a+b+c+d+e)/5,
       c-d
  FROM t1
 WHERE d>e
   AND c>d
   AND a IS NULL
----

query I rowsort
SELECT CASE a+1 WHEN b THEN 111 WHEN c THEN 222
        WHEN d THEN 333  WHEN e THEN 444 ELSE 555 END
  FROM t1
 WHERE b>c
   AND c>d
   AND EXISTS(SELECT 1 FROM t1 AS x WHERE x.b<t1.b)
----
111
111
333
444

query III rowsort
SELECT d-e,
       a,
       d
  FROM t1
 WHERE (e>c OR e<d)
   AND a IS NULL
----
4
NULL
114

query IIII rowsort
SELECT abs(a),
       CASE a+1 WHEN b THEN 111 WHEN c THEN 222
        WHEN d THEN 333  WHEN e THEN 444 ELSE 555 END,
       CASE WHEN c>(SELECT avg(c) FROM t1) THEN a*2 ELSE b*10 END,
       (a+b+c+d+e)/5
  FROM t1
 WHERE (e>c OR e<d)
   AND a IS NULL
   AND e+d BETWEEN a+b-10 AND c+130
----

query IIII rowsort
SELECT CASE WHEN a<b-3 THEN 111 WHEN a<=b THEN 222
        WHEN a<b+3 THEN 333 ELSE 444 END,
       b,
       abs(a),
       a+b*2+c*3+d*4
  FROM t1
 WHERE b>c
   AND (e>a AND e<b)
   AND b IS NOT NULL
----
111
249
245
2476
222
194
191
1918

query II rowsort
SELECT a+b*2,
       abs(a)
  FROM t1
 WHERE d NOT BETWEEN 110 AND 150
   AND a IS NULL
----
NULL
NULL

query I rowsort
SELECT c
  FROM t1
 WHERE (e>a AND e<b)
----
193
224
247

query I rowsort
SELECT b-c
  FROM t1
 WHERE (c<=d-2 OR c>=d+2)
   AND d NOT BETWEEN 110 AND 150
   AND a>b
----
-1
-1
-1
-3
1

query IIII rowsort
SELECT CASE WHEN a<b-3 THEN 111 WHEN a<=b THEN 222
        WHEN a<b+3 THEN 333 ELSE 444 END,
       a+b*2+c*3,
       (SELECT count(*) FROM t1 AS x WHERE x.b<t1.b),
       a-b
  FROM t1
 WHERE a>b
   AND b>c
   AND (c<=d-2 OR c>=d+2)
----
333
1391
23
2

query IIIII rowsort
SELECT a+b*2+c*3+d*4+e*5,
       (SELECT count(*) FROM t1 AS x WHERE x.b<t1.b),
       c,
       CASE WHEN c>(SELECT avg(c) FROM t1) THEN a*2 ELSE b*10 END,
       a
  FROM t1
 WHERE (a>b-2 AND a<b+2)
   AND d NOT BETWEEN 110 AND 150
   AND d>e
----
2728
14
184
364
182

query IIII rowsort
SELECT a,
       (SELECT count(*) FROM t1 AS x WHERE x.b<t1.b),
       a+b*2+c*3+d*4+e*5,
       d
  FROM t1
 WHERE a IS NULL
----
NULL
1
NULL
114
NULL
18
NULL
207

query IIIII rowsort
SELECT b,
       d-e,
       a+b*2+c*3+d*4,
       (SELECT count(*) FROM t1 AS x WHERE x.b<t1.b),
       CASE WHEN a<b-3 THEN 111 WHEN a<=b THEN 222
        WHEN a<b+3 THEN 333 ELSE 444 END
  FROM t1
 WHERE a IS NULL
   AND d>e
----
112
4
NULL
1
444

query IIIII rowsort
SELECT (SELECT count(*) FROM t1 AS x WHERE x.b<t1.b),
       c,
       a,
       a+b*2+c*3+d*4+e*5,
       CASE WHEN c>(SELECT avg(c) FROM t1) THEN a*2 ELSE b*10 END
  FROM t1
 WHERE (e>c OR e<d)
   AND a IS NULL
----
1
113
NULL
NULL
1120

query IIIIIII rowsort
SELECT b-c,
       (SELECT count(*) FROM t1 AS x WHERE x.b<t1.b),
       c,
       a+b*2,
       d-e,
       a+b*2+c*3+d*4,
       c-d
  FROM t1
 WHERE a IS NULL
   AND c>d
----
-2
18
208
NULL
NULL
NULL
1

query II rowsort
SELECT abs(a),
       (SELECT count(*) FROM t1 AS x WHERE x.b<t1.b)
  FROM t1
 WHERE e+d BETWEEN a+b-10 AND c+130
   AND c BETWEEN b-2 AND d+2
----
107
0

query IIIIII rowsort
SELECT a-b,
       d-e,
       abs(b-c),
       e,
       a+b*2,
       b-c
  FROM t1
 WHERE a IS NULL
   AND b>c
   AND (a>b-2 AND a<b+2)
----

query IIII rowsort
SELECT b,
       a,
       a+b*2+c*3+d*4,
       (a+b+c+d+e)/5
  FROM t1
 WHERE b>c
   AND (a>b-2 AND a<b+2)
   AND e+d BETWEEN a+b-10 AND c+130
----

query I rowsort
SELECT a+b*2+c*3
  FROM t1
 WHERE EXISTS(SELECT 1 FROM t1 AS x WHERE x.b<t1.b)
   AND a IS NULL
   AND coalesce(a,b,c,d,e)<>0
----
NULL
NULL

query IIIII rowsort
SELECT d,
       abs(b-c),
       b-c,
       CASE a+1 WHEN b THEN 111 WHEN c THEN 222
        WHEN d THEN 333  WHEN e THEN 444 ELSE 555 END,
       b
  FROM t1
 WHERE (c<=d-2 OR c>=d+2)
   AND a IS NULL
   AND c>d
----

query IIIIII rowsort
SELECT c,
       e,
       a+b*2,
       abs(b-c),
       b-c,
       a
  FROM t1
 WHERE a IS NULL
   AND (e>a AND e<b)
   AND b IS NOT NULL
----

query IIII rowsort
SELECT CASE a+1 WHEN b THEN 111 WHEN c THEN 222
        WHEN d THEN 333  WHEN e THEN 444 ELSE 555 END,
       d,
       (a+b+c+d+e)/5,
       (SELECT count(*) FROM t1 AS x WHERE x.b<t1.b)
  FROM t1
 WHERE a>b
   AND (e>a AND e<b)
----

query II rowsort
SELECT c-d,
       abs(b-c)
  FROM t1
 WHERE (c<=d-2 OR c>=d+2)
   AND e+d BETWEEN a+b-10 AND c+130
   AND b IS NOT NULL
----
-2
1
-3
4

query IIIIII rowsort
SELECT (a+b+c+d+e)/5,
       a+b*2+c*3+d*4+e*5,
       d-e,
       a+b*2,
       CASE a+1 WHEN b THEN 111 WHEN c THEN 222
        WHEN d THEN 333  WHEN e THEN 444 ELSE 555 END,
       c
  FROM t1
 WHERE c BETWEEN b-2 AND d+2
   AND b>c
   AND a>b
----
232
3473
3
698
555
231