SQL dialect translation does not take effect

[JerryZ521]

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

[terrymanu]

Problem Understanding

• Target issue: SQL dialect translation does not take effect #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
• 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.