07-SubQuery¶
¶
Subquery: basic test
Verification testing during the development process.
¶
Subquery: count(1)
1. Use count trigger mode
2. Output results include 4 dimensions:
No grouping
Group by table name
Group by tags
Group by ordinary columns
3. Generate 100 SQL statements using the following syntax combinations:
Tables: system tables, super tables, child tables, normal tables, virtual super tables, virtual child tables
Functions:
Single-row functions (math/string/conversion/time functions)
Aggregate functions
Selection functions
Time-series-specific functions
Geometry functions
System functions
Queries: projection queries, nested queries, join queries, window queries (time/event/count/session/state), SHOW commands, GROUP BY, PARTITION BY, ORDER BY, LIMIT, SLIMIT, UNION, etc.
Filters: time comparisons, ordinary column comparisons, tag column comparisons
Operators: arithmetic, string, bitwise, comparison, logical, JSON operators
Others:
Queries on databases/tables same as/different from the trigger table
View queries
4. Include the following combinations in step 3 query results:
Use all data types: numeric, binary, string, geometry, json, etc.
Use all pseudo-columns: _qstart, _qend, _wstart, _wend, _wduration, _c0, _rowts, irowts, _irowtsorigin, tbname, etc.
Include data columns and tag columns
Randomly include None and NULL in result sets
Result set sizes: 1 row, n rows
Include duplicate timestamp in result sets
5. Test placeholder usage in step 3's queries, including:
Placeholders in various positions like FROM, SELECT, WHERE
Each placeholder: _twstart, _twend, _twduration, _twrownum, _tcurrent_ts, _tgrpid, _tlocaltime, %%n, %%tbname, %%tbrows
6. Validation checks:
Verify table structures and table counts
Validate correctness of calculation results
Validate the accuracy of placeholder data, such as %%trows
¶
Subquery: count(2)
1. Use count trigger mode
2. Output results include 4 dimensions:
No grouping
Group by table name
Group by tags
Group by ordinary columns
3. Generate 100 SQL statements using the following syntax combinations:
Tables: system tables, super tables, child tables, normal tables, virtual super tables, virtual child tables
Functions:
Single-row functions (math/string/conversion/time functions)
Aggregate functions
Selection functions
Time-series-specific functions
Geometry functions
System functions
Queries: projection queries, nested queries, join queries, window queries (time/event/count/session/state), SHOW commands, GROUP BY, PARTITION BY, ORDER BY, LIMIT, SLIMIT, UNION, etc.
Filters: time comparisons, ordinary column comparisons, tag column comparisons
Operators: arithmetic, string, bitwise, comparison, logical, JSON operators
Others:
Queries on databases/tables same as/different from the trigger table
View queries
4. Include the following combinations in step 3 query results:
Use all data types: numeric, binary, string, geometry, json, etc.
Use all pseudo-columns: _qstart, _qend, _wstart, _wend, _wduration, _c0, _rowts, irowts, _irowtsorigin, tbname, etc.
Include data columns and tag columns
Randomly include None and NULL in result sets
Result set sizes: 1 row, n rows
Include duplicate timestamp in result sets
5. Test placeholder usage in step 3's queries, including:
Placeholders in various positions like FROM, SELECT, WHERE
Each placeholder: _twstart, _twend, _twduration, _twrownum, _tcurrent_ts, _tgrpid, _tlocaltime, %%n, %%tbname, %%tbrows
6. Validation checks:
Verify table structures and table counts
Validate correctness of calculation results
Validate the accuracy of placeholder data, such as %%trows
¶
Subquery: event
1. Use event trigger mode
2. Output results include 4 dimensions:
No grouping
Group by table name
Group by tags
Group by ordinary columns
3. Generate 100 SQL statements using the following syntax combinations:
Tables: system tables, super tables, child tables, normal tables, virtual super tables, virtual child tables
Functions:
Single-row functions (math/string/conversion/time functions)
Aggregate functions
Selection functions
Time-series-specific functions
Geometry functions
System functions
Queries: projection queries, nested queries, join queries, window queries (time/event/count/session/state), SHOW commands, GROUP BY, PARTITION BY, ORDER BY, LIMIT, SLIMIT, UNION, etc.
Filters: time comparisons, ordinary column comparisons, tag column comparisons
Operators: arithmetic, string, bitwise, comparison, logical, JSON operators
Others:
Queries on databases/tables same as/different from the trigger table
View queries
4. Include the following combinations in step 3 query results:
Use all data types: numeric, binary, string, geometry, json, etc.
Use all pseudo-columns: _qstart, _qend, _wstart, _wend, _wduration, _c0, _rowts, irowts, _irowtsorigin, tbname, etc.
Include data columns and tag columns
Randomly include None and NULL in result sets
Result set sizes: 1 row, n rows
Include duplicate timestamp in result sets
5. Test placeholder usage in step 3's queries, including:
Placeholders in various positions like FROM, SELECT, WHERE
Each placeholder: _twstart, _twend, _twduration, _twrownum, _tcurrent_ts, _tgrpid, _tlocaltime, %%n, %%tbname, %%tbrows
6. Validation checks:
Verify table structures and table counts
Validate correctness of calculation results
Validate the accuracy of placeholder data, such as %%trows
¶
Subquery: stream aggregate derived-table join result correctness
Verifies actual stream output for safe aggregate derived-table INNER JOIN
queries that use _twstart or _twend as the join key, including a
three-way INNER JOIN over aggregate derived tables.
¶
Subquery: session
1. Use session trigger mode
2. Output results include 4 dimensions:
No grouping
Group by table name
Group by tags
Group by ordinary columns
3. Generate 100 SQL statements using the following syntax combinations:
Tables: system tables, super tables, child tables, normal tables, virtual super tables, virtual child tables
Functions:
Single-row functions (math/string/conversion/time functions)
Aggregate functions
Selection functions
Time-series-specific functions
Geometry functions
System functions
Queries: projection queries, nested queries, join queries, window queries (time/event/count/session/state), SHOW commands, GROUP BY, PARTITION BY, ORDER BY, LIMIT, SLIMIT, UNION, etc.
Filters: time comparisons, ordinary column comparisons, tag column comparisons
Operators: arithmetic, string, bitwise, comparison, logical, JSON operators
Others:
Queries on databases/tables same as/different from the trigger table
View queries
4. Include the following combinations in step 3 query results:
Use all data types: numeric, binary, string, geometry, json, etc.
Use all pseudo-columns: _qstart, _qend, _wstart, _wend, _wduration, _c0, _rowts, irowts, _irowtsorigin, tbname, etc.
Include data columns and tag columns
Randomly include None and NULL in result sets
Result set sizes: 1 row, n rows
Include duplicate timestamp in result sets
5. Test placeholder usage in step 3's queries, including:
Placeholders in various positions like FROM, SELECT, WHERE
Each placeholder: _twstart, _twend, _twduration, _twrownum, _tcurrent_ts, _tgrpid, _tlocaltime, %%n, %%tbname, %%tbrows
6. Validation checks:
Verify table structures and table counts
Validate correctness of calculation results
Validate the accuracy of placeholder data, such as %%trows
¶
Subquery: sliding
1. Use sliding trigger mode
2. Output results include 4 dimensions:
No grouping
Group by table name
Group by tags
Group by ordinary columns
3. Generate 100 SQL statements using the following syntax combinations:
Tables: system tables, super tables, child tables, normal tables, virtual super tables, virtual child tables
Functions:
Single-row functions (math/string/conversion/time functions)
Aggregate functions
Selection functions
Time-series-specific functions
Geometry functions
System functions
Queries: projection queries, nested queries, join queries, window queries (time/event/count/session/state), SHOW commands, GROUP BY, PARTITION BY, ORDER BY, LIMIT, SLIMIT, UNION, etc.
Filters: time comparisons, ordinary column comparisons, tag column comparisons
Operators: arithmetic, string, bitwise, comparison, logical, JSON operators
Others:
Queries on databases/tables same as/different from the trigger table
View queries
4. Include the following combinations in step 3 query results:
Use all data types: numeric, binary, string, geometry, json, etc.
Use all pseudo-columns: _qstart, _qend, _wstart, _wend, _wduration, _c0, _rowts, irowts, _irowtsorigin, tbname, etc.
Include data columns and tag columns
Randomly include None and NULL in result sets
Result set sizes: 1 row, n rows
Include duplicate timestamp in result sets
5. Test placeholder usage in step 3's queries, including:
Placeholders in various positions like FROM, SELECT, WHERE
Each placeholder: _twstart, _twend, _twduration, _twrownum, _tcurrent_ts, _tgrpid, _tlocaltime, %%n, %%tbname, %%tbrows
6. Validation checks:
Verify table structures and table counts
Validate correctness of calculation results
Validate the accuracy of placeholder data, such as %%trows
¶
Subquery: state
1. Use state trigger mode
2. Output results include 4 dimensions:
No grouping
Group by table name
Group by tags
Group by ordinary columns
3. Generate 100 SQL statements using the following syntax combinations:
Tables: system tables, super tables, child tables, normal tables, virtual super tables, virtual child tables
Functions:
Single-row functions (math/string/conversion/time functions)
Aggregate functions
Selection functions
Time-series-specific functions
Geometry functions
System functions
Queries: projection queries, nested queries, join queries, window queries (time/event/count/session/state), SHOW commands, GROUP BY, PARTITION BY, ORDER BY, LIMIT, SLIMIT, UNION, etc.
Filters: time comparisons, ordinary column comparisons, tag column comparisons
Operators: arithmetic, string, bitwise, comparison, logical, JSON operators
Others:
Queries on databases/tables same as/different from the trigger table
View queries
4. Include the following combinations in step 3 query results:
Use all data types: numeric, binary, string, geometry, json, etc.
Use all pseudo-columns: _qstart, _qend, _wstart, _wend, _wduration, _c0, _rowts, irowts, _irowtsorigin, tbname, etc.
Include data columns and tag columns
Randomly include None and NULL in result sets
Result set sizes: 1 row, n rows
Include duplicate timestamp in result sets
5. Test placeholder usage in step 3's queries, including:
Placeholders in various positions like FROM, SELECT, WHERE
Each placeholder: _twstart, _twend, _twduration, _twrownum, _tcurrent_ts, _tgrpid, _tlocaltime, %%n, %%tbname, %%tbrows
6. Validation checks:
Verify table structures and table counts
Validate correctness of calculation results
Validate the accuracy of placeholder data, such as %%trows
¶
Subquery: state window aggregate filter
Reproduce bug: outer WHERE on aggregated column in stream subquery
does not take effect. Also covers the empty output block case:
first finalized window is filtered out, and later window should
still be output.
¶
Subquery: state window + join + outer filter should output empty result
Reproduce bug:
stream query outputs rows even when join-side filter removes all rows
(expected empty result).
¶
Subquery: usage restrictions
Verify the usage restrictions of each placeholder:
1. Non-window triggers cannot use _twstart, _twend, _twduration, _twrownum.
2. Non-sliding triggers cannot use _tcurrent_ts, _tprev_ts, _tnext_ts.
3. Only timed triggers can use _tprev_localtime, _tnext_localtime.
4. %%trows can only be used in the FROM clause.
5. Other placeholders can only be used in the SELECT and WHERE clauses.
6. The range of values for n in %%n.
7. Misspelled placeholders.
8. INSERT or other statements that do not return a result set are not allowed.
¶
Subquery: virtual table meta change
test meta change (add/drop/modify) cases to stream for virtual table in subquery