Comments on: CHAR and spaces https://shlomi-noach.github.io/blog/mysql/char-and-spaces Blog by Shlomi Noach Thu, 14 Jun 2012 16:49:40 +0000 hourly 1 https://wordpress.org/?v=5.3.3 By: Daniël van Eeden https://shlomi-noach.github.io/blog/mysql/char-and-spaces/comment-page-1#comment-100190 Thu, 14 Jun 2012 16:49:40 +0000 https://shlomi-noach.github.io/blog/?p=4528#comment-100190 And now with pre tags:

chartest=# CREATE TABLE c_test (c char(1) DEFAULT NULL);
CREATE TABLE
chartest=# INSERT INTO c_test VALUES (‘a’);
INSERT 0 1
chartest=# INSERT INTO c_test VALUES (‘b’);
INSERT 0 1
chartest=# INSERT INTO c_test VALUES (‘ ‘);
INSERT 0 1
chartest=# SELECT c, LENGTH(c), c = ‘a’, c = ‘b’, c = ‘ ‘, LENGTH(‘ ‘) FROM c_test;
c | length | ?column? | ?column? | ?column? | length
—+——–+———-+———-+———-+——–
a | 1 | t | f | f | 1
b | 1 | f | t | f | 1
| 0 | f | f | t | 1
(3 rows)

chartest=# SELECT c, LENGTH(c), c = ‘ ‘, LENGTH(‘ ‘), c = ”, LENGTH(”) FROM c_test;
c | length | ?column? | length | ?column? | length
—+——–+———-+——–+———-+——–
a | 1 | f | 1 | f | 0
b | 1 | f | 1 | f | 0
| 0 | t | 1 | t | 0
(3 rows)

chartest=# select version();
version
—————————————————————————————————————–
PostgreSQL 8.4.9 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.4.5 20110214 (Red Hat 4.4.5-6), 64-bit
(1 row)

]]> By: Daniël van Eeden https://shlomi-noach.github.io/blog/mysql/char-and-spaces/comment-page-1#comment-100189 Thu, 14 Jun 2012 16:47:50 +0000 https://shlomi-noach.github.io/blog/?p=4528#comment-100189 The behaviour for PostgreSQL 8.4 seems identical.

chartest=# CREATE TABLE c_test (c char(1) DEFAULT NULL);
CREATE TABLE
chartest=# INSERT INTO c_test VALUES (‘a’);
INSERT 0 1
chartest=# INSERT INTO c_test VALUES (‘b’);
INSERT 0 1
chartest=# INSERT INTO c_test VALUES (‘ ‘);
INSERT 0 1
chartest=# SELECT c, LENGTH(c), c = ‘a’, c = ‘b’, c = ‘ ‘, LENGTH(‘ ‘) FROM c_test;
c | length | ?column? | ?column? | ?column? | length
—+——–+———-+———-+———-+——–
a | 1 | t | f | f | 1
b | 1 | f | t | f | 1
| 0 | f | f | t | 1
(3 rows)

chartest=# SELECT c, LENGTH(c), c = ‘ ‘, LENGTH(‘ ‘), c = ”, LENGTH(”) FROM c_test;
c | length | ?column? | length | ?column? | length
—+——–+———-+——–+———-+——–
a | 1 | f | 1 | f | 0
b | 1 | f | 1 | f | 0
| 0 | t | 1 | t | 0
(3 rows)

]]> By: shlomi https://shlomi-noach.github.io/blog/mysql/char-and-spaces/comment-page-1#comment-100039 Thu, 14 Jun 2012 05:08:18 +0000 https://shlomi-noach.github.io/blog/?p=4528#comment-100039 @Rick,

Well, only for truly fixed length fields which do not end with spaces, that is.
I had a perfectly true fixed length field which was spaces, those spaces being ignored…

]]> By: Rick James https://shlomi-noach.github.io/blog/mysql/char-and-spaces/comment-page-1#comment-99999 Wed, 13 Jun 2012 23:43:17 +0000 https://shlomi-noach.github.io/blog/?p=4528#comment-99999 Bottom line: Use CHAR only for truly fixed length fields.

]]> By: shlomi https://shlomi-noach.github.io/blog/mysql/char-and-spaces/comment-page-1#comment-99567 Tue, 12 Jun 2012 16:38:29 +0000 https://shlomi-noach.github.io/blog/?p=4528#comment-99567 Peter,
Yes, that would be a bug, I believe. Will report.

]]> By: Davi Arnaut https://shlomi-noach.github.io/blog/mysql/char-and-spaces/comment-page-1#comment-99534 Tue, 12 Jun 2012 14:10:06 +0000 https://shlomi-noach.github.io/blog/?p=4528#comment-99534 “I add a comparison with the empty string ”, and it turns out the space character equals both a space character and the empty string, though their lengths do not agree.”

I think this might be blank-padding semantics, where the SQL standard requires padding so that when compared the values will have the same length.

Test with, let’say PostgreSQL, it should also have this behavior.

]]> By: Peter Laursen https://shlomi-noach.github.io/blog/mysql/char-and-spaces/comment-page-1#comment-99510 Tue, 12 Jun 2012 12:26:57 +0000 https://shlomi-noach.github.io/blog/?p=4528#comment-99510 What is absurd here is that “SELECT c = ”;” returns “1”, right? (in other words: empty string == SPACE-character). This I consider a plain bug. What say?

“SELECT LENGTH(c)” will simply return the column width with this SQL_mode, I think, as “When CHAR values are stored, they are right-padded with spaces to the specified length.” (quote from http://dev.mysql.com/doc/refman/5.5/en/char.html)

]]> By: shlomi https://shlomi-noach.github.io/blog/mysql/char-and-spaces/comment-page-1#comment-99496 Tue, 12 Jun 2012 11:52:49 +0000 https://shlomi-noach.github.io/blog/?p=4528#comment-99496 @Peter,
Even with sql_mode=’PAD_CHAR_TO_FULL_LENGTH’, I get:

SELECT c, LENGTH(c), c = ' ', LENGTH(' '), c = '', LENGTH('') FROM c_test;
+------+-----------+---------+-------------+--------+------------+
| c    | LENGTH(c) | c = ' ' | LENGTH(' ') | c = '' | LENGTH('') |
+------+-----------+---------+-------------+--------+------------+
| a    |         1 |       0 |           1 |      0 |          0 |
| b    |         1 |       0 |           1 |      0 |          0 |
|      |         1 |       1 |           1 |      1 |          0 |
+------+-----------+---------+-------------+--------+------------+

Which is different than before, but still absurd.

]]> By: shlomi https://shlomi-noach.github.io/blog/mysql/char-and-spaces/comment-page-1#comment-99492 Tue, 12 Jun 2012 11:48:23 +0000 https://shlomi-noach.github.io/blog/?p=4528#comment-99492 Peter,
There’s an sql_mode to that, too?!?
My hatred towards sql_mode is growing by the minute!

]]> By: shlomi https://shlomi-noach.github.io/blog/mysql/char-and-spaces/comment-page-1#comment-99490 Tue, 12 Jun 2012 11:47:20 +0000 https://shlomi-noach.github.io/blog/?p=4528#comment-99490 Roland,
You were aware enough to use it in _wrap_select_list_columns:
“…
declare v_whitespace varchar(1) default ”;
…”

]]>