You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2022/05/31 06:30:37 UTC
[GitHub] [shardingsphere] LGHome opened a new issue, #18090: Cross schema query sql with 'order by' Error
LGHome opened a new issue, #18090:
URL: https://github.com/apache/shardingsphere/issues/18090
VERSION：shardingsphere-jdbc-core-spring-boot-starter 5.0.0
Question:
1. i want to excute a cross schema query sql, but it went wrong,
so i set the 'sql-federation-enabled' to 'true' ,the sql can normal excute.
2.but when i want to excute the across schema query sql with 'order by' ,It will cause errors.
if remove the 'order by' it will normal excute.
How do I solve this problem?
My configuration is as follows:
sql：
`SELECT
hf.file_id,
iu.b,
iu.c
FROM
hfle_file hf
LEFT JOIN hpfm_tenant_tl ht ON hf.tenant_id = ht.tenant_id
AND ht.lang = ?
LEFT JOIN iam_user iu ON hf.created_by = iu.id
WHERE
1 = 1
AND hf.tenant_id = ?
ORDER BY
hf.file_id DESC
LIMIT 10`
Remarks：table 'hfle_file' in 'filedb' schema，table 'iam_user ','hpfm_tenant_tl ' in another schema。
--
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
[GitHub] [shardingsphere] LGHome commented on issue #18090: Cross schema query sql with 'order by' Error
Posted by GitBox <gi...@apache.org>.
LGHome commented on issue #18090:
URL: https://github.com/apache/shardingsphere/issues/18090#issuecomment-1144629335
the reason is version 5.0.0 sharding-sphere-jdbc not auto load dependency ‘federation-executor' and 'federation-optimizer'
if you upgrade to 5.1.0^ or Add these dependency,
and open the federation-enable props in application.yml.
this problem will be solved
--
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
[GitHub] [shardingsphere] 735100049 commented on issue #18090: Cross schema query sql with 'order by' Error
Posted by GitBox <gi...@apache.org>.
735100049 commented on issue #18090:
URL: https://github.com/apache/shardingsphere/issues/18090#issuecomment-1148425430
>
My version is 5.1.1 sharding-sphere-jdbc，and the federation-enable props is opend，this problem is still not be solved.
--
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
[GitHub] [shardingsphere] 735100049 commented on issue #18090: Cross schema query sql with 'order by' Error
Posted by GitBox <gi...@apache.org>.
735100049 commented on issue #18090:
URL: https://github.com/apache/shardingsphere/issues/18090#issuecomment-1144397099
I have the same problem. Do you have any solutions
[ERROR] 2022-06-02 11:01:52.410 [Connection-1-ThreadExecutor] o.a.s.p.f.c.CommandExecutorTask - Exception occur:
java.sql.SQLException: Error while preparing statement [SELECT
*
FROM
t_order o
LEFT JOIN t_user u ON o.user_id = u.user_id
WHERE
o.user_id != 1
ORDER BY
o.user_id DESC
LIMIT 2]
at org.apache.calcite.avatica.Helper.createException(Helper.java:56)
at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
at org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement_(CalciteConnectionImpl.java:239)
at org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement(CalciteConnectionImpl.java:218)
at org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement(CalciteConnectionImpl.java:101)
at org.apache.calcite.avatica.AvaticaConnection.prepareStatement(AvaticaConnection.java:175)
at org.apache.shardingsphere.infra.federation.executor.original.OriginalFilterableExecutor.executeQuery(OriginalFilterableExecutor.java:79)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.doExecuteFederation(JDBCDatabaseCommunicationEngine.java:155)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.execute(JDBCDatabaseCommunicationEngine.java:131)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.execute(JDBCDatabaseCommunicationEngine.java:73)
at org.apache.shardingsphere.proxy.backend.text.data.impl.SchemaAssignedDatabaseBackendHandler.execute(SchemaAssignedDatabaseBackendHandler.java:56)
at org.apache.shardingsphere.proxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutor.execute(MySQLComQueryPacketExecutor.java:97)
at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:100)
at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:72)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
at java.base/java.lang.Thread.run(Thread.java:833)
Caused by: java.lang.RuntimeException: Error while applying rule EnumerableMergeJoinRule(in:NONE,out:ENUMERABLE), args [rel#92:LogicalJoin.NONE.[](left=RelSubset#119,right=RelSubset#20,condition==($1, $2),joinType=left)]
--
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
[GitHub] [shardingsphere] terrymanu commented on issue #18090: Cross schema query sql with 'order by' Error
Posted by GitBox <gi...@apache.org>.
terrymanu commented on issue #18090:
URL: https://github.com/apache/shardingsphere/issues/18090#issuecomment-1164434028
@735100049 Can you provide your configuration?
--
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
[GitHub] [shardingsphere] LGHome closed issue #18090: Cross schema query sql with 'order by' Error
Posted by GitBox <gi...@apache.org>.
LGHome closed issue #18090: Cross schema query sql with 'order by' Error
URL: https://github.com/apache/shardingsphere/issues/18090
--
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
[GitHub] [shardingsphere] LGHome commented on issue #18090: Cross schema query sql with 'order by' Error
Posted by GitBox <gi...@apache.org>.
LGHome commented on issue #18090:
URL: https://github.com/apache/shardingsphere/issues/18090#issuecomment-1174491149
configuration:
spring:
shardingsphere:
datasource:
#数据源名称
names: filedb,platformdb
#数据源信息
filedb:
type: com.zaxxer.hikari.HikariDataSource
driverClassName: com.mysql.cj.jdbc.Driver
jdbc-url: ${SPRING_DATASOURCE_URL:jdbc:mysql://xxx.xxx.xxx:3306/xxx_db?useUnicode=true&characterEncoding=utf-8&useSSL=false}
username: ${SPRING_DATASOURCE_USERNAME:xxxx}
password: ${SPRING_DATASOURCE_PASSWORD:xxxx}
#数据库连接池的其它属性
# 连接池最小空闲连接数
minimum-idle: ${SPRING_DATASOURCE_MINIMUM_IDLE:20}
# 连接池允许的最大连接数
maximum-pool-size: ${SPRING_DATASOURCE_MAXIMUM_POOL_SIZE:200}
# 等待连接池分配连接的最大时长（毫秒）
connection-timeout: ${SPRING_DATASOURCE_CONNECTION_TIMEOUT:30000}
#数据源信息
platform:
type: com.zaxxer.hikari.HikariDataSource
driverClassName: com.mysql.cj.jdbc.Driver
jdbc-url: ${SPRING_DATASOURCE_URL_PLATFORM:jdbc:mysql://xxxx.xxxx.xxxx:3306/xxxx_xxx_db?useUnicode=true&characterEncoding=utf-8&useSSL=false}
username: ${SPRING_DATASOURCE_USERNAME:xxx}
password: ${SPRING_DATASOURCE_PASSWORD:xxx}
#数据库连接池的其它属性
# 连接池最小空闲连接数
minimum-idle: ${SPRING_DATASOURCE_MINIMUM_IDLE:20}
# 连接池允许的最大连接数
maximum-pool-size: ${SPRING_DATASOURCE_MAXIMUM_POOL_SIZE:200}
# 等待连接池分配连接的最大时长（毫秒）
connection-timeout: ${SPRING_DATASOURCE_CONNECTION_TIMEOUT:30000}
rules:
sharding:
tables:
xxxx_xxxx:
actual-data-nodes: filedb.xxxx_xxxx$->{0..63}
table-strategy:
hint:
sharding-algorithm-name: hfleFileHintShardingAlgorithm
props:
sql-show: true
#支持跨库join查询
sql-federation-enabled: true
--
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
[GitHub] [shardingsphere] LGHome commented on issue #18090: Cross schema query sql with 'order by' Error
Posted by GitBox <gi...@apache.org>.
LGHome commented on issue #18090:
URL: https://github.com/apache/shardingsphere/issues/18090#issuecomment-1174511247
solutions
1. 5.0.0 version not auto dependency federation jar
2. open federation config
--
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