Character in a string is not displayed as a string

[pgulutzan]

I have Ubuntu 18.04.
I have Tarantool 2.2 pulled from source today.

I use a character 🆘 which is Unicode 1F198 SQUARED SOS.
I insert it using the hex of the UTF8 i.e. X'F09F8698'.
I could also insert it using the decimal of the code point i.e. char(127384).
I could also insert it directly (on my platform) as '🆘'.

I issue these requests:
\set language sql
\set delimiter ;
CREATE TABLE L (s1 VARCHAR(5) COLLATE "unicode_ci" PRIMARY KEY);
INSERT INTO L VALUES (CAST(X'F09F8698' AS VARCHAR(5)));
INSERT INTO L VALUES ('SOS');
SELECT * FROM L;
\set language lua
\set delimiter
x = box.space.L:select()
utf8.len(x[1])

The INSERT INTO L VALUES ('SOS'); fails, duplicate key,
which is good -- that proves the character is correctly
recognized and is known to be equal to 'SOS'.

The SELECT * FROM l; fails, it says

• • [!!binary 8J+GmA==]
    but it should show the result as a string.

The utf8.len(x[1]) fails, it says

• error: 'Usage: utf8.len(, [i, [j]])'
  but there is no error, it is a valid 1-character string.

This is not the same as issue#2344 which mentioned disallowing
invalid strings. This is about failing to recognize valid strings.

[Totktonada]

!!binary is how yaml encodes a string with special characters (don't sure about exact character set). It has nothing with cdata or SQL types.

Re utf8.len(x[1]): x[1] is a tuple, use x[1][1] to pass a string to utf8.len.

[pgulutzan]

Indeed my use of x[1] was my mistake, sorry. Disregarding my nonsense about cdata, though, we are left with: why is the client displaying nonsense about "!!binary 8J+GmA=="? We know that the character set is UTF-8, and what I call special characters can be displayed, but not this one. Perhaps it cannot display any characters whose code point value is greater than FFFF? Update: I changed the title to remove 'cdata'.

[Totktonada]

Just note (I see the label sql): this is yaml formatting and it is not specific for sql.

[Totktonada]

I would not change the default behaviour of our yaml encoder, because our tests (in tarantool and other projects) often generates yaml-formatted result files to compare them byte-per-byte with expected ones. I propose to add an option that will let yaml produce a regular string when it is valid UTF8. We need to elaborate should non-ascii strings be always quoted: it depends how our decoder and other decoders usually work.

[Totktonada]

Aside of a yaml option we need to introduce a console option (\set ... directive?) to enable / disable it for console's output formatter.

[SudoBobo]

@pgulutzan
Could you please explain your opinion on the topic "How should Tarantool output emojis?" As far as I understood your comments, you propose to output such characters as they are (as code points - like this 🆘) Could you please also provide some justification.

I tried the commands in different DBs:

CREATE TABLE L (s1 VARCHAR(5) COLLATE "unicode_ci" PRIMARY KEY);
INSERT INTO L VALUES (CAST(X'F09F8698' AS VARCHAR(5)));
INSERT INTO L VALUES ('SOS');
SELECT * FROM L;

with the following results:
IBM DB2: printed 'SOS'
Postgresql: printed byte sequence
Oralce : printed 'SOS'
MSSQL: printed some random unicode symbols
MySQL: printed 'SOS'

[kostja]

I don't see any need for directive. This is a valid UTF8 character, just like э ы ю я. Why should it get a special treatment?

[pgulutzan]

Replying to SudoBobo:
(1) My opinion of emojis is not important. I used an emoji in this example but there are many characters outside the BMP that are not emojis.
(2) For PostgreSQL you should use a different syntax:
"
CREATE TABLE L (s1 VARCHAR(5));
INSERT INTO L VALUES (U&'+01F198');
SELECT * FROM L;
"
It works here so I think the correct comment is:
PostgreSQL : printed 'SOS'
(3) You want "justification" for displaying a character correctly. Well, my only justification is: it would be correct. If that is not good enough, okay.
By the way, I cannot check with SQL Server, but I will suggest: if the default character set is not UTF-8 (it usually isn't), then maybe the insert could be tried with nchar().

[Totktonada]

I don't see any need for directive. This is a valid UTF8 character, just like э ы ю я. Why should it get a special treatment?

I'll cite myself (see the whole comment above):

I would not change the default behaviour of our yaml encoder, because our tests (in tarantool and other projects) often generates yaml-formatted result files to compare them byte-per-byte with expected ones.

[kostja]

I don't understand this argument. byte-by-byte comparison will need to be updated, yes, so what?

[Totktonada]

Maybe. At least we should consider that it can break some external (testing?) code. That is especially matters when one intend to support several tarantool versions: (s)he will need to support several variants of a test case for different tarantool versions.

[kostja]

The test output is broken now - it prints a printable character as a binary escaped sequence. By not fixing it we prolong broken legacy. Basically you're saying that the change needs to go into 2.x only.