This is an automated email from the ASF dual-hosted git repository.
panjuan pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/shardingsphere.git
The following commit(s) were added to refs/heads/master by this push:
new 4a4b2bc fix sql support doc (#6293)
4a4b2bc is described below
commit 4a4b2bc5bc76f1034a8661fe35a5ce3488825b53
Author: JingShang Lu <[email protected]>
AuthorDate: Tue Jul 14 17:51:47 2020 +0800
fix sql support doc (#6293)
* fix sql support doc
* add sharding rewrite test case and fix sql support doc
* fix
* fix
* fix
* fix
---
.../content/features/sharding/use-norms/sql.cn.md | 8 ++++----
.../content/features/sharding/use-norms/sql.en.md | 7 ++++---
.../src/test/resources/sharding/select.xml | 22 ++++++++++++++++++++++
3 files changed, 30 insertions(+), 7 deletions(-)
diff --git a/docs/document/content/features/sharding/use-norms/sql.cn.md
b/docs/document/content/features/sharding/use-norms/sql.cn.md
index d376718..bc08462 100644
--- a/docs/document/content/features/sharding/use-norms/sql.cn.md
+++ b/docs/document/content/features/sharding/use-norms/sql.cn.md
@@ -110,6 +110,7 @@ SELECT * FROM t_order WHERE to_date(create_time,
'yyyy-mm-dd') = '2019-01-01';
| DROP INDEX idx_name
| |
| SELECT DISTINCT * FROM tbl_name WHERE col1 = ?
| |
| SELECT COUNT(DISTINCT col1) FROM tbl_name
| |
+| SELECT subquery_alias.col1 FROM (select tbl_name.col1 from tbl_name where
tbl_name.col2=?) subquery_alias
| |
### 不支持的SQL
@@ -117,12 +118,12 @@ SELECT * FROM t_order WHERE to_date(create_time,
'yyyy-mm-dd') = '2019-01-01';
|
------------------------------------------------------------------------------------------
| -------------------------- |
| INSERT INTO tbl_name (col1, col2, ...) VALUES(1+2, ?, ...)
| VALUES语句不支持运算表达式 |
| INSERT INTO tbl_name (col1, col2, ...) SELECT col1, col2, ... FROM tbl_name
WHERE col3 = ? | INSERT .. SELECT |
-| SELECT COUNT(col1) as count_alias FROM tbl_name GROUP BY col1 HAVING
count_alias > ? | HAVING |
| SELECT * FROM tbl_name1 UNION SELECT * FROM tbl_name2
| UNION |
| SELECT * FROM tbl_name1 UNION ALL SELECT * FROM tbl_name2
| UNION ALL |
-| SELECT * FROM ds.tbl_name1
| 包含schema |
| SELECT SUM(DISTINCT col1), SUM(col1) FROM tbl_name
| 详见DISTINCT支持情况详细说明 |
| SELECT * FROM tbl_name WHERE to_date(create_time, 'yyyy-mm-dd') = ?
| 会导致全路由 |
+| (SELECT * FROM tbl_name)
| 暂不支持加括号的查询 |
+| SELECT MAX(tbl_name.col1) FROM tbl_name
| 查询列是函数表达式时,查询列前不能使用表名;若查询表存在别名,则可使用表的别名|
## DISTINCT支持情况详细说明
@@ -149,5 +150,4 @@ SELECT * FROM t_order WHERE to_date(create_time,
'yyyy-mm-dd') = '2019-01-01';
| SQL
| 不支持原因 |
|
-------------------------------------------------------------------------------------------
|----------------------------------- |
-| SELECT SUM(DISTINCT col1), SUM(col1) FROM tbl_name
| 同时使用普通聚合函数和DISTINCT聚合函数 |
-
+| SELECT SUM(DISTINCT tbl_name.col1), SUM(tbl_name.col1) FROM tbl_name
| 查询列是函数表达式时,查询列前不能使用表名;若查询表存在别名,则可使用表的别名 |
diff --git a/docs/document/content/features/sharding/use-norms/sql.en.md
b/docs/document/content/features/sharding/use-norms/sql.en.md
index 3799ce5..f47ffff 100644
--- a/docs/document/content/features/sharding/use-norms/sql.en.md
+++ b/docs/document/content/features/sharding/use-norms/sql.en.md
@@ -110,6 +110,7 @@ When shardingColumn in expressions and functions,
ShardingSphere will use full r
| DROP INDEX idx_name
| |
| SELECT DISTINCT * FROM tbl_name WHERE col1 = ?
| |
| SELECT COUNT(DISTINCT col1) FROM tbl_name
| |
+| SELECT subquery_alias.col1 FROM (select tbl_name.col1 from tbl_name where
tbl_name.col2=?) subquery_alias
| |
### Unsupported SQL
@@ -117,12 +118,12 @@ When shardingColumn in expressions and functions,
ShardingSphere will use full r
|
------------------------------------------------------------------------------------------
| --------------------------------------------------- |
| INSERT INTO tbl_name (col1, col2, ...) VALUES(1+2, ?, ...)
| VALUES clause does not support operation expression |
| INSERT INTO tbl_name (col1, col2, ...) SELECT col1, col2, ... FROM tbl_name
WHERE col3 = ? | INSERT .. SELECT |
-| SELECT COUNT(col1) as count_alias FROM tbl_name GROUP BY col1 HAVING
count_alias > ? | HAVING |
| SELECT * FROM tbl_name1 UNION SELECT * FROM tbl_name2
| UNION |
| SELECT * FROM tbl_name1 UNION ALL SELECT * FROM tbl_name2
| UNION ALL |
-| SELECT * FROM ds.tbl_name1
| Contain schema |
| SELECT SUM(DISTINCT col1), SUM(col1) FROM tbl_name
| See DISTINCT availability detail |
| SELECT * FROM tbl_name WHERE to_date(create_time, 'yyyy-mm-dd') = ?
| Lead to full routing |
+| (SELECT * FROM tbl_name)
| Contain brackets |
+| SELECT MAX(tbl_name.col1) FROM tbl_name
| The select function item contains TableName. Otherwise, If this
query table had an alias, then TableAlias could work well in select function
items. |
## DISTINCT Availability Explanation
@@ -149,4 +150,4 @@ When shardingColumn in expressions and functions,
ShardingSphere will use full r
| SQL | Reason
|
| -------------------------------------------------- |
----------------------------------------------------------------------------------
|
-| SELECT SUM(DISTINCT col1), SUM(col1) FROM tbl_name | Use normal aggregation
function and DISTINCT aggregation function in the same time |
\ No newline at end of file
+| SELECT SUM(DISTINCT tbl_name.col1), SUM(tbl_name.col1) FROM tbl_name | The
select function item contains TableName. Otherwise, If this query table had an
alias, then TableAlias could work well in select function items. |
\ No newline at end of file
diff --git
a/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-rewrite/src/test/resources/sharding/select.xml
b/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-rewrite/src/test/resources/sharding/select.xml
index 374205d..26328d1 100644
---
a/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-rewrite/src/test/resources/sharding/select.xml
+++
b/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-rewrite/src/test/resources/sharding/select.xml
@@ -27,6 +27,28 @@
<output sql="SELECT * FROM t_account_0 WHERE account_id = 100" />
</rewrite-assertion>
+ <rewrite-assertion id="select_with_sum_fun">
+ <input sql="SELECT SUM(DISTINCT account_id), SUM(account_id) FROM
t_account WHERE account_id = 100" />
+ <output sql="SELECT SUM(DISTINCT account_id), SUM(account_id) FROM
t_account_0 WHERE account_id = 100" />
+ </rewrite-assertion>
+
+ <rewrite-assertion id="select_with_avg_fun">
+ <input sql="SELECT AVG(DISTINCT t.account_id), account_id FROM
t_account t WHERE account_id = 100" />
+ <output sql="SELECT AVG(DISTINCT t.account_id), account_id FROM
t_account_0 t WHERE account_id = 100" />
+ </rewrite-assertion>
+
+ <rewrite-assertion id="select_with_having">
+ <input sql="SELECT COUNT(account_id) as count_alias FROM t_account
GROUP BY amount HAVING count_alias > ?" parameters="1"/>
+ <output sql="SELECT COUNT(account_id) as count_alias , amount AS
GROUP_BY_DERIVED_0 FROM t_account_0 GROUP BY amount ORDER BY amount ASC HAVING
count_alias > ?" parameters="1"/>
+ <output sql="SELECT COUNT(account_id) as count_alias , amount AS
GROUP_BY_DERIVED_0 FROM t_account_1 GROUP BY amount ORDER BY amount ASC HAVING
count_alias > ?" parameters="1"/>
+ </rewrite-assertion>
+
+ <rewrite-assertion id="select_with_schema">
+ <input sql="SELECT * FROM db.t_account" />
+ <output sql="SELECT * FROM db.t_account_0"/>
+ <output sql="SELECT * FROM db.t_account_1"/>
+ </rewrite-assertion>
+
<rewrite-assertion id="select_with_subquery" db-type="MySQL">
<input sql="SELECT * FROM (select t_account.account_id from t_account
where t_account.account_id=?) a WHERE account_id = 100" parameters="100" />
<output sql="SELECT * FROM (select t_account_0.account_id from
t_account_0 where t_account_0.account_id=?) a WHERE account_id = 100"
parameters="100" />
