[I] Reject SELECT queries routed across different storage database types in the pushdown engine [shardingsphere]

["RaigorJiang (via GitHub)" <gi...@apache.org>]

RaigorJiang opened a new issue, #40368:
URL: https://github.com/apache/shardingsphere/issues/40368

   ## Feature Request
   
   ### Is your feature request related to a problem?
   
   Yes. A logical database can contain heterogeneous storage units, but ordinary pushdown execution does not provide a general compatibility contract for combining results from different storage database types.
   
   Successful SQL translation and backend execution do not establish consistent result semantics. The result merger also needs compatible value domains, precision, comparison and equality rules, result metadata, and backend partial computations.
   
   Review and verification of [PR #40322](https://github.com/apache/shardingsphere/pull/40322), which preserves ordinary MySQL TIME precision, exposed this gap. TIME provides a concrete reproduction, but the concern extends beyond temporal Java classes.
   
   #### Observed TIME merge failures
   
   Mixed-storage verification was performed against the candidate branch for #40322 using:
   
   - ShardingSphere-Proxy in Standalone mode with the MySQL frontend protocol.
   - SQL Federation disabled.
   - MySQL 8.0.37 with Connector/J 8.4.0.
   - PostgreSQL 16.3 with PgJDBC 42.7.13.
   - One logical table sharded across both storage types.
   - Text queries and server-side prepared statements.
   
   For two rows on different storage types with the identical TIME value `12:34:56`:
   
   - `ORDER BY`, `MIN`, and `MAX` could fail with `ClassCastException`, including `java.time.LocalTime cannot be cast to java.util.Date`.
   - An in-memory `GROUP BY` could produce two groups with a count of one each, instead of one group with a count of two.
   
   For example:
   
   ```sql
   -- sample = 1 selects one row from each storage type.
   -- Both rows have v6 = '12:34:56'.
   
   SELECT v6, COUNT(*) AS n
   FROM t_time
   WHERE sample = 1
   GROUP BY v6
   ORDER BY n DESC;
   ```
   
   Equivalent homogeneous MySQL-only and PostgreSQL-only controls completed the relevant sorting, grouping, and MIN/MAX operations correctly.
   
   These observations describe the candidate implementation in #40322, rather than a reproduction against every released version.
   
   #### Why not use LocalTime for both databases?
   
   Using `LocalTime` for both databases is a valid approach for ordinary within-day TIME values, provided both retrieval paths preserve precision.
   
   In the native driver verification, typed `getObject(columnIndex, LocalTime.class)` reads retained matching full-precision values across 24 tested ordinary TIME sample/column combinations, including NULL. This establishes that common representation is feasible for the tested subset; it does not constitute verification of an implemented production merger fix.
   
   There are two separate responsibilities:
   
   1. **Preserve information during retrieval.** Converting an already truncated `Time` value to `LocalTime` cannot recover lost microseconds. Both databases would need precise retrieval before normalization.
   2. **Define compatible semantics throughout execution.** Reading both values as the same class must also preserve equality, ordering, grouping, metadata, JDBC getter behavior, and protocol output.
   
   A general TIME policy also needs to account for values outside the ordinary within-day subset. PostgreSQL accepts `24:00:00`, and PgJDBC 42.7.13 maps that endpoint to `LocalTime.MAX`. MySQL TIME additionally represents negative and greater-than-24-hour durations, which `LocalTime` cannot faithfully represent. These cases require explicit compatibility decisions; they are not requests to expand #40322.
   
   References: [PostgreSQL 16 temporal types](https://www.postgresql.org/docs/16/datatype-datetime.html), [PgJDBC 42.7.13 TIME conversion](https://github.com/pgjdbc/pgjdbc/blob/REL42.7.13/pgjdbc/src/main/java/org/postgresql/jdbc/TimestampUtils.java#L489), and [MySQL 8.0 TIME semantics](https://dev.mysql.com/doc/refman/8.0/en/time.html).
   
   Therefore, this proposal does not claim that heterogeneous TIME support is technically impossible. It proposes an explicit execution boundary while a broader compatibility contract remains undefined.
   
   #### Evidence beyond TIME
   
   The following examples distinguish existing representation differences from documented semantic risks. They are not presented as additional completed end-to-end Proxy reproductions.
   
   **1. SMALLINT: an existing difference in the memory-loading path**
   
   Before #40322, `MySQLResultSetMapper#getSmallintValue` already returned `getInt()`, producing `Integer`, while `PostgreSQLResultSetMapper#getSmallintValue` returned `getShort()`, producing `Short`.
   
   The merger's direct `Comparable.compareTo` calls and Java object equality do not establish numeric compatibility between these classes. Equal SQL numbers can therefore reach comparison or grouping as incompatible Java values.
   
   Promoting both to a common numeric representation is possible, but requires a defined comparison and equality policy. Changing TIME retrieval does not address this existing difference.
   
   Sources: [MySQL mapper before #40322](https://github.com/apache/shardingsphere/blob/50cd58771c484f5f6b281a349aff9c3f7c27ffbb/database/connector/dialect/mysql/src/main/java/org/apache/shardingsphere/database/connector/mysql/resultset/MySQLResultSetMapper.java#L33) and [PostgreSQL mapper before #40322](https://github.com/apache/shardingsphere/blob/50cd58771c484f5f6b281a349aff9c3f7c27ffbb/database/connector/dialect/postgresql/src/main/java/org/apache/shardingsphere/database/connector/postgresql/resultset/PostgreSQLResultSetMapper.java#L31).
   
   **2. Integer signedness: compatible objects also need compatible ranges and metadata**
   
   MySQL `BIGINT UNSIGNED` supports values through `2^64 - 1`, while PostgreSQL `bigint` is signed and ends at `2^63 - 1`. Connector/J represents unsigned BIGINT values as `BigInteger`.
   
   A common exact numeric representation can be designed. Supporting such logical-column mappings also requires an explicit promotion policy and compatible result metadata; converting everything to `Long` would lose part of the supported domain.
   
   References: [MySQL integer ranges](https://dev.mysql.com/doc/refman/8.0/en/integer-types.html) and [PostgreSQL 16 numeric types](https://www.postgresql.org/docs/16/datatype-numeric.html).
   
   **3. DATETIME/TIMESTAMP: representation depends on the retrieval path**
   
   With the tested Connector/J defaults, native `getObject()` returns `LocalDateTime` for MySQL DATETIME, while PgJDBC returns `Timestamp` for PostgreSQL timestamp.
   
   The memory loader already normalizes JDBC TIMESTAMP through `getTimestamp()`, whereas an object-reading streaming path can retain native representations. Compatibility must therefore be assessed across both loading paths.
   
   A complete temporal policy must also distinguish local date/time values from instants requiring time-zone interpretation. Merely choosing one Java class does not define that distinction.
   
   References: [Connector/J 8.4.0 object mapping](https://github.com/mysql/mysql-connector-j/blob/8.4.0/src/main/user-impl/java/com/mysql/cj/jdbc/result/ResultSetImpl.java#L1208), [PgJDBC 42.7.13 result-set implementation](https://github.com/pgjdbc/pgjdbc/blob/REL42.7.13/pgjdbc/src/main/java/org/postgresql/jdbc/PgResultSet.java), and [PostgreSQL timestamp semantics](https://www.postgresql.org/docs/16/datatype-datetime.html#DATATYPE-DATETIME-INPUT-TIMESTAMPS).
   
   **4. VARCHAR/TEXT: the same Java class can still have different SQL semantics**
   
   Both databases can return `String`, yet their configured collations can define different equality and ordering.
   
   For example, MySQL `utf8mb4_unicode_ci` can consider `Bar` and `Bär` equal, while PostgreSQL `COLLATE "C"` distinguishes their bytes.
   
   If a backend has already combined these values during a pushed-down `GROUP BY`, changing their Java representation in the merger cannot reconstruct the original groups. Similarly, streaming ordered merging requires backend ordering compatible with the merge comparator.
   
   This is a documented semantic risk, not a new Proxy reproduction from the TIME investigation.
   
   References: [MySQL collation examples](https://dev.mysql.com/doc/refman/8.0/en/charset-collation-effect.html) and [PostgreSQL 16 collation behavior](https://www.postgresql.org/docs/16/collation.html).
   
   Together, these examples show that a complete solution involves logical type mapping and compatible execution semantics, including operations already performed by the backends. A collection of Java casts is insufficient.
   
   ### Describe the feature you would like.
   
   Introduce the following restriction for ordinary pushdown SELECT execution:
   
   > Reject a SELECT before executing its backend business SQL when its actual execution units span more than one storage database type.
   
   Apply the same rule to ShardingSphere-Proxy and ShardingSphere-JDBC.
   
   #### Proposed boundary
   
   | Scenario | Proposed behavior |
   | --- | --- |
   | Multiple storage units of the same database type | Allow |
   | A heterogeneous logical database, with this SELECT routed only to MySQL | Allow |
   | A heterogeneous logical database, with this SELECT routed only to PostgreSQL | Allow |
   | A SELECT routed across MySQL and PostgreSQL through ordinary pushdown execution | Reject explicitly |
   | A SELECT actually handled by SQL Federation | Follow SQL Federation's own capabilities and validation |
   
   Use the actual execution units and their storage database types from existing metadata. Do not determine compatibility from the frontend protocol or reject heterogeneous storage registration.
   
   For the initial implementation, apply this rule to all cross-type pushdown SELECT statements, including simple queries that may currently succeed. This is a conservative support-boundary choice, not a claim that every heterogeneous query produces incorrect results.
   
   #### SQL Federation handling
   
   Bypass this pushdown restriction only when SQL Federation actually handles the query.
   
   Enabling Federation alone is insufficient: a query that falls back to ordinary pushdown must still be validated. The validation must also avoid interfering with Federation's internal table scans.
   
   This proposal does not establish additional heterogeneous-query support in SQL Federation.
   


-- 
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: notifications-unsubscribe@shardingsphere.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [I] Reject SELECT queries routed across different storage database types in the pushdown engine [shardingsphere]

["RaigorJiang (via GitHub)" <gi...@apache.org>]

RaigorJiang closed issue #40368: Reject SELECT queries routed across different storage database types in the pushdown engine
URL: https://github.com/apache/shardingsphere/issues/40368


-- 
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: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org