You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "JerryZ521 (via GitHub)" <gi...@apache.org> on 2026/02/14 07:18:39 UTC

[I] SQL dialect translation does not take effect [shardingsphere]

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

   I tried using the MySQL driver to access the backend PostgreSQL database through the proxy, but it was unsuccessful.
   
   version:5.5.2
   global config:
   `mode:
     type: Standalone
     repository:
       type: JDBC
       props:
         provider: H2
         jdbc_url: jdbc:h2:mem:config;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=false;MODE=MYSQL
         username: test
         password: Test@9876
   
   authority:
     users:
       - user: root@%
         password: QFTKyBr9Q4AKnnA
         admin: true
     privilege:
       type: ALL_PERMITTED
   
   props:
     proxy-frontend-database-protocol-type: MySQL
     proxy-frontend-ssl-mode: disabled
     sql-show: true
     sql-simple: false
     sql-federation-enabled: true`
   
   db config:
   `databaseName: vh_pg
   
   dataSources:
     vh_pg:
       url: jdbc:postgresql://10.4.241.41:5432/vh?serverTimezone=Asia/Shanghai&stringtype=unspecified&databaseType=postgresql
       username: admin
       password: 6786868
       connectionTimeoutMilliseconds: 30000
       idleTimeoutMilliseconds: 60000
       maxLifetimeMilliseconds: 1800000
       maxPoolSize: 50
       minPoolSize: 1
   
   rules:
     - !SINGLE
       defaultDataSource: vh_pg
       tables:
         - "*.*"
     - !SQL_TRANSLATOR
       type: NATIVE
       useOriginalSQLWhenTranslatingFailed: true`
   
   
   logs:
   `[INFO ] 2026-02-14 07:15:16.068 [ShardingSphere-Command-0] ShardingSphere-SQL - Logic SQL: /* ApplicationName=DBeaver 25.0.0 - SQLEditor <Script-12.sql> */ SELECT
           count(1)
   FROM
       a_task
   LIMIT 0, 200
   [INFO ] 2026-02-14 07:15:16.068 [ShardingSphere-Command-0] ShardingSphere-SQL - Actual SQL: vh_pg ::: /* ApplicationName=DBeaver 25.0.0 - SQLEditor <Script-12.sql> */ SELECT
           count(1)
   FROM
       a_task
   LIMIT 0, 200
   [ERROR] 2026-02-14 07:15:16.083 [ShardingSphere-Command-0] o.a.s.p.f.c.CommandExecutorTask - Exception occur:
   org.postgresql.util.PSQLException: ERROR: LIMIT #,# syntax is not supported
     Hint: Use separate LIMIT and OFFSET clauses.
     Position: 110
   `
   
   a_task table:
   `CREATE TABLE a_task (
       id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
       t_info JSONB NOT NULL
   );`
   


-- 
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] SQL dialect translation does not take effect [shardingsphere]

Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu closed issue #38043: SQL dialect translation does not take effect
URL: https://github.com/apache/shardingsphere/issues/38043


-- 
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


Re: [I] SQL dialect translation does not take effect [shardingsphere]

Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu commented on issue #38043:
URL: https://github.com/apache/shardingsphere/issues/38043#issuecomment-3901683226

   Problem Understanding
   
     - Target issue: https://github.com/apache/shardingsphere/issues/38043 (opened on 2026-02-14).
     - Reported setup from issue body: ShardingSphere-Proxy 5.5.2, proxy-frontend-database-protocol-type: MySQL, backend PostgreSQL datasource, !SQL_TRANSLATOR type: NATIVE.
     - Reported symptom: SQL LIMIT 0, 200 is sent unchanged to PostgreSQL and fails with ERROR: LIMIT #,# syntax is not supported.
     - Topology (from provided config): Proxy + Standalone, mode repository JDBC(H2), registry/config center N/A.
     - Fast triage gate:
   
     1. Repro evidence present? Partially yes (version/mode/SQL/config/logs present), but DB server version missing.
     2. Expected behavior documented? Yes, but conflicting docs exist.
     3. Code/tests show mismatch vs expectation? Yes (provisional, see INF-1/INF-2).
   
     - Provisional type: Needs More Info (with provisional Documentation Gap).
   
     Root Cause
   
     - Definitive root-cause closure is blocked by missing minimum evidence (DB server version and runtime rule/plugin confirmation).
     - Provisional root cause:
     - INF-1 (from OBS-1, OBS-5, OBS-6, OBS-7, OBS-8): the configured NATIVE translator currently behaves as a no-op in repository code, so MySQL pagination syntax can reach PostgreSQL unchanged.
     - INF-2 (from OBS-3, OBS-4, OBS-5, OBS-6): official docs are internally inconsistent (gateway capability claim vs native translator “not implemented”), indicating a documentation boundary gap.
   
     Problem Analysis
   
     - OBS-1: Issue body includes full config and log showing unchanged actual SQL and PostgreSQL syntax error.
       Source: https://github.com/apache/shardingsphere/issues/38043
     - OBS-2: No comments or linked PR activity yet.
       Source: https://api.github.com/repos/apache/shardingsphere/issues/38043/comments, https://api.github.com/repos/apache/shardingsphere/issues/38043/timeline
     - OBS-3: Gateway reference claims automatic dialect conversion (MySQL protocol/dialect to PostgreSQL storage).
       Source: https://github.com/apache/shardingsphere/blob/master/docs/document/content/reference/gateway/_index.en.md
     - OBS-4: DB-gateway limitations page says translation is experimental and MySQL/PostgreSQL can be auto translated.
       Source: https://github.com/apache/shardingsphere/blob/master/docs/document/content/features/db-gateway/limitations.en.md
     - OBS-5: Built-in SQL translator doc states Native translator “does not implement yet.”
       Source: https://github.com/apache/shardingsphere/blob/master/docs/document/content/user-manual/common-config/builtin-algorithm/sql-translator.en.md
     - OBS-6: NativeSQLTranslator contains // TODO and returns original SQL/params.
       Source: kernel/sql-translator/provider/native/src/main/java/org/apache/shardingsphere/sqltranslator/natived/NativeSQLTranslator.java:35
     - OBS-7: Unit test expects NATIVE translation result to equal input SQL.
       Source: kernel/sql-translator/provider/native/src/test/java/org/apache/shardingsphere/sqltranslator/natived/NativeSQLTranslatorTest.java:38
     - OBS-8: Rewrite engine applies translator result if present, otherwise keeps original SQL.
       Source: infra/rewrite/core/src/main/java/org/apache/shardingsphere/infra/rewrite/engine/RouteSQLRewriteEngine.java:208
     - OBS-9: SQLTranslatorRule skips when no translator and only attempts translation for parser/storage type mismatch.
       Source: kernel/sql-translator/core/src/main/java/org/apache/shardingsphere/sqltranslator/rule/SQLTranslatorRule.java:67
     - Execution record (analysis-only, all exit code 0): curl issue/comments/timeline APIs, rg doc/code scans, nl -ba ... | sed -n ... file inspections.
     - Repository edits: none.
   
     Problem Conclusion
   
     - Current classification: Needs More Info (INF-1, INF-2).
     - Consolidated missing evidence to request:
   
     1. PostgreSQL server version.
     2. Output of SHOW SQL_TRANSLATOR RULE; from this proxy instance.
     3. Proxy runtime package details (whether shardingsphere-sql-translator-native-provider jar is present in runtime libs).
     4. Startup log snippet around loading global rules/SPI for SQL translator.


-- 
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


Re: [I] SQL dialect translation does not take effect [shardingsphere]

Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #38043:
URL: https://github.com/apache/shardingsphere/issues/38043#issuecomment-3947865660

   There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.


-- 
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


Re: [I] SQL dialect translation does not take effect [shardingsphere]

Posted by "JerryZ521 (via GitHub)" <gi...@apache.org>.
JerryZ521 commented on issue #38043:
URL: https://github.com/apache/shardingsphere/issues/38043#issuecomment-3988215764

   > Problem Understanding
   > 
   > * Target issue: [SQL dialect translation does not take effect #38043](https://github.com/apache/shardingsphere/issues/38043) (opened on 2026-02-14).
   > * Reported setup from issue body: ShardingSphere-Proxy 5.5.2, proxy-frontend-database-protocol-type: MySQL, backend PostgreSQL datasource, !SQL_TRANSLATOR type: NATIVE.
   > * Reported symptom: SQL LIMIT 0, 200 is sent unchanged to PostgreSQL and fails with ERROR: LIMIT #,# syntax is not supported.
   > * Topology (from provided config): Proxy + Standalone, mode repository JDBC(H2), registry/config center N/A.
   > * Fast triage gate:
   > 
   > 1. Repro evidence present? Partially yes (version/mode/SQL/config/logs present), but DB server version missing.
   > 2. Expected behavior documented? Yes, but conflicting docs exist.
   > 3. Code/tests show mismatch vs expectation? Yes (provisional, see INF-1/INF-2).
   > 
   > * Provisional type: Needs More Info (with provisional Documentation Gap).
   > 
   > Root Cause
   > 
   > * Definitive root-cause closure is blocked by missing minimum evidence (DB server version and runtime rule/plugin confirmation).
   > * Provisional root cause:
   > * INF-1 (from OBS-1, OBS-5, OBS-6, OBS-7, OBS-8): the configured NATIVE translator currently behaves as a no-op in repository code, so MySQL pagination syntax can reach PostgreSQL unchanged.
   > * INF-2 (from OBS-3, OBS-4, OBS-5, OBS-6): official docs are internally inconsistent (gateway capability claim vs native translator “not implemented”), indicating a documentation boundary gap.
   > 
   > Problem Analysis
   > 
   > * OBS-1: Issue body includes full config and log showing unchanged actual SQL and PostgreSQL syntax error.
   >   Source: [SQL dialect translation does not take effect #38043](https://github.com/apache/shardingsphere/issues/38043)
   > * OBS-2: No comments or linked PR activity yet.
   >   Source: https://api.github.com/repos/apache/shardingsphere/issues/38043/comments, https://api.github.com/repos/apache/shardingsphere/issues/38043/timeline
   > * OBS-3: Gateway reference claims automatic dialect conversion (MySQL protocol/dialect to PostgreSQL storage).
   >   Source: https://github.com/apache/shardingsphere/blob/master/docs/document/content/reference/gateway/_index.en.md
   > * OBS-4: DB-gateway limitations page says translation is experimental and MySQL/PostgreSQL can be auto translated.
   >   Source: https://github.com/apache/shardingsphere/blob/master/docs/document/content/features/db-gateway/limitations.en.md
   > * OBS-5: Built-in SQL translator doc states Native translator “does not implement yet.”
   >   Source: https://github.com/apache/shardingsphere/blob/master/docs/document/content/user-manual/common-config/builtin-algorithm/sql-translator.en.md
   > * OBS-6: NativeSQLTranslator contains // TODO and returns original SQL/params.
   >   Source: kernel/sql-translator/provider/native/src/main/java/org/apache/shardingsphere/sqltranslator/natived/NativeSQLTranslator.java:35
   > * OBS-7: Unit test expects NATIVE translation result to equal input SQL.
   >   Source: kernel/sql-translator/provider/native/src/test/java/org/apache/shardingsphere/sqltranslator/natived/NativeSQLTranslatorTest.java:38
   > * OBS-8: Rewrite engine applies translator result if present, otherwise keeps original SQL.
   >   Source: infra/rewrite/core/src/main/java/org/apache/shardingsphere/infra/rewrite/engine/RouteSQLRewriteEngine.java:208
   > * OBS-9: SQLTranslatorRule skips when no translator and only attempts translation for parser/storage type mismatch.
   >   Source: kernel/sql-translator/core/src/main/java/org/apache/shardingsphere/sqltranslator/rule/SQLTranslatorRule.java:67
   > * Execution record (analysis-only, all exit code 0): curl issue/comments/timeline APIs, rg doc/code scans, nl -ba ... | sed -n ... file inspections.
   > * Repository edits: none.
   > 
   > Problem Conclusion
   > 
   > * Current classification: Needs More Info (INF-1, INF-2).
   > * Consolidated missing evidence to request:
   > 
   > 1. PostgreSQL server version.
   > 2. Output of SHOW SQL_TRANSLATOR RULE; from this proxy instance.
   > 3. Proxy runtime package details (whether shardingsphere-sql-translator-native-provider jar is present in runtime libs).
   > 4. Startup log snippet around loading global rules/SPI for SQL translator.
   
   > 1. PostgreSQL server version:16
   > 3. Output of SHOW SQL_TRANSLATOR RULE; from this proxy instance.
   
   <img width="531" height="123" alt="Image" src="https://github.com/user-attachments/assets/923bac4a-7085-4699-b989-f860755cadaa" />
   
   > 5. Proxy runtime package details (whether shardingsphere-sql-translator-native-provider jar is present in runtime libs).> 
   
   <img width="714" height="225" alt="Image" src="https://github.com/user-attachments/assets/c03dbb3b-6132-4c4e-8b5b-1a4211d059cb" />
   
   > 7. Startup log snippet around loading global rules/SPI for SQL translator.
   <img width="1308" height="138" alt="Image" src="https://github.com/user-attachments/assets/e32a99cb-a82d-41b7-bffe-709f3e8ae109" />


-- 
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