You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by pa...@apache.org on 2020/07/14 09:52:07 UTC
[shardingsphere] branch master updated: fix sql support doc (#6293)
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 <ji...@gmail.com>
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" />