[jira] [Comment Edited] (CALCITE-4888) Unexpected `RexNode` when call `RelBuilder.in` to create an `IN` predicate with a list of varchar literals which have different length

["Jing Zhang (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-4888?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17444432#comment-17444432 ] 

Jing Zhang edited comment on CALCITE-4888 at 11/16/21, 11:06 AM:
-----------------------------------------------------------------

[~julianhyde] , Thanks for your response.

I checked the CALCITE-4590 and other related issues.

This JIRA is related CALCITE-4590 , but is not deduplicate of to CALCITE-4590.

The root cause of this JIRA is that there is a minor inconsistent behavior in `RexBuilder#makeIn`. It does not take `RelDataTypeSystem.shouldConvertRaggedUnionTypesToVarying()` into consideration. 

I guess it's reasonable if `shouldConvertRaggedUnionTypesToVarying` is true, we should use less restrictive type based on all ranges types instead of first range type, right?

Go back to this JIRA, I add the following test case in `RelBuilderTest`, if `shouldConvertRaggedUnionTypesToVarying` is true, we don't need to cast 'A' to char(5), right?
{code:java}
@Test void testFilterOrIn1() {
  final Function<RelBuilder, RelNode> f = b ->
      b.scan("EMP")
          .filter(b.in(b.field("JOB"), b.literal("CLERK"), b.literal("A")))
          .build();
  final String expected = ""
      + "LogicalFilter(condition=[SEARCH($2, Sarg['A':CHAR(5), 'CLERK']:CHAR(5))])\n"
      + "  LogicalTableScan(table=[[scott, EMP]])\n";
  final String expectedWithoutSimplify = ""
      + "LogicalFilter(condition=[SEARCH($2, Sarg['A':CHAR(5), 'CLERK']:CHAR(5))])\n"
      + "  LogicalTableScan(table=[[scott, EMP]])\n";
  assertThat(f.apply(createBuilder()), hasTree(expected));
  assertThat(f.apply(createBuilder(c -> c.withSimplify(false))),
      hasTree(expectedWithoutSimplify));
} {code}
Please correct me if I'm wrong, thanks a lot.


was (Author: qingru zhang):
[~julianhyde] , Thanks for your response.

I checked the CALCITE-4590 and other related issues. And find out there is a minor inconsistent behavior in `RexBuilder#makeIn`. It does not take `RelDataTypeSystem.shouldConvertRaggedUnionTypesToVarying()` into consideration. 

I guess it's reasonable if `shouldConvertRaggedUnionTypesToVarying` is true, we should use less restrictive type based on all ranges types instead of first range type, right?

Go back to this JIRA, I add the following test case in `RelBuilderTest`, if `shouldConvertRaggedUnionTypesToVarying` is true, we don't need to cast 'A' to char(5), right?
{code:java}
@Test void testFilterOrIn1() {
  final Function<RelBuilder, RelNode> f = b ->
      b.scan("EMP")
          .filter(b.in(b.field("JOB"), b.literal("CLERK"), b.literal("A")))
          .build();
  final String expected = ""
      + "LogicalFilter(condition=[SEARCH($2, Sarg['A':CHAR(5), 'CLERK']:CHAR(5))])\n"
      + "  LogicalTableScan(table=[[scott, EMP]])\n";
  final String expectedWithoutSimplify = ""
      + "LogicalFilter(condition=[SEARCH($2, Sarg['A':CHAR(5), 'CLERK']:CHAR(5))])\n"
      + "  LogicalTableScan(table=[[scott, EMP]])\n";
  assertThat(f.apply(createBuilder()), hasTree(expected));
  assertThat(f.apply(createBuilder(c -> c.withSimplify(false))),
      hasTree(expectedWithoutSimplify));
} {code}
Please correct me if I'm wrong, thanks a lot.

> Unexpected `RexNode` when call `RelBuilder.in` to create an `IN` predicate with a list of varchar literals which have different length
> --------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: CALCITE-4888
>                 URL: https://issues.apache.org/jira/browse/CALCITE-4888
>             Project: Calcite
>          Issue Type: Bug
>          Components: core
>            Reporter: Jing Zhang
>            Priority: Major
>
> when call `RelBuilder.in` to create an `IN` predicate with a list of varchar literals which have different length, generated `RexNode` are expected.
> for example, ranges parameters are `b.literal("CLERK")` and  `b.literal("A")`
> Generated `RexNode` seems to be unexpected because it would cast 'A' to char(5).



--
This message was sent by Atlassian Jira
(v8.20.1#820001)