Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-9842

LOAD DATA INFILE does not work well with a TEXT column when using sjis

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 5.5, 10.0, 10.1
    • Fix Version/s: 10.2.0
    • Component/s: Character Sets
    • Labels:
      None

      Description

      I create a text file:

      mysql --default-character-set=sjis --skip-column-names --exec="SELECT CONCAT('x', REPEAT(_sjis 0x835C, 200))" >/tmp/test.txt
      

      It consists of one LATIN SMALL LETTER X, followed by 200 characters KATAKANA LETTER SO (which is encoded as 0x835C in SJIS).

      Now I create a table and load the file into it:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a TEXT CHARACTER SET sjis);
      LOAD DATA INFILE '/tmp/test.txt' INTO TABLE t1 CHARACTER SET sjis;
      SHOW WARNINGS;
      SELECT a FROM t1;
      

      It returns the following warning:

      +---------+------+------------------------------------------------------------+
      | Level   | Code | Message                                                    |
      +---------+------+------------------------------------------------------------+
      | Warning | 1366 | Incorrect string value: '\x83\x0A' for column 'a' at row 1 |
      +---------+------+------------------------------------------------------------+
      

      and this result (make sure to scroll the below window to the right):

      +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | a                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
      +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | xソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソャャャャャャャャャャャャャャャャャャャャャャャャャャャャャャャャャャャャ?
                                                                                                                                                                          |
      +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      

      The warning and the result look wrong:

      • Notice, it started to import the data well, but at some offset KATAKANA LETTER SO changed to something else.
      • The question mark at the end is wrong.

      If I now change the column type from TEXT to VARCHAR(1000), it loads the file without problems and without warnings:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a VARCHAR(1000) CHARACTER SET sjis);
      LOAD DATA INFILE '/tmp/test.txt' INTO TABLE t1 CHARACTER SET sjis; 
      SELECT a FROM t1;
      

      +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | a                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
      +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | xソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソ                                                                                                                                                                                                         |
      +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                bar Alexander Barkov
                Reporter:
                bar Alexander Barkov
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: