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