Re: BUG #17258: Unexpected results in CHAR(1) data type

Hi,

> I haven't studied the behavior of char(n) on other RDBMS products.

At school, at SQL language course, the following was teached:
   CHAR( size) is a fixed length string of length "size" characters (not bytes). Whatever data you enter, space is added at the end.

I can offer Oracle behaviour.

SQL> create table test (
  id NUMBER
  , c2 char(2 char)
  , c10 char(10 char)
);

Table TEST created.

SQL> insert into test values (1, ' ', ' ');
1 row inserted.

SQL> insert into test values (2, 'ä', 'ä');
1 row inserted.

SQL> select id, length(c2), length(c10) from test;
ID LENGTH(C2) LENGTH(C10)
-- ---------- -----------
 1          2          10
 2          2          10

2 rows selected.

-- LENGTH IN BYTES
SQL> select id, lengthb(c2), lengthb(c10) from test;
ID LENGTHB(C2) LENGTHB(C10)
-- ----------- ------------
 1           2           10
 2           3           11

2 rows selected.

-- LENGTH IN CHARS
SQL> select id, lengthc(c2), lengthc(c10) from test;
ID LENGTHC(C2) LENGTHC(C10)
-- ----------- ------------
 1           2           10
 2           2           10

2 rows selected.

-- DUMP OF REAL VALUE ON STORAGE
SQL> select id, dump(c10, 1015) from test;
ID DUMP(C10,1015)                                                          
-- -----------------------------------------------------------------------
 1 Typ=96 Len=10 CharacterSet=AL32UTF8: 32,32,32,32,32,32,32,32,32,32      
 2 Typ=96 Len=11 CharacterSet=AL32UTF8: 195,164,32,32,32,32,32,32,32,32,32

2 rows selected.

-- ARE THEY EQUAL? YES
SQL> select * from test where c2 = c10;
ID C2 C10        
-- -- ----------
 1              
 2 ä  ä          

2 rows selected.

-- 

SQL> select * from test where c10 = 'ä ';
ID C2 C10        
-- -- ----------
 2 ä  ä          

1 rows selected.

SQL> select banner_full from v$version;
BANNER_FULL                                                                              
-----------------------------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.2.0.0.0

Regards

Marek