You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by su...@apache.org on 2022/12/29 09:32:20 UTC
[shardingsphere] branch master updated: Add DML/DQL cases cover MySQL unsigned integer types (#23162)
This is an automated email from the ASF dual-hosted git repository.
sunnianjun 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 581d96fafe8 Add DML/DQL cases cover MySQL unsigned integer types (#23162)
581d96fafe8 is described below
commit 581d96fafe8bf61fd021cfb8d53766598cf9c627
Author: 吴伟杰 <wu...@apache.org>
AuthorDate: Thu Dec 29 17:32:13 2022 +0800
Add DML/DQL cases cover MySQL unsigned integer types (#23162)
* Add test cases cover MySQL unsigned integer types
* Add DQL cases cover MySQL unsigned integer types
* Fix DML cases
---
..._values_into_single_table_integer_unsigned.xml} | 22 ++++++++++------------
..._values_into_single_table_integer_unsigned.xml} | 22 ++++++++++------------
.../cases/dml/dml-integration-test-cases.xml | 7 +++++++
.../cases/dql/dql-integration-test-cases.xml | 5 +++++
.../scenario/passthrough/data/actual/dataset.xml | 10 ++++++++++
.../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 | 12 +++++++++++-
.../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 +
12 files changed, 59 insertions(+), 25 deletions(-)
diff --git a/test/e2e/suite/src/test/resources/env/scenario/passthrough/data/expected/dataset.xml b/test/e2e/suite/src/test/resources/cases/dml/dataset/passthrough/mysql/insert_max_values_into_single_table_integer_unsigned.xml
similarity index 53%
copy from test/e2e/suite/src/test/resources/env/scenario/passthrough/data/expected/dataset.xml
copy to test/e2e/suite/src/test/resources/cases/dml/dataset/passthrough/mysql/insert_max_values_into_single_table_integer_unsigned.xml
index 9c01f90ee85..b254c416adf 100644
--- a/test/e2e/suite/src/test/resources/env/scenario/passthrough/data/expected/dataset.xml
+++ b/test/e2e/suite/src/test/resources/cases/dml/dataset/passthrough/mysql/insert_max_values_into_single_table_integer_unsigned.xml
@@ -15,18 +15,16 @@
~ limitations under the License.
-->
-<dataset>
- <metadata data-nodes="expected_dataset.t_data_type_integer">
+<dataset update-count="1">
+ <metadata data-nodes="passthrough.t_data_type_integer_unsigned">
<column name="id" type="numeric" />
- <column name="col_bigint" type="numeric" />
- <column name="col_int" type="numeric" />
- <column name="col_mediumint" type="numeric" />
- <column name="col_smallint" type="numeric" />
- <column name="col_tinyint" type="numeric" />
- </metadata>
- <metadata data-nodes="passthrough.t_data_type_floating_point">
- <column name="id" type="numeric" />
- <column name="col_float" type="numeric" />
- <column name="col_double" type="numeric" />
+ <column name="col_bigint_unsigned" type="numeric" />
+ <column name="col_int_unsigned" type="numeric" />
+ <column name="col_mediumint_unsigned" type="numeric" />
+ <column name="col_smallint_unsigned" type="numeric" />
+ <column name="col_tinyint_unsigned" type="numeric" />
</metadata>
+ <row data-node="passthrough.t_data_type_integer_unsigned" values="1, 18446744073709551615, 4294967295, 16777215, 65535, 255" />
+ <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" />
</dataset>
diff --git a/test/e2e/suite/src/test/resources/env/scenario/passthrough/data/expected/dataset.xml b/test/e2e/suite/src/test/resources/cases/dml/dataset/passthrough/mysql/insert_min_values_into_single_table_integer_unsigned.xml
similarity index 55%
copy from test/e2e/suite/src/test/resources/env/scenario/passthrough/data/expected/dataset.xml
copy to test/e2e/suite/src/test/resources/cases/dml/dataset/passthrough/mysql/insert_min_values_into_single_table_integer_unsigned.xml
index 9c01f90ee85..8fa748532b8 100644
--- a/test/e2e/suite/src/test/resources/env/scenario/passthrough/data/expected/dataset.xml
+++ b/test/e2e/suite/src/test/resources/cases/dml/dataset/passthrough/mysql/insert_min_values_into_single_table_integer_unsigned.xml
@@ -15,18 +15,16 @@
~ limitations under the License.
-->
-<dataset>
- <metadata data-nodes="expected_dataset.t_data_type_integer">
+<dataset update-count="1">
+ <metadata data-nodes="passthrough.t_data_type_integer_unsigned">
<column name="id" type="numeric" />
- <column name="col_bigint" type="numeric" />
- <column name="col_int" type="numeric" />
- <column name="col_mediumint" type="numeric" />
- <column name="col_smallint" type="numeric" />
- <column name="col_tinyint" type="numeric" />
- </metadata>
- <metadata data-nodes="passthrough.t_data_type_floating_point">
- <column name="id" type="numeric" />
- <column name="col_float" type="numeric" />
- <column name="col_double" type="numeric" />
+ <column name="col_bigint_unsigned" type="numeric" />
+ <column name="col_int_unsigned" type="numeric" />
+ <column name="col_mediumint_unsigned" type="numeric" />
+ <column name="col_smallint_unsigned" type="numeric" />
+ <column name="col_tinyint_unsigned" type="numeric" />
</metadata>
+ <row data-node="passthrough.t_data_type_integer_unsigned" values="2, 0, 0, 0, 0, 0" />
+ <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" />
</dataset>
diff --git a/test/e2e/suite/src/test/resources/cases/dml/dml-integration-test-cases.xml b/test/e2e/suite/src/test/resources/cases/dml/dml-integration-test-cases.xml
index c96169bd520..dc361d56db7 100644
--- a/test/e2e/suite/src/test/resources/cases/dml/dml-integration-test-cases.xml
+++ b/test/e2e/suite/src/test/resources/cases/dml/dml-integration-test-cases.xml
@@ -297,6 +297,13 @@
<assertion parameters="3:int, -1:long, -1:int, -1:int, 0:smallint, -1:tinyint" expected-data-file="insert_negative_values_into_single_table_integer.xml" />
<!-- <assertion parameters="4:int, 0:int, 0:int, 0:int, -1:smallint, 0:int" expected-data-file="insert_negative_smallint_into_single_table_integer.xml" />-->
</test-case>
+
+ <test-case sql="INSERT INTO t_data_type_integer_unsigned (id, col_bigint_unsigned, col_int_unsigned, col_mediumint_unsigned, col_smallint_unsigned, col_tinyint_unsigned) values (?, ?, ?, ?, ?, ?)" db-types="MySQL" scenario-types="passthrough">
+ <!-- TODO Test unsigned with MySQL Connector/J 8.0.x client https://github.com/apache/shardingsphere/issues/14349 -->
+ <assertion parameters="1:int, 18446744073709551615:decimal, 4294967295:long, 16777215:int, 65535:int, 255:smallint" expected-data-file="insert_max_values_into_single_table_integer_unsigned.xml" />
+ <assertion parameters="2:int, 0:long, 0:int, 0:int, 0:smallint, 0:tinyint" expected-data-file="insert_min_values_into_single_table_integer_unsigned.xml" />
+ </test-case>
+
<test-case sql="INSERT INTO t_data_type_integer (id, col_bigint, col_int, col_mediumint, col_smallint, col_tinyint) values (?, ?, ?, ?, ?, ?)" db-types="PostgreSQL,openGauss" scenario-types="passthrough">
<assertion parameters="1:int, 9223372036854775807:long, 2147483647:int, 8388607:int, 32767:smallint, 127:tinyint" expected-data-file="insert_max_values_into_single_table_integer.xml" />
<assertion parameters="2:int, -9223372036854775808:long, -2147483648:int, -8388608:int, -32768:smallint, -128:tinyint" expected-data-file="insert_min_values_into_single_table_integer.xml" />
diff --git a/test/e2e/suite/src/test/resources/cases/dql/dql-integration-test-cases.xml b/test/e2e/suite/src/test/resources/cases/dql/dql-integration-test-cases.xml
index 2c6df90329c..2d300c7c86c 100644
--- a/test/e2e/suite/src/test/resources/cases/dql/dql-integration-test-cases.xml
+++ b/test/e2e/suite/src/test/resources/cases/dql/dql-integration-test-cases.xml
@@ -1218,4 +1218,9 @@
<test-case sql="SELECT country_id, COUNT(1) FROM t_merchant WHERE business_code LIKE '_1000018' GROUP BY country_id" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db,encrypt" scenario-comments="Test single table's LIKE operator underscore wildcard in select group by statement when use sharding feature.|Test encrypt table's LIKE operator underscore wildcard in select group by statement when use encrypt feature.">
<assertion expected-data-source-name="read_dataset" />
</test-case>
+
+ <test-case sql="SELECT * FROM t_data_type_integer_unsigned" db-types="MySQL" scenario-types="passthrough" scenario-comments="Test ShardingSphere-Proxy MySQL compatibility for unsigned integer data types">
+ <assertion expected-data-source-name="expected_dataset" />
+ </test-case>
+
</integration-test-cases>
diff --git a/test/e2e/suite/src/test/resources/env/scenario/passthrough/data/actual/dataset.xml b/test/e2e/suite/src/test/resources/env/scenario/passthrough/data/actual/dataset.xml
index b3437a47e41..5bc3deec6b0 100644
--- a/test/e2e/suite/src/test/resources/env/scenario/passthrough/data/actual/dataset.xml
+++ b/test/e2e/suite/src/test/resources/env/scenario/passthrough/data/actual/dataset.xml
@@ -24,9 +24,19 @@
<column name="col_smallint" type="numeric" />
<column name="col_tinyint" type="numeric" />
</metadata>
+ <metadata data-nodes="passthrough.t_data_type_integer_unsigned">
+ <column name="id" type="numeric" />
+ <column name="col_bigint_unsigned" type="decimal" />
+ <column name="col_int_unsigned" type="numeric" />
+ <column name="col_mediumint_unsigned" type="numeric" />
+ <column name="col_smallint_unsigned" type="numeric" />
+ <column name="col_tinyint_unsigned" type="numeric" />
+ </metadata>
<metadata data-nodes="passthrough.t_data_type_floating_point">
<column name="id" type="numeric" />
<column name="col_float" type="numeric" />
<column name="col_double" type="numeric" />
</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" />
</dataset>
diff --git a/test/e2e/suite/src/test/resources/env/scenario/passthrough/data/actual/init-sql/mysql/01-actual-init.sql b/test/e2e/suite/src/test/resources/env/scenario/passthrough/data/actual/init-sql/mysql/01-actual-init.sql
index e0d764eaa0d..a636629c5ba 100644
--- a/test/e2e/suite/src/test/resources/env/scenario/passthrough/data/actual/init-sql/mysql/01-actual-init.sql
+++ b/test/e2e/suite/src/test/resources/env/scenario/passthrough/data/actual/init-sql/mysql/01-actual-init.sql
@@ -22,4 +22,5 @@ DROP DATABASE IF EXISTS passthrough;
CREATE DATABASE passthrough;
CREATE TABLE passthrough.t_data_type_integer (id INT PRIMARY KEY, col_bigint BIGINT NOT NULL, col_int INT NOT NULL, col_mediumint MEDIUMINT NOT NULL, col_smallint SMALLINT NOT NULL, col_tinyint TINYINT NOT NULL);
+CREATE TABLE passthrough.t_data_type_integer_unsigned (id INT PRIMARY KEY, col_bigint_unsigned BIGINT UNSIGNED NOT NULL, col_int_unsigned INT UNSIGNED NOT NULL, col_mediumint_unsigned MEDIUMINT UNSIGNED NOT NULL, col_smallint_unsigned SMALLINT UNSIGNED NOT NULL, col_tinyint_unsigned TINYINT UNSIGNED NOT NULL);
CREATE TABLE passthrough.t_data_type_floating_point (id INT PRIMARY KEY, col_float REAL NOT NULL, col_double DOUBLE PRECISION NOT NULL);
diff --git a/test/e2e/suite/src/test/resources/env/scenario/passthrough/data/actual/init-sql/opengauss/01-actual-init.sql b/test/e2e/suite/src/test/resources/env/scenario/passthrough/data/actual/init-sql/opengauss/01-actual-init.sql
index 102dc1a3bfb..f38cd5a206b 100644
--- a/test/e2e/suite/src/test/resources/env/scenario/passthrough/data/actual/init-sql/opengauss/01-actual-init.sql
+++ b/test/e2e/suite/src/test/resources/env/scenario/passthrough/data/actual/init-sql/opengauss/01-actual-init.sql
@@ -23,4 +23,5 @@ GRANT ALL PRIVILEGES ON DATABASE passthrough TO test_user;
DROP TABLE IF EXISTS t_data_type_integer;
CREATE TABLE t_data_type_integer (id INT PRIMARY KEY, col_bigint BIGINT NOT NULL, col_int INT NOT NULL, col_mediumint INT4 NOT NULL, col_smallint SMALLINT NOT NULL, col_tinyint INT2 NOT NULL);
+CREATE TABLE t_data_type_integer_unsigned (id INT PRIMARY KEY, col_bigint_unsigned DECIMAL NOT NULL, col_int_unsigned DECIMAL NOT NULL, col_mediumint_unsigned DECIMAL NOT NULL, col_smallint_unsigned DECIMAL NOT NULL, col_tinyint_unsigned DECIMAL NOT NULL);
CREATE TABLE t_data_type_floating_point (id INT PRIMARY KEY, col_float REAL NOT NULL, col_double DOUBLE PRECISION NOT NULL);
diff --git a/test/e2e/suite/src/test/resources/env/scenario/passthrough/data/actual/init-sql/postgresql/01-actual-init.sql b/test/e2e/suite/src/test/resources/env/scenario/passthrough/data/actual/init-sql/postgresql/01-actual-init.sql
index 102dc1a3bfb..f38cd5a206b 100644
--- a/test/e2e/suite/src/test/resources/env/scenario/passthrough/data/actual/init-sql/postgresql/01-actual-init.sql
+++ b/test/e2e/suite/src/test/resources/env/scenario/passthrough/data/actual/init-sql/postgresql/01-actual-init.sql
@@ -23,4 +23,5 @@ GRANT ALL PRIVILEGES ON DATABASE passthrough TO test_user;
DROP TABLE IF EXISTS t_data_type_integer;
CREATE TABLE t_data_type_integer (id INT PRIMARY KEY, col_bigint BIGINT NOT NULL, col_int INT NOT NULL, col_mediumint INT4 NOT NULL, col_smallint SMALLINT NOT NULL, col_tinyint INT2 NOT NULL);
+CREATE TABLE t_data_type_integer_unsigned (id INT PRIMARY KEY, col_bigint_unsigned DECIMAL NOT NULL, col_int_unsigned DECIMAL NOT NULL, col_mediumint_unsigned DECIMAL NOT NULL, col_smallint_unsigned DECIMAL NOT NULL, col_tinyint_unsigned DECIMAL NOT NULL);
CREATE TABLE t_data_type_floating_point (id INT PRIMARY KEY, col_float REAL NOT NULL, col_double DOUBLE PRECISION NOT NULL);
diff --git a/test/e2e/suite/src/test/resources/env/scenario/passthrough/data/expected/dataset.xml b/test/e2e/suite/src/test/resources/env/scenario/passthrough/data/expected/dataset.xml
index 9c01f90ee85..0998b8b34d3 100644
--- a/test/e2e/suite/src/test/resources/env/scenario/passthrough/data/expected/dataset.xml
+++ b/test/e2e/suite/src/test/resources/env/scenario/passthrough/data/expected/dataset.xml
@@ -24,9 +24,19 @@
<column name="col_smallint" type="numeric" />
<column name="col_tinyint" type="numeric" />
</metadata>
- <metadata data-nodes="passthrough.t_data_type_floating_point">
+ <metadata data-nodes="expected_dataset.t_data_type_integer_unsigned">
+ <column name="id" type="numeric" />
+ <column name="col_bigint_unsigned" type="decimal" />
+ <column name="col_int_unsigned" type="numeric" />
+ <column name="col_mediumint_unsigned" type="numeric" />
+ <column name="col_smallint_unsigned" type="numeric" />
+ <column name="col_tinyint_unsigned" type="numeric" />
+ </metadata>
+ <metadata data-nodes="expected_dataset.t_data_type_floating_point">
<column name="id" type="numeric" />
<column name="col_float" type="numeric" />
<column name="col_double" type="numeric" />
</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" />
</dataset>
diff --git a/test/e2e/suite/src/test/resources/env/scenario/passthrough/data/expected/init-sql/mysql/01-expected-init.sql b/test/e2e/suite/src/test/resources/env/scenario/passthrough/data/expected/init-sql/mysql/01-expected-init.sql
index 0225046eb30..eb5100438ac 100644
--- a/test/e2e/suite/src/test/resources/env/scenario/passthrough/data/expected/init-sql/mysql/01-expected-init.sql
+++ b/test/e2e/suite/src/test/resources/env/scenario/passthrough/data/expected/init-sql/mysql/01-expected-init.sql
@@ -22,4 +22,5 @@ DROP DATABASE IF EXISTS expected_dataset;
CREATE DATABASE expected_dataset;
CREATE TABLE expected_dataset.t_data_type_integer (id INT PRIMARY KEY, col_bigint BIGINT NOT NULL, col_int INT NOT NULL, col_mediumint MEDIUMINT NOT NULL, col_smallint SMALLINT NOT NULL, col_tinyint TINYINT NOT NULL);
+CREATE TABLE expected_dataset.t_data_type_integer_unsigned (id INT PRIMARY KEY, col_bigint_unsigned BIGINT UNSIGNED NOT NULL, col_int_unsigned INT UNSIGNED NOT NULL, col_mediumint_unsigned MEDIUMINT UNSIGNED NOT NULL, col_smallint_unsigned SMALLINT UNSIGNED NOT NULL, col_tinyint_unsigned TINYINT UNSIGNED NOT NULL);
CREATE TABLE expected_dataset.t_data_type_floating_point (id INT PRIMARY KEY, col_float REAL NOT NULL, col_double DOUBLE PRECISION NOT NULL);
diff --git a/test/e2e/suite/src/test/resources/env/scenario/passthrough/data/expected/init-sql/opengauss/01-expected-init.sql b/test/e2e/suite/src/test/resources/env/scenario/passthrough/data/expected/init-sql/opengauss/01-expected-init.sql
index 57aefdfc7e5..ccb682b9811 100644
--- a/test/e2e/suite/src/test/resources/env/scenario/passthrough/data/expected/init-sql/opengauss/01-expected-init.sql
+++ b/test/e2e/suite/src/test/resources/env/scenario/passthrough/data/expected/init-sql/opengauss/01-expected-init.sql
@@ -24,4 +24,5 @@ GRANT ALL PRIVILEGES ON DATABASE expected_dataset TO test_user;
DROP TABLE IF EXISTS t_data_type_integer;
CREATE TABLE t_data_type_integer (id INT PRIMARY KEY, col_bigint BIGINT NOT NULL, col_int INT NOT NULL, col_mediumint INT4 NOT NULL, col_smallint SMALLINT NOT NULL, col_tinyint INT2 NOT NULL);
+CREATE TABLE t_data_type_integer_unsigned (id INT PRIMARY KEY, col_bigint_unsigned DECIMAL NOT NULL, col_int_unsigned DECIMAL NOT NULL, col_mediumint_unsigned DECIMAL NOT NULL, col_smallint_unsigned DECIMAL NOT NULL, col_tinyint_unsigned DECIMAL NOT NULL);
CREATE TABLE t_data_type_floating_point (id INT PRIMARY KEY, col_float REAL NOT NULL, col_double DOUBLE PRECISION NOT NULL);
diff --git a/test/e2e/suite/src/test/resources/env/scenario/passthrough/data/expected/init-sql/postgresql/01-expected-init.sql b/test/e2e/suite/src/test/resources/env/scenario/passthrough/data/expected/init-sql/postgresql/01-expected-init.sql
index 9699ff41d83..62a718ca9c8 100644
--- a/test/e2e/suite/src/test/resources/env/scenario/passthrough/data/expected/init-sql/postgresql/01-expected-init.sql
+++ b/test/e2e/suite/src/test/resources/env/scenario/passthrough/data/expected/init-sql/postgresql/01-expected-init.sql
@@ -24,4 +24,5 @@ GRANT ALL PRIVILEGES ON DATABASE expected_dataset TO test_user;
DROP TABLE IF EXISTS t_data_type_integer;
CREATE TABLE t_data_type_integer (id INT PRIMARY KEY, col_bigint BIGINT NOT NULL, col_int INT NOT NULL, col_mediumint INT4 NOT NULL, col_smallint SMALLINT NOT NULL, col_tinyint INT2 NOT NULL);
+CREATE TABLE t_data_type_integer_unsigned (id INT PRIMARY KEY, col_bigint_unsigned DECIMAL NOT NULL, col_int_unsigned DECIMAL NOT NULL, col_mediumint_unsigned DECIMAL NOT NULL, col_smallint_unsigned DECIMAL NOT NULL, col_tinyint_unsigned DECIMAL NOT NULL);
CREATE TABLE t_data_type_floating_point (id INT PRIMARY KEY, col_float REAL NOT NULL, col_double DOUBLE PRECISION NOT NULL);