[
https://issues.apache.org/jira/browse/CALCITE-4493?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17324333#comment-17324333
]
Julian Hyde commented on CALCITE-4493:
--------------------------------------
Thanks for doing this research into the behavior of various databases. It’s
very valuable. In fact, we should have tests that prove we can emulate those
databases’ behavior.
PostgreSQL’s behavior seems subtle and complicated. How is the “desired type”
propagated into the char literal? Can it propagate through sub-selects, views,
unions? I think we need to know the semantics more fully before we emulate them.
in other databases, is the type of char literals literally VARCHAR?
We already have a property for ragged Union in both conformance and
RelDataTypeSystem. I noticed this week that it is inconsistently applied - if
you create a type system in PlannerImpl vs CalciteConnectionImpl. I would like
to know how your new property relates to the existing properties.
> 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
> 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)
