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)
