← Back to branch summary

~vcs-imports/mammoth-replicator/trunk

« back to all changes in this revision

Viewing changes to src/test/regress/sql/subselect.sql

  • Committer: alvherre
  • Date: 2005-12-16 21:24:52 UTC
  • Revision ID: svn-v4:db760fc0-0f08-0410-9d63-cc6633f64896:trunk:1
Initial import of the REL8_0_3 sources from the Pgsql CVS repository.

Show diffs side-by-side

added added

removed removed

Lines of Context:
src/test/regress/sql/subselect.sql
 
1
--
 
2
-- SUBSELECT
 
3
--
 
4
 
 
5
SELECT 1 AS one WHERE 1 IN (SELECT 1);
 
6
 
 
7
SELECT 1 AS zero WHERE 1 NOT IN (SELECT 1);
 
8
 
 
9
SELECT 1 AS zero WHERE 1 IN (SELECT 2);
 
10
 
 
11
-- Set up some simple test tables
 
12
 
 
13
CREATE TABLE SUBSELECT_TBL (
 
14
  f1 integer,
 
15
  f2 integer,
 
16
  f3 float
 
17
);
 
18
 
 
19
INSERT INTO SUBSELECT_TBL VALUES (1, 2, 3);
 
20
INSERT INTO SUBSELECT_TBL VALUES (2, 3, 4);
 
21
INSERT INTO SUBSELECT_TBL VALUES (3, 4, 5);
 
22
INSERT INTO SUBSELECT_TBL VALUES (1, 1, 1);
 
23
INSERT INTO SUBSELECT_TBL VALUES (2, 2, 2);
 
24
INSERT INTO SUBSELECT_TBL VALUES (3, 3, 3);
 
25
INSERT INTO SUBSELECT_TBL VALUES (6, 7, 8);
 
26
INSERT INTO SUBSELECT_TBL VALUES (8, 9, NULL);
 
27
 
 
28
SELECT '' AS eight, * FROM SUBSELECT_TBL;
 
29
 
 
30
-- Uncorrelated subselects
 
31
 
 
32
SELECT '' AS two, f1 AS "Constant Select" FROM SUBSELECT_TBL
 
33
  WHERE f1 IN (SELECT 1);
 
34
 
 
35
SELECT '' AS six, f1 AS "Uncorrelated Field" FROM SUBSELECT_TBL
 
36
  WHERE f1 IN (SELECT f2 FROM SUBSELECT_TBL);
 
37
 
 
38
SELECT '' AS six, f1 AS "Uncorrelated Field" FROM SUBSELECT_TBL
 
39
  WHERE f1 IN (SELECT f2 FROM SUBSELECT_TBL WHERE
 
40
    f2 IN (SELECT f1 FROM SUBSELECT_TBL));
 
41
 
 
42
SELECT '' AS three, f1, f2
 
43
  FROM SUBSELECT_TBL
 
44
  WHERE (f1, f2) NOT IN (SELECT f2, CAST(f3 AS int4) FROM SUBSELECT_TBL
 
45
                         WHERE f3 IS NOT NULL);
 
46
 
 
47
-- Correlated subselects
 
48
 
 
49
SELECT '' AS six, f1 AS "Correlated Field", f2 AS "Second Field"
 
50
  FROM SUBSELECT_TBL upper
 
51
  WHERE f1 IN (SELECT f2 FROM SUBSELECT_TBL WHERE f1 = upper.f1);
 
52
 
 
53
SELECT '' AS six, f1 AS "Correlated Field", f3 AS "Second Field"
 
54
  FROM SUBSELECT_TBL upper
 
55
  WHERE f1 IN
 
56
    (SELECT f2 FROM SUBSELECT_TBL WHERE CAST(upper.f2 AS float) = f3);
 
57
 
 
58
SELECT '' AS six, f1 AS "Correlated Field", f3 AS "Second Field"
 
59
  FROM SUBSELECT_TBL upper
 
60
  WHERE f3 IN (SELECT upper.f1 + f2 FROM SUBSELECT_TBL
 
61
               WHERE f2 = CAST(f3 AS integer));
 
62
 
 
63
SELECT '' AS five, f1 AS "Correlated Field"
 
64
  FROM SUBSELECT_TBL
 
65
  WHERE (f1, f2) IN (SELECT f2, CAST(f3 AS int4) FROM SUBSELECT_TBL
 
66
                     WHERE f3 IS NOT NULL);
 
67
 
 
68
--
 
69
-- Use some existing tables in the regression test
 
70
--
 
71
 
 
72
SELECT '' AS eight, ss.f1 AS "Correlated Field", ss.f3 AS "Second Field"
 
73
  FROM SUBSELECT_TBL ss
 
74
  WHERE f1 NOT IN (SELECT f1+1 FROM INT4_TBL
 
75
                   WHERE f1 != ss.f1 AND f1 < 2147483647);
 
76
 
 
77
select q1, float8(count(*)) / (select count(*) from int8_tbl)
 
78
from int8_tbl group by q1 order by q1;
 
79
 
 
80
--
 
81
-- Test cases to catch unpleasant interactions between IN-join processing
 
82
-- and subquery pullup.
 
83
--
 
84
 
 
85
select count(*) from
 
86
  (select 1 from tenk1 a
 
87
   where unique1 IN (select hundred from tenk1 b)) ss;
 
88
select count(distinct ss.ten) from
 
89
  (select ten from tenk1 a
 
90
   where unique1 IN (select hundred from tenk1 b)) ss;
 
91
select count(*) from
 
92
  (select 1 from tenk1 a
 
93
   where unique1 IN (select distinct hundred from tenk1 b)) ss;
 
94
select count(distinct ss.ten) from
 
95
  (select ten from tenk1 a
 
96
   where unique1 IN (select distinct hundred from tenk1 b)) ss;
 
97
 
 
98
--
 
99
-- Test case to catch problems with multiply nested sub-SELECTs not getting
 
100
-- recalculated properly.  Per bug report from Didier Moens.
 
101
--
 
102
 
 
103
CREATE TABLE orderstest (
 
104
    approver_ref integer,
 
105
    po_ref integer,
 
106
    ordercancelled boolean
 
107
);
 
108
 
 
109
INSERT INTO orderstest VALUES (1, 1, false);
 
110
INSERT INTO orderstest VALUES (66, 5, false);
 
111
INSERT INTO orderstest VALUES (66, 6, false);
 
112
INSERT INTO orderstest VALUES (66, 7, false);
 
113
INSERT INTO orderstest VALUES (66, 1, true);
 
114
INSERT INTO orderstest VALUES (66, 8, false);
 
115
INSERT INTO orderstest VALUES (66, 1, false);
 
116
INSERT INTO orderstest VALUES (77, 1, false);
 
117
INSERT INTO orderstest VALUES (1, 1, false);
 
118
INSERT INTO orderstest VALUES (66, 1, false);
 
119
INSERT INTO orderstest VALUES (1, 1, false);
 
120
 
 
121
CREATE VIEW orders_view AS
 
122
SELECT *,
 
123
(SELECT CASE
 
124
   WHEN ord.approver_ref=1 THEN '---' ELSE 'Approved'
 
125
 END) AS "Approved",
 
126
(SELECT CASE
 
127
 WHEN ord.ordercancelled
 
128
 THEN 'Cancelled'
 
129
 ELSE
 
130
  (SELECT CASE
 
131
                WHEN ord.po_ref=1
 
132
                THEN
 
133
                 (SELECT CASE
 
134
                                WHEN ord.approver_ref=1
 
135
                                THEN '---'
 
136
                                ELSE 'Approved'
 
137
                        END)
 
138
                ELSE 'PO'
 
139
        END) 
 
140
END) AS "Status",
 
141
(CASE
 
142
 WHEN ord.ordercancelled
 
143
 THEN 'Cancelled'
 
144
 ELSE
 
145
  (CASE
 
146
                WHEN ord.po_ref=1
 
147
                THEN
 
148
                 (CASE
 
149
                                WHEN ord.approver_ref=1
 
150
                                THEN '---'
 
151
                                ELSE 'Approved'
 
152
                        END)
 
153
                ELSE 'PO'
 
154
        END) 
 
155
END) AS "Status_OK"
 
156
FROM orderstest ord;
 
157
 
 
158
SELECT * FROM orders_view;
 
159
 
 
160
DROP TABLE orderstest cascade;