aboutsummaryrefslogtreecommitdiff
path: root/src/test/regress/sql/select.sql
blob: 6a8e3bb02e925d570bd123402fe121299be40e2b (plain)
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
--
-- SELECT
--

-- btree index
-- awk '{if($1<10){print;}else{next;}}' onek.data | sort +0n -1
--
SELECT onek.* WHERE onek.unique1 < 10
   ORDER BY onek.unique1;

--
-- awk '{if($1<20){print $1,$14;}else{next;}}' onek.data | sort +0nr -1
--
SELECT onek.unique1, onek.stringu1
   WHERE onek.unique1 < 20 
   ORDER BY unique1 using >;

--
-- awk '{if($1>980){print $1,$14;}else{next;}}' onek.data | sort +1d -2
--
SELECT onek.unique1, onek.stringu1
   WHERE onek.unique1 > 980 
   ORDER BY stringu1 using <;
	
--
-- awk '{if($1>980){print $1,$16;}else{next;}}' onek.data |
-- sort +1d -2 +0nr -1
--
SELECT onek.unique1, onek.string4
   WHERE onek.unique1 > 980 
   ORDER BY string4 using <, unique1 using >;
	
--
-- awk '{if($1>980){print $1,$16;}else{next;}}' onek.data |
-- sort +1dr -2 +0n -1
--
SELECT onek.unique1, onek.string4
   WHERE onek.unique1 > 980
   ORDER BY string4 using >, unique1 using <;
	
--
-- awk '{if($1<20){print $1,$16;}else{next;}}' onek.data |
-- sort +0nr -1 +1d -2
--
SELECT onek.unique1, onek.string4
   WHERE onek.unique1 < 20
   ORDER BY unique1 using >, string4 using <;

--
-- awk '{if($1<20){print $1,$16;}else{next;}}' onek.data |
-- sort +0n -1 +1dr -2
--
SELECT onek.unique1, onek.string4
   WHERE onek.unique1 < 20 
   ORDER BY unique1 using <, string4 using >;

--
-- test partial btree indexes
--
-- As of 7.2, planner probably won't pick an indexscan without stats,
-- so ANALYZE first.
--
ANALYZE onek2;

--
-- awk '{if($1<10){print $0;}else{next;}}' onek.data | sort +0n -1
--
SELECT onek2.* WHERE onek2.unique1 < 10;

--
-- awk '{if($1<20){print $1,$14;}else{next;}}' onek.data | sort +0nr -1
--
SELECT onek2.unique1, onek2.stringu1
    WHERE onek2.unique1 < 20 
    ORDER BY unique1 using >;

--
-- awk '{if($1>980){print $1,$14;}else{next;}}' onek.data | sort +1d -2
--
SELECT onek2.unique1, onek2.stringu1
   WHERE onek2.unique1 > 980;


SELECT two, stringu1, ten, string4
   INTO TABLE tmp
   FROM onek;

--
-- awk '{print $1,$2;}' person.data |
-- awk '{if(NF!=2){print $3,$2;}else{print;}}' - emp.data |
-- awk '{if(NF!=2){print $3,$2;}else{print;}}' - student.data |
-- awk 'BEGIN{FS="      ";}{if(NF!=2){print $4,$5;}else{print;}}' - stud_emp.data
--
-- SELECT name, age FROM person*; ??? check if different
SELECT p.name, p.age FROM person* p;

--
-- awk '{print $1,$2;}' person.data |
-- awk '{if(NF!=2){print $3,$2;}else{print;}}' - emp.data |
-- awk '{if(NF!=2){print $3,$2;}else{print;}}' - student.data |
-- awk 'BEGIN{FS="      ";}{if(NF!=1){print $4,$5;}else{print;}}' - stud_emp.data |
-- sort +1nr -2
--
SELECT p.name, p.age FROM person* p ORDER BY age using >, name;

-- 
-- Test between syntax
--
SELECT 2 BETWEEN 1 AND 3;
SELECT 2 BETWEEN 3 AND 1;
SELECT 2 BETWEEN ASYMMETRIC 1 AND 3;
SELECT 2 BETWEEN ASYMMETRIC 3 AND 1;
SELECT 2 BETWEEN SYMMETRIC 1 AND 3;
SELECT 2 BETWEEN SYMMETRIC 3 AND 1;
SELECT 2 NOT BETWEEN 1 AND 3;
SELECT 2 NOT BETWEEN 3 AND 1;
SELECT 2 NOT BETWEEN ASYMMETRIC 1 AND 3;
SELECT 2 NOT BETWEEN ASYMMETRIC 3 AND 1;
SELECT 2 NOT BETWEEN SYMMETRIC 1 AND 3;
SELECT 2 NOT BETWEEN SYMMETRIC 3 AND 1;
SELECT -4 BETWEEN -1 AND -3;
SELECT -4 BETWEEN -3 AND -1;
SELECT -4 BETWEEN ASYMMETRIC -1 AND -3;
SELECT -4 BETWEEN ASYMMETRIC -3 AND -1;
SELECT -4 BETWEEN SYMMETRIC -1 AND -3;
SELECT -4 BETWEEN SYMMETRIC -3 AND -1;
SELECT -4 NOT BETWEEN -1 AND -3;
SELECT -4 NOT BETWEEN -3 AND -1;
SELECT -4 NOT BETWEEN ASYMMETRIC -1 AND -3;
SELECT -4 NOT BETWEEN ASYMMETRIC -3 AND -1;
SELECT -4 NOT BETWEEN SYMMETRIC -1 AND -3;
SELECT -4 NOT BETWEEN SYMMETRIC -3 AND -1;