This is an automated email from the ASF dual-hosted git repository.
duanzhengqiang 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 8a5b728a313 Add datetime type e2e (#28235)
8a5b728a313 is described below
commit 8a5b728a3134890d1bb7aa9bebd732b8b6878d9b
Author: ZhangCheng <[email protected]>
AuthorDate: Wed Aug 23 19:24:42 2023 +0800
Add datetime type e2e (#28235)
* Add date type e2e
* Add
* add
* fix
---
.../env/runtime/IntegrationTestEnvironment.java | 2 ++
.../test/e2e/cases/value/SQLValue.java | 8 ++++++-
.../test/e2e/engine/type/dql/BaseDQLE2EIT.java | 5 +++++
.../dml/dataset/passthrough/insert_date_values.xml | 26 ++++++++++++++++++++++
.../dml/dataset/passthrough/update_date_values.xml | 25 +++++++++++++++++++++
.../resources/cases/dml/dml-integration-insert.xml | 4 ++++
.../resources/cases/dml/dml-integration-update.xml | 4 ++++
.../resources/cases/dql/dql-integration-select.xml | 5 +++++
.../scenario/passthrough/data/actual/dataset.xml | 6 +++++
.../data/actual/init-sql/mysql/01-actual-init.sql | 1 +
.../actual/init-sql/opengauss/01-actual-init.sql | 1 +
.../actual/init-sql/postgresql/01-actual-init.sql | 1 +
.../scenario/passthrough/data/expected/dataset.xml | 6 +++++
.../expected/init-sql/mysql/01-expected-init.sql | 1 +
.../init-sql/opengauss/01-expected-init.sql | 1 +
.../init-sql/postgresql/01-expected-init.sql | 1 +
16 files changed, 96 insertions(+), 1 deletion(-)
diff --git
a/test/e2e/env/src/test/java/org/apache/shardingsphere/test/e2e/env/runtime/IntegrationTestEnvironment.java
b/test/e2e/env/src/test/java/org/apache/shardingsphere/test/e2e/env/runtime/IntegrationTestEnvironment.java
index cfacdf51297..755a9d656f3 100644
---
a/test/e2e/env/src/test/java/org/apache/shardingsphere/test/e2e/env/runtime/IntegrationTestEnvironment.java
+++
b/test/e2e/env/src/test/java/org/apache/shardingsphere/test/e2e/env/runtime/IntegrationTestEnvironment.java
@@ -26,6 +26,7 @@ import java.io.IOException;
import java.io.InputStream;
import java.util.Collection;
import java.util.Properties;
+import java.util.TimeZone;
/**
* Integration test environment.
@@ -47,6 +48,7 @@ public final class IntegrationTestEnvironment {
Properties props = loadProperties();
runModes =
Splitter.on(",").trimResults().splitToList(props.getProperty("it.run.modes"));
runAdditionalTestCases =
Boolean.parseBoolean(props.getProperty("it.run.additional.cases"));
+
TimeZone.setDefault(TimeZone.getTimeZone(props.getProperty("it.timezone",
"UTC")));
scenarios = getScenarios(props);
clusterEnvironment = new ClusterEnvironment(props);
}
diff --git
a/test/e2e/sql/src/test/java/org/apache/shardingsphere/test/e2e/cases/value/SQLValue.java
b/test/e2e/sql/src/test/java/org/apache/shardingsphere/test/e2e/cases/value/SQLValue.java
index 7089c608a6b..667f11e32ce 100644
---
a/test/e2e/sql/src/test/java/org/apache/shardingsphere/test/e2e/cases/value/SQLValue.java
+++
b/test/e2e/sql/src/test/java/org/apache/shardingsphere/test/e2e/cases/value/SQLValue.java
@@ -46,6 +46,8 @@ public final class SQLValue {
private final DateTimeFormatter timeFormatter =
DateTimeFormatter.ofPattern("HH:mm:ss");
+ private final DateTimeFormatter dateTimeFormatter =
DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
+
private final DateTimeFormatter timestampFormatter =
DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.S");
public SQLValue(final String value, final String type, final int index) {
@@ -84,8 +86,12 @@ public final class SQLValue {
case "boolean":
return Boolean.parseBoolean(value);
case "Date":
- case "datetime":
return Date.valueOf(LocalDate.parse(value, dateFormatter));
+ case "datetime":
+ if (10 == value.length()) {
+ return Date.valueOf(LocalDate.parse(value, dateFormatter));
+ }
+ return Date.valueOf(LocalDate.parse(value, dateTimeFormatter));
case "time":
return Time.valueOf(LocalTime.parse(value, timeFormatter));
case "timestamp":
diff --git
a/test/e2e/sql/src/test/java/org/apache/shardingsphere/test/e2e/engine/type/dql/BaseDQLE2EIT.java
b/test/e2e/sql/src/test/java/org/apache/shardingsphere/test/e2e/engine/type/dql/BaseDQLE2EIT.java
index 2879c0e21fe..fd673015fb0 100644
---
a/test/e2e/sql/src/test/java/org/apache/shardingsphere/test/e2e/engine/type/dql/BaseDQLE2EIT.java
+++
b/test/e2e/sql/src/test/java/org/apache/shardingsphere/test/e2e/engine/type/dql/BaseDQLE2EIT.java
@@ -35,6 +35,8 @@ import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
+import java.sql.Timestamp;
+import java.time.LocalDateTime;
import java.util.Collection;
import java.util.HashSet;
import java.util.LinkedList;
@@ -191,6 +193,9 @@ public abstract class BaseDQLE2EIT {
Object expectedValue = expectedResultSet.getObject(i + 1);
if (actualValue instanceof Double || actualValue instanceof
Float || actualValue instanceof BigDecimal) {
assertThat(Math.floor(Double.parseDouble(actualValue.toString())),
is(Math.floor(Double.parseDouble(expectedValue.toString()))));
+ } else if (actualValue instanceof Timestamp && expectedValue
instanceof LocalDateTime) {
+ // TODO Since mysql 8.0.23, for the DATETIME type, the
mysql driver returns the LocalDateTime type, but the proxy returns the
Timestamp type.
+ assertThat(((Timestamp) actualValue).toLocalDateTime(),
is(expectedValue));
} else {
assertThat(String.valueOf(actualValue),
is(String.valueOf(expectedValue)));
}
diff --git
a/test/e2e/sql/src/test/resources/cases/dml/dataset/passthrough/insert_date_values.xml
b/test/e2e/sql/src/test/resources/cases/dml/dataset/passthrough/insert_date_values.xml
new file mode 100644
index 00000000000..bdea0fbb7d3
--- /dev/null
+++
b/test/e2e/sql/src/test/resources/cases/dml/dataset/passthrough/insert_date_values.xml
@@ -0,0 +1,26 @@
+<!--
+ ~ Licensed to the Apache Software Foundation (ASF) under one or more
+ ~ contributor license agreements. See the NOTICE file distributed with
+ ~ this work for additional information regarding copyright ownership.
+ ~ The ASF licenses this file to You under the Apache License, Version 2.0
+ ~ (the "License"); you may not use this file except in compliance with
+ ~ the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing, software
+ ~ distributed under the License is distributed on an "AS IS" BASIS,
+ ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ ~ See the License for the specific language governing permissions and
+ ~ limitations under the License.
+ -->
+
+<dataset update-count="1">
+ <metadata data-nodes="passthrough.t_data_type_date">
+ <column name="id" type="numeric" />
+ <column name="creation_date" type="Date" />
+ <column name="update_date" type="datetime" />
+ </metadata>
+ <row data-node="passthrough.t_data_type_date" values="1, 2017-08-08,
2017-08-08 00:00:00.0" />
+ <row data-node="passthrough.t_data_type_date" values="2, 2017-08-08,
2017-08-08 00:00:00.0" />
+</dataset>
diff --git
a/test/e2e/sql/src/test/resources/cases/dml/dataset/passthrough/update_date_values.xml
b/test/e2e/sql/src/test/resources/cases/dml/dataset/passthrough/update_date_values.xml
new file mode 100644
index 00000000000..e75a6aeab41
--- /dev/null
+++
b/test/e2e/sql/src/test/resources/cases/dml/dataset/passthrough/update_date_values.xml
@@ -0,0 +1,25 @@
+<!--
+ ~ Licensed to the Apache Software Foundation (ASF) under one or more
+ ~ contributor license agreements. See the NOTICE file distributed with
+ ~ this work for additional information regarding copyright ownership.
+ ~ The ASF licenses this file to You under the Apache License, Version 2.0
+ ~ (the "License"); you may not use this file except in compliance with
+ ~ the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing, software
+ ~ distributed under the License is distributed on an "AS IS" BASIS,
+ ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ ~ See the License for the specific language governing permissions and
+ ~ limitations under the License.
+ -->
+
+<dataset update-count="1">
+ <metadata data-nodes="passthrough.t_data_type_date">
+ <column name="id" type="numeric" />
+ <column name="creation_date" type="Date" />
+ <column name="update_date" type="datetime" />
+ </metadata>
+ <row data-node="passthrough.t_data_type_date" values="1, 2018-08-08,
2018-08-08 00:00:00.0" />
+</dataset>
diff --git
a/test/e2e/sql/src/test/resources/cases/dml/dml-integration-insert.xml
b/test/e2e/sql/src/test/resources/cases/dml/dml-integration-insert.xml
index af974effe29..e1b709edbda 100644
--- a/test/e2e/sql/src/test/resources/cases/dml/dml-integration-insert.xml
+++ b/test/e2e/sql/src/test/resources/cases/dml/dml-integration-insert.xml
@@ -211,6 +211,10 @@
<assertion parameters="1:int, some values:bytes"
expected-data-file="insert_bytea_values.xml"/>
</test-case>
+ <test-case sql="INSERT INTO t_data_type_date (id, creation_date,
update_date) VALUES (?, ?, ?)" db-types="MySQL" scenario-types="passthrough">
+ <assertion parameters="2:int, 2017-08-08:Date, 2017-08-08:datetime"
expected-data-file="insert_date_values.xml"/>
+ </test-case>
+
<test-case sql="/* SHARDINGSPHERE_HINT: DATA_SOURCE_NAME=encrypt_ds_0
*/INSERT INTO t_order_0 values (1, 1, 'ok', 1, 'mark', '2021-01-01')"
db-types="MySQL,PostgreSQL" scenario-types="sharding_and_encrypt">
<assertion expected-data-file="insert_into_t_order_0.xml" />
</test-case>
diff --git
a/test/e2e/sql/src/test/resources/cases/dml/dml-integration-update.xml
b/test/e2e/sql/src/test/resources/cases/dml/dml-integration-update.xml
index bf1029dccb2..d1345c00736 100644
--- a/test/e2e/sql/src/test/resources/cases/dml/dml-integration-update.xml
+++ b/test/e2e/sql/src/test/resources/cases/dml/dml-integration-update.xml
@@ -102,4 +102,8 @@
<test-case sql="UPDATE t_data_type_money SET val = val + ?::money WHERE id
= ?" db-types="PostgreSQL,openGauss" scenario-types="passthrough">
<assertion parameters="-12345.6789:String, 1002:int"
expected-data-file="update_money_subtract_value.xml"/>
</test-case>
+
+ <test-case sql="UPDATE t_data_type_date SET creation_date = ?, update_date
= ? where id = ?" db-types="MySQL" scenario-types="passthrough">
+ <assertion parameters="2018-08-08:Date, 2018-08-08:datetime, 1:int"
expected-data-file="update_date_values.xml"/>
+ </test-case>
</integration-test-cases>
diff --git
a/test/e2e/sql/src/test/resources/cases/dql/dql-integration-select.xml
b/test/e2e/sql/src/test/resources/cases/dql/dql-integration-select.xml
index 2422e36649e..b37cc0bacc6 100644
--- a/test/e2e/sql/src/test/resources/cases/dql/dql-integration-select.xml
+++ b/test/e2e/sql/src/test/resources/cases/dql/dql-integration-select.xml
@@ -241,6 +241,11 @@
<assertion parameters="$122.00:String, 1001:int"
expected-data-source-name="expected_dataset" />
<assertion parameters="$124.00:String, 1001:int"
expected-data-source-name="expected_dataset" />
</test-case>
+
+ <test-case sql="SELECT * FROM t_data_type_date WHERE id = ?"
db-types="MySQL,PostgreSQL,openGauss" scenario-types="passthrough"
+ scenario-comments="Test ShardingSphere-Proxy compatibility for
MySQL/PostgreSQL/openGauss date type">
+ <assertion parameters="1:int"
expected-data-source-name="expected_dataset" />
+ </test-case>
<test-case sql="select * from shardingsphere.cluster_information;"
db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
<assertion expected-data-file="select_cluster_information.xml" />
diff --git
a/test/e2e/sql/src/test/resources/env/scenario/passthrough/data/actual/dataset.xml
b/test/e2e/sql/src/test/resources/env/scenario/passthrough/data/actual/dataset.xml
index da704299ae7..9695514e4ae 100644
---
a/test/e2e/sql/src/test/resources/env/scenario/passthrough/data/actual/dataset.xml
+++
b/test/e2e/sql/src/test/resources/env/scenario/passthrough/data/actual/dataset.xml
@@ -49,8 +49,14 @@
<column name="id" type="numeric" />
<column name="val" type="varchar" />
</metadata>
+ <metadata data-nodes="passthrough.t_data_type_date">
+ <column name="id" type="numeric" />
+ <column name="creation_date" type="Date" />
+ <column name="update_date" type="datetime" />
+ </metadata>
<row data-node="passthrough.t_data_type_integer_unsigned" values="1001,
18446744073709551615, 4294967295, 16777215, 65535, 255" />
<row data-node="passthrough.t_data_type_integer_unsigned" values="1002, 0,
0, 0, 0, 0" />
<row data-node="passthrough.t_data_type_money" values="1001, 123" />
<row data-node="passthrough.t_data_type_money" values="1002, 456" />
+ <row data-node="passthrough.t_data_type_date" values="1, 2017-08-08,
2017-08-08 00:00:00" />
</dataset>
diff --git
a/test/e2e/sql/src/test/resources/env/scenario/passthrough/data/actual/init-sql/mysql/01-actual-init.sql
b/test/e2e/sql/src/test/resources/env/scenario/passthrough/data/actual/init-sql/mysql/01-actual-init.sql
index 80037d3fd84..a2e2368d880 100644
---
a/test/e2e/sql/src/test/resources/env/scenario/passthrough/data/actual/init-sql/mysql/01-actual-init.sql
+++
b/test/e2e/sql/src/test/resources/env/scenario/passthrough/data/actual/init-sql/mysql/01-actual-init.sql
@@ -27,3 +27,4 @@ CREATE TABLE passthrough.t_data_type_floating_point (id INT
PRIMARY KEY, col_flo
CREATE TABLE passthrough.t_with_generated_id (id INT AUTO_INCREMENT PRIMARY
KEY, val VARCHAR(100) NOT NULL);
CREATE TABLE passthrough.t_data_type_money (id INT PRIMARY KEY, val
NUMERIC(16, 2));
CREATE TABLE passthrough.t_data_type_bytea (id INT PRIMARY KEY, val BLOB NOT
NULL);
+CREATE TABLE passthrough.t_data_type_date (id INT PRIMARY KEY, creation_date
DATE NOT NULL, update_date DATETIME NOT NULL);
diff --git
a/test/e2e/sql/src/test/resources/env/scenario/passthrough/data/actual/init-sql/opengauss/01-actual-init.sql
b/test/e2e/sql/src/test/resources/env/scenario/passthrough/data/actual/init-sql/opengauss/01-actual-init.sql
index 38728287440..5b8773bd753 100644
---
a/test/e2e/sql/src/test/resources/env/scenario/passthrough/data/actual/init-sql/opengauss/01-actual-init.sql
+++
b/test/e2e/sql/src/test/resources/env/scenario/passthrough/data/actual/init-sql/opengauss/01-actual-init.sql
@@ -28,3 +28,4 @@ CREATE TABLE t_data_type_floating_point (id INT PRIMARY KEY,
col_float REAL NOT
CREATE TABLE t_with_generated_id (id SERIAL PRIMARY KEY, val VARCHAR NOT NULL);
CREATE TABLE t_data_type_money (id INT PRIMARY KEY, val money);
CREATE TABLE t_data_type_bytea (id INT PRIMARY KEY, val bytea NOT NULL);
+CREATE TABLE t_data_type_date (id INT PRIMARY KEY, creation_date DATE NOT
NULL, update_date TIMESTAMP NOT NULL);
diff --git
a/test/e2e/sql/src/test/resources/env/scenario/passthrough/data/actual/init-sql/postgresql/01-actual-init.sql
b/test/e2e/sql/src/test/resources/env/scenario/passthrough/data/actual/init-sql/postgresql/01-actual-init.sql
index 38728287440..5b8773bd753 100644
---
a/test/e2e/sql/src/test/resources/env/scenario/passthrough/data/actual/init-sql/postgresql/01-actual-init.sql
+++
b/test/e2e/sql/src/test/resources/env/scenario/passthrough/data/actual/init-sql/postgresql/01-actual-init.sql
@@ -28,3 +28,4 @@ CREATE TABLE t_data_type_floating_point (id INT PRIMARY KEY,
col_float REAL NOT
CREATE TABLE t_with_generated_id (id SERIAL PRIMARY KEY, val VARCHAR NOT NULL);
CREATE TABLE t_data_type_money (id INT PRIMARY KEY, val money);
CREATE TABLE t_data_type_bytea (id INT PRIMARY KEY, val bytea NOT NULL);
+CREATE TABLE t_data_type_date (id INT PRIMARY KEY, creation_date DATE NOT
NULL, update_date TIMESTAMP NOT NULL);
diff --git
a/test/e2e/sql/src/test/resources/env/scenario/passthrough/data/expected/dataset.xml
b/test/e2e/sql/src/test/resources/env/scenario/passthrough/data/expected/dataset.xml
index c8c8a3b5d71..5e865e65acc 100644
---
a/test/e2e/sql/src/test/resources/env/scenario/passthrough/data/expected/dataset.xml
+++
b/test/e2e/sql/src/test/resources/env/scenario/passthrough/data/expected/dataset.xml
@@ -49,8 +49,14 @@
<column name="id" type="numeric" />
<column name="val" type="varchar" />
</metadata>
+ <metadata data-nodes="expected_dataset.t_data_type_date">
+ <column name="id" type="numeric" />
+ <column name="creation_date" type="Date" />
+ <column name="update_date" type="datetime" />
+ </metadata>
<row data-node="expected_dataset.t_data_type_integer_unsigned"
values="1001, 18446744073709551615, 4294967295, 16777215, 65535, 255" />
<row data-node="expected_dataset.t_data_type_integer_unsigned"
values="1002, 0, 0, 0, 0, 0" />
<row data-node="expected_dataset.t_data_type_money" values="1001, 123" />
<row data-node="expected_dataset.t_data_type_money" values="1002, 456" />
+ <row data-node="expected_dataset.t_data_type_date" values="1, 2017-08-08,
2017-08-08 00:00:00" />
</dataset>
diff --git
a/test/e2e/sql/src/test/resources/env/scenario/passthrough/data/expected/init-sql/mysql/01-expected-init.sql
b/test/e2e/sql/src/test/resources/env/scenario/passthrough/data/expected/init-sql/mysql/01-expected-init.sql
index 12d63624cec..b3c3f764ffb 100644
---
a/test/e2e/sql/src/test/resources/env/scenario/passthrough/data/expected/init-sql/mysql/01-expected-init.sql
+++
b/test/e2e/sql/src/test/resources/env/scenario/passthrough/data/expected/init-sql/mysql/01-expected-init.sql
@@ -27,3 +27,4 @@ CREATE TABLE expected_dataset.t_data_type_floating_point (id
INT PRIMARY KEY, co
CREATE TABLE expected_dataset.t_with_generated_id (id INT AUTO_INCREMENT
PRIMARY KEY, val VARCHAR(100) NOT NULL);
CREATE TABLE expected_dataset.t_data_type_money (id INT PRIMARY KEY, val
NUMERIC(16, 2));
CREATE TABLE expected_dataset.t_data_type_bytea (id INT PRIMARY KEY, val BLOB
NOT NULL);
+CREATE TABLE expected_dataset.t_data_type_date (id INT PRIMARY KEY,
creation_date DATE NOT NULL, update_date DATETIME NOT NULL);
diff --git
a/test/e2e/sql/src/test/resources/env/scenario/passthrough/data/expected/init-sql/opengauss/01-expected-init.sql
b/test/e2e/sql/src/test/resources/env/scenario/passthrough/data/expected/init-sql/opengauss/01-expected-init.sql
index 579ac4a61f9..fba69f833f2 100644
---
a/test/e2e/sql/src/test/resources/env/scenario/passthrough/data/expected/init-sql/opengauss/01-expected-init.sql
+++
b/test/e2e/sql/src/test/resources/env/scenario/passthrough/data/expected/init-sql/opengauss/01-expected-init.sql
@@ -29,3 +29,4 @@ CREATE TABLE t_data_type_floating_point (id INT PRIMARY KEY,
col_float REAL NOT
CREATE TABLE t_with_generated_id (id SERIAL PRIMARY KEY, val VARCHAR NOT NULL);
CREATE TABLE t_data_type_money (id INT PRIMARY KEY, val money);
CREATE TABLE t_data_type_bytea (id INT PRIMARY KEY, val bytea NOT NULL);
+CREATE TABLE t_data_type_date (id INT PRIMARY KEY, creation_date DATE NOT
NULL, update_date TIMESTAMP NOT NULL);
diff --git
a/test/e2e/sql/src/test/resources/env/scenario/passthrough/data/expected/init-sql/postgresql/01-expected-init.sql
b/test/e2e/sql/src/test/resources/env/scenario/passthrough/data/expected/init-sql/postgresql/01-expected-init.sql
index 43118c9a4fd..c5c871b2811 100644
---
a/test/e2e/sql/src/test/resources/env/scenario/passthrough/data/expected/init-sql/postgresql/01-expected-init.sql
+++
b/test/e2e/sql/src/test/resources/env/scenario/passthrough/data/expected/init-sql/postgresql/01-expected-init.sql
@@ -29,3 +29,4 @@ CREATE TABLE t_data_type_floating_point (id INT PRIMARY KEY,
col_float REAL NOT
CREATE TABLE t_with_generated_id (id SERIAL PRIMARY KEY, val VARCHAR NOT NULL);
CREATE TABLE t_data_type_money (id INT PRIMARY KEY, val money);
CREATE TABLE t_data_type_bytea (id INT PRIMARY KEY, val bytea NOT NULL);
+CREATE TABLE t_data_type_date (id INT PRIMARY KEY, creation_date DATE NOT
NULL, update_date TIMESTAMP NOT NULL);
