[
https://issues.apache.org/jira/browse/CALCITE-4493?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
James Starr updated CALCITE-4493:
---------------------------------
Summary: Postgres/Mysql String Literal Equality Creates incorrect results.
(was: Postgres String Literal Equality Creates incorrect results.)
> Postgres/Mysql 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
> Priority: Major
> Labels: pull-request-available
> Time Spent: 10m
> Remaining Estimate: 0h
>
> 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}
> Concerning other dialects, I ran the following query:
> {code:sql}
> 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
> ;{code}
>
> |*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\||
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
