7.30.4 Conditions under which derived table expansion is not performed
If any of the following conditions are satisfied, derived table expansion is not performed.
-
If, in the outermost query specification for a derived query, you specify a derived query name with SELECT DISTINCT in the FROM clause, and one of the following specifications is in the query specification that directly includes that FROM clause:
-
GROUP BY clause, HAVING clause, or set function
The following is an example:
SELECT SUM("C1") FROM (SELECT DISTINCT * FROM "T1") AS "V1" GROUP BY "C2"
-
Table join (including joined table)
The following is an example:
SELECT * FROM (SELECT DISTINCT * FROM "T1") AS "V1","T1" WHERE "V1"."C1"="T1"."C1"
-
Selection expression in which a derived column derived from a value expression that includes a column specification is not specified as a single column specification
The following is an example:
SELECT "VC1" FROM (SELECT DISTINCT "C1","C2" FROM "T1") AS "V1"("VC1","VC2")
Because derived column VC2 is not specified in the selection expression, derived table V1 is not expanded.
SELECT "VC1"*1.05,"VC2" FROM (SELECT DISTINCT * FROM "T1") AS "V1"("VC1","VC2")
Because derived column VC1 is not specified as a single column specification in the selection expression, derived table V1 is not expanded.
-
Selection expression containing a value expression that includes scalar function RANDOM, scalar function RANDOM_NORMAL, scalar function RANDOMROW, a scalar subquery, or a window function
The following is an example:
SELECT "VC1","VC2",RANDOM() FROM (SELECT DISTINCT "C1","C2" FROM "T1") AS "V1"("VC1","VC2")
-
Selection expression in which a derived column derived from the following value expression is not specified as a single column specification: a value expression that includes scalar function RANDOM, scalar function RANDOM_NORMAL, scalar function RANDOMROW, or a set function, and does not include a column specification
The following is an example:
SELECT "VC1" FROM (SELECT DISTINCT "C1",RANDOM() FROM "T1") AS "V1"("VC1","VC2")
-
Sort key value expression that is not specified as a selection expression
The following is an example:
SELECT * FROM (SELECT DISTINCT * FROM "T1") AS "V1" ORDER BY ("C2"+1)
-
-
If, in the outermost query specification for a derived query, you specify a derived query name with a GROUP BY clause in the FROM clause, and one of the following specifications is in the query specification that directly includes that FROM clause:
-
A DISTINCT set function or inverse distribution function is specified.
The following is an example:
SELECT "C1",SUM(DISTINCT "C2") FROM (SELECT "C1","C2" FROM "T1" GROUP BY "C1","C2") AS "V1" GROUP BY "C1","C2"
-
Table join (including joined table)
The following is an example:
SELECT * FROM (SELECT "C1","C2" FROM "T1" GROUP BY "C1","C2") AS "V1","T1"
-
-
If, all of the following conditions are met: 1) In the outermost query specification for a derived query, the name of a derived query that contains the GROUP BY clause is specified for a FROM clause. 2) The query specification that directly contains that FROM clause also contains the GROUP BY clause, the HAVING clause, or a set function. 3) Either of the following conditions is met:
-
The number of grouping columns in the query specification for operating a derived query is different from the number of grouping columns in the derived query.
The following is an example:
SELECT "C1","C2" FROM (SELECT "C1","C2" FROM "T1" GROUP BY "C1","C2","C3") AS "V1" GROUP BY "C1","C2"
-
The derived column derived from the column referenced by a grouping column for a derived query is not specified alone in a grouping column that has a query specification for operating the derived query.
The following is an example:
SELECT "C1","C2"+1 FROM (SELECT "C1","C2" FROM "T1" GROUP BY "C1","C2") AS "V1" GROUP BY "C1","C2"+1
-
-
If, in the outermost query specification for a derived query, you specify a derived query name with a GROUP BY clause in the FROM clause including a value expression, and one of the following specifications is in the query specification that directly includes that FROM clause:
-
A grouping column of the derived query that was derived from a value expression that includes the column specification is specified as a column that makes an external reference.
The following is an example:
SELECT * FROM (SELECT "G1" FROM "T1" GROUP BY C1+1 "G1") AS "V1" WHERE EXISTS (SELECT * FROM "T2" WHERE "T2"."C1" = "V1"."G1")
-
-
If, in the selection expression of the outermost query specification for a derived query, you specify a derived query name with a value expression that includes a column specification in the FROM clause of that selection expression, and one of the following occurs in the query specification that directly includes that FROM clause:
-
A column of the derived query name that was derived from a value expression that includes a column specification is specified in a selection expression or in the HAVING clause as a grouping column that makes an external reference.
The following is an example:
SELECT "DC1" FROM (SELECT "C1"+1 AS "DC1" FROM "T1") AS "V1" GROUP BY "DC1" HAVING EXISTS (SELECT * FROM "T2" WHERE "T2"."C1" = "V1"."DC1")
-
Multiple inverse distribution functions are specified.
The following is an example:
SELECT MEDIAN("C1") FROM (SELECT ABS("C1") AS "C1","C2" FROM "T1") AS "V1" HAVING MEDIAN("C1")>100
-
A set function argument that makes an external reference to a column of the derived query name that was derived from a value expression that includes the column specification
The following is an example:
SELECT "C1" FROM (SELECT SUBSTR("C1",5) AS "C1","C2" FROM "T1") AS "V1" GROUP BY "C1" HAVING EXISTS(SELECT * FROM "T1" WHERE MAX("V1"."C1")="C1")
-
-
If, in the selection expression of the outermost query specification for a derived query, a derived query name (for which a value expression that does not include a column specification) is specified in the table reference of a joined table that is on the side filled with null values
The following is an example:
SELECT * FROM "T1" LEFT OUTER JOIN (SELECT SUBSTR('ABC',2) AS "C1","C2" FROM "T2") AS "V1" ON "T1"."C1"="V1"."C1"
-
If, in the selection expression of the outermost query specification for a derived query, you specify a derived query name with a value expression that does not include a column specification in the FROM clause of that selection expression, and one of the following occurs in the query specification that directly includes that FROM clause:
-
Multiple inverse distribution functions are specified.
The following is an example:
SELECT MEDIAN("C1") FROM (SELECT ABS(100) AS "C1","C2" FROM "T1") AS "V1" HAVING MEDIAN("C1")>100
-
A window function containing a derived query column name derived from a value expression that does not include a column specification
The following is an example:
SELECT "C2",SUM("C2") OVER(ORDER BY "C1") FROM (SELECT SUBSTR('ABC',2) AS "C1","C2" FROM "T1") AS "V1"
-
-
If, in the selection expression of the outermost query specification of a derived query, you specify, in a FROM clause, the name of a derived query in which a value expression that includes the scalar function RANDOM or RANDOM_NORMAL is specified, and you specify, in the SQL statement, a column derived from the scalar function RANDOM or RANDOM_NORMAL
The following is an example:
SELECT "C1", "C2" FROM (SELECT "C1"+RANDOM() AS "C1", "C2" FROM "T2") AS "V1"
-
If, all of the following conditions are met: 1) In the selection expression of the outermost query specification for a derived query, the name of a derived query that uses a value expression that includes the scalar function RANDOMCURSOR is specified for a FROM clause. 2) In the query specification that directly contains that FROM clause, the column derived from the value expression that includes scalar function RANDOMCURSOR for a derived query is specified in an item other than a selection expression and ORDER BY clause.
The following is an example:
SELECT "C1","C2" FROM (SELECT "C1"+RANDOMCURSOR(1,10,20) AS "C1","C2" FROM "T2") AS "V1" WHERE "C1">1000
-
If, all of the following conditions are met: 1) In the selection expression of the outermost query specification for a derived query, the name of a derived query that uses a value expression that includes the scalar function RANDOMROW is specified for a FROM clause. 2) The query specification that directly contains that FROM clause also contains the following specification:
-
An item (other than a selection expression and ORDER BY clause) in which the column derived from a value expression that includes scalar function RANDOMROW for a derived query is specified
The following is an example:
SELECT "C1","C2" FROM (SELECT "C1"+RANDOMROW(1,10,20) AS "C1","C2" FROM "T2") AS "V1" WHERE "C1">1000
-
A set function argument for which the column derived from a value expression that includes scalar function RANDOMROW for a derived query is specified
The following is an example:
SELECT SUM("C1") FROM (SELECT "C1"+RANDOMROW(1,10,20) AS "C1","C2" FROM "T2") AS "V1"
-
A window function in which the column derived from a value expression that includes scalar function RANDOMROW for a derived query is specified
The following is an example:
SELECT "C1",SUM("C2") OVER(ORDER BY "C1") FROM (SELECT "C1", "C2"+RANDOMROW(1,10,20) AS "C2" FROM "T2") AS "V1"
-
The scalar function RANDOMROW in which the column derived from a value expression that includes the scalar function RANDOMROW for a derived query is specified
The following is an example:
SELECT RANDOMROW(1,"C1","C2") FROM (SELECT "C1"+RANDOMROW(1,10,20) AS "C1","C2" FROM "T2") AS "V1"
-
Table join (including joined table)
The following is an example:
SELECT * FROM (SELECT "C1"+RANDOMROW(1,10,20) AS "C1","C2" FROM "T2") AS "V1","T1"
-
-
If, in the selection expression of the outermost query specification of a derived query, you specify a derived query name with a value expression that includes a dynamic parameter in the FROM clause of that selection expression, and you specify a column derived from the dynamic parameter in the query specification.
The following is an example:
SELECT "C1" FROM (SELECT SUBSTR("C1",?) AS "C1","C2" FROM "T1") AS "V1"
-
If, in the selection expression of the outermost query specification of a derived query, you specify a derived query name that specifies a value expression that includes a scalar subquery in the FROM clause of that selection expression.
The following is an example:
SELECT "C1" FROM (SELECT (SELECT "C1" FROM "T2") + 10 AS "C1" FROM "T1") AS "V1"
-
If, in the selection expression of the outermost query specification of a derived query, you specify a derived query name with a value expression that includes an inverse distribution function in the FROM clause of that selection expression, and you specify a column derived from the inverse distribution function in the query specification.
The following is an example:
SELECT "C1" FROM (SELECT MEDIAN("C1") AS "C1",MAX("C2") FROM "T1") AS "V1"
-
If, all of the following conditions are met: 1) In the selection expression of the outermost query specification for a derived query, the name of a derived query that uses a set function is specified for a FROM clause. 2) The query specification that directly contains that FROM clause also contains the following specification:
-
The GROUP BY clause that includes a value expression, DISTINCT set function, or inverse distribution function is specified
The following is an example:
SELECT SUM(DISTINCT "C1") FROM (SELECT COUNT("C1") AS "C1" FROM "T1") AS "V1" GROUP BY "C1"
-
Table join (including joined table)
The following is an example:
SELECT * FROM (SELECT COUNT("C1") AS "C1" FROM "T1") AS "V1","T1" WHERE "V1"."C1"="T1"."C1"
-
A set function argument for which a column of the derived query name that was derived from a set function is specified as a column that makes an external reference
The following is an example:
SELECT "C1" FROM (SELECT COUNT("C1") AS "C1" FROM "T1") AS "V1" GROUP BY "C1" HAVING EXISTS(SELECT * FROM "T2" WHERE MAX("V1"."C1")="C1")
-
-
If a derived query name specifying a window function is specified in a FROM clause in the outermost query specification of a derived query
The following is an example:
SELECT "C2" FROM (SELECT "C1",AVG("C1") OVER(ORDER BY "C2") AS "C2" FROM "T1") AS "V1"
-
The LIMIT clause is included in the outermost query specification of a derived query.
The following is an example:
SELECT "C1","C2" FROM (SELECT "C1","C2" FROM "T1" LIMIT 10) AS "V1"
-
A derived query name for which a comma join is specified is specified for a table reference to a joined table in the outermost query specification of a derived query.
The following is an example:
SELECT "VC1","VC2" FROM (SELECT "T1"."C1","T2"."C1" FROM "T1","T2","T3" WHERE "T1"."C1"="T2"."C1" AND "T2"."C1"="T3"."C1") AS "V1"("VC1","VC2") LEFT JOIN "T3" ON "VC1" = "T3"."C1"
-
A derived query is specified for a table reference in FULL OUTER JOIN.
The following is an example:
SELECT * FROM (SELECT "C1" FROM "T1") AS "V1" FULL OUTER JOIN "T2" ON "V1"."C1"="T2"."C1"
-
VARCHAR-type data larger than 32,000 bytes is contained in the column derived as a result of a set operation that uses an operand that is the outermost query specification of a derived query.
The following is an example:
SELECT "C1" FROM (SELECT "C1" FROM "T1" UNION SELECT "DEFINE_SOURCE" FROM "MASTER"."SQL_DEFINE_SOURCE" ) AS "V1"
-
At least one of the conditions under which an internal derived table in preceding items 1 to 18 is not expanded is satisfied in the following case: the query specification in an operand of a set operation specified in a derived query is assumed to be a derived query.
The following is an example:
SELECT "C1" FROM (SELECT DISTINCT "C1","C2" FROM "T1" UNION ALL SELECT "C1","C2" FROM "T2" ) AS "V1"
Assume that SELECT DISTINCT "C1","C2" FROM "T1", which is a query specification in an operand of a set operation, is a derived query. In this case, a condition# under which the internal derived table in item 1 is not expanded is satisfied. Therefore, derived table V1 is not expanded.
- #
-
The FROM clause includes the name of a derived query in which SELECT DISTINCT is specified in the outermost query specification, and the FROM clause is directly included in a query specification that satisfies the following condition:
-
A derived column derived from a value expression that includes a column specification is not specified as a single column specification in a selection expression.
-
-
If, in the FROM clause of the outermost query specification for a derived query, you specify a derived query name that specifies only the UNION ALL set operator, and one of the following is specified in the query specification that directly contains that FROM clause:
-
GROUP BY clause, HAVING clause, or set function
The following is an example:
SELECT "C1","C2" FROM ( SELECT "C1","C2" FROM "T1" UNION ALL SELECT "C1","C2" FROM "T2" ) AS "V1" GROUP BY "C1","C2"
-
Window function specified in the selection expression
The following is an example:
SELECT "C1",SUM("C1") OVER(ORDER BY "C2") FROM ( SELECT "C1","C2" FROM "T1" UNION ALL SELECT "C1","C2" FROM "T2" ) AS "V1"
-
Table join (including joined table)
The following is an example:
SELECT * FROM ( SELECT "C1", "C2" FROM "T1" UNION ALL SELECT "C1","C2" FROM "T2" ) AS "V1","T3"
-
Sort key that is a derived query name column that is not specified in the selection expression
The following is an example:
SELECT "C1" FROM ( SELECT "C1","C2" FROM "T1" UNION ALL SELECT "C1","C2" FROM "T2" ) AS "V1" ORDER BY "C2"
-
Sort key value expression that is not specified as a selection expression
The following is an example:
SELECT "C1" FROM ( SELECT "C1","C2" FROM "T1" UNION ALL SELECT "C1","C2" FROM "T2" ) AS "V1" ORDER BY ("C1"+1)
-
-
If, in the FROM clause of the outermost query specification for a derived query, you specify a derived query name that specifies a set operator other than UNION ALL, and one of the following is specified in the query specification that directly contains that FROM clause:
-
SELECT DISTINCT
The following is an example:
SELECT DISTINCT "C1" FROM ( SELECT "C1" FROM "T1" EXCEPT ALL SELECT "C1" FROM "T2" ) AS "V1"
-
GROUP BY clause, HAVING clause, or set function
The following is an example:
SELECT "C1","C2" FROM ( SELECT "C1","C2" FROM "T1" UNION SELECT "C1","C2" FROM "T2" ) AS "V1" GROUP BY "C1","C2"
-
Window function specified in the selection expression
The following is an example:
SELECT "C1",SUM("C1") OVER(ORDER BY "C2") FROM ( SELECT "C1","C2" FROM "T1" INTERSECT ALL SELECT "C1","C2" FROM "T2" ) AS "V1"
-
Table join (including joined table)
The following is an example:
SELECT * FROM ( SELECT "C1","C2" FROM "T1" EXCEPT SELECT "C1","C2" FROM "T2" ) AS "V1","T3"
-
Selection expression containing a value expression that includes scalar function RANDOM, scalar function RANDOM_NORMAL, scalar function RANDOMROW, a scalar subquery, or a window function
The following is an example:
SELECT "VC1","VC2",RANDOM() FROM ( SELECT "C1","C2" FROM "T1" UNION SELECT "C1","C2" FROM "T2" ) AS "V1"("VC1","VC2")
-
Selection expression containing one or more derived query name columns that are not specified as single column specifications
The following is an example:
SELECT "VC1" FROM ( SELECT "C1","C2" FROM "T1" INTERSECT SELECT "C1","C2" FROM "T2" ) AS "V1"("VC1","VC2")
Because derived column VC2 is not specified in the selection expression, derived table V1 is not expanded.
The following is an example:
SELECT "VC1"*1.05,"VC2" FROM ( SELECT "C1","C2" FROM "T1" UNION SELECT "C1","C2" FROM "T2" ) AS "V1"("VC1","VC2")
Because derived column VC1 is not specified as a single column specification in the selection expression, derived table V1 is not expanded.
-
Sort key that is a derived query name column that is not specified in the selection expression
The following is an example:
SELECT "C1" FROM ( SELECT "C1","C2" FROM "T1" UNION SELECT "C1","C2" FROM "T2" ) AS "V1" ORDER BY "C2"
-
Sort key value expression that is not specified as a selection expression
The following is an example:
SELECT "C1" FROM ( SELECT "C1","C2" FROM "T1" UNION SELECT "C1","C2" FROM "T2" ) AS "V1" ORDER BY ("C1"+1)
-