Concatenating CHAR(N) and VARCHAR strings

[mb...@gmail.com]

Hello,

 

What happens when you concatenate a CHAR(N) and a VARCHAR value?

Calcite seems to insert no casts before concatenation, so the trailing
spaces for the CHAR(N) value are preserved.

However, Postgres has the following test:

 

https://github.com/postgres/postgres/blob/5bcc7e6dc8cb6fb6ab16c118a1e710fc85
096f76/src/test/regress/expected/strings.out#L1802C1-L1805C21

 

SELECT char(20) 'characters' || ' and text' AS "Concat char to unknown
type";

Concat char to unknown type 

-----------------------------

characters and text

 

The same test (adapted for the Babel syntax) evaluates statically in Calcite
to:

 

"characters           and text"

 

It looks like Postgres implicitly casts the CHAR(20) value to TEXT before
concatenation.

I have stared at tables with type conversions in various databases for a
long time, but I could not figure out what the result is supposed to be. 

 

Thank you,

Mihai

RE: Concatenating CHAR(N) and VARCHAR strings

[mb...@gmail.com]

Actually Postgres documents this behavior:

 

https://www.postgresql.org/docs/15/functions-string.html

text || anynonarray → text

anynonarray || text → text

Converts the non-string input to text, then concatenates the two strings.
(The non-string input cannot be of an array type, because that would create
ambiguity with the array || operators. If you want to concatenate an array's
text equivalent, cast it to text explicitly.)

So in Postgres concatenation always converts arguments to VARCHAR, which
strips the trailing spaces.

 

Mihai

 

From: mbudiu@gmail.com 
Sent: Thursday, June 29, 2023 5:28 PM
To: dev@calcite.apache.org
Subject: Concatenating CHAR(N) and VARCHAR strings

 

Hello,

 

What happens when you concatenate a CHAR(N) and a VARCHAR value?

Calcite seems to insert no casts before concatenation, so the trailing
spaces for the CHAR(N) value are preserved.

However, Postgres has the following test:

 

https://github.com/postgres/postgres/blob/5bcc7e6dc8cb6fb6ab16c118a1e710fc85
096f76/src/test/regress/expected/strings.out#L1802C1-L1805C21

 

SELECT char(20) 'characters' || ' and text' AS "Concat char to unknown
type";

Concat char to unknown type 

-----------------------------

characters and text

 

The same test (adapted for the Babel syntax) evaluates statically in Calcite
to:

 

"characters           and text”

 

It looks like Postgres implicitly casts the CHAR(20) value to TEXT before
concatenation.

I have stared at tables with type conversions in various databases for a
long time, but I could not figure out what the result is supposed to be. 

 

Thank you,

Mihai