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