[
https://issues.apache.org/jira/browse/CALCITE-7829?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18120753#comment-18120753
]
Tomasz Gawęda commented on CALCITE-7829:
----------------------------------------
{code:java}
package reproducer;
import org.apache.calcite.adapter.java.AbstractQueryableTable;
import org.apache.calcite.jdbc.CalciteConnection;
import org.apache.calcite.linq4j.Enumerator;
import org.apache.calcite.linq4j.Linq4j;
import org.apache.calcite.linq4j.QueryProvider;
import org.apache.calcite.linq4j.Queryable;
import org.apache.calcite.plan.RelOptCluster;
import org.apache.calcite.plan.RelOptTable;
import org.apache.calcite.plan.RelOptUtil;
import org.apache.calcite.prepare.Prepare;
import org.apache.calcite.rel.RelNode;
import org.apache.calcite.rel.core.Project;
import org.apache.calcite.rel.core.TableModify;
import org.apache.calcite.rel.core.Values;
import org.apache.calcite.rel.logical.LogicalTableModify;
import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.rel.type.RelDataTypeFactory;
import org.apache.calcite.rex.RexCall;
import org.apache.calcite.rex.RexInputRef;
import org.apache.calcite.rex.RexNode;
import org.apache.calcite.schema.ModifiableTable;
import org.apache.calcite.schema.SchemaPlus;
import org.apache.calcite.schema.impl.AbstractTableQueryable;
import org.apache.calcite.sql.SqlKind;
import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.type.SqlTypeName;
import org.apache.calcite.sql2rel.SqlToRelConverter;
import org.apache.calcite.tools.Frameworks;
import org.apache.calcite.tools.Planner;
import org.junit.jupiter.api.Test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import static org.junit.jupiter.api.Assertions.assertAll;
import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertInstanceOf;
import static org.junit.jupiter.api.Assertions.assertNotNull;
class TimeAssignmentTest {
private static final String SQL =
"INSERT INTO T (ID, PAYLOAD) VALUES (0, CAST('01:42:00' AS TIME))";
@Test
void insertTimeIntoObjectShouldPreserveTemporalType() throws Exception {
ObjectTable table = new ObjectTable();
try (Connection connection =
DriverManager.getConnection("jdbc:calcite:");
Statement statement = connection.createStatement()) {
CalciteConnection calcite =
connection.unwrap(CalciteConnection.class);
SchemaPlus schema = calcite.getRootSchema();
schema.add("T", table);
assertEquals(Object.class, calcite.getTypeFactory().getJavaClass(
table.getRowType(calcite.getTypeFactory()).getFieldList().get(1).getType()));
// Isolate SQL-to-rel conversion, before any physical optimizer
rules.
RexNode withoutSimplification = assignmentSource(convert(schema,
false));
RexNode withSimplification = assignmentSource(convert(schema,
true));
System.out.println("Without project simplification: " +
withoutSimplification
+ " : " + withoutSimplification.getType());
System.out.println("With project simplification: " +
withSimplification
+ " : " + withSimplification.getType());
assertEquals(SqlTypeName.TIME,
withoutSimplification.getType().getSqlTypeName());
// Execute the same INSERT and inspect storage, without a JDBC
readback.
assertEquals(1, statement.executeUpdate(SQL));
assertEquals(1, table.rows.size());
assertEquals(0, table.rows.get(0)[0]);
Object stored = table.rows.get(0)[1];
assertNotNull(stored);
System.out.printf("Stored value=%s, class=%s%n", stored,
stored.getClass().getName());
assertEquals(6_120_000L, assertInstanceOf(Number.class,
stored).longValue());
// ANY must not erase the temporal type of the expression assigned
to it.
assertAll(
() -> assertEquals(SqlTypeName.TIME,
withSimplification.getType().getSqlTypeName(),
() -> "SQL-to-rel conversion replaced TIME with " +
withSimplification),
// Stock Enumerable's TIME representation is Integer, not
LocalTime.
() -> assertEquals(Integer.class, stored.getClass(),
"Stored temporal representation"));
}
}
private static RelNode convert(SchemaPlus schema, boolean simplify) throws
Exception {
try (Planner planner =
Frameworks.getPlanner(Frameworks.newConfigBuilder()
.defaultSchema(schema)
.sqlToRelConverterConfig(SqlToRelConverter.config()
.withRelBuilderConfigTransform(config ->
config.withSimplify(simplify)))
.build())) {
SqlNode validated = planner.validate(planner.parse(SQL));
RelNode plan = planner.rel(validated).rel;
System.out.println("SQL-to-rel plan (simplify=" + simplify + "):\n"
+ RelOptUtil.toString(plan));
return plan;
}
}
private static RexNode assignmentSource(RelNode plan) {
TableModify insert = assertInstanceOf(TableModify.class, plan);
RelNode input = insert.getInput();
int column = 1;
RexNode value;
while (true) {
if (input instanceof Project) {
value = ((Project) input).getProjects().get(column);
if (value instanceof RexInputRef) {
column = ((RexInputRef) value).getIndex();
input = ((Project) input).getInput();
continue;
}
} else {
value = assertInstanceOf(Values.class,
input).getTuples().get(0).get(column);
}
break;
}
// Unwrap only assignment casts to ANY, retaining the source SQL type.
while (value.getKind() == SqlKind.CAST &&
value.getType().getSqlTypeName() == SqlTypeName.ANY) {
value = ((RexCall) value).getOperands().get(0);
}
return value;
}
/**
* A table that stores exactly the rows supplied by Calcite's INSERT
operator.
*/
public static class ObjectTable extends AbstractQueryableTable implements
ModifiableTable {
final List<Object[]> rows = new ArrayList<>();
public ObjectTable() {
super(Object[].class);
}
@Override
public RelDataType getRowType(RelDataTypeFactory typeFactory) {
return typeFactory.builder().add("ID", SqlTypeName.INTEGER)
// Calcite's built-in SQL type for an arbitrary
Java Object is ANY.
.add("PAYLOAD",
SqlTypeName.ANY).nullable(true).build();
}
@Override
public Collection<Object[]> getModifiableCollection() {
return rows;
}
@Override
public TableModify toModificationRel(RelOptCluster cluster, RelOptTable
table, Prepare.CatalogReader catalogReader,
RelNode child,
TableModify.Operation operation, List<String> updateColumnList,
List<RexNode>
sourceExpressionList, boolean flattened) {
return LogicalTableModify.create(table, catalogReader, child,
operation, updateColumnList, sourceExpressionList,
flattened);
}
@Override
public <T> Queryable<T> asQueryable(QueryProvider queryProvider,
SchemaPlus schema, String tableName) {
return new AbstractTableQueryable<T>(queryProvider, schema, this,
tableName) {
@Override
@SuppressWarnings("unchecked")
public Enumerator<T> enumerator() {
return (Enumerator<T>) Linq4j.enumerator(rows);
}
};
}
}
}
{code}
> RexSimplify rewrites Date to int
> --------------------------------
>
> Key: CALCITE-7829
> URL: https://issues.apache.org/jira/browse/CALCITE-7829
> Project: Calcite
> Issue Type: Bug
> Reporter: Tomasz Gawęda
> Priority: Major
> Attachments: ObjectTable.java, TimeAssignmentTest.java,
> calcite-7829-reproducer.zip
>
>
> In Hazelcast we have a test called RowAssignmentTypeCoercionTest. When trying
> to upgrade to Calcite 1.42 we've noticed, that some cases fail (despite
> working previously).
>
> For example, queries:
> {code:sql}
> CREATE MAPPING m type IMap OPTIONS('keyFormat'='int', 'valueFormat'='java',
> 'valueJavaClass'='com.hazelcast.jet.sql.impl.support.expressions.ExpressionValue$ObjectVal')
> SINK INTO m VALUES(0, cast('01:42:00' as time), 0)
> {code}
> translate to:
> {code:java}
> LogicalTableSink(table=[[hazelcast, public, m[projects=[$0, $1, $2, $3]]]],
> operation=[INSERT], flattened=[false])
> LogicalCalc(expr#0=[\{inputs}], expr#1=[0], expr#2=[6120000],
> expr#3=[CAST($t2):OBJECT], expr#4=[null:OBJECT], __key=[$t1], field1=[$t3],
> key=[$t1], this=[$t4])
> LogicalValues(tuples=[[\{ 0 }]])
> {code}
> As you can see, `01:42:00` was translated to 6120000. This integer value is
> then written to a field of type OBJECT.
> Previously, in 1.39 dates and times landed in "object" field with their real
> types, not converted to integer, which makes me believe, that RexSimplify
> tries. to simplify a bit too much.
> (cast(cast('01:42:00' as time) as object) = time written to object column,
> not integer value.
> More from trace logs from this test:
> {code:java}
> 16:13:53,549 TRACE |test_insertValues[TestParams{id=2014, srcType=TIME,
> targetType=OBJECT, srcValue=01:42}]| - [parser] test_insertValues - After
> unconditional rewrite: SINK INTO `m`
> VALUES ROW(0, CAST('01:42:00' AS TIME), 0)
> 16:13:53,576 TRACE |test_insertValues[TestParams{id=2014, srcType=TIME,
> targetType=OBJECT, srcValue=01:42}]| - [parser] test_insertValues - After
> validation: SINK INTO `hazelcast`.`public`.`m` (`__key`, `field1`, `key`)
> VALUES ROW(CAST(0 AS INTEGER), CAST('01:42:00' AS TIME), CAST(0 AS INTEGER))
> 16:13:53,759 DEBUG |test_insertValues[TestParams{id=2014, srcType=TIME,
> targetType=OBJECT, srcValue=01:42}]| - [sql2rel] test_insertValues - Plan
> after converting SqlNode to RelNode
> LogicalTableSink(table=[[hazelcast, public, m[projects=[$0, $1, $2, $3]]]],
> operation=[INSERT], flattened=[false])
> LogicalProject(__key=[0], field1=[CAST(6120000):OBJECT], key=[0],
> this=[null:OBJECT])
> LogicalValues(tuples=[[{ 0 }]])
> {code}
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
