Hitachi

Hitachi Advanced Database SQL Reference

7.30.5 Summary of when derived table expansion is performed

The following table summarizes the conditions under which derived table expansion is and is not performed.

Table 7‒40: Summary of when derived table expansion is performed (1/2)

Specification of the SQL statement that manipulates a derived query

Specification of the derived query

SEL_

DIST

GRP

GRP_EXP

SEL_EXP

SEL_

NCOL

SEL_RAND

SEL_

RANDCRS

SEL_

RANDROW

SEL_PRM

SEL_DIST

Y

Y

Y

Y

Y

Y#5

Y

Y

N

GRP

N

Y#6

Y#6

Y#2

Y#2

Y#5

D

D

N

GRP_EXP

N

N

N

Y

Y

Y#5

D

D

N

A-FUNC

N

Y

#6, #7

Y

#6, #7

Y

Y

Y#5

Y#8

D

N

D-FUNC

N

N

N

Y

Y

Y#5

Y#8

D

N

I-FUNC2

N

N

N

D

D

Y#5

Y#8

D

N

WIN_AGG

N

Y

Y

Y

Y

Y#5

Y#8

D

N

WIN_PAR

N

Y

Y

Y

D

Y#5

Y#8

D

N

WIN_ORD

N

Y

Y

Y

Y

Y#5

Y#8

D

N

SEL_EXP

Y

Y

Y

Y

Y

Y#5

Y

Y#9

N

SEL_RAND

N

Y

Y

Y

Y

Y#5

Y

Y

N

SEL_RANDROW

N

Y

Y

Y

Y

Y#5

Y

Y#9

N

SEL_SUBQ

N

Y

Y

Y

Y

Y#5

Y

Y

N

SEL_WINDOW

N

Y

Y

Y

Y

Y#5

Y

Y

N

SEL_CNDRV

N

Y

Y

Y

Y

Y#5

Y

Y

N

SEL_NCNDRV

Y#11

Y

Y

Y

Y

Y#5

Y

Y

N

JOIN

N

N

N

Y

Y

Y#5

Y

Y#5

N

IN_J_TBL

N

N

N

Y#4

N

Y#5

Y

Y#5

N

J_TBL#12

N

N

N

Y

Y

Y#5

Y

Y#5

N

FJ_TBL

N

N

N

N

N

N

N

N

N

S_KEY

Y

Y

Y

Y

Y

Y#5

Y

Y

N

SEXP_KEY

N

Y

Y

Y

Y

Y#5

Y

Y#9

N

O_REF

Y

Y

D

Y

Y

Y#5

D

D

N

O_REF_FUNC

N

N

N

D

D

Y#5

D

D

N

Other items

Y

Y

Y

Y

Y

Y#5

Y#8

Y

#8, #9

N
Table 7‒41: Summary of when derived table expansion is performed (2/2)

Specification of the SQL statement that manipulates a derived query

Specification of the derived query

SUBQ

SEL_

IFN

FUNC_

COL

FUNC_

EXP

WINDOW

CJOIN#12

LIMIT

U_ALL#3, #10

SET_OP#3, #10

SETOP_

VCH

32000

Other items

SEL_DIST

N

N

Y

Y

N

Y

N

Y

Y#1

N

Y

GRP

N

N

D

D

N

Y

N

N

N

N

Y

GRP_EXP

N

N

D

D

N

Y

N

N

N

N

Y

A-FUNC

N

N

Y#7

Y#7

N

Y

N

N

N

N

Y

D-FUNC

N

N

D

D

N

Y

N

N

N

N

Y

I-FUNC2

N

N

D

D

N

Y

N

N

N

N

Y

WIN_AGG

N

N

Y

Y

N

Y

N

N

N

N

Y

WIN_PAR

N

N

Y

Y

N

Y

N

N

N

N

Y

WIN_ORD

N

N

Y

Y

N

Y

N

N

N

N

Y

SEL_EXP

N

N

Y

Y

N

Y

N

Y

Y

N

Y

SEL_RAND

N

N

Y

Y

N

Y

N

Y

N

N

Y

SEL_RANDROW

N

N

Y

Y

N

Y

N

Y

N

N

Y

SEL_SUBQ

N

N

Y

Y

N

Y

N

Y

N

N

Y

SEL_WINDOW

N

N

Y

Y

N

Y

N

Y

N

N

Y

SEL_CNDRV

N

N

Y

Y

N

Y

N

Y

N

N

Y

SEL_NCNDRV

N

N

Y

Y

N

Y

N

Y

N

N

Y

JOIN

N

N

N

N

N

Y

N

N

N

N

Y

IN_J_TBL

N

N

N

N

N

N

N

N

N

N

Y

J_TBL#12

N

N

N

N

N

N

N

N

N

N

Y

FJ_TBL

N

N

N

N

N

N

N

N

N

N

N

S_KEY

N

N

Y

Y

N

Y

N

Y

Y

N

Y

SEXP_KEY

N

N

Y

Y

N

Y

N

N

N

N

Y

O_REF

N

N

Y

D

N

Y

N

Y

Y

N

Y

O_REF_FUNC

N

N

N

N

N

Y

N

N

N

N

Y

Other items

N

N

Y

Y

N

Y

N

Y

Y

N

Y
Legend:

Y: Derived table expansion is performed.

D: In general, derived table expansion is performed. However, if you specify a derived column that is derived from the specification of the derived query in a location in which an SQL statement that manipulates a derived query is specified, derived table expansion is not performed.

N: Derived table expansion is not performed.

  • A-FUNC: The ALL set function, but only if a column of the derived query name is specified as an aggregated argument

  • CJOIN: A comma join

  • D-FUNC: A DISTINCT set function, but only if a column of the derived query name is specified as an aggregated argument. Alternatively, only one inverse distribution function in which a column of the derived query name is specified as an aggregated argument.

  • I-FUNC2: Two or more inverse distribution functions in which a column of the derived query name is specified as an aggregated argument

  • FJ_TBL: A derived query name is specified in a table reference of FULL OUTER JOIN

  • FUNC_COL: A set function, but only if column specifications appear as aggregated arguments

  • FUNC_EXP: A set function, but only if no column specifications appear as aggregated arguments

  • GRP: GROUP BY clause, a HAVING clause, or a set function

  • GRP_EXP: GROUP BY clause without column specifications (such as scalar operations)

  • IN_J_TBL: The corresponding derived query name for the table reference of a joined table that is on the side filled with null values

  • JOIN: Multiple tables

  • J_TBL: A derived query name specified in a table reference to a joined table

  • LIMIT: A LIMIT clause

  • O_REF: A column of the derived query name used as an external reference column

  • O_REF_FUNC: A column of the derived query name used as an external reference column in the argument of a set function

  • S_KEY: A column of the derived query name specified in the selection expression used as a sort key

  • SEL_CNDRV: 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.

  • SEL_IFN: An inverse distribution function specified in a selection expression

  • SEL_DIST: A SELECT DISTINCT clause

  • SEL_EXP: Non-column specifications (such as scalar operations) used in a selection expression (even if column specifications are used in value expressions)

  • SEL_NCNDRV: A derived column derived from a value expression that does not include a column specification is not specified as a single column specification in a selection expression.

  • SEL_NCOL: A value expression consisting of non-column specifications in the selection expression

  • SEL_PRM: A dynamic parameter appears in the selection expression.

  • SEL_RAND: The selection expression includes the scalar function RANDOM or RANDOM_NORMAL.

  • SEL_RANDCRS: The selection expression includes the scalar function RANDOMCURSOR.

  • SEL_RANDROW: The selection expression includes the scalar function RANDOMROW.

  • SEL_SUBQ: A selection expression includes a scalar subquery.

  • SEL_WINDOW: A selection expression includes a window function.

  • SET_OP: Set operations specified in cases other than U_ALL

  • SETOP_VCH32000: Column of the VARCHAR type larger than 32,000 bytes among the columns derived by the result of a set operation

  • SEXP_KEY: A sort key value expression that is not specified as a selection expression

    Example:

    SELECT "C1"+"C2","C2" AS "DC1" FROM "T1" ORDER BY "C1"/"C2"

  • SUBQ: Subquery included in a selection expression

  • U_ALL: Set operations that specify only UNION ALL

  • WINDOW: Window function

  • WIN_AGG: Derived query name column specified in a set function specified as a window function

  • WIN_PAR: Derived query name column specified in a window partition clause in a window function

  • WIN_ORD: Derived query name column specified in a window order clause in a window function

#1

Expansion of the derived table is not performed if the set operator that is evaluated last in the set operations specified in the derived query is EXCEPT ALL.

Example where expansion is not performed:

SELECT DISTINCT "C1","C2" FROM (
                  (SELECT "C1","C2" FROM "T1"
                   UNION ALL
                   SELECT "C1","C2" FROM "T2")
                   EXCEPT ALL
                   SELECT "C1","C2" FROM "T2") AS "V1"

In the SQL statement above, expansion of the derived table is not performed because the set operator that is evaluated last is EXCEPT ALL.

Example where expansion is performed:

SELECT DISTINCT "C1","C2" FROM (
                    SELECT "C1","C2" FROM "T1"
                    UNION ALL
                   (SELECT "C1","C2" FROM "T2"
                    EXCEPT ALL
                    SELECT "C1","C2" FROM "T2")) AS "V1"

In the SQL statement above, expansion of the derived table is performed because the set operator that is evaluated last is UNION ALL.

#2

If all of the following conditions are met, derived table expansion is not performed:

  1. The derived query name column that was derived from a value expression is specified as a grouping column.

  2. The derived query name column in condition 1 is specified in either of the following items and is an external reference column:

  • Selection expression

    Example where expansion is not performed:

    SELECT (SELECT "C1" FROM "T2" WHERE "T2"."C1" = "V1"."DC1") "DC2"
    FROM (SELECT "C1"+1 AS "DC1" FROM "T1") AS "V1"
    GROUP BY "DC1"

  • HAVING clause

    Example where expansion is not performed:

    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")
#3

If FULL OUTER JOIN is specified in a subquery that is included in a query specification for manipulating a derived table with a set operation specified, expansion of a set operation derived table is not performed.

Example where expansion is not performed:

SELECT * FROM (SELECT "C1","C2" FROM "T1"
               UNION ALL
               SELECT "C1","C2" FROM "T2") "DT2"
    WHERE "C1"=ANY(SELECT "X"."C1" FROM "T3" FULL OUTER JOIN "T4"
                                             ON "T3"."C2"> "T4"."C2")
#4

If one of the following value expressions is specified in the selection expression of the outermost query specification for a derived query, derived table expansion is not performed:

  • Scalar function COALESCE

  • Scalar function ISNULL

  • Scalar function NULLIF

  • Scalar function NVL

  • Scalar function DECODE

  • Scalar function LTDECODE

  • CASE expression

#5

If you specify, in the SQL statement, a derived column to which the details specified in the derived query apply, the derived table is not expanded. If you do not specify such a derived column, the derived table is expanded.

Example where expansion is not performed:

SELECT "DC1" FROM (SELECT RANDOM("C1","C2") AS "DC1" FROM "T1") AS "V1"

In the SQL statement above, the derived column "DC1" is specified in the SQL statement that manipulates the derived query. Because this derived column corresponds to a column in the selection expression that includes the scalar function RANDOM, which is specified in the derived query, the derived table is not expanded.

#6

If all of the following conditions are met, the derived table is expanded:

  • The number of grouping columns in the query specification for operating a derived query is the same as the number of grouping columns in the derived query.

  • All derived columns derived from the grouping column specified in a selection expression for a derived query are specified in a grouping column that has a query specification for operating the derived query.

Example where expansion is performed (1):

SELECT "C1","C2" 
    FROM (SELECT "C1","C2" FROM "T1" GROUP BY "C1","C2") AS "V1"
    GROUP BY "C1","C2"

Example where expansion is performed (2):

SELECT "C1","DC2"
    FROM (SELECT "C1","C2"+1 AS "DC2" FROM "T1" GROUP BY "C1","C2"+1) AS "V1"
    GROUP BY "C1","DC2"
#7

The derived table is expanded when all set functions included in the query specification that operates the derived query satisfy Condition 1 or Condition 2, as follows:

Condition 1:

  • The set function included in the query specification that operates the derived query is COUNT(*)#.

#

The set function COUNT (with ALL specified) for which a literal (or a value expression equivalent to a literal) is specified as an argument is replaced by the set function COUNT(*) and is treated as the set function COUNT(*).

Condition 2:

  • One of the following set functions is included in the query specification that operates the derived query:

    • Set function MAX with ALL specified

    • Set function MIN with ALL specified

    • Set function SUM with ALL specified

    • Set function AVG with ALL specified

  • A derived column consisting of the set functions specified in a derived query is specified as an aggregated argument of the preceding set function.

Example where expansion is performed:

SELECT "C1", "C2",SUM("C3")
    FROM (SELECT "C1","C2",COUNT("C3") AS "C3"
             FROM "T1"
             GROUP BY "C1","C2") "V1"
    GROUP BY "C1","C2"
#8

If a value expression that includes the derived column subject to the specification of the derived query is specified in an item other than a selection expression and ORDER BY clause, the derived table is not expanded.

#9

If the derived column subject to the specification of the derived query is specified in the scalar function RANDOMROW, the derived table is not expanded.

#10

The derived table is not expanded if at least one of the conditions under which an internal derived table 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.

#11

The derived table is not expanded if the following derived column is not specified as a single column specification in a selection expression: a derived column derived from a value expression that does not include column specifications and includes any of the following specifications:

  • Scalar function RANDOM

  • Scalar function RANDOM_NORMAL

  • Scalar function RANDOMROW

  • Set function

#12

The HADB server might convert INNER JOIN or CROSS JOIN to a comma join. Therefore, if INNER JOIN or CROSS JOIN specified in a derived query is converted to a comma join, it is assumed that a comma join is included in a derived query (CJOIN in the preceding table applies). Also, if a joined table disappears from a query specification in a case where INNER JOIN or CROSS JOIN specified in a query specification that manipulates a derived query is converted to a comma join, it is assumed that no joined table is specified (J_TBL in the preceding table no longer applies).

To Page Top

All Rights Reserved. Copyright (C) 2012, 2020, Hitachi, Ltd.