← Back to branch summary

~ubuntu-branches/ubuntu/oneiric/postgresql-9.1/oneiric-security

« back to all changes in this revision

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

  • Committer: Bazaar Package Importer
  • Author(s): Martin Pitt
  • Date: 2011-05-11 10:41:53 UTC
  • Revision ID: james.westby@ubuntu.com-20110511104153-psbh2o58553fv1m0
Tags: upstream-9.1~beta1
Import upstream version 9.1~beta1

Show diffs side-by-side

added added

removed removed

Lines of Context:
src/test/regress/sql/functional_deps.sql
 
1
-- from http://www.depesz.com/index.php/2010/04/19/getting-unique-elements/
 
2
 
 
3
CREATE TEMP TABLE articles (
 
4
    id int CONSTRAINT articles_pkey PRIMARY KEY,
 
5
    keywords text,
 
6
    title text UNIQUE NOT NULL,
 
7
    body text UNIQUE,
 
8
    created date
 
9
);
 
10
 
 
11
CREATE TEMP TABLE articles_in_category (
 
12
    article_id int,
 
13
    category_id int,
 
14
    changed date,
 
15
    PRIMARY KEY (article_id, category_id)
 
16
);
 
17
 
 
18
-- test functional dependencies based on primary keys/unique constraints
 
19
 
 
20
-- base tables
 
21
 
 
22
-- group by primary key (OK)
 
23
SELECT id, keywords, title, body, created
 
24
FROM articles
 
25
GROUP BY id;
 
26
 
 
27
-- group by unique not null (fail/todo)
 
28
SELECT id, keywords, title, body, created
 
29
FROM articles
 
30
GROUP BY title;
 
31
 
 
32
-- group by unique nullable (fail)
 
33
SELECT id, keywords, title, body, created
 
34
FROM articles
 
35
GROUP BY body;
 
36
 
 
37
-- group by something else (fail)
 
38
SELECT id, keywords, title, body, created
 
39
FROM articles
 
40
GROUP BY keywords;
 
41
 
 
42
-- multiple tables
 
43
 
 
44
-- group by primary key (OK)
 
45
SELECT a.id, a.keywords, a.title, a.body, a.created
 
46
FROM articles AS a, articles_in_category AS aic
 
47
WHERE a.id = aic.article_id AND aic.category_id in (14,62,70,53,138)
 
48
GROUP BY a.id;
 
49
 
 
50
-- group by something else (fail)
 
51
SELECT a.id, a.keywords, a.title, a.body, a.created
 
52
FROM articles AS a, articles_in_category AS aic
 
53
WHERE a.id = aic.article_id AND aic.category_id in (14,62,70,53,138)
 
54
GROUP BY aic.article_id, aic.category_id;
 
55
 
 
56
-- JOIN syntax
 
57
 
 
58
-- group by left table's primary key (OK)
 
59
SELECT a.id, a.keywords, a.title, a.body, a.created
 
60
FROM articles AS a JOIN articles_in_category AS aic ON a.id = aic.article_id
 
61
WHERE aic.category_id in (14,62,70,53,138)
 
62
GROUP BY a.id;
 
63
 
 
64
-- group by something else (fail)
 
65
SELECT a.id, a.keywords, a.title, a.body, a.created
 
66
FROM articles AS a JOIN articles_in_category AS aic ON a.id = aic.article_id
 
67
WHERE aic.category_id in (14,62,70,53,138)
 
68
GROUP BY aic.article_id, aic.category_id;
 
69
 
 
70
-- group by right table's (composite) primary key (OK)
 
71
SELECT aic.changed
 
72
FROM articles AS a JOIN articles_in_category AS aic ON a.id = aic.article_id
 
73
WHERE aic.category_id in (14,62,70,53,138)
 
74
GROUP BY aic.category_id, aic.article_id;
 
75
 
 
76
-- group by right table's partial primary key (fail)
 
77
SELECT aic.changed
 
78
FROM articles AS a JOIN articles_in_category AS aic ON a.id = aic.article_id
 
79
WHERE aic.category_id in (14,62,70,53,138)
 
80
GROUP BY aic.article_id;
 
81
 
 
82
 
 
83
-- example from documentation
 
84
 
 
85
CREATE TEMP TABLE products (product_id int, name text, price numeric);
 
86
CREATE TEMP TABLE sales (product_id int, units int);
 
87
 
 
88
-- OK
 
89
SELECT product_id, p.name, (sum(s.units) * p.price) AS sales
 
90
    FROM products p LEFT JOIN sales s USING (product_id)
 
91
    GROUP BY product_id, p.name, p.price;
 
92
 
 
93
-- fail
 
94
SELECT product_id, p.name, (sum(s.units) * p.price) AS sales
 
95
    FROM products p LEFT JOIN sales s USING (product_id)
 
96
    GROUP BY product_id;
 
97
 
 
98
ALTER TABLE products ADD PRIMARY KEY (product_id);
 
99
 
 
100
-- OK now
 
101
SELECT product_id, p.name, (sum(s.units) * p.price) AS sales
 
102
    FROM products p LEFT JOIN sales s USING (product_id)
 
103
    GROUP BY product_id;
 
104
 
 
105
 
 
106
-- Drupal example, http://drupal.org/node/555530
 
107
 
 
108
CREATE TEMP TABLE node (
 
109
    nid SERIAL,
 
110
    vid integer NOT NULL default '0',
 
111
    type varchar(32) NOT NULL default '',
 
112
    title varchar(128) NOT NULL default '',
 
113
    uid integer NOT NULL default '0',
 
114
    status integer NOT NULL default '1',
 
115
    created integer NOT NULL default '0',
 
116
    -- snip
 
117
    PRIMARY KEY (nid, vid)
 
118
);
 
119
 
 
120
CREATE TEMP TABLE users (
 
121
    uid integer NOT NULL default '0',
 
122
    name varchar(60) NOT NULL default '',
 
123
    pass varchar(32) NOT NULL default '',
 
124
    -- snip
 
125
    PRIMARY KEY (uid),
 
126
    UNIQUE (name)
 
127
);
 
128
 
 
129
-- OK
 
130
SELECT u.uid, u.name FROM node n
 
131
INNER JOIN users u ON u.uid = n.uid
 
132
WHERE n.type = 'blog' AND n.status = 1
 
133
GROUP BY u.uid, u.name;
 
134
 
 
135
-- OK
 
136
SELECT u.uid, u.name FROM node n
 
137
INNER JOIN users u ON u.uid = n.uid
 
138
WHERE n.type = 'blog' AND n.status = 1
 
139
GROUP BY u.uid;
 
140
 
 
141
 
 
142
-- Check views and dependencies
 
143
 
 
144
-- fail
 
145
CREATE TEMP VIEW fdv1 AS
 
146
SELECT id, keywords, title, body, created
 
147
FROM articles
 
148
GROUP BY body;
 
149
 
 
150
-- OK
 
151
CREATE TEMP VIEW fdv1 AS
 
152
SELECT id, keywords, title, body, created
 
153
FROM articles
 
154
GROUP BY id;
 
155
 
 
156
-- fail
 
157
ALTER TABLE articles DROP CONSTRAINT articles_pkey RESTRICT;
 
158
 
 
159
DROP VIEW fdv1;
 
160
 
 
161
 
 
162
-- multiple dependencies
 
163
CREATE TEMP VIEW fdv2 AS
 
164
SELECT a.id, a.keywords, a.title, aic.category_id, aic.changed
 
165
FROM articles AS a JOIN articles_in_category AS aic ON a.id = aic.article_id
 
166
WHERE aic.category_id in (14,62,70,53,138)
 
167
GROUP BY a.id, aic.category_id, aic.article_id;
 
168
 
 
169
ALTER TABLE articles DROP CONSTRAINT articles_pkey RESTRICT; -- fail
 
170
ALTER TABLE articles_in_category DROP CONSTRAINT articles_in_category_pkey RESTRICT; --fail
 
171
 
 
172
DROP VIEW fdv2;
 
173
 
 
174
 
 
175
-- nested queries
 
176
 
 
177
CREATE TEMP VIEW fdv3 AS
 
178
SELECT id, keywords, title, body, created
 
179
FROM articles
 
180
GROUP BY id
 
181
UNION
 
182
SELECT id, keywords, title, body, created
 
183
FROM articles
 
184
GROUP BY id;
 
185
 
 
186
ALTER TABLE articles DROP CONSTRAINT articles_pkey RESTRICT; -- fail
 
187
 
 
188
DROP VIEW fdv3;
 
189
 
 
190
 
 
191
CREATE TEMP VIEW fdv4 AS
 
192
SELECT * FROM articles WHERE title IN (SELECT title FROM articles GROUP BY id);
 
193
 
 
194
ALTER TABLE articles DROP CONSTRAINT articles_pkey RESTRICT; -- fail
 
195
 
 
196
DROP VIEW fdv4;
 
197
 
 
198
 
 
199
-- prepared query plans: this results in failure on reuse
 
200
 
 
201
PREPARE foo AS
 
202
  SELECT id, keywords, title, body, created
 
203
  FROM articles
 
204
  GROUP BY id;
 
205
 
 
206
EXECUTE foo;
 
207
 
 
208
ALTER TABLE articles DROP CONSTRAINT articles_pkey RESTRICT;
 
209
 
 
210
EXECUTE foo;  -- fail