[
https://issues.apache.org/jira/browse/CALCITE-7918?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Darpan Lunagariya (e6data) updated CALCITE-7918:
------------------------------------------------
Description:
h2. There are 2 problems, which is contributing to this huge latency:
* *Ambiguity checking:* {{DelegatingScope.hasAmbiguousField}} scans every field
to count matching names, even when ordinary case-sensitive lookup already uses
an index.
* *Case-insensitive lookup:* {{RelDataTypeImpl.getField}} only uses the
existing field-name map when {{caseSensitive=true}}. Case-insensitive requests
scan the field list instead.
h2. Reproduction
Both examples are self-contained and require no registered tables or data.
Parse and validate using:
{code:java}
SqlParser.config()
.withLex(Lex.JAVA)
.withCaseSensitive(true)
{code}
Use more than 20 fields because the existing {{RelRecordType}} field-name map
is only created above that threshold. The following examples use 21 fields;
increase the width to observe the scaling.
h3. 1. Ambiguity checking ignores indexed lookup
{code:sql}
SELECT
T.C0, T.C1, T.C2, T.C3, T.C4, T.C5, T.C6,
T.C7, T.C8, T.C9, T.C10, T.C11, T.C12, T.C13,
T.C14, T.C15, T.C16, T.C17, T.C18, T.C19, T.C20
FROM (
VALUES (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
11, 12, 13, 14, 15, 16, 17, 18, 19, 20)
) AS T(
C0, C1, C2, C3, C4, C5, C6,
C7, C8, C9, C10, C11, C12, C13,
C14, C15, C16, C17, C18, C19, C20
)
{code}
Ordinary field lookups use the existing exact-name map. However,
{{fullyQualify}} also calls {{hasAmbiguousField}}, which scans the entire row
for each resolved identifier.
h3. 2. Case-insensitive lookup bypasses indexing
{code:sql}
SELECT
T.R['c0'] AS C0, T.R['c1'] AS C1,
T.R['c2'] AS C2, T.R['c3'] AS C3,
T.R['c4'] AS C4, T.R['c5'] AS C5,
T.R['c6'] AS C6, T.R['c7'] AS C7,
T.R['c8'] AS C8, T.R['c9'] AS C9,
T.R['c10'] AS C10, T.R['c11'] AS C11,
T.R['c12'] AS C12, T.R['c13'] AS C13,
T.R['c14'] AS C14, T.R['c15'] AS C15,
T.R['c16'] AS C16, T.R['c17'] AS C17,
T.R['c18'] AS C18, T.R['c19'] AS C19,
T.R['c20'] AS C20
FROM (
SELECT CAST(
ROW(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
11, 12, 13, 14, 15, 16, 17, 18, 19, 20)
AS ROW(
C0 INTEGER, C1 INTEGER, C2 INTEGER,
C3 INTEGER, C4 INTEGER, C5 INTEGER,
C6 INTEGER, C7 INTEGER, C8 INTEGER,
C9 INTEGER, C10 INTEGER, C11 INTEGER,
C12 INTEGER, C13 INTEGER, C14 INTEGER,
C15 INTEGER, C16 INTEGER, C17 INTEGER,
C18 INTEGER, C19 INTEGER, C20 INTEGER
)
) AS R
) AS T
{code}
h2. Observed scaling
The following counts were measured by observing field-name reads during
validation. The fixed counts include one-time index construction.
|| Fields || Original ambiguity scans, example 1 || Original insensitive scans,
example 2 ||
| 128 | 49,152 | 16,512 |
| 256 | 196,608 | 65,792 |
| 512 | 786,432 | 262,656 |
was:
h2. There are 2 problems, which is contributing to this huge latency:
* *Ambiguity checking:* {{DelegatingScope.hasAmbiguousField}} scans every field
to count matching names, even when ordinary case-sensitive lookup already uses
an index.
* *Case-insensitive lookup:* {{RelDataTypeImpl.getField}} only uses the
existing field-name map when {{caseSensitive=true}}. Case-insensitive requests
scan the field list instead.
h2. Reproduction
Both examples are self-contained and require no registered tables or data.
Parse and validate using:
{code:java}
SqlParser.config()
.withLex(Lex.JAVA)
.withCaseSensitive(true)
{code}
Use more than 20 fields because the existing {{RelRecordType}} field-name map
is only created above that threshold. The following examples use 21 fields;
increase the width to observe the scaling.
h3. 1. Ambiguity checking ignores indexed lookup
{code:sql}
SELECT
T.C0, T.C1, T.C2, T.C3, T.C4, T.C5, T.C6,
T.C7, T.C8, T.C9, T.C10, T.C11, T.C12, T.C13,
T.C14, T.C15, T.C16, T.C17, T.C18, T.C19, T.C20
FROM (
VALUES (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
11, 12, 13, 14, 15, 16, 17, 18, 19, 20)
) AS T(
C0, C1, C2, C3, C4, C5, C6,
C7, C8, C9, C10, C11, C12, C13,
C14, C15, C16, C17, C18, C19, C20
)
{code}
Ordinary field lookups use the existing exact-name map. However,
{{fullyQualify}} also calls {{hasAmbiguousField}}, which scans the entire row
for each resolved identifier.
h3. 2. Case-insensitive lookup bypasses indexing
{code:sql}
SELECT
T.R['c0'] AS C0, T.R['c1'] AS C1,
T.R['c2'] AS C2, T.R['c3'] AS C3,
T.R['c4'] AS C4, T.R['c5'] AS C5,
T.R['c6'] AS C6, T.R['c7'] AS C7,
T.R['c8'] AS C8, T.R['c9'] AS C9,
T.R['c10'] AS C10, T.R['c11'] AS C11,
T.R['c12'] AS C12, T.R['c13'] AS C13,
T.R['c14'] AS C14, T.R['c15'] AS C15,
T.R['c16'] AS C16, T.R['c17'] AS C17,
T.R['c18'] AS C18, T.R['c19'] AS C19,
T.R['c20'] AS C20
FROM (
SELECT CAST(
ROW(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
11, 12, 13, 14, 15, 16, 17, 18, 19, 20)
AS ROW(
C0 INTEGER, C1 INTEGER, C2 INTEGER,
C3 INTEGER, C4 INTEGER, C5 INTEGER,
C6 INTEGER, C7 INTEGER, C8 INTEGER,
C9 INTEGER, C10 INTEGER, C11 INTEGER,
C12 INTEGER, C13 INTEGER, C14 INTEGER,
C15 INTEGER, C16 INTEGER, C17 INTEGER,
C18 INTEGER, C19 INTEGER, C20 INTEGER
)
) AS R
) AS T
{code}
h2. Observed scaling
The following counts were measured by observing field-name reads during
validation. The fixed counts include one-time index construction.
|| Fields || Original ambiguity scans, example 1 || Original insensitive scans,
example 2 || Index construction after fix, either example ||
| 128 | 49,152 | 16,512 | 128 |
| 256 | 196,608 | 65,792 | 256 |
| 512 | 786,432 | 262,656 | 512 |
> SQL validation takes too long for wider row types
> -------------------------------------------------
>
> Key: CALCITE-7918
> URL: https://issues.apache.org/jira/browse/CALCITE-7918
> Project: Calcite
> Issue Type: Improvement
> Components: core
> Affects Versions: 1.42.0
> Reporter: Darpan Lunagariya (e6data)
> Assignee: Darpan Lunagariya (e6data)
> Priority: Minor
>
> h2. There are 2 problems, which is contributing to this huge latency:
> * *Ambiguity checking:* {{DelegatingScope.hasAmbiguousField}} scans every
> field to count matching names, even when ordinary case-sensitive lookup
> already uses an index.
> * *Case-insensitive lookup:* {{RelDataTypeImpl.getField}} only uses the
> existing field-name map when {{caseSensitive=true}}. Case-insensitive
> requests scan the field list instead.
> h2. Reproduction
> Both examples are self-contained and require no registered tables or data.
> Parse and validate using:
> {code:java}
> SqlParser.config()
> .withLex(Lex.JAVA)
> .withCaseSensitive(true)
> {code}
> Use more than 20 fields because the existing {{RelRecordType}} field-name map
> is only created above that threshold. The following examples use 21 fields;
> increase the width to observe the scaling.
> h3. 1. Ambiguity checking ignores indexed lookup
> {code:sql}
> SELECT
> T.C0, T.C1, T.C2, T.C3, T.C4, T.C5, T.C6,
> T.C7, T.C8, T.C9, T.C10, T.C11, T.C12, T.C13,
> T.C14, T.C15, T.C16, T.C17, T.C18, T.C19, T.C20
> FROM (
> VALUES (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
> 11, 12, 13, 14, 15, 16, 17, 18, 19, 20)
> ) AS T(
> C0, C1, C2, C3, C4, C5, C6,
> C7, C8, C9, C10, C11, C12, C13,
> C14, C15, C16, C17, C18, C19, C20
> )
> {code}
> Ordinary field lookups use the existing exact-name map. However,
> {{fullyQualify}} also calls {{hasAmbiguousField}}, which scans the entire row
> for each resolved identifier.
> h3. 2. Case-insensitive lookup bypasses indexing
> {code:sql}
> SELECT
> T.R['c0'] AS C0, T.R['c1'] AS C1,
> T.R['c2'] AS C2, T.R['c3'] AS C3,
> T.R['c4'] AS C4, T.R['c5'] AS C5,
> T.R['c6'] AS C6, T.R['c7'] AS C7,
> T.R['c8'] AS C8, T.R['c9'] AS C9,
> T.R['c10'] AS C10, T.R['c11'] AS C11,
> T.R['c12'] AS C12, T.R['c13'] AS C13,
> T.R['c14'] AS C14, T.R['c15'] AS C15,
> T.R['c16'] AS C16, T.R['c17'] AS C17,
> T.R['c18'] AS C18, T.R['c19'] AS C19,
> T.R['c20'] AS C20
> FROM (
> SELECT CAST(
> ROW(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
> 11, 12, 13, 14, 15, 16, 17, 18, 19, 20)
> AS ROW(
> C0 INTEGER, C1 INTEGER, C2 INTEGER,
> C3 INTEGER, C4 INTEGER, C5 INTEGER,
> C6 INTEGER, C7 INTEGER, C8 INTEGER,
> C9 INTEGER, C10 INTEGER, C11 INTEGER,
> C12 INTEGER, C13 INTEGER, C14 INTEGER,
> C15 INTEGER, C16 INTEGER, C17 INTEGER,
> C18 INTEGER, C19 INTEGER, C20 INTEGER
> )
> ) AS R
> ) AS T
> {code}
> h2. Observed scaling
> The following counts were measured by observing field-name reads during
> validation. The fixed counts include one-time index construction.
> || Fields || Original ambiguity scans, example 1 || Original insensitive
> scans, example 2 ||
> | 128 | 49,152 | 16,512 |
> | 256 | 196,608 | 65,792 |
> | 512 | 786,432 | 262,656 |
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
