1-- ===================================================================
2-- test recursive planning functionality on local tables
3-- ===================================================================
4
5CREATE SCHEMA subquery_local_tables;
6SET search_path TO subquery_local_tables, public;
7
8
9CREATE TABLE users_table_local AS SELECT * FROM users_table;
10CREATE TABLE events_table_local AS SELECT * FROM events_table;
11
12SET client_min_messages TO DEBUG1;
13
14-- foo is only on the local tables, thus can be replaced
15-- bar is on the distributed tables with LIMIT, should be replaced
16SELECT
17   foo.user_id
18FROM
19    (SELECT
20    	DISTINCT users_table_local.user_id
21     FROM
22     	users_table_local, events_table_local
23     WHERE
24     	users_table_local.user_id = events_table_local.user_id AND
25     event_type IN (1,2,3,4)
26     ORDER BY 1 DESC LIMIT 5
27     ) as foo,
28    (SELECT
29    	DISTINCT users_table.user_id
30     FROM
31     	users_table, events_table
32     WHERE
33     	users_table.user_id = events_table.user_id AND
34     event_type IN (5,6,7,8)
35     ORDER BY 1 DESC LIMIT 5
36     ) as bar
37
38    WHERE bar.user_id = foo.user_id
39    ORDER BY 1 DESC;
40
41-- foo is only on the local tables, thus can be replaced
42SELECT
43   foo.user_id
44FROM
45    (SELECT
46    	DISTINCT users_table_local.user_id
47     FROM
48     	users_table_local, events_table_local
49     WHERE
50     	users_table_local.user_id = events_table_local.user_id AND
51     event_type IN (1,2,3,4)
52     ORDER BY 1 DESC LIMIT 5
53     ) as foo,
54    (SELECT
55    	DISTINCT users_table.user_id
56     FROM
57     	users_table, events_table
58     WHERE
59     	users_table.user_id = events_table.user_id AND
60     event_type IN (5,6,7,8)
61     ) as bar
62    WHERE bar.user_id = foo.user_id
63    ORDER BY 1 DESC;
64
65
66-- subqueries in WHERE could be replaced even if they are on the local tables
67SELECT DISTINCT user_id
68FROM users_table
69WHERE
70	user_id IN (SELECT DISTINCT value_2 FROM users_table_local WHERE value_1 = 1)
71ORDER BY 1 LIMIT 5;
72
73
74-- subquery in FROM -> FROM -> FROM should be replaced if
75-- it contains onle local tables
76SELECT
77	DISTINCT user_id
78FROM
79	(
80		SELECT users_table.user_id FROM users_table,
81							(
82								SELECT
83									event_type, user_id
84								FROM
85									(SELECT event_type, users_table.user_id FROM users_table,
86															(SELECT user_id, event_type FROM events_table_local WHERE value_2 < 3 OFFSET 3) as foo
87															WHERE foo.user_id = users_table.user_id
88															) bar
89
90							) as baz
91									WHERE baz.user_id = users_table.user_id
92
93	) as sub1
94	ORDER BY 1 DESC
95	LIMIT 3;
96
97
98-- subquery in FROM -> FROM -> WHERE -> WHERE should be replaced if
99-- it contains onle local tables
100-- Later the upper level query is also recursively planned due to LIMIT
101SELECT user_id, array_length(events_table, 1)
102FROM (
103  SELECT user_id, array_agg(event ORDER BY time) AS events_table
104  FROM (
105    SELECT
106    	u.user_id, e.event_type::text AS event, e.time
107    FROM
108    	users_table AS u,
109        events_table AS e
110    WHERE u.user_id = e.user_id AND
111    		u.user_id IN
112    		(
113    			SELECT
114    				user_id
115    			FROM
116    				users_table
117    			WHERE value_2 >= 5
118			    AND  EXISTS (SELECT user_id FROM events_table_local WHERE event_type > 1 AND event_type <= 3 AND value_3 > 1)
119				AND  NOT EXISTS (SELECT user_id FROM events_table WHERE event_type > 3 AND event_type <= 4  AND value_3 > 1 AND user_id = users_table.user_id)
120				LIMIT 5
121    		)
122  ) t
123  GROUP BY user_id
124) q
125ORDER BY 2 DESC, 1;
126
127
128
129-- subquery (i.e., subquery_2) in WHERE->FROM should be replaced due to local tables
130SELECT
131	user_id
132FROM
133	users_table
134WHERE
135 user_id IN
136(
137	SELECT
138	  user_id
139	 FROM (
140	  SELECT
141	 	 subquery_1.user_id, count_pay
142	  FROM
143	  (
144	    (SELECT
145	      users_table.user_id,
146	      'action=>1' AS event,
147	      events_table.time
148	    FROM
149	      users_table,
150	      events_table
151	    WHERE
152	      users_table.user_id = events_table.user_id AND
153	      users_table.user_id >= 1 AND
154	      users_table.user_id <= 3 AND
155	      events_table.event_type > 1 AND events_table.event_type < 3
156	      )
157	    UNION
158	    (SELECT
159	      users_table.user_id,
160	      'action=>2' AS event,
161	      events_table.time
162	    FROM
163	      users_table,
164	      events_table
165	    WHERE
166	      users_table.user_id = events_table.user_id AND
167	      users_table.user_id >= 1 AND
168	      users_table.user_id <= 3 AND
169	      events_table.event_type > 2 AND events_table.event_type < 4
170	    )
171	  ) AS subquery_1
172	  LEFT JOIN
173	    (SELECT
174	       user_id,
175	      COUNT(*) AS count_pay
176	    FROM
177	      users_table_local
178	    WHERE
179	      user_id >= 1 AND
180	      user_id <= 3 AND
181	      users_table_local.value_1 > 3 AND users_table_local.value_1 < 5
182	    GROUP BY
183	      user_id
184	    HAVING
185	      COUNT(*) > 1
186	      LIMIT 10
187
188	      ) AS subquery_2
189	  ON
190	    subquery_1.user_id = subquery_2.user_id
191	  GROUP BY
192	    subquery_1.user_id,
193	    count_pay) AS subquery_top
194	GROUP BY
195	  count_pay, user_id
196)
197GROUP BY user_id
198HAVING count(*) > 1 AND sum(value_2) > 29
199ORDER BY 1;
200
201SET client_min_messages TO DEFAULT;
202
203-- Test https://github.com/citusdata/citus/issues/2717
204create table test_dist (id int, table_name text, column_name text);
205select create_distributed_table('test_dist','id');
206insert into test_dist values (1, 'test_dist', 'table_name');
207with q as (
208    select icl.* from test_dist td
209    join information_schema.columns icl on icl.table_name::text = lower(td.table_name) and icl.column_name::text = lower(td.column_name)
210) select column_name from q;
211
212\set VERBOSITY terse
213DROP SCHEMA subquery_local_tables CASCADE;
214SET search_path TO public;
215