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