[jira] [Created] (CALCITE-4493) Postgres String Literal Equality Creates incorrect results.

["James Starr (Jira)" <ji...@apache.org>]

James Starr created CALCITE-4493:
------------------------------------

             Summary: Postgres String Literal Equality Creates incorrect results.
                 Key: CALCITE-4493
                 URL: https://issues.apache.org/jira/browse/CALCITE-4493
             Project: Calcite
          Issue Type: Bug
            Reporter: James Starr


Calcite always interprets String Literals as CHAR type.  This is normally not an issue since if are literal is being compared to VARCHAR column, then the literal is cast to a VARCHAR.  VARCHAR and CHAR equality have slight different behavior.  CHAR ignores trailing whitespace when comparing, where VARCHAR does not.  Postgres defaults the a strings literals type as unkown in a given expression, but will default them to text if there evaluated to row.

Postgres treats string literals types as unknown or TEXT.  Postgres TEXT follows VARCHAR semantics.

RelToSqlConverterTest.java
{code: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\")");
}
{code}
 This is generating incorrect results since postgres would treat the literals TEXT which follow the VARCHAR semantics and treats trailing whitespace as significant.
{code:sql}
postgres=# SELECT pg_typeof(a), pg_typeof('b') FROM (VALUES('foo')) AS t(a);
 pg_typeof | pg_typeof 
-----------+-----------
 text      | unknown
(1 row)
{code}
 
{code:sql}
postgres=# SELECT 'foo ' = 'foo';
 ?column? 
----------
 f
(1 row)
{code}



--
This message was sent by Atlassian Jira
(v8.3.4#803005)