strongduanmu
commented
3 years ago @danianyu Thank you for your feedback, i will investigate this issue later.
Closed danianyu closed 3 years ago
strongduanmu
commented
3 years ago @danianyu Thank you for your feedback, i will investigate this issue later.
strongduanmu
commented
3 years ago 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.
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 | |
+---------+--------------+------+-----+---------+-------+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.
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';When I tested, I found another problem, that is, calcite has problems with time processing.
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.
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:
@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.
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.
Caused by: java.lang.RuntimeException: parse failed: Encountered ". date" at line 6, column 28. Was expecting one of: