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 2021/06/18 09:41:50 UTC
[shardingsphere] branch master updated: update supported sql doc
(#10865)
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 6e5a37c update supported sql doc (#10865)
6e5a37c is described below
commit 6e5a37c65b5737f4787df8bc1ee9ddefe75cf2bd
Author: Zhengqiang Duan <st...@gmail.com>
AuthorDate: Fri Jun 18 17:41:25 2021 +0800
update supported sql doc (#10865)
* update supported sql doc
* update supported sql doc
---
docs/document/content/features/sharding/use-norms/sql.cn.md | 10 +++++-----
docs/document/content/features/sharding/use-norms/sql.en.md | 10 +++++-----
2 files changed, 10 insertions(+), 10 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 1485c4c..a6c36a8 100644
--- a/docs/document/content/features/sharding/use-norms/sql.cn.md
+++ b/docs/document/content/features/sharding/use-norms/sql.cn.md
@@ -53,7 +53,7 @@ tbl_name [AS] alias] [index_hint_list]
* `CASE WHEN` 中包含子查询不支持
* `CASE WHEN` 中使用逻辑表名不支持（请使用表别名）
-不支持 HAVING、UNION (ALL)
+不支持 UNION (ALL)
部分支持子查询
* 子查询和外层查询同时指定分片键时，分片键的值必须保持一致
@@ -78,8 +78,6 @@ SELECT COUNT(*) FROM (SELECT * FROM t_order WHERE order_id = 1) o WHERE o.order_
简单来说，通过子查询进行非功能需求，在大部分情况下是可以支持的。比如分页、统计总数等；而通过子查询实现业务查询当前并不能支持。
-由于归并的限制，子查询中包含聚合函数目前无法支持。
-
不支持包含schema的SQL。因为ShardingSphere的理念是像使用一个数据源一样使用多数据源，因此对SQL的访问都是在同一个逻辑schema之上。
### 对分片键进行操作
@@ -107,9 +105,11 @@ SELECT * FROM t_order WHERE to_date(create_time, 'yyyy-mm-dd') = '2019-01-01';
| SELECT * FROM tbl_name WHERE col1 = ? ORDER BY col2 DESC LIMIT ? | |
| SELECT COUNT(*), SUM(col1), MIN(col1), MAX(col1), AVG(col1) FROM tbl_name WHERE col1 = ? | |
| SELECT COUNT(col1) FROM tbl_name WHERE col2 = ? GROUP BY col1 ORDER BY col3 DESC LIMIT ?, ? | |
+| SELECT col1, SUM(col2) FROM tbl_name GROUP BY col1 HAVING SUM(col2) > 10 | |
| 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 | |
+| SELECT (SELECT MAX(col1) FROM tbl_name) a, col2 from tbl_name | |
| (SELECT * FROM tbl_name) | |
| INSERT INTO tbl_name (col1, col2,...) VALUES (?, ?, ....) | |
| INSERT INTO tbl_name VALUES (?, ?,....) | |
@@ -135,7 +135,6 @@ SELECT * FROM t_order WHERE to_date(create_time, 'yyyy-mm-dd') = '2019-01-01';
| REPLACE INTO tbl_name (col1, col2, ...) SELECT * FROM tbl_name WHERE col3 = ? | SELECT子句暂不支持使用*号简写及内置的分布式主键生成器 |
| SELECT * FROM tbl_name1 UNION SELECT * FROM tbl_name2 | UNION |
| SELECT * FROM tbl_name1 UNION ALL SELECT * FROM tbl_name2 | UNION ALL |
-| SELECT SUM(DISTINCT col1), SUM(col1) FROM tbl_name | 详见DISTINCT支持情况详细说明 |
| SELECT * FROM tbl_name WHERE to_date(create_time, 'yyyy-mm-dd') = ? | 会导致全路由 |
| SELECT MAX(tbl_name.col1) FROM tbl_name | 查询列是函数表达式时,查询列前不能使用表名;若查询表存在别名,则可使用表的别名|
@@ -153,6 +152,7 @@ SELECT * FROM t_order WHERE to_date(create_time, 'yyyy-mm-dd') = '2019-01-01';
| SELECT DISTINCT(col1) FROM tbl_name |
| SELECT AVG(DISTINCT col1) FROM tbl_name |
| SELECT SUM(DISTINCT col1) FROM tbl_name |
+| SELECT SUM(DISTINCT col1), SUM(col1) FROM tbl_name |
| SELECT COUNT(DISTINCT col1) FROM tbl_name |
| SELECT COUNT(DISTINCT col1) FROM tbl_name GROUP BY col1 |
| SELECT COUNT(DISTINCT col1 + col2) FROM tbl_name |
@@ -164,4 +164,4 @@ SELECT * FROM t_order WHERE to_date(create_time, 'yyyy-mm-dd') = '2019-01-01';
| SQL | 不支持原因 |
| ------------------------------------------------------------------------------------------- |----------------------------------- |
-| SELECT SUM(DISTINCT tbl_name.col1), SUM(tbl_name.col1) FROM tbl_name | 查询列是函数表达式时,查询列前不能使用表名;若查询表存在别名,则可使用表的别名 |
+| SELECT SUM(DISTINCT tbl_name.col1), tbl_name.col2 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 68dc232..8d5b77f 100644
--- a/docs/document/content/features/sharding/use-norms/sql.en.md
+++ b/docs/document/content/features/sharding/use-norms/sql.en.md
@@ -53,7 +53,7 @@ Partially support CASE WHEN
* `CASE WHEN` containing sub-query is not supported
* `CASE WHEN` containing logical-table is not supported(please use alias of table)
-Do not support HAVING and UNION (ALL)
+Do not support UNION (ALL)
Partly available sub-query
* If subquery and outer query specify sharding key at the same time, the value of sharding key must be consistent.
@@ -78,8 +78,6 @@ SELECT COUNT(*) FROM (SELECT * FROM t_order WHERE order_id = 1) o WHERE o.order_
To be simple, through sub-query, non-functional requirements are available in most cases, such as pagination, sum count and so on; but functional requirements are unavailable for now.
-Due to the restriction of merger, sub-query that contains aggregation function is unavailable for now.
-
Do not support SQL that contains schema, for the concept of ShardingSphere is to use multiple data source as one data source, so all the SQL visits are based on one logic schema.
### Operation for shardingColumn
@@ -107,9 +105,11 @@ When shardingColumn in expressions and functions, ShardingSphere will use full r
| SELECT * FROM tbl_name WHERE col1 = ? ORDER BY col2 DESC LIMIT ? | |
| SELECT COUNT(*), SUM(col1), MIN(col1), MAX(col1), AVG(col1) FROM tbl_name WHERE col1 = ? | |
| SELECT COUNT(col1) FROM tbl_name WHERE col2 = ? GROUP BY col1 ORDER BY col3 DESC LIMIT ?, ? | |
+| SELECT col1, SUM(col2) FROM tbl_name GROUP BY col1 HAVING SUM(col2) > 10 | |
| 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 | |
+| SELECT (SELECT MAX(col1) FROM tbl_name) a, col2 from tbl_name | |
| (SELECT * FROM tbl_name) | |
| INSERT INTO tbl_name (col1, col2,...) VALUES (?, ?, ....) | |
| INSERT INTO tbl_name VALUES (?, ?,....) | |
@@ -135,7 +135,6 @@ When shardingColumn in expressions and functions, ShardingSphere will use full r
| REPLACE INTO tbl_name (col1, col2, ...) SELECT * FROM tbl_name WHERE col3 = ? | SELECT clause does not support *-shorthand and built-in key generators |
| SELECT * FROM tbl_name1 UNION SELECT * FROM tbl_name2 | UNION |
| SELECT * FROM tbl_name1 UNION ALL SELECT * FROM tbl_name2 | UNION ALL |
-| 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 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. |
@@ -153,6 +152,7 @@ When shardingColumn in expressions and functions, ShardingSphere will use full r
| SELECT DISTINCT(col1) FROM tbl_name |
| SELECT AVG(DISTINCT col1) FROM tbl_name |
| SELECT SUM(DISTINCT col1) FROM tbl_name |
+| SELECT SUM(DISTINCT col1), SUM(col1) FROM tbl_name |
| SELECT COUNT(DISTINCT col1) FROM tbl_name |
| SELECT COUNT(DISTINCT col1) FROM tbl_name GROUP BY col1 |
| SELECT COUNT(DISTINCT col1 + col2) FROM tbl_name |
@@ -164,4 +164,4 @@ When shardingColumn in expressions and functions, ShardingSphere will use full r
| SQL | Reason |
| -------------------------------------------------- | ---------------------------------------------------------------------------------- |
-| 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
+| SELECT SUM(DISTINCT tbl_name.col1), tbl_name.col2 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