[
https://issues.apache.org/jira/browse/CALCITE-5382?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17635761#comment-17635761
]
Jiajun Xie edited comment on CALCITE-5382 at 11/18/22 9:43 AM:
---------------------------------------------------------------
[~lasyard] I mock your table
{code:java}
final MockTable t3 = MockTable.create(this, tSchema, "T3", false, 7.0,
null);
t3.addColumn("t3_int", f.intType, true);
t3.addColumn("t3_varchar", f.varcharType);
t3.addColumn("t3_double",
typeFactory.createTypeWithNullability(f.doubleType, true));
registerTable(t3);
{code}
run the test
{code:java}
@Test void testInsertValuesNullQuerySourceCoercion() {
final String sql = "insert into T3 values\n"
+ "(1, 'Alice', 1.0),\n"
+ "(2, 'Betty', null),\n"
+ "(3, 'Cindy', 3.0)";
sql(sql).ok();
}
{code}
get the validated sql node
{code:java}
INSERT INTO `CATALOG`.`SALES`.`T3`
VALUES ROW(1, 'Alice', CAST(1.0 AS DOUBLE)),
ROW(2, 'Betty', NULL),
ROW(3, 'Cindy', CAST(3.0 AS DOUBLE)){code}
So it is duplicate of CALCITE-5141
was (Author: jiajunbernoulli):
I mock your table
{code:java}
final MockTable t3 = MockTable.create(this, tSchema, "T3", false, 7.0,
null);
t3.addColumn("t3_int", f.intType, true);
t3.addColumn("t3_varchar", f.varcharType);
t3.addColumn("t3_double",
typeFactory.createTypeWithNullability(f.doubleType, true));
registerTable(t3);
{code}
run the test
{code:java}
@Test void testInsertValuesNullQuerySourceCoercion() {
final String sql = "insert into T3 values\n"
+ "(1, 'Alice', 1.0),\n"
+ "(2, 'Betty', null),\n"
+ "(3, 'Cindy', 3.0)";
sql(sql).ok();
}
{code}
get the validated sql node
{code:java}
INSERT INTO `CATALOG`.`SALES`.`T3`
VALUES ROW(1, 'Alice', CAST(1.0 AS DOUBLE)),
ROW(2, 'Betty', NULL),
ROW(3, 'Cindy', CAST(3.0 AS DOUBLE)){code}
So it is duplicate of
[CALCITE-5141|https://issues.apache.org/jira/browse/CALCITE-5141]
> Values are not casted to correct type in insertion
> --------------------------------------------------
>
> Key: CALCITE-5382
> URL: https://issues.apache.org/jira/browse/CALCITE-5382
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.32.0
> Reporter: Yueguang Jiao
> Priority: Major
>
> Assume we have created a table by
> {code:sql}
> create table test (
> id int not null,
> name varchar not null,
> score double,
> primary key(id)
> )
> {code}
> Note the column 'score' is nullable. Then try to parse and validate a
> statement as
> {code:sql}
> insert into test values
> (1, 'Alice', 1.0),
> (2, 'Betty', null),
> (3, 'Cindy', 3.0)
> {code}
> Before validation, the 'SqlNode' is
> {code:sql}
> INSERT INTO `test`
> VALUES ROW(1, 'Alice', 1.0),
> ROW(2, 'Betty', NULL),
> ROW(3, 'Cindy', 3.0)
> {code}
> After validation, the `SqlNode` is
> {code:sql}
> VALUES ROW(1, 'Alice', CAST(1.0 AS DOUBLE)),
> ROW(2, 'Betty', NULL),
> ROW(3, 'Cindy', 3.0)
> {code}
> The 'score' column in the first row is casted, but not the one in the third
> row. I think this is not correct.
> This is tested by the following Java code:
> {code:java}
> SqlParser parser = SqlParser.create(sql);
> SqlNode sqlNode = parser.parseQuery();
> log.debug("sqlNode = {}", sqlNode);
> SqlValidator validator = SqlValidatorUtil.newValidator(
> SqlStdOperatorTable.instance(),
> catalogReader,
> new JavaTypeFactoryImpl(),
> SqlValidator.Config.DEFAULT
> );
> sqlNode = validator.validate(sqlNode);
> log.debug("sqlNode = {}", sqlNode);
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
