MySQL SELECT and CREATE gives different results: Character respectively hexadecimal

Question

I have a table with person (id) and one characteristic (var0) at different timepoints t. At some timepoints the characteristic is missing and I would like to fill the gaps with the former value. Here is an example of a table:

+---+---+----+             +----+---+------+------+------------------+
|id | t |var0|             | id | t | var0 | var1 | @prev_id   := id |
+---+---+----+             +----+---+------+------+------------------+
| 1 | 1 | a  |             |  1 | 1 | a    | a    |                1 |
| 1 | 3 | \N |             |  1 | 3 | \N   | a    |                1 |
| 1 | 7 | \N |             |  1 | 7 | \N   | a    |                1 |
| 1 | 8 | b  |             |  1 | 8 | b    | b    |                1 |
| 1 | 9 | \N |             |  1 | 9 | \N   | b    |                1 |
| 2 | 2 | \N |             |  2 | 2 | \N   | \N   |                2 |
| 2 | 4 | u  |             |  2 | 4 | u    | u    |                2 |
| 2 | 5 | u  |             |  2 | 5 | u    | u    |                2 |
| 2 | 6 | \N |             |  2 | 6 | \N   | u    |                2 |
| 2 | 7 | \N |             |  2 | 7 | \N   | u    |                2 |
| 2 | 8 | v  |             |  2 | 8 | v    | v    |                2 |
| 2 | 9 | \N |             |  2 | 9 | \N   | v    |                2 |
+---+---+----+             +----+---+------+------+------------------+

The left table is the orignal x1 table and the right table is the requested results. Here is the code to get the result:

DROP TABLE IF EXISTS x1;
CREATE TABLE test01.x1 (
  id   INTEGER
, t    INTEGER
, var0 CHAR(1)
) ENGINE = InnoDB 
DEFAULT CHARACTER SET = utf8 COLLATE = utf8_unicode_ci
;

INSERT INTO test01.x1(id,t,var0) VALUES
( 1,1,'a' )
,(1,3,NULL)
,(1,7,NULL)
,(1,8,'b' )
,(1,9,NULL)
,(2,2,NULL)
,(2,4,'u' )
,(2,5,'u' )
,(2,6,NULL)
,(2,7,NULL)
,(2,8,'v' )
,(2,9,NULL)
;

-- DROP TABLE IF EXISTS test01.x2;
-- CREATE TABLE test01.x2
SELECT id, t
       , var0
       , @prev_var0 := IF(id = @prev_id AND var0 IS NULL AND @prev_var0 IS NOT NULL 
                       , @prev_var0
                       , var0 
                       ) var1
       , @prev_id   := id
FROM test01.x1, (SELECT @prev_id    := NULL
                    ,@prev_var0 := NULL
        ) init
ORDER BY id, t
;

UPDATE test01.x2
SET var1 = UNHEX(var1)
;

If I want to save the result as table x2 (remove comments marks --), I get hexadecimals instead of characters:

+----+---+------+-----------+------------------+
| id | t | var0 | HEX(var1) | @prev_id   := id |
+----+---+------+-----------+------------------+
|  1 | 1 | a    | 0x61      |                1 |
|  1 | 3 | \N   | 0x61      |                1 |
|  1 | 7 | \N   | 0x61      |                1 |
|  1 | 8 | b    | 0x62      |                1 |
|  1 | 9 | \N   | 0x62      |                1 |
|  2 | 2 | \N   | \N        |                2 |
|  2 | 4 | u    | 0x75      |                2 |
|  2 | 5 | u    | 0x75      |                2 |
|  2 | 6 | \N   | 0x75      |                2 |
|  2 | 7 | \N   | 0x75      |                2 |
|  2 | 8 | v    | 0x76      |                2 |
|  2 | 9 | \N   | 0x76      |                2 |
+----+---+------+-----------+------------------+

Is there a way to get characters instead of the hexadecimal? Why does this happened?
Thanks for helps.

giordano · Answer 1 · 2014-12-03T10:55:00.073

I found a solution myself through casting var1:

CREATE TABLE test01.x2
SELECT id, t
       , var0
       , @prev_var0 := CAST(IF(id = @prev_id AND var0 IS NULL AND @prev_var0 IS NOT NULL 
                           , @prev_var0
                               , var0 
                               ) AS CHAR
                            )  var1
       , @prev_id   := id
FROM test01.x1, (SELECT @prev_id    := NULL
                    ,@prev_var0 := NULL
        ) init
ORDER BY id, t
;

The datatype of var1 is LONGTEXT. Since I want to have CHAR(1) I have to modfiy it:

ALTER TABLE test01.x2 MODIFY var1 CHAR(1) DEFAULT NULL;

Anyone has an idea why the hexadecimal is displayed?

MySQL SELECT and CREATE gives different results: Character respectively hexadecimal

1 Answers1

Linked