JackieTien97 opened a new pull request, #18857:
URL: https://github.com/apache/iotdb/pull/18857

   ## Description
   
   In the table model, an explicit `LIMIT 0` can be pushed into a scan and then 
eliminated because the scan also uses zero to mean "unlimited". As a result, 
queries such as `SELECT * FROM t LIMIT 0` can return rows.
   
   This PR makes an explicit `LIMIT 0` return zero rows while preserving result 
column names and types, aligning its behavior with MySQL and PostgreSQL.
   
   ### Behavior change
   
   | Query | Result after this change |
   | --- | --- |
   | `SELECT * FROM t LIMIT 0` | An empty result with the original column 
metadata |
   | `SELECT count(*) FROM t LIMIT 0` | Zero rows |
   | `SELECT count(*) FROM (SELECT * FROM t LIMIT 0) q` | One row containing 
`0` |
   | `SELECT (SELECT v FROM t LIMIT 0) AS value` | One row containing `NULL` |
   
   Applications that used `LIMIT 0` to request an unlimited result should omit 
`LIMIT` or use `LIMIT ALL`. Positive limits and offsets retain their existing 
behavior. This change is limited to the table model; tree-model `LIMIT` 
validation is unchanged.
   
   ### Implementation
   
   - Replace zero-count limit nodes with empty `ValuesNode` relations before 
scan pushdown, TopK planning, and subquery rewrites. Preserve output symbols 
and expose the values node's exact cardinality to existing optimizations.
   - Handle the resulting empty inputs throughout the plan: avoid stream-sort 
conversion for values sources, and retain outer/ASOF join keys instead of 
selecting an unsupported nested-loop join.
   - Make empty scalar subqueries produce one typed NULL row, while continuing 
to reject scalar subqueries that return multiple rows. Include the NULL row in 
memory estimates and localize the multiple-row error message.
   - Correct `ValuesNode` serialization so the buffer and stream encodings 
agree, and deserialize the column count and row count using their encoded 
integer widths.
   
   ### Test coverage
   
   The new `IoTDBLimitZeroIT` covers scans and expressions; tag/field filters; 
zero, positive, and maximum offsets; sorting and TopK; global/grouped/distinct 
aggregation and HAVING; window functions, fill, and gap fill; nested limits; 
table views; UNION/INTERSECT/EXCEPT with DISTINCT/ALL semantics; inner, cross, 
outer, and ASOF joins; scalar, EXISTS, IN, and quantified subqueries, including 
correlated cases; inline/materialized CTEs; queries without FROM; information 
schema and SHOW DEVICES; JDBC column metadata; and INSERT ... SELECT.
   
   Controls also check omitted limits, `LIMIT ALL`, positive limits, invalid 
columns/tables, negative limits, and multiple-row scalar subquery errors.
   
   Unit coverage includes zero-limit logical/distributed plans, existing 
limit/offset and set-operation optimizations, scalar-operator behavior for 
empty/single/multiple rows and empty batches, and values serialization through 
both output APIs and a read-only input buffer.
   
   ### Validation
   
   All checks passed with JDK 17:
   
   | Validation | Result |
   | --- | --- |
   | Focused unit tests, English and Chinese builds | 29 tests passed in each 
build |
   | `IoTDBLimitZeroIT`, `TableSimpleIT`, English, 1 ConfigNode + 1 DataNode | 
15 test methods passed |
   | `IoTDBLimitZeroIT`, `TableClusterIT` + `with-zh-locale`, 1 ConfigNode + 3 
DataNodes | 16 test methods passed, including the additional ASOF case |
   | Full-reactor `mvn test-compile -DskipTests` | Passed |
   | Full-reactor `mvn test-compile -DskipTests -P with-zh-locale` | Passed |
   
   The final Chinese cluster validation used a clean reactor `verify` build 
with `-pl integration-test -am -P 
TableClusterIT,with-integration-tests,with-zh-locale`, selecting only the 
focused unit tests and `-Dit.test=IoTDBLimitZeroIT`. Formatting, Checkstyle, 
and license checks passed.
   
   <hr>
   
   This PR has:
   
   - [x] been self-reviewed.
   - [x] added comments explaining the intent of non-obvious changes.
   - [x] added unit tests and updated existing planner tests.
   - [x] added integration tests.
   - [x] been tested in single-DataNode and three-DataNode IoTDB clusters.
   
   ### Key changed/added classes
   
   `RemoveZeroLimit`, `LogicalOptimizeFactory`, `QueryCardinalityUtil`, 
`TransformSortToStreamSort`, `PushPredicateIntoTableScan`, 
`EnforceSingleRowOperator`, `TableOperatorGenerator`, `ValuesNode`, and 
`IoTDBLimitZeroIT`.
   
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: [email protected]

For queries about this service, please contact Infrastructure at:
[email protected]

Reply via email to