[FLINK-15141] Using decimal type in a sink table, the result returns a not match ValidationException

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Duplicate
• Affects Version/s: 1.10.0
• Component/s: Table SQL / Client
• Assignee: Jark Wu
• Reporter: xiaojin.wy
• Created: 09/Dec/19 07:25
• Updated: 17/Dec/19 13:30
• Resolved: 17/Dec/19 13:30

Description

The planner what I used is blink.

The source table is:
CREATE TABLE `aggtest` (
    a smallint,
    b float
) WITH (
    'format.field-delimiter'='|',
    'connector.type'='filesystem',
    'format.derive-schema'='true',
    'connector.path'='hdfs://zthdev/defender_test_data/daily/test_aggregates/sources/aggtest.csv',
    'format.type'='csv'
); 
 

The sink table is:
CREATE TABLE `agg_decimal_res` (
    avg_107_943 DECIMAL(10, 3)
) WITH (
    'format.field-delimiter'='|',
    'connector.type'='filesystem',
    'format.derive-schema'='true',
    'connector.path'='hdfs://zthdev/defender_test_data/daily/test_aggregates/test_aggregates__test_avg_cast_batch/results/agg_decimal_res.csv',
    'format.type'='csv'
);
 

The sql is:

INSERT INTO agg_decimal_res SELECT CAST(avg(b) AS numeric(10,3)) AS avg_107_943 FROM aggtest;

 

After execute the sql, there will be a exception appear, just like this:

[INFO] Submitting SQL update statement to the cluster...
[ERROR] Could not execute SQL statement. Reason:
org.apache.flink.table.api.ValidationException: Field types of query result and registered TableSink `default_catalog`.`default_database`.`agg_decimal_res1` do not match.
Query result schema: [avg_107_943: DECIMAL(10, 3)]
TableSink schema: [avg_107_943: DECIMAL(38, 18)]

 

Issue Links

• is duplicated by: FLINK-15124 types with precision can't be executed in sql client with blink planner
• is related to: FLINK-15124 types with precision can't be executed in sql client with blink planner