sql-tests/inputs/explain.sql

0001 --SET spark.sql.codegen.wholeStage = true
0002 --SET spark.sql.adaptive.enabled = false
0003
0004 -- Test tables
0005 CREATE table  explain_temp1 (key int, val int) USING PARQUET;
0006 CREATE table  explain_temp2 (key int, val int) USING PARQUET;
0007 CREATE table  explain_temp3 (key int, val int) USING PARQUET;
0008 CREATE table  explain_temp4 (key int, val string) USING PARQUET;
0009
0010 SET spark.sql.codegen.wholeStage = true;
0011
0012 -- single table
0013 EXPLAIN FORMATTED
0014   SELECT key, max(val)
0015   FROM   explain_temp1
0016   WHERE  key > 0
0017   GROUP  BY key
0018   ORDER  BY key;
0019
0020 EXPLAIN FORMATTED
0021   SELECT key, max(val)
0022   FROM explain_temp1
0023   WHERE key > 0
0024   GROUP BY key
0025   HAVING max(val) > 0;
0026
0027 -- simple union
0028 EXPLAIN FORMATTED
0029   SELECT key, val FROM explain_temp1 WHERE key > 0
0030   UNION
0031   SELECT key, val FROM explain_temp1 WHERE key > 0;
0032
0033 -- Join
0034 EXPLAIN FORMATTED
0035   SELECT *
0036   FROM   explain_temp1 a,
0037          explain_temp2 b
0038   WHERE  a.key = b.key;
0039
0040 EXPLAIN FORMATTED
0041   SELECT *
0042   FROM   explain_temp1 a
0043          LEFT OUTER JOIN explain_temp2 b
0044                       ON a.key = b.key;
0045
0046 -- Subqueries nested.
0047 EXPLAIN FORMATTED
0048   SELECT *
0049   FROM   explain_temp1
0050   WHERE  key = (SELECT max(key)
0051                 FROM   explain_temp2
0052                 WHERE  key = (SELECT max(key)
0053                               FROM   explain_temp3
0054                               WHERE  val > 0)
0055                        AND val = 2)
0056          AND val > 3;
0057
0058 EXPLAIN FORMATTED
0059   SELECT *
0060   FROM   explain_temp1
0061   WHERE  key = (SELECT max(key)
0062                 FROM   explain_temp2
0063                 WHERE  val > 0)
0064          OR
0065          key = (SELECT avg(key)
0066                 FROM   explain_temp3
0067                 WHERE  val > 0);
0068
0069 -- Reuse subquery
0070 EXPLAIN FORMATTED
0071   SELECT (SELECT Avg(key) FROM explain_temp1) + (SELECT Avg(key) FROM explain_temp1)
0072   FROM explain_temp1;
0073
0074 -- CTE + ReuseExchange
0075 EXPLAIN FORMATTED
0076   WITH cte1 AS (
0077     SELECT *
0078     FROM explain_temp1
0079     WHERE key > 10
0080   )
0081   SELECT * FROM cte1 a, cte1 b WHERE a.key = b.key;
0082
0083 EXPLAIN FORMATTED
0084   WITH cte1 AS (
0085     SELECT key, max(val)
0086     FROM explain_temp1
0087     WHERE key > 10
0088     GROUP BY key
0089   )
0090   SELECT * FROM cte1 a, cte1 b WHERE a.key = b.key;
0091
0092 -- A spark plan which has innerChildren other than subquery
0093 EXPLAIN FORMATTED
0094   CREATE VIEW explain_view AS
0095     SELECT key, val FROM explain_temp1;
0096
0097 -- HashAggregate
0098 EXPLAIN FORMATTED
0099   SELECT
0100     COUNT(val) + SUM(key) as TOTAL,
0101     COUNT(key) FILTER (WHERE val > 1)
0102   FROM explain_temp1;
0103
0104 -- ObjectHashAggregate
0105 EXPLAIN FORMATTED
0106   SELECT key, sort_array(collect_set(val))[0]
0107   FROM explain_temp4
0108   GROUP BY key;
0109
0110 -- SortAggregate
0111 EXPLAIN FORMATTED
0112   SELECT key, MIN(val)
0113   FROM explain_temp4
0114   GROUP BY key;
0115
0116 -- cleanup
0117 DROP TABLE explain_temp1;
0118 DROP TABLE explain_temp2;
0119 DROP TABLE explain_temp3;
0120 DROP TABLE explain_temp4;