You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2021/08/13 03:11:32 UTC
[GitHub] [shardingsphere] danianyu opened a new issue #11794: SQL alias problem of join type.
danianyu opened a new issue #11794:
URL: https://github.com/apache/shardingsphere/issues/11794
### Which version of ShardingSphere did you use?
5.0.0-RC1-SNAPSHOT
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-JDBC
### Expected behavior
Hope to support this feature
### Actual behavior
When the join type SQL is used and the alias of the column of the main table is omitted, the following exception will be thrown. This is different from native SQL.
ERROR:
Caused by: java.lang.RuntimeException: parse failed: Encountered ". date" at line 6, column 28.
Was expecting one of:
<EOF>
"EXCEPT" ...
"FETCH" ...
"GROUP" ...
"HAVING" ...
"INTERSECT" ...
"LIMIT" ...
"OFFSET" ...
"ORDER" ...
"MINUS" ...
"UNION" ...
"WHERE" ...
"WINDOW" ...
"NATURAL" ...
"JOIN" ...
"INNER" ...
"LEFT" ...
"RIGHT" ...
"FULL" ...
"CROSS" ...
"," ...
"OUTER" ...
"." <IDENTIFIER> ...
"." <HYPHENATED_IDENTIFIER> ...
"." <QUOTED_IDENTIFIER> ...
"." <BACK_QUOTED_IDENTIFIER> ...
"." <BRACKET_QUOTED_IDENTIFIER> ...
"." <UNICODE_QUOTED_IDENTIFIER> ...
"NOT" ...
"IN" ...
"<" ...
"<=" ...
">" ...
">=" ...
"=" ...
"<>" ...
"!=" ...
"BETWEEN" ...
"LIKE" ...
"SIMILAR" ...
"+" ...
"-" ...
"*" ...
"/" ...
"%" ...
"||" ...
"AND" ...
"OR" ...
"IS" ...
"MEMBER" ...
"SUBMULTISET" ...
"CONTAINS" ...
"OVERLAPS" ...
"EQUALS" ...
"PRECEDES" ...
"SUCCEEDS" ...
"IMMEDIATELY" ...
"MULTISET" ...
"[" ...
"FORMAT" ...
"." ...
"." "*" ...
"(" ...
at org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:623) ~[calcite-core-1.26.0.jar:1.26.0]
at org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:508) ~[calcite-core-1.26.0.jar:1.26.0]
at org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:478) ~[calcite-core-1.26.0.jar:1.26.0]
at org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:231) ~[calcite-core-1.26.0.jar:1.26.0]
at org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement_(CalciteConnectionImpl.java:213) ~[calcite-core-1.26.0.jar:1.26.0]
... 128 common frames omitted
### Reason analyze (If you can)
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
Example SQL: select s.a from user s join course c on s.student_id = c.stuend_id where student_name='Tony';
User is a single table. Course is divided into tables and not divided into libraries
### Example codes for reproduce this issue (such as a github link).
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #11794: SQL alias problem of join type.
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #11794:
URL: https://github.com/apache/shardingsphere/issues/11794#issuecomment-918940633
When I tested, I found another problem, that is, calcite has problems with time processing.
```sql
mysql> insert into t_single(single_id, content, date) values(1, '11', '2021-09-14');
Query OK, 1 row affected (0.03 sec)
```
I executed the following SQL and inserted a record with the date `2021-09-14` into the t_single table, but when I query it through calcite, it returns `2021-09-13`.
```sql
mysql> select * from t_single s inner join t_order_test t ON s.single_id = t.test_id;
+---------+------------+-----------+----------+---------+
| content | date | single_id | content0 | test_id |
+---------+------------+-----------+----------+---------+
| 11 | 2021-09-13 | 1 | 11 | 1 |
+---------+------------+-----------+----------+---------+
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #11794: SQL alias problem of join type.
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #11794:
URL: https://github.com/apache/shardingsphere/issues/11794#issuecomment-918938050
Since date is a keyword in calcite parsing, and calcite does not process the keywords, it causes parsing exceptions. Using quotation marks can temporarily avoid this problem. Later, calcite parsing will be skipped and shardingsphere parsing will be used directly.
```sql
select s.content from t_single s inner join t_order_test t ON s.single_id = t.test_id WHERE 'date' = '2021-09-14';
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu edited a comment on issue #11794: SQL alias problem of join type.
Posted by GitBox <gi...@apache.org>.
strongduanmu edited a comment on issue #11794:
URL: https://github.com/apache/shardingsphere/issues/11794#issuecomment-902367368
I found that the exception occurs only when the date field must be included, and other fields will not cause the exception. I used the following SQL to reproduce this problem.
```sql
select s.content from t_single s inner join t_order_test t ON s.single_id = t.test_id WHERE date = '2021-08-20';
```
t_single(single table):
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| single_id | int(11) | YES | | NULL | |
| content | varchar(100) | YES | | NULL | |
| date | date | YES | | NULL | |
+-----------+--------------+------+-----+---------+-------+
t_order_test(sharding table in same database):
mysql> desc t_order_test;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| test_id | int(11) | YES | | NULL | |
| content | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu edited a comment on issue #11794: SQL alias problem of join type.
Posted by GitBox <gi...@apache.org>.
strongduanmu edited a comment on issue #11794:
URL: https://github.com/apache/shardingsphere/issues/11794#issuecomment-918940633
When I tested, I found another problem, that is, calcite has problems with time processing.
```sql
mysql> insert into t_single(single_id, content, date) values(1, '11', '2021-09-14');
Query OK, 1 row affected (0.03 sec)
```
I executed the following SQL and inserted a record with the date `2021-09-14` into the t_single table, but when I query it through calcite, it returns `2021-09-13`.
```sql
mysql> select * from t_single s inner join t_order_test t ON s.single_id = t.test_id;
+---------+------------+-----------+----------+---------+
| content | date | single_id | content0 | test_id |
+---------+------------+-----------+----------+---------+
| 11 | 2021-09-13 | 1 | 11 | 1 |
+---------+------------+-----------+----------+---------+
mysql> select * from t_single s inner join t_order_test t ON s.single_id = t.test_id WHERE create_date_time = '2021-09-14';
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Connection id: 1
Current database: sharding_db
+---------+---------------------+-----------+----------+---------+
| content | create_date_time | single_id | content0 | test_id |
+---------+---------------------+-----------+----------+---------+
| 11 | 2021-09-13 16:00:00 | 1 | 11 | 1 |
+---------+---------------------+-----------+----------+---------+
```
The DateAccessor logic is as follows:
```java
@Override public Date getDate(Calendar calendar) throws SQLException {
java.sql.Date date = (Date) getObject();
if (date == null) {
return null;
}
if (calendar != null) {
long v = date.getTime();
v -= calendar.getTimeZone().getOffset(v);
date = new Date(v);
}
return date;
}
```
When calculating the time, the difference between the current time and the standard time is subtracted, which will cause the result to be 8 hours less than the real time (because we are in the GMT+8). The solution is to set the time zone to GMT in the Calcite url parameter.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu edited a comment on issue #11794: SQL alias problem of join type.
Posted by GitBox <gi...@apache.org>.
strongduanmu edited a comment on issue #11794:
URL: https://github.com/apache/shardingsphere/issues/11794#issuecomment-918938050
Since date is a keyword in calcite parsing, and calcite does not process the keywords, it causes parsing exceptions. Using quotation marks or rename can temporarily avoid this problem. Later, calcite parsing will be skipped and shardingsphere parsing will be used directly.
```sql
select s.content from t_single s inner join t_order_test t ON s.single_id = t.test_id WHERE 'date' = '2021-09-14';
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu edited a comment on issue #11794: SQL alias problem of join type.
Posted by GitBox <gi...@apache.org>.
strongduanmu edited a comment on issue #11794:
URL: https://github.com/apache/shardingsphere/issues/11794#issuecomment-902367368
I found that the exception occurs only when the date field must be included, and other fields will not cause the exception. I used the following SQL to reproduce this problem.
```sql
select s.content from t_single s inner join t_order_test t ON s.single_id = t.test_id WHERE date = '2021-08-20';
```
t_single(single table):
```
mysql> desc t_single;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| single_id | int(11) | YES | | NULL | |
| content | varchar(100) | YES | | NULL | |
| date | date | YES | | NULL | |
+-----------+--------------+------+-----+---------+-------+
```
t_order_test(sharding table in same database):
```
mysql> desc t_order_test;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| test_id | int(11) | YES | | NULL | |
| content | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #11794: SQL alias problem of join type.
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #11794:
URL: https://github.com/apache/shardingsphere/issues/11794#issuecomment-898464946
@danianyu Thank you for your feedback, i will investigate this issue later.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu edited a comment on issue #11794: SQL alias problem of join type.
Posted by GitBox <gi...@apache.org>.
strongduanmu edited a comment on issue #11794:
URL: https://github.com/apache/shardingsphere/issues/11794#issuecomment-918940633
When I tested, I found another problem, that is, calcite has problems with time processing.
```sql
mysql> insert into t_single(single_id, content, date) values(1, '11', '2021-09-14');
Query OK, 1 row affected (0.03 sec)
```
I executed the following SQL and inserted a record with the date `2021-09-14` into the t_single table, but when I query it through calcite, it returns `2021-09-13`.
```sql
mysql> select * from t_single s inner join t_order_test t ON s.single_id = t.test_id;
+---------+------------+-----------+----------+---------+
| content | date | single_id | content0 | test_id |
+---------+------------+-----------+----------+---------+
| 11 | 2021-09-13 | 1 | 11 | 1 |
+---------+------------+-----------+----------+---------+
```
The DateAccessor logic is as follows:
```java
@Override public Date getDate(Calendar calendar) throws SQLException {
java.sql.Date date = (Date) getObject();
if (date == null) {
return null;
}
if (calendar != null) {
long v = date.getTime();
v -= calendar.getTimeZone().getOffset(v);
date = new Date(v);
}
return date;
}
```
When calculating the time, the difference between the current time and the standard time is subtracted, which will cause the result to be 8 hours less than the real time (because we are in the GMT+8). The solution is to set the time zone to GMT in the Calcite url parameter.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero closed issue #11794: SQL alias problem of join type.
Posted by GitBox <gi...@apache.org>.
tristaZero closed issue #11794:
URL: https://github.com/apache/shardingsphere/issues/11794
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #11794: SQL alias problem of join type.
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #11794:
URL: https://github.com/apache/shardingsphere/issues/11794#issuecomment-902367368
I found that the exception occurs only when the date field must be included, and other fields will not cause the exception. I used the following SQL to reproduce this problem.
```sql
select s.content from t_single s inner join t_order_test t ON s.single_id = t.test_id WHERE date = '2021-08-20';
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org