Description\n-----------\n\nImplicit type conversion takes place when MariaDB is using operands or different types, in order to make the operands compatible.\n\nIt is best practice not to rely upon implicit conversion; rather use CAST to explicitly convert types.\n\nRules for Conversion on Comparison\n\n If either argument is NULL, the result of the comparison is NULL unless the NULL-safe <=> equality comparison operator is used.\n If both arguments are integers, they are compared as integers.\n If both arguments are strings, they are compared as strings.\n If one argument is decimal and the other argument is decimal or integer, they are compared as decimals.\n If one argument is decimal and the other argument is a floating point, they are compared as floating point values.\n If one argument is string and the other argument is integer, they are compared as decimals.\n If a hexadecimal argument is not compared to a number, it is treated as a binary string.\n If a constant is compared to a TIMESTAMP or DATETIME, the constant is converted to a timestamp, unless used as an argument to the IN function.\n In other cases, arguments are compared as floating point, or real, numbers.\n\nNote that if a string column is being compared with a numeric value, MariaDB will not use the index on the column, as there are numerous alternatives that may evaluate as equal (see examples below).\n\nComparison Examples\n\nConverting a string to a number:\n\n``sql\nSELECT 15+'15';\n+---------+\n| 15+'15' |\n+---------+\n|      30 |\n+---------+\n`\n\nConverting a number to a string:\n\n`sql\nSELECT CONCAT(15,'15');\n+-----------------+\n| CONCAT(15,'15') |\n+-----------------+\n| 1515            |\n+-----------------+\n`\n\nFloating point number errors:\n\n`sql\nSELECT '9746718491924563214' = 9746718491924563213;\n+---------------------------------------------+\n| '9746718491924563214' = 9746718491924563213 |\n+---------------------------------------------+\n|                                           1 |\n+---------------------------------------------+\n`\n\nNumeric equivalence with strings:\n\n`sql\nSELECT '5' = 5;\n+---------+\n| '5' = 5 |\n+---------+\n|       1 |\n+---------+\n\nSELECT '   5' = 5;\n+------------+\n| '   5' = 5 |\n+------------+\n|          1 |\n+------------+\n\nSELECT '   5  ' = 5;\n+--------------+\n| '   5  ' = 5 |\n+--------------+\n|            1 |\n+--------------+\n1 row in set, 1 warning (0.000 sec)\n\nSHOW WARNINGS;\n+-------+------+--------------------------------------------+\n| Level | Code | Message                                    |\n+-------+------+--------------------------------------------+\n| Note  | 1292 | Truncated incorrect DOUBLE value: '   5  ' |\n+-------+------+--------------------------------------------+\n`\n\nAs a result of the above, MariaDB cannot use the index when comparing a string with a numeric value in the example below:\n\n`sql\nCREATE TABLE t (a VARCHAR(10), b VARCHAR(10), INDEX idx_a (a));\n\nINSERT INTO t VALUES \n  ('1', '1'), ('2', '2'), ('3', '3'), \n  ('4', '4'), ('5', '5'), ('1', '5');\n\nEXPLAIN SELECT  FROM t WHERE a = '3' \\G\n************************ 1. row ***********************\n           id: 1\n  select_type: SIMPLE\n        table: t\n         type: ref\npossible_keys: idx_a\n          key: idx_a\n      key_len: 13\n          ref: const\n         rows: 1\n        Extra: Using index condition\n\nEXPLAIN SELECT  FROM t WHERE a = 3 \\G\n************************ 1. row ***********************\n           id: 1\n  select_type: SIMPLE\n        table: t\n         type: ALL\npossible_keys: idx_a\n          key: NULL\n      key_len: NULL\n          ref: NULL\n         rows: 6\n        Extra: Using where\n`\n\nRules for Conversion on Dyadic Arithmetic Operations\n\nImplicit type conversion also takes place on dyadic arithmetic operations (+,-,\\,/). MariaDB chooses the minimum data type that is guaranteed to fit the result and converts both arguments to the result data type.\n\nFor addition (+), subtraction (-) and multiplication (\\), the result data type is chosen as follows:\n\n If either of the arguments is an approximate number (float, double), the result is double.\n If either of the arguments is a string (char, varchar, text), the result is double.\n If either of the arguments is a decimal number, the result is decimal.\n If either of the arguments is of a temporal type with a non-zero fractional second precision (time(N), datetime(N), timestamp(N)), the result is decimal.\n If either of the arguments is of a temporal type with a zero fractional second precision (time(0), date, datetime(0), timestamp(0)), the result may vary between int, int unsigned, bigint or bigint unsigned, depending on the exact data type combination.\n If both arguments are integer numbers (tinyint, smallint, mediumint, bigint), the result may vary between int, int unsigned, bigint or bigint unsigned, depending of the exact data types and their signs.\n\nFor division (/), the result data type is chosen as follows:\n\n If either of the arguments is an approximate number (float, double), the result is double.\n If either of the arguments is a string (char, varchar, text), the result is double.\n Otherwise, the result is decimal.\n\nArithmetic Examples\n\nNote, the above rules mean that when an argument of a temporal data type appears in addition or subtraction, it's treated as a number by default.\n\n`sql\nSELECT TIME'10:20:30' + 1;\n+--------------------+\n| TIME'10:20:30' + 1 |\n+--------------------+\n|             102031 |\n+--------------------+\n`\n\nIn order to do temporal addition or subtraction instead, use the DATE_ADD() or DATE_SUB() functions, or an INTERVAL expression as the second argument:\n\n`sql\nSELECT TIME'10:20:30' + INTERVAL 1 SECOND;\n+------------------------------------+\n| TIME'10:20:30' + INTERVAL 1 SECOND |\n+------------------------------------+\n| 10:20:31                           |\n+------------------------------------+\n`\n\n`sql\nSELECT "2.2" + 3;\n+-----------+\n| "2.2" + 3 |\n+-----------+\n|       5.2 |\n+-----------+\n\nSELECT 2.2 + 3;\n+---------+\n| 2.2 + 3 |\n+---------+\n| 5.2     |\n+---------+\n\nSELECT 2.2 / 3;\n+---------+\n| 2.2 / 3 |\n+---------+\n| 0.73333 |\n+---------+\n\nSELECT "2.2" / 3;\n+--------------------+\n| "2.2" / 3          |\n+--------------------+\n| 0.7333333333333334 |\n+--------------------+\n``\n\nURL: https://mariadb.com/docs/server/reference/sql-functions/string-functions/type-conversion