apache / apache/shardingsphere

SQL federation does not support SQL exception occurs when I execute sql with bigint column

Open
#33,978 2 comments 0 reactions 1 assignee Claimed by @strongduanmu View on GitHub
feature: SQL federation type: bug
Dominant language
Java
Stars
20.8k
Forks
6.9k
Avg merge
11h 35m
Merged PRs (30d)
326

Description

### Which version of ShardingSphere did you use?
5.5.1

### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-Proxy

### Expected behavior
Query returns accurate results.

### Actual behavior
ERROR 20101 (42000): SQL federation does not support SQL 'select u.id, u.username, i.invite_code from user as u inner join invitation as i on u.id = i.user_id where u.id = 1072863298099085312'.
More details: org.apache.calcite.runtime.CalciteContextException: At line 0, column 0: Cannot apply '=' to arguments of type ' = '. Supported form(s): ' = '

### Reason analyze (If you can)

### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

This is my table creation statement.

``` sql
CREATE TABLE `user` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '用户ID,主键',
`tenant_id` bigint unsigned NOT NULL DEFAULT 0 COMMENT '租户id',
`username` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户名',
`password` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '密码',
`user_type` varchar(3) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '100' COMMENT '用户类型:100=系统用户',
`nickname` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '用户昵称',
`phone` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '手机',
`country_code` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '国际区号',
`email` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '用户邮箱',
`avatar` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '用户头像',
`status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '状态:1=正常,2=停用',
`remark` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '备注',
`created_at` int NOT NULL DEFAULT 0 COMMENT '创建时间',
`updated_at` int NOT NULL DEFAULT 0 COMMENT '更新时间',
`deleted_at` int NOT NULL DEFAULT 0 COMMENT '删除时间',
PRIMARY KEY (`id`),
KEY `idx_tenant_id` (`tenant_id`),
UNIQUE KEY `user_username_unique` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户信息表';

CREATE TABLE `invitation` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`tenant_id` bigint unsigned NOT NULL DEFAULT 0 COMMENT '租户id',
`user_id` bigint unsigned NOT NULL DEFAULT 0 COMMENT '用户id',
`invite_code` varchar(20) NOT NULL DEFAULT '' COMMENT '邀请码',
`created_at` int NOT NULL DEFAULT 0 COMMENT '创建时间',
`updated_at` int NOT NULL DEFAULT 0 COMMENT '更新时间',
`deleted_at` int NOT NULL DEFAULT 0 COMMENT '删除时间',
PRIMARY KEY (`id`),
UNIQUE KEY `user_invite_code_unique` (`tenant_id`, `user_id`, `invite_code`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='邀请系统表';

```

This is my shard configuration.

database-test.yaml

``` yaml
databaseName: test

dataSources:
ds_0:
url: jdbc:mysql://127.0.0.1:3306/test?useSSL=false&allowPublicKeyRetrieval=true
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
ds_1:
url: jdbc:mysql://127.0.0.1:3307/test?useSSL=false&allowPublicKeyRetrieval=true
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1

rules:
- !SINGLE
tables:
- "*.*"# 默认数据源,仅在执行 CREATE TABLE 创建单表时有效。缺失值为空,表示随机单播路由。
- !SHARDING
autoTables:
# 分片逻辑表
user:
# 指定数据库
actualDataSources: ds_${0..1}
# 分片策略
shardingStrategy:
standard:
# 分片字段
shardingColumn: tenant_id
# 策略名称,对应下方的策略
shardingAlgorithmName: user_mod
# 主键生成策略
keyGenerateStrategy:
# 主键字段
column: id
# 主键生成策略名称,对应下方主键策略
keyGeneratorName: snowflake
# 分片逻辑表
invitation:
# 指定数据库
actualDataSources: ds_${0..1}
# 分片策略
shardingStrategy:
standard:
# 分片字段
shardingColumn: tenant_id
# 策略名称,对应下方的策略
shardingAlgorithmName: invitation_mod
# 主键生成策略
keyGenerateStrategy:
# 主键字段
column: id
# 主键生成策略名称,对应下方主键策略
keyGeneratorName: snowflake
bindingTables:
- user
- invitation
shardingAlgorithms:
user_mod:
type: MOD
props:
sharding-count: 4
invitation_mod:
type: MOD
props:
sharding-count: 4
keyGenerators:
snowflake:
type: SNOWFLAKE

```

This is my global configuration.

global.yaml

``` yaml

mode:
type: Standalone

authority:
users:
- user: root
password: root
- user: sharding
password: sharding
privilege:
type: ALL_PERMITTED

props:
max-connections-size-per-query: 1
kernel-executor-size: 16 # Infinite by default.
proxy-frontend-flush-threshold: 128 # The default value is 128.
proxy-hint-enabled: true
sql-show: true

sqlFederation:
sqlFederationEnabled: true
allQueryUseSQLFederation: false
executionPlanCache:
initialCapacity: 2000
maximumSize: 65535

```

when I insert a batch of data, the data looks like this:
![data2](https://github.com/user-attachments/assets/2a9ef254-37f1-40db-9ad0-51439d5c6952)

attention to the last query in the image, an error occurred.
ERROR 20101 (42000): SQL federation does not support SQL 'select * from user as u inner join invitation as i on u.id = i.user_id where u.id = 1072871697079599104'.
More details: org.apache.calcite.runtime.CalciteContextException: At line 0, column 0: Cannot apply '=' to arguments of type ' = '. Supported form(s): ' = '

### Example codes for reproduce this issue (such as a github link).

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.