Why CHAR column comparing to numeric literal return true?


  • QA Engineer

    This bug my mind I have this table

    CREATE TABLE `t_slot` (
      `id` int unsigned NOT NULL,
      `uid` int unsigned NOT NULL,
      `code` char(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
      `pType` tinyint unsigned NOT NULL,
      `pos` tinyint unsigned DEFAULT NULL,
      `pid` int unsigned DEFAULT NULL,
      `lChild` int unsigned DEFAULT NULL,
      `rChild` int unsigned DEFAULT NULL,
      `encodeTime` datetime NOT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `code` (`code`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    

    Then I tried to do query something like this

    SELECT * FROM t_slot WHERE code = 5;
    

    And with a big surprise it match 1 row with value of

    mysql> select * from t_slot where code = 5;
    +----+-----+-----------+-------+------+------+--------+--------+---------------------+
    | id | uid | code      | pType | pos  | pid  | lChild | rChild | encodeTime          |
    +----+-----+-----------+-------+------+------+--------+--------+---------------------+
    |  2 |   4 | 5FPSLB-BP |    40 |    0 |    1 |      3 |      5 | 2022-03-19 17:39:03 |
    +----+-----+-----------+-------+------+------+--------+--------+---------------------+
    1 row in set, 50 warnings (0.00 sec)
    

    And got this warnings

    mysql> show warnings;
    +---------+------+-----------------------------------------------+
    | Level   | Code | Message                                       |
    +---------+------+-----------------------------------------------+
    | Warning | 1292 | Truncated incorrect DOUBLE value: 'Y8VHM9-BP' |
    | Warning | 1292 | Truncated incorrect DOUBLE value: '5FPSLB-BP' |
    | Warning | 1292 | Truncated incorrect DOUBLE value: 'HBV29S-BP' |
    ... etc
    

    I didn't really expect that 5 will match with 5FPSLB-BP.

    How does 5 is equal to 5FPSLB-BP.



  • "Truncated incorrect DOUBLE value" -- Show us the command that caused these. It sounds like LOAD DATA without the OPTIONALLY QUOTED or something.

    WHERE code = 5 -- When comparing a CHAR-type column to a numeric literal (5), the string is converted to numeric. Yeah, that is surprising, but that is what happens in MySQL.

    Since code is "5FPSLB-BP", you should probably quote the string you are comparing it to: WHERE code = "5". In that case the test will correctly fail.


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2