[CALCITE-4493] Postgres/Mysql String Literal Equality Creates incorrect results.

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Labels: pull-request-available
• Assignee: Unassigned
• Reporter: James Starr
• Created: 10/Feb/21 20:48
• Updated: 24/May/21 20:01

Description

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

@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\")");
}

 This is generating incorrect results since postgres would treat the literals TEXT which follow the VARCHAR semantics and treats trailing whitespace as significant.

postgres=# SELECT pg_typeof(a), pg_typeof('b') FROM (VALUES('foo')) AS t(a);
 pg_typeof | pg_typeof 
-----------+-----------
 text      | unknown
(1 row)

 

postgres=# SELECT 'foo ' = 'foo';
 ?column? 
----------
 f
(1 row)

Concerning other dialects, I ran the following query:

SELECT
case when t.a = t.b then 'true' else 'false' end as example1, --example 1 No Type Value literal equality
case when 'foo' = 'foo ' then 'true' else 'false' end as example2, --example 2 No Type literal comparison
case when t.a = 'foo ' then 'true' else 'false' end as example3, --example 3 No Type Value to No Type Literal
case when t.c = 'foo ' then 'true' else 'false' end as example4, --example 4 Typed Value to No Type Literal
CASE WHEN t.b = CAST('foo' AS CHAR(3)) then 'true' else 'false' end as example5, --example 5 No Type Value to Typed Literal
'|' || CAST('a ' AS VARCHAR(2)) || '|' as example6
FROM (
        select 'foo' a, 'foo ' b, CAST('foo' AS CHAR(3)) c --FROM DUAL 
) t
;

 

DB	a = b	‘foo’ = ‘foo ‘	a = ‘foo ‘	c = ‘foo ‘	b = CAST(‘foo’ AS CHAR(3))	CAST(‘a ‘ TO VARCHAR(2)
Dremio	false	false	false	false	false	|a |
Postgres	false	false	false	true	false	|a |
Oracle	false	false	false	true	true	|a |
MySQL	false	false	false	false	false	|a |
SQL Server	true	true	true	true	true	|a |
Snowflake	false	false	false	false	false	|a |
ISO SQL	true	true	true	true	true	|a|

Issue Links

• is related to: CALCITE-4590 Incorrect query result with fixed-length string
• links to: GitHub Pull Request #2405