-
Notifications
You must be signed in to change notification settings - Fork 191
/
old_version_shardingsphere
168 lines (136 loc) · 13.9 KB
/
old_version_shardingsphere
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
commit 9a53991546d8991fd2acab8faac1353885ce3efe
Author: Zhengqiang Duan <strongduanmu@gmail.com>
Date: Fri Jun 11 18:59:48 2021 +0800
update sharding document (#10766)
* update sharding document
* update sharding document
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 4cfa3f0df7..1485c4ca07 100644
--- a/docs/document/content/features/sharding/use-norms/sql.cn.md
+++ b/docs/document/content/features/sharding/use-norms/sql.cn.md
@@ -17,7 +17,7 @@ weight = 1
### 路由至多数据节点
-全面支持DML、DDL、DCL、TCL和部分DAL。支持分页、去重、排序、分组、聚合、关联查询（不支持跨库关联）。以下用最为复杂的DML举例：
+全面支持DML、DDL、DCL、TCL和部分DAL。支持分页、去重、排序、分组、聚合、关联查询。以下用最为复杂的DML举例：
- SELECT主语句
@@ -52,10 +52,11 @@ tbl_name [AS] alias] [index_hint_list]
部分支持CASE WHEN
* `CASE WHEN` 中包含子查询不支持
* `CASE WHEN` 中使用逻辑表名不支持（请使用表别名）
+
不支持 HAVING、UNION (ALL)
部分支持子查询
-* 子查询中使用WHERE条件时，必须包含分片键，当外层查询中也包含分片键时，子查询和外层查询中的分片键必须保持一致
+* 子查询和外层查询同时指定分片键时，分片键的值必须保持一致
除了分页子查询的支持之外(详情请参考[分页](/cn/features/sharding/use-norms/pagination))，也支持同等模式的子查询。无论嵌套多少层，ShardingSphere都可以解析至第一个包含数据表的子查询，一旦在下层嵌套中再次找到包含数据表的子查询将直接抛出解析异常。
@@ -63,14 +64,15 @@ tbl_name [AS] alias] [index_hint_list]
```sql
SELECT COUNT(*) FROM (SELECT * FROM t_order) o;
+SELECT COUNT(*) FROM (SELECT * FROM t_order) o WHERE o.order_id = 1;
SELECT COUNT(*) FROM (SELECT * FROM t_order WHERE order_id = 1) o;
SELECT COUNT(*) FROM (SELECT * FROM t_order WHERE order_id = 1) o WHERE o.order_id = 1;
+SELECT COUNT(*) FROM (SELECT * FROM t_order WHERE product_id = 1) o;
```
以下子查询不支持：
```sql
-SELECT COUNT(*) FROM (SELECT * FROM t_order WHERE product_id = 1) o;
SELECT COUNT(*) FROM (SELECT * FROM t_order WHERE order_id = 1) o WHERE o.order_id = 2;
```
@@ -105,8 +107,13 @@ 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 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 * FROM tbl_name) | |
| INSERT INTO tbl_name (col1, col2,...) VALUES (?, ?, ....) | |
| INSERT INTO tbl_name VALUES (?, ?,....) | |
+| INSERT INTO tbl_name (col1, col2, ...) VALUES(1 + 2, ?, ...) | |
| INSERT INTO tbl_name (col1, col2, ...) VALUES (?, ?, ....), (?, ?, ....) | |
| INSERT INTO tbl_name (col1, col2, ...) SELECT col1, col2, ... FROM tbl_name WHERE col3 = ? | INSERT表和SELECT表必须为相同表或绑定表 |
| REPLACE INTO tbl_name (col1, col2, ...) SELECT col1, col2, ... FROM tbl_name WHERE col3 = ? | REPLACE表和SELECT表必须为相同表或绑定表 |
@@ -119,22 +126,17 @@ SELECT * FROM t_order WHERE to_date(create_time, 'yyyy-mm-dd') = '2019-01-01';
| CREATE INDEX idx_name ON tbl_name | |
| DROP INDEX idx_name ON tbl_name | |
| 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
| SQL | 不支持原因 |
| ------------------------------------------------------------------------------------------ | -------------------------- |
-| INSERT INTO tbl_name (col1, col2, ...) VALUES(1+2, ?, ...) | VALUES语句不支持运算表达式 |
| INSERT INTO tbl_name (col1, col2, ...) SELECT * FROM tbl_name WHERE col3 = ? | SELECT子句暂不支持使用*号简写及内置的分布式主键生成器 |
| 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 * FROM tbl_name) | 暂不支持加括号的查询 |
| SELECT MAX(tbl_name.col1) FROM tbl_name | 查询列是函数表达式时,查询列前不能使用表名;若查询表存在别名,则可使用表的别名|
## DISTINCT支持情况详细说明
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 4d9a9bf04d..68dc232b6b 100644
--- a/docs/document/content/features/sharding/use-norms/sql.en.md
+++ b/docs/document/content/features/sharding/use-norms/sql.en.md
@@ -17,7 +17,7 @@ It is inevitably to have some unlisted SQLs, welcome to supplement for that. We
### Route to multiple data nodes
-Fully support DML, DDL, DCL, TCL and some DAL. Support pagination, DISTINCT, ORDER BY, GROUP BY, aggregation and JOIN (does not support cross-database relevance). Here is an example of a most complex kind of DML:
+Fully support DML, DDL, DCL, TCL and some DAL. Support pagination, DISTINCT, ORDER BY, GROUP BY, aggregation and JOIN. Here is an example of a most complex kind of DML:
- Main SELECT
@@ -54,8 +54,9 @@ Partially support CASE WHEN
* `CASE WHEN` containing logical-table is not supported(please use alias of table)
Do not support HAVING and UNION (ALL)
+
Partly available sub-query
-* If subquery contains `WHERE` condition, the sharding key must be included. If the outer query also contains the sharding key, the sharding key in subquery and outer query must be consistent.
+* If subquery and outer query specify sharding key at the same time, the value of sharding key must be consistent.
Support not only pagination sub-query (see [pagination](https://shardingsphere.apache.org/document/current/cn/features/sharding/usage-standard/pagination) for more details), but also sub-query with the same mode. No matter how many layers are nested, ShardingSphere can parse to the first sub-query that contains data table. Once it finds another sub-query of this kind in the sub-level nested, it will directly throw a parsing exception.
@@ -63,14 +64,15 @@ For example, the following sub-query is available:
```sql
SELECT COUNT(*) FROM (SELECT * FROM t_order) o;
+SELECT COUNT(*) FROM (SELECT * FROM t_order) o WHERE o.order_id = 1;
SELECT COUNT(*) FROM (SELECT * FROM t_order WHERE order_id = 1) o;
SELECT COUNT(*) FROM (SELECT * FROM t_order WHERE order_id = 1) o WHERE o.order_id = 1;
+SELECT COUNT(*) FROM (SELECT * FROM t_order WHERE product_id = 1) o;
```
The following sub-query is unavailable:
```sql
-SELECT COUNT(*) FROM (SELECT * FROM t_order WHERE product_id = 1) o;
SELECT COUNT(*) FROM (SELECT * FROM t_order WHERE order_id = 1) o WHERE o.order_id = 2;
```
@@ -105,8 +107,13 @@ 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 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 * FROM tbl_name) | |
| INSERT INTO tbl_name (col1, col2,...) VALUES (?, ?, ....) | |
| INSERT INTO tbl_name VALUES (?, ?,....) | |
+| INSERT INTO tbl_name (col1, col2, ...) VALUES(1 + 2, ?, ...) | |
| INSERT INTO tbl_name (col1, col2, ...) VALUES (?, ?, ....), (?, ?, ....) | |
| INSERT INTO tbl_name (col1, col2, ...) SELECT col1, col2, ... FROM tbl_name WHERE col3 = ? | The table inserted and the table selected must be the same or bind tables |
| REPLACE INTO tbl_name (col1, col2, ...) SELECT col1, col2, ... FROM tbl_name WHERE col3 = ? | The table replaced and the table selected must be the same or bind tables |
@@ -119,22 +126,17 @@ When shardingColumn in expressions and functions, ShardingSphere will use full r
| CREATE INDEX idx_name ON tbl_name | |
| DROP INDEX idx_name ON tbl_name | |
| 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
| SQL | Reason |
| ------------------------------------------------------------------------------------------ | --------------------------------------------------- |
-| INSERT INTO tbl_name (col1, col2, ...) VALUES(1+2, ?, ...) | VALUES clause does not support operation expression |
| INSERT INTO tbl_name (col1, col2, ...) SELECT * FROM tbl_name WHERE col3 = ? | SELECT clause does not support *-shorthand and built-in key generators |
| 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 * 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