6
-- awk '{if($1<10){print;}else{next;}}' onek.data | sort +0n -1
9
WHERE onek.unique1 < 10
10
ORDER BY onek.unique1;
13
-- awk '{if($1<20){print $1,$14;}else{next;}}' onek.data | sort +0nr -1
15
SELECT onek.unique1, onek.stringu1 FROM onek
16
WHERE onek.unique1 < 20
17
ORDER BY unique1 using >;
20
-- awk '{if($1>980){print $1,$14;}else{next;}}' onek.data | sort +1d -2
22
SELECT onek.unique1, onek.stringu1 FROM onek
23
WHERE onek.unique1 > 980
24
ORDER BY stringu1 using <;
27
-- awk '{if($1>980){print $1,$16;}else{next;}}' onek.data |
28
-- sort +1d -2 +0nr -1
30
SELECT onek.unique1, onek.string4 FROM onek
31
WHERE onek.unique1 > 980
32
ORDER BY string4 using <, unique1 using >;
35
-- awk '{if($1>980){print $1,$16;}else{next;}}' onek.data |
36
-- sort +1dr -2 +0n -1
38
SELECT onek.unique1, onek.string4 FROM onek
39
WHERE onek.unique1 > 980
40
ORDER BY string4 using >, unique1 using <;
43
-- awk '{if($1<20){print $1,$16;}else{next;}}' onek.data |
44
-- sort +0nr -1 +1d -2
46
SELECT onek.unique1, onek.string4 FROM onek
47
WHERE onek.unique1 < 20
48
ORDER BY unique1 using >, string4 using <;
51
-- awk '{if($1<20){print $1,$16;}else{next;}}' onek.data |
52
-- sort +0n -1 +1dr -2
54
SELECT onek.unique1, onek.string4 FROM onek
55
WHERE onek.unique1 < 20
56
ORDER BY unique1 using <, string4 using >;
59
-- test partial btree indexes
61
-- As of 7.2, planner probably won't pick an indexscan without stats,
62
-- so ANALYZE first. Also, we want to prevent it from picking a bitmapscan
63
-- followed by sort, because that could hide index ordering problems.
67
SET enable_seqscan TO off;
68
SET enable_bitmapscan TO off;
69
SET enable_sort TO off;
72
-- awk '{if($1<10){print $0;}else{next;}}' onek.data | sort +0n -1
74
SELECT onek2.* FROM onek2 WHERE onek2.unique1 < 10;
77
-- awk '{if($1<20){print $1,$14;}else{next;}}' onek.data | sort +0nr -1
79
SELECT onek2.unique1, onek2.stringu1 FROM onek2
80
WHERE onek2.unique1 < 20
81
ORDER BY unique1 using >;
84
-- awk '{if($1>980){print $1,$14;}else{next;}}' onek.data | sort +1d -2
86
SELECT onek2.unique1, onek2.stringu1 FROM onek2
87
WHERE onek2.unique1 > 980;
90
RESET enable_bitmapscan;
94
SELECT two, stringu1, ten, string4
99
-- awk '{print $1,$2;}' person.data |
100
-- awk '{if(NF!=2){print $3,$2;}else{print;}}' - emp.data |
101
-- awk '{if(NF!=2){print $3,$2;}else{print;}}' - student.data |
102
-- awk 'BEGIN{FS=" ";}{if(NF!=2){print $4,$5;}else{print;}}' - stud_emp.data
104
-- SELECT name, age FROM person*; ??? check if different
105
SELECT p.name, p.age FROM person* p;
108
-- awk '{print $1,$2;}' person.data |
109
-- awk '{if(NF!=2){print $3,$2;}else{print;}}' - emp.data |
110
-- awk '{if(NF!=2){print $3,$2;}else{print;}}' - student.data |
111
-- awk 'BEGIN{FS=" ";}{if(NF!=1){print $4,$5;}else{print;}}' - stud_emp.data |
114
SELECT p.name, p.age FROM person* p ORDER BY age using >, name;
117
-- Test some cases involving whole-row Var referencing a subquery
119
select foo from (select 1) as foo;
120
select foo from (select null) as foo;
121
select foo from (select 'xyzzy',1,null) as foo;
126
select * from onek, (values(147, 'RFAAAA'), (931, 'VJAAAA')) as v (i, j)
127
WHERE onek.unique1 = v.i and onek.stringu1 = v.j;
129
-- a more complex case
130
-- looks like we're coding lisp :-)
132
(values ((select i from
133
(values(10000), (2), (389), (1000), (2000), ((select 10029))) as foo(i)
134
order by i asc limit 1))) bar (i)
135
where onek.unique1 = bar.i;
137
-- try VALUES in a subquery
139
where (unique1,ten) in (values (1,1), (20,0), (99,9), (17,99))
142
-- VALUES is also legal as a standalone query or a set-operation member
143
VALUES (1,2), (3,4+4), (7,77.7);
145
VALUES (1,2), (3,4+4), (7,77.7)
152
-- Test ORDER BY options
155
CREATE TEMP TABLE foo (f1 int);
157
INSERT INTO foo VALUES (42),(3),(10),(7),(null),(null),(1);
159
SELECT * FROM foo ORDER BY f1;
160
SELECT * FROM foo ORDER BY f1 ASC; -- same thing
161
SELECT * FROM foo ORDER BY f1 NULLS FIRST;
162
SELECT * FROM foo ORDER BY f1 DESC;
163
SELECT * FROM foo ORDER BY f1 DESC NULLS LAST;
165
-- check if indexscans do the right things
166
CREATE INDEX fooi ON foo (f1);
167
SET enable_sort = false;
169
SELECT * FROM foo ORDER BY f1;
170
SELECT * FROM foo ORDER BY f1 NULLS FIRST;
171
SELECT * FROM foo ORDER BY f1 DESC;
172
SELECT * FROM foo ORDER BY f1 DESC NULLS LAST;
175
CREATE INDEX fooi ON foo (f1 DESC);
177
SELECT * FROM foo ORDER BY f1;
178
SELECT * FROM foo ORDER BY f1 NULLS FIRST;
179
SELECT * FROM foo ORDER BY f1 DESC;
180
SELECT * FROM foo ORDER BY f1 DESC NULLS LAST;
183
CREATE INDEX fooi ON foo (f1 DESC NULLS LAST);
185
SELECT * FROM foo ORDER BY f1;
186
SELECT * FROM foo ORDER BY f1 NULLS FIRST;
187
SELECT * FROM foo ORDER BY f1 DESC;
188
SELECT * FROM foo ORDER BY f1 DESC NULLS LAST;
191
-- Test some corner cases that have been known to confuse the planner
194
-- ORDER BY on a constant doesn't really need any sorting
195
SELECT 1 AS x ORDER BY x;
197
-- But ORDER BY on a set-valued expression does
198
create function sillysrf(int) returns setof int as
199
'values (1),(10),(2),($1)' language sql immutable;
202
select sillysrf(-1) order by 1;
204
drop function sillysrf(int);
206
-- X = X isn't a no-op, it's effectively X IS NOT NULL assuming = is strict
208
select * from (values (2),(null),(1)) v(k) where k = k order by k;
209
select * from (values (2),(null),(1)) v(k) where k = k;