src/test/regress/sql/icudp/gp_interconnect_default_query.sql.template

-- 
-- @description Interconncet flow control test case: single guc value
-- @created 2012-11-27
-- @modified 2012-11-27
-- @tags executor
-- @gpdb_version [4.2.3.0,main]
-- @skip skip this random para generation test case in continus integration

$$$GUC_VALUE$$$

-- Create tables
CREATE TEMP TABLE small_table(dkey INT, jkey INT, rval REAL, tval TEXT default 'abcdefghijklmnopqrstuvwxyz') DISTRIBUTED BY (dkey);
CREATE TEMP TABLE media_table(dkey INT, jkey INT, rval REAL, tval TEXT default 'i like travelling') DISTRIBUTED BY (jkey);
CREATE TEMP TABLE big_table(dkey INT, jkey INT, rval REAL, tval TEXT default 'can you tell me a joke') DISTRIBUTED BY (rval);

-- Generate some data
INSERT INTO small_table VALUES(generate_series(1, 5000), generate_series(5001, 10000), sqrt(generate_series(5001, 10000)));
INSERT INTO media_table VALUES(generate_series(1, 5000), generate_series(5001, 10000), sqrt(generate_series(5001, 10000)));
INSERT INTO big_table VALUES(generate_series(1, 5000), generate_series(5001, 10000), sqrt(generate_series(5001, 10000)));

-- Functional tests
-- Skew with gather+redistribute
SELECT ROUND(rval * rval)::INT % 30 AS rval2, SUM(dkey) AS sum_dkey, AVG(jkey) AS ave_jkey,
                                SUM(length(tval)) AS sum_len_tval, SUM(length(tval2)) AS sum_len_tval2, SUM(length(tval3)) AS sum_len_tval3 
FROM
 (SELECT dkey, big_table.jkey, big_table.rval, foo2.tval, foo2.tval2, big_table.tval AS tval3  FROM
  (SELECT rval, media_table.jkey, media_table.dkey, media_table.tval AS tval2, foo1.tval  FROM
   (SELECT jkey, dkey,  small_table.rval, small_table.tval  FROM
     (SELECT  jkey, rval, tval  FROM
       small_table ORDER BY dkey ) foo
        JOIN small_table USING(jkey) ORDER BY dkey) foo1
          JOIN media_table USING(rval) ORDER BY jkey) foo2
             JOIN big_table USING(dkey) ORDER BY rval) foo3
GROUP BY rval2
ORDER BY rval2;