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)