The 'select' result is strange

Off-Topic
1

Hi,

While testing to compare char and varchar, I found a strange result.

Look at the result of the second select statement. ("Oxford ": spaces at the end)

select length (street), concat (‘[’, street, ‘]’) from temp where street = 'Oxford ';

Test procedure summary:

memsql> select @@memsql_version;
+------------------+
| @@memsql_version |
+------------------+
| 7.1.1            |
+------------------+
1 row in set (0.01 sec)

memsql> Create table temp (City CHAR(10), Street VARCHAR(10));
Query OK, 0 rows affected (0.05 sec)

memsql> desc temp;
+--------+-------------+------+------+---------+-------+
| Field  | Type        | Null | Key  | Default | Extra |
+--------+-------------+------+------+---------+-------+
| City   | char(10)    | YES  |      | NULL    |       |
| Street | varchar(10) | YES  |      | NULL    |       |
+--------+-------------+------+------+---------+-------+
2 rows in set (0.00 sec)

memsql> Insert into temp values('Pune','Oxford');
Query OK, 1 row affected (0.02 sec)

memsql> Insert into temp values('Pune ','Oxford ');
Query OK, 1 row affected (0.02 sec)

memsql> Insert into temp values('Pune   ','Oxford ');
Query OK, 1 row affected (0.00 sec)


memsql>  select length(city), length(street), concat('[', street, ']') from temp;
+--------------+----------------+--------------------------+
| length(city) | length(street) | concat('[', street, ']') |
+--------------+----------------+--------------------------+
|            4 |              7 | [Oxford ]                |
|            4 |              6 | [Oxford]                 |
|            4 |              7 | [Oxford ]                |
+--------------+----------------+--------------------------+

**memsql>  select length(street), concat('[', street, ']') from temp where street='Oxford ';**
**+----------------+--------------------------+**
**| length(street) | concat('[', street, ']') |**
**+----------------+--------------------------+**
**|              7 | [Oxford ]                |**
**|              6 | [Oxford]                 |**
**|              7 | [Oxford ]                |**
**+----------------+--------------------------+**
**3 rows in set (0.04 sec)**

memsql>  select length(street), concat('[', street, ']') from temp where street='Oxford';
+----------------+--------------------------+
| length(street) | concat('[', street, ']') |
+----------------+--------------------------+
|              6 | [Oxford]                 |
|              7 | [Oxford ]                |
|              7 | [Oxford ]                |
+----------------+--------------------------+
3 rows in set (0.00 sec)

memsql>  select length(street), concat('[', street, ']') from temp where street like 'Oxford';
+----------------+--------------------------+
| length(street) | concat('[', street, ']') |
+----------------+--------------------------+
|              6 | [Oxford]                 |
+----------------+--------------------------+
1 row in set (0.03 sec)

memsql>  select length(street), concat('[', street, ']') from temp where street like 'Oxford ';
+----------------+--------------------------+
| length(street) | concat('[', street, ']') |
+----------------+--------------------------+
|              7 | [Oxford ]                |
|              7 | [Oxford ]                |
+----------------+--------------------------+
2 rows in set (0.00 sec)

memsql>  select length(street), concat('[', street, ']') from temp where street <> 'Oxford ';
Empty set (0.04 sec)

memsql>  select length(street), concat('[', street, ']') from temp where street <> 'Oxford';
Empty set (0.00 sec)

Thanks…

2

Yes, this is by-design, but we should do a better job documenting this in our docs. We follow how MySQL deals collations and trailing spaces. Those spaces are ignored in comparisons (MySQL talks about this here: https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-sets.html - the section about Pad attributes).

-Adam

3

Hi adam,

Thanks for your answer.

However, the data type of the Street column is varchar(10).

memsql> select length(street), concat(‘[’, street, ‘]’) from temp where street=‘Oxford ‘;
±---------------±-------------------------+
| length(street) | concat(’[’, street, ‘]’) |
±---------------±-------------------------+
| 7 | [Oxford ] |
| 6 | [Oxford] |
| 7 | [Oxford ] |
±---------------±-------------------------+
3 rows in set (0.04 sec)

I think the results below are correct.
Of course, the result of ‘mysql’ is like this.

memsql> select length(street), concat(‘[’, street, ‘]’) from temp where street=‘Oxford ‘;
±---------------±-------------------------+
| length(street) | concat(’[’, street, ‘]’) |
±---------------±-------------------------+
| 7 | [Oxford ] |
| 7 | [Oxford ] |
±---------------±-------------------------+
2 rows in set (x.xx sec)

Is this by design?

Thanks in advance.

4

Which version of MySQL did you try? I get the same result as MemSQL when I try it on MySQL. If the Varchar column is using the utf8_general_ci collation you’ll get the same results as MemSQL. Different collations give different results.

This is actually part of the ANSI SQL standard and a source of a lot of confusion in various SQL databases (sql server - Behavior of Varchar with spaces at the end - Database Administrators Stack Exchange)