Supporting Postgres String Literals Default Type

[James Starr <ja...@gmail.com>]

I created the ticket CALCITE-4493
<https://issues.apache.org/jira/browse/CALCITE-4493>*.*


Postgres does not assume string literals are type CHAR, instead it assigns
them to unknown or TEXT.  This behavior is particularly relevant when a
literal is compared to another literal.  Postgres TEXT type from the type
system perspective is equivalent to VARCHAR.  However, it is further
complicated by a string literal compared to a CHAR is treated as CHAR.


Currently the flow for generating literal types goes:

1. Sql parsing, during SqlLiteral creation, a char type is created and
stored

2. Sql Validation, a row types are created from the SqlLiteral

3. Sql To Rel, RexBuilder.createCharLiteral creates a RexLiteral, then the
Rex Literal is cast to type of the SqlLiteral.


RexLiteral and SqlLiteral currently do not allow VARCHAR literals.


Non-starter Routes forward:

1.  During sql parsing add a cast to VARCHAR.  This will create the
incorrect behavior for example 7.

2.  Rework row type creation during validation, and it's interaction in Sql
to Rel .  Then, override RexBuilder.createCharLiteral, using the type
returned by Rexbuilder.  The type is determined contextually which
RexBuilder.createCharLiteral will not have.



Possible Routes Forward:

1.  Create a new type with 2 members: default type, down cast type.  For
this particular instance it would be VARCHAR and CHAR.  When checking for a
cast the later is used, otherwise the form is used.  This would closely
model the postgres behavior, but would also alter the type system.
2.  Add another property to SqlLiteral, default type.  This would have an
identical value as sqlTypeName except for postgres string literals, where
sqlTypeName would be CHAR and default type of VARCHAR.  RowType creation
would use the default type as well as determining if casting is required
for literals.

Example of Calcite not correctly normalizing a Postgres query using
RelToSqlConverterTest.java:

@Test void testDefaultTypeStringLiteralType() {
  RuleSet rules = RuleSets.ofList(CoreRules.PROJECT_VALUES_MERGE,
CoreRules.PROJECT_REDUCE_EXPRESSIONS);
  HepProgramBuilder builder = new HepProgramBuilder();
  builder.addRuleClass(ProjectToWindowRule.class);
  HepPlanner hepPlanner = new HepPlanner(builder.build());
  sql("SELECT 'foo ' = 'foo'")
      .optimize(rules, hepPlanner)
      .withPostgresql().ok("SELECT *\nFROM (VALUES (TRUE)) AS \"t\"
(\"EXPR$0\")");
}


Example against Postgres:

SELECT
  pg_typeof(a), --example 1 No Type Value Literal
  pg_typeof(c), --example 2 Literal cast to char(x)
  pg_typeof('b'), --example 3 No Type Literal
  a = b, --example 4 No Type Value literal equality
  'foo' = 'foo ', --example 5 No Type literal comparison
  a = 'foo ', --example 6 No Type Value to No Type Literal
  c = 'foo ', --example 7 Typed Value to No Type Literal
  b = CAST('foo' AS CHAR(3)) --example 8 No Type Value to Typed Literal
  FROM (VALUES('foo', 'foo ', CAST('foo' AS CHAR(3)))) AS t(a, b, c);


  pg_typeof | pg_typeof | pg_typeof | ?column? | ?column? | ?column? |
?column? | ?column?
 -----------+-----------+-----------+----------+----------+----------+----------+----------
  text      | character | unknown   | f        | f        | f        | t
     | f
 (1 row)