coalesce white space in varchar , whitespace in char return empty string

[Jiunn Jye Ng]

Hi All, 

Let's say

   white space = ' '

   empty string = ''

coalesce should return the first non null value. 

Test Case: 

   create table testCoalesce ( colvarchar varchar, colchar char);

   insert into testCoalesce (colvarchar, colchar)  values (' ', ' ');

   select coalesce(colvarchar, colchar), length(coalesce(colvarchar, colchar)) from testCoalesce

   return an empty string ''

I understand that char does not store the trailing spaces. That explain where the empty string come from. But the result should have been the white space ' '.

This can be simulated on h2 version 1.4.182.

Thank you.

Rgds,

jay

[Rami Ojares]

coalesce returns the first non-null value in it's arguments.
Neither '' nor ' ' is null.

- rami

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[Jiunn Jye Ng]

Hi Rami, 

Yes. That was my point. Let me try to illustrate in a different way. The result for 

select coalesce(cast(' ' as varchar), cast(' ' as char)) from dual;     -- both parameter are white space. 

should be ' ' (a whitespace). However, h2 is returning '' (empty string).  

Rgds,

jay

[Rami Ojares]

Oh I see.
Something fishy is going on.
Eg.
select cast(' ' as varchar) = '' from dual;
-> FALSE
select cast(' ' as char) = '' from dual;
-> TRUE
select coalesce(cast(' ' as varchar), cast(' ' as char)) = '' from dual
-> TRUE

- rami

[Thomas Mueller]

Hi,

Currently, operations that involve char and varchar result in char. That's why the result of coalesce(char, varchar) is char.

I wonder, why do you use "char" and not "varchar"?

Regards,

Thomas

--

[Jiunn Jye Ng]

Hi Thomas

Thanks for the clarification. 

I was running some test on BI software when I discovered this behavior. I don't think it is a major concern as long the correct column is being picked.

Rgds,

jay

On Tuesday, November 4, 2014 1:24:46 AM UTC+8, Thomas Mueller wrote:

Hi,

Currently, operations that involve char and varchar result in char. That's why the result of coalesce(char, varchar) is char.

I wonder, why do you use "char" and not "varchar"?

Regards,

Thomas

On Mon, Nov 3, 2014 at 11:57 AM, Jiunn Jye Ng <jiun...@gmail.com> wrote:

Hi All, 

Let's say

   white space = ' '

   empty string = ''

coalesce should return the first non null value. 

Test Case: 

   create table testCoalesce ( colvarchar varchar, colchar char);

   insert into testCoalesce (colvarchar, colchar)  values (' ', ' ');

   select coalesce(colvarchar, colchar), length(coalesce(colvarchar, colchar)) from testCoalesce

   return an empty string ''

I understand that char does not store the trailing spaces. That explain where the empty string come from. But the result should have been the white space ' '.

This can be simulated on h2 version 1.4.182.

Thank you.

Rgds,

jay

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscribe@googlegroups.com.