Left Join values from non-existing table to a real one

[jazzman1]

Hi guys,
what do I want to accomplish is to LEFT JOIN the values of list of strings against values of a real database table, named T1.

mysql> SELECT id, T1col
    -> FROM `test`.`T1`;
+----+-------+
| id | T1col |
+----+-------+
|  1 | a     |
|  3 | c     |
+----+-------+
2 rows in set (0.00 sec)

So, if I try next comparing a string data, the query failed:

SELECT tmp.string
  FROM ( SELECT 'a' AS string 
         UNION ALL
         SELECT 'b' 
         UNION ALL
         SELECT 'c'
         UNION ALL
         SELECT 'd'
       ) AS tmp
LEFT JOIN T1 ON (T1.T1col = tmp.string)
WHERE T1.T1col IS NULL

No problems with numbers:

Let's say,

SELECT tmp.number
  FROM ( SELECT 1 AS number 
         UNION ALL
         SELECT 2 
         UNION ALL
         SELECT 3
         UNION ALL
         SELECT 4
       ) AS tmp
LEFT JOIN T1 ON (T1.id = tmp.number)
WHERE T1.id IS NULL

Result:

+--------+
| number |
+--------+
|      2 |
|      4 |
+--------+

So, what's wrong with strings here?

[Barand]

Do the values in T1col contain whitespace?

[jazzman1]

No master. It's pretty much the same.

[Barand]

Should work

mysql> describe t1;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(11) | NO   | PRI | NULL    | auto_increment |
| T1col | char(1) | YES  |     | NULL    |                |
+-------+---------+------+-----+---------+----------------+
2 rows in set (0.02 sec)

mysql> select * from t1;
+----+-------+
| id | T1col |
+----+-------+
|  1 | a     |
|  2 | d     |
+----+-------+
2 rows in set (0.00 sec)

mysql> SELECT tmp.string
    ->     FROM ( SELECT 'a' AS string
    ->     UNION ALL
    ->     SELECT 'b'
    ->     UNION ALL
    ->     SELECT 'c'
    ->     UNION ALL
    ->     SELECT 'd'
    ->     ) AS tmp
    ->     LEFT JOIN T1 ON (T1.T1col = tmp.string)
    ->     WHERE T1.T1col IS NULL;
+--------+
| string |
+--------+
| b      |
| c      |
+--------+
2 rows in set (0.00 sec)

[jazzman1]

My T1col is varchar(45). Do I need to change to char(1)?

[Barand]

I changed mine to varchar(45)

mysql> describe t1;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| T1col | varchar(45) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.03 sec)

mysql> select * from t1;
+----+-------+
| id | T1col |
+----+-------+
|  1 | a     |
|  2 | d     |
+----+-------+
2 rows in set (0.00 sec)

mysql> SELECT tmp.string
    ->     FROM ( SELECT 'a' AS string
    ->     UNION ALL
    ->     SELECT 'b'
    ->     UNION ALL
    ->     SELECT 'c'
    ->     UNION ALL
    ->     SELECT 'd'
    ->     ) AS tmp
    ->     LEFT JOIN T1 ON (T1.T1col = tmp.string)
    ->     WHERE T1.T1col IS NULL;
+--------+
| string |
+--------+
| b      |
| c      |
+--------+
2 rows in set (0.00 sec)

Same!

[Barand]

have you tried

mysql> SELECT HEX(T1col) FROM t1;
+------------+
| HEX(T1col) |
+------------+
| 61         |
| 64         |
+------------+

[jazzman1]

mysql> describe `test`.`T1`;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| T1col | varchar(45)      | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+

mysql> SELECT LENGTH(T1col) FROM T1
    -> ;
+---------------+
| LENGTH(T1col) |
+---------------+
|             1 |
|             1 |
+---------------+

I have no idea. What's your db version?

mysql> SELECT HEX(T1col) FROM T1;
+------------+
| HEX(T1col) |
+------------+
| 61         |
| 63         |
+------------+
2 rows in set (0.00 sec)

[Barand]

mysql> select version();

+------------------+

| version()        |

+------------------+

| 5.1.57-community |

+------------------+

[jazzman1]

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.35    |
+-----------+

Let me try to restart it!

 

No, same result.

 

I changed mine varchar(45) to char(1) and the result is the same.

[Barand]

1:20am - I need all the beauty sleep I can get. Back in 8 hours

[jazzman1]

No problem Barry. Good night from Canada

[kicken]

Are you just getting an empty result set with the strings or are you getting some kind of error?

[jazzman1]

I got an error running the query through mysql command line instead mysql work bench:

 

 

ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='
 

 

Everything is speaking in UTF-8 and the terminals of my linux machines(clients)  too. I don't have any of latin1_swidish charset in my DB.

 

Hey kick,

 

I've got an empty result in mysql workbench, into mysql command line I got the above error of llegal mix of collations.

[kicken]

Yea I just tried on my server,

+-------------------------+
| version()               |
+-------------------------+
| 5.5.34-0ubuntu0.13.04.1 |
+-------------------------+

and got a similar message. The following fixed it:

SELECT tmp.string
FROM ( 
        SELECT CONVERT('a' USING latin1)  AS string
        UNION ALL
        SELECT 'b'
        UNION ALL
        SELECT 'c'
        UNION ALL
        SELECT 'd'
) AS tmp
LEFT JOIN t1 ON (t1.T1col = tmp.string)
WHERE t1.T1col IS NULL;

[jazzman1]

Yeap, this solved the issue.

Thanks guys.

[jazzman1]

Maybe, because this table is not really existing into a DB, mysql tries to use it as a default latin1_swedish_ci collation, but my linux terminals are on utf8 and failed to find itself a proper collation...or.. it's a just a bug on this db version.

 

PS: I made a test to one of the godaddy database with version 5.0.96 using mysql workbench via SSH and works without any problems. 

[kicken]

In my case it was the real table that is set to latin1, so I assume the constants were utf8. That makes sense as converting the constant to latin1 fixed the issue.

 

I don't know if the server version makes much difference, it is probable Barand's test was on a server that defaults to utf8 when creating a table so there was no conflict.

[jazzman1]

This is the status of mine and godaddy DB server.

As you can see the default connection is under utf8_general_ci. That's important thing I think. 

mysql> SHOW VARIABLES LIKE 'collation%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

[jazzman1]

More info:

mysql> 
     SELECT character_set_name FROM information_schema.`COLUMNS` C

    -> WHERE table_schema = "test"
    -> 
    ->   AND table_name = "T1"
    -> 
    ->   AND column_name = "T1col";
+--------------------+
| character_set_name |
+--------------------+
| utf8               |
+--------------------+
1 row in set (0.01 sec)

And....this, it's driving me crazy and don't still understand why should I have to convert something to latin collation if it's not exsit

[Barand]

On my server

mysql> SHOW VARIABLES LIKE 'collation%';
+----------------------+------------------+
| Variable_name        | Value            |
+----------------------+------------------+
| collation_connection | cp850_general_ci |
| collation_database   | utf8_general_ci  |
| collation_server     | utf8_general_ci  |
+----------------------+------------------+

and

mysql> SHOW VARIABLES LIKE 'character%';
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | cp850                                                   |
| character_set_connection | cp850                                                   |
| character_set_database   | utf8                                                    |
| character_set_filesystem | binary                                                  |
| character_set_results    | cp850                                                   |
| character_set_server     | utf8                                                    |
| character_set_system     | utf8                                                    |
+--------------------------+---------------------------------------------------------+ 

[jazzman1]

Hm....this is my collation's status.

mysql> SHOW VARIABLES LIKE 'collation%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | utf8_general_ci   |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+

mysql> SHOW VARIABLES LIKE 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

mysql> SELECT character_set_name FROM information_schema.`COLUMNS` C 
 WHERE table_schema = "test" AND table_name = "T1" AND column_name = "T1col";
+--------------------+
| character_set_name |
+--------------------+
| utf8               |
+--------------------+

This is the encoding of the linux terminal.

[lxc@lxc1 ~]$ echo $LANG
en_CA.UTF-8

If I try to run the query without CONVERT() I got an error. And YES, this is the right db sever, database and table name.

I think this is just a bug but.... I'm still not sure exactly where on the application or db server?