This is an automated email from the ASF dual-hosted git repository. morningman pushed a commit to branch branch-1.2-lts in repository https://gitbox.apache.org/repos/asf/doris.git
commit d8c29836d80015fa9b321599329451750932fcc3 Author: Tiewei Fang <[email protected]> AuthorDate: Sun Feb 26 09:05:41 2023 +0800 [BugFix](oracle-catalog) Modify the doris data type mapping of oracle `NUMBER(p,s)` type (#17051) The data type `NUMBER(p,s)` of oracle has some different of doris decimal type in semantics. For Oracle Number(p,s) type： 1. if s<0 , it means this is an Interger. This `NUMBER(p,s)` has (p+|s| ) significant digit, and rounding will be performed at s position. eg: if we insert 1234567 into `NUMBER(5,-2)` type, then the oracle will store 1234500. In this case, Doris will use int type (`TINYINT/SMALLINT/INT/.../LARGEINT`). 2. if s>=0 && s<p , it just like doris Decimal(p,s) behavior. 3. if s>=0 && s>p, it means this is a decimal(like 0.xxxxx). p represents how many digits can be left to the left after the decimal point, the figure after the decimal point s will be rounded. eg: we can not insert 0.0123456 into `NUMBER(5,7)` type, because there must be two zeros on the right side of the decimal point, we can insert 0.0012345 into `NUMBER(5,7)` type. In this case, Doris will use `DECIMAL(s,s)` 4. if we don't specify p and s for `NUMBER(p,s)` like `NUMBER`, the p and s of `NUMBER` are uncertain. In this case, doris can not determine p and s, so doris can not determine data type. --- .../docker-compose/oracle/init/03-create-table.sql | 22 ++++++ .../docker-compose/oracle/init/04-insert.sql | 21 ++++++ .../org/apache/doris/external/jdbc/JdbcClient.java | 84 +++++++++++----------- .../apache/doris/planner/StreamLoadPlanner.java | 3 +- .../jdbc_catalog_p0/test_oracle_jdbc_catalog.out | 25 +++++++ .../test_oracle_jdbc_catalog.groovy | 4 ++ 6 files changed, 117 insertions(+), 42 deletions(-) diff --git a/docker/thirdparties/docker-compose/oracle/init/03-create-table.sql b/docker/thirdparties/docker-compose/oracle/init/03-create-table.sql index d2d8d6af7e..03aa1d5114 100644 --- a/docker/thirdparties/docker-compose/oracle/init/03-create-table.sql +++ b/docker/thirdparties/docker-compose/oracle/init/03-create-table.sql @@ -84,3 +84,25 @@ id varchar2(128), name varchar2(128), age number(5) ); + +create table doris_test.test_number( + id number(11) not null primary key, + num1 NUMBER(5,2), + num2 NUMBER(5, -2), + num4 NUMBER(5,7) +); + +create table doris_test.test_number2( + id number(11) not null primary key, + num1 NUMBER(38, -5) +); + +create table doris_test.test_number3 ( + id number(11) not null primary key, + num1 NUMBER(38, -84) +); + +create table doris_test.test_number4 ( + id number(11) not null primary key, + num1 NUMBER(5,-7) +); diff --git a/docker/thirdparties/docker-compose/oracle/init/04-insert.sql b/docker/thirdparties/docker-compose/oracle/init/04-insert.sql index 0c474e8f20..d4638d0348 100644 --- a/docker/thirdparties/docker-compose/oracle/init/04-insert.sql +++ b/docker/thirdparties/docker-compose/oracle/init/04-insert.sql @@ -53,4 +53,25 @@ insert into doris_test.test_timestamp (id, t4) values (5, to_timestamp_tz('20191 insert into doris_test.test_timestamp (id, t5) values (6, interval '11' year); insert into doris_test.test_timestamp (id, t5) values (7, interval '223-9' year(3) to month); insert into doris_test.test_timestamp (id, t6) values (8, interval '12 10:23:01.1234568' day to second); + +insert into doris_test.test_number values (1, 123.45, 12345, 0.0012345); +insert into doris_test.test_number values (2, 123.45, 12345, 0.0099999); +insert into doris_test.test_number values (3, 123.456, 123456.12, 0.00123456); +insert into doris_test.test_number values (4, 12.3456, 1234567, 0.001234567); +insert into doris_test.test_number values (5, 123.56, 9999899, 0.009999899); + +insert into doris_test.test_number2 values (1, 12345678901234567890123456789012345678); +insert into doris_test.test_number2 values (2, 99999999999999999999999999999999999999); +insert into doris_test.test_number2 values (3, 999999999999999999999999999999999999999); +insert into doris_test.test_number2 values (4, 12345678); +insert into doris_test.test_number2 values (5, 123.123); +insert into doris_test.test_number2 values (6, 0.999999999999); + +insert into doris_test.test_number3 values (1, 9999); +insert into doris_test.test_number3 values (2, 12345678901234567890123456789012345678); +insert into doris_test.test_number3 values (3, 99999999999999999999999999999999999999); +insert into doris_test.test_number3 values (4, 0.99999); + +insert into doris_test.test_number4 values (1, 12345678); +insert into doris_test.test_number4 values (2, 123456789012); commit; diff --git a/fe/fe-core/src/main/java/org/apache/doris/external/jdbc/JdbcClient.java b/fe/fe-core/src/main/java/org/apache/doris/external/jdbc/JdbcClient.java index 98f8b0581a..6337e5651f 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/external/jdbc/JdbcClient.java +++ b/fe/fe-core/src/main/java/org/apache/doris/external/jdbc/JdbcClient.java @@ -364,14 +364,7 @@ public class JdbcClient { case "DECIMAL": int precision = fieldSchema.getColumnSize() + 1; int scale = fieldSchema.getDecimalDigits(); - if (precision <= ScalarType.MAX_DECIMAL128_PRECISION) { - if (!Config.enable_decimal_conversion && precision > ScalarType.MAX_DECIMALV2_PRECISION) { - return ScalarType.createStringType(); - } - return ScalarType.createDecimalType(precision, scale); - } else { - return ScalarType.createStringType(); - } + return createDecimalOrStringType(precision, scale); default: throw new JdbcClientException("Unknown UNSIGNED type of mysql, type: [" + mysqlType + "]"); } @@ -406,14 +399,7 @@ public class JdbcClient { case "DECIMALV3": // for jdbc catalog connecting Doris database int precision = fieldSchema.getColumnSize(); int scale = fieldSchema.getDecimalDigits(); - if (precision <= ScalarType.MAX_DECIMAL128_PRECISION) { - if (!Config.enable_decimal_conversion && precision > ScalarType.MAX_DECIMALV2_PRECISION) { - return ScalarType.createStringType(); - } - return ScalarType.createDecimalType(precision, scale); - } else { - return ScalarType.createStringType(); - } + return createDecimalOrStringType(precision, scale); case "CHAR": ScalarType charType = ScalarType.createType(PrimitiveType.CHAR); charType.setLength(fieldSchema.columnSize); @@ -460,14 +446,7 @@ public class JdbcClient { case "numeric": { int precision = fieldSchema.getColumnSize(); int scale = fieldSchema.getDecimalDigits(); - if (precision <= ScalarType.MAX_DECIMAL128_PRECISION) { - if (!Config.enable_decimal_conversion && precision > ScalarType.MAX_DECIMALV2_PRECISION) { - return ScalarType.createStringType(); - } - return ScalarType.createDecimalType(precision, scale); - } else { - return ScalarType.createStringType(); - } + return createDecimalOrStringType(precision, scale); } case "float4": return Type.FLOAT; @@ -523,14 +502,7 @@ public class JdbcClient { String[] accuracy = ckType.substring(8, ckType.length() - 1).split(", "); int precision = Integer.parseInt(accuracy[0]); int scale = Integer.parseInt(accuracy[1]); - if (precision <= ScalarType.MAX_DECIMAL128_PRECISION) { - if (!Config.enable_decimal_conversion && precision > ScalarType.MAX_DECIMALV2_PRECISION) { - return ScalarType.createStringType(); - } - return ScalarType.createDecimalType(precision, scale); - } else { - return ScalarType.createStringType(); - } + return createDecimalOrStringType(precision, scale); } else if ("String".contains(ckType) || ckType.startsWith("Enum") || ckType.startsWith("IPv") || "UUID".contains(ckType) || ckType.startsWith("FixedString")) { @@ -583,10 +555,29 @@ public class JdbcClient { return ScalarType.getDefaultDateType(Type.DATETIME); } switch (oracleType) { + /** + * The data type NUMBER(p,s) of oracle has some different of doris decimal type in semantics. + * For Oracle Number(p,s) type: + * 1. if s<0 , it means this is an Interger. + * This NUMBER(p,s) has (p+|s| ) significant digit, and rounding will be performed at s position. + * eg: if we insert 1234567 into NUMBER(5,-2) type, then the oracle will store 1234500. + * In this case, Doris will use INT type (TINYINT/SMALLINT/INT/.../LARGEINT). + * 2. if s>=0 && s<p , it just like doris Decimal(p,s) behavior. + * 3. if s>=0 && s>p, it means this is a decimal(like 0.xxxxx). + * p represents how many digits can be left to the left after the decimal point, + * the figure after the decimal point s will be rounded. + * eg: we can not insert 0.0123456 into NUMBER(5,7) type, + * because there must be two zeros on the right side of the decimal point, + * we can insert 0.0012345 into NUMBER(5,7) type. + * In this case, Doris will use DECIMAL(s,s) + * 4. if we don't specify p and s for NUMBER(p,s), just NUMBER, the p and s of NUMBER are uncertain. + * In this case, doris can not determine p and s, so doris can not determine data type. + */ case "NUMBER": int precision = fieldSchema.getColumnSize(); int scale = fieldSchema.getDecimalDigits(); - if (scale == 0) { + if (scale <= 0) { + precision -= scale; if (precision < 3) { return Type.TINYINT; } else if (precision < 5) { @@ -596,18 +587,17 @@ public class JdbcClient { } else if (precision < 19) { return Type.BIGINT; } else if (precision < 39) { + // LARGEINT supports up to 38 numbers. return Type.LARGEINT; - } - return ScalarType.createStringType(); - } - if (precision <= ScalarType.MAX_DECIMAL128_PRECISION) { - if (!Config.enable_decimal_conversion && precision > ScalarType.MAX_DECIMALV2_PRECISION) { + } else { return ScalarType.createStringType(); } - return ScalarType.createDecimalType(precision, scale); - } else { - return ScalarType.createStringType(); } + // scale > 0 + if (precision < scale) { + precision = scale; + } + return createDecimalOrStringType(precision, scale); case "FLOAT": return Type.DOUBLE; case "DATE": @@ -678,6 +668,18 @@ public class JdbcClient { } } + private Type createDecimalOrStringType(int precision, int scale) { + if (precision <= ScalarType.MAX_DECIMAL128_PRECISION) { + if (!Config.enable_decimal_conversion && (precision > ScalarType.MAX_DECIMALV2_PRECISION + || scale > ScalarType.MAX_DECIMALV2_SCALE)) { + return ScalarType.createStringType(); + } + return ScalarType.createDecimalType(precision, scale); + } + return ScalarType.createStringType(); + } + + public List<Column> getColumnsFromJdbc(String dbName, String tableName) { List<JdbcFieldSchema> jdbcTableSchema = getJdbcColumnsInfo(dbName, tableName); List<Column> dorisTableSchema = Lists.newArrayListWithCapacity(jdbcTableSchema.size()); diff --git a/fe/fe-core/src/main/java/org/apache/doris/planner/StreamLoadPlanner.java b/fe/fe-core/src/main/java/org/apache/doris/planner/StreamLoadPlanner.java index 7af5ec7f2f..07ff6caf64 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/planner/StreamLoadPlanner.java +++ b/fe/fe-core/src/main/java/org/apache/doris/planner/StreamLoadPlanner.java @@ -191,7 +191,8 @@ public class StreamLoadPlanner { fileStatus.setSize(-1); // must set to -1, means stream. } fileScanNode.setLoadInfo(loadId, taskInfo.getTxnId(), destTable, BrokerDesc.createForStreamLoad(), - fileGroup, fileStatus, taskInfo.isStrictMode(), taskInfo.getFileType(), taskInfo.getHiddenColumns()); + fileGroup, fileStatus, taskInfo.isStrictMode(), taskInfo.getFileType(), + taskInfo.getHiddenColumns()); scanNode = fileScanNode; } else { scanNode = new StreamLoadScanNode(loadId, new PlanNodeId(0), scanTupleDesc, destTable, taskInfo); diff --git a/regression-test/data/jdbc_catalog_p0/test_oracle_jdbc_catalog.out b/regression-test/data/jdbc_catalog_p0/test_oracle_jdbc_catalog.out index cc5c0f50f2..67595ff0a9 100644 --- a/regression-test/data/jdbc_catalog_p0/test_oracle_jdbc_catalog.out +++ b/regression-test/data/jdbc_catalog_p0/test_oracle_jdbc_catalog.out @@ -41,6 +41,31 @@ 7 \N \N \N \N 223-9 \N 8 \N \N \N \N \N 12 10:23:1.123457 +-- !test7 -- +1 123.45 12300 0.0012345 +2 123.45 12300 0.0099999 +3 123.46 123500 0.0012346 +4 12.35 1234600 0.0012346 +5 123.56 9999900 0.0099999 + +-- !test8 -- +1 12345678901234567890123456789012300000 +2 100000000000000000000000000000000000000 +3 1000000000000000000000000000000000000000 +4 12300000 +5 0 +6 0 + +-- !test9 -- +1 0 +2 0 +3 0 +4 0 + +-- !test10 -- +1 10000000 +2 123460000000 + -- !test_insert1 -- doris1 18 diff --git a/regression-test/suites/jdbc_catalog_p0/test_oracle_jdbc_catalog.groovy b/regression-test/suites/jdbc_catalog_p0/test_oracle_jdbc_catalog.groovy index 7ddcce6c10..078e97df4d 100644 --- a/regression-test/suites/jdbc_catalog_p0/test_oracle_jdbc_catalog.groovy +++ b/regression-test/suites/jdbc_catalog_p0/test_oracle_jdbc_catalog.groovy @@ -64,6 +64,10 @@ suite("test_oracle_jdbc_catalog", "p0") { order_qt_test3 """ select * from TEST_INT order by ID; """ order_qt_test5 """ select * from TEST_DATE order by ID; """ order_qt_test6 """ select * from TEST_TIMESTAMP order by ID; """ + order_qt_test7 """ select * from TEST_NUMBER order by ID; """ + order_qt_test8 """ select * from TEST_NUMBER2 order by ID; """ + order_qt_test9 """ select * from TEST_NUMBER3 order by ID; """ + order_qt_test10 """ select * from TEST_NUMBER4 order by ID; """ // The result of TEST_RAW will change // So instead of qt, we're using sql here. --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
