String '2.0.0' converts to the integer 20: WHERE int_col = '2.0.0' finds 20 with a key, and an INT column stores 20

CREATE TABLE t1 (a INT, KEY (a));

INSERT INTO t1 VALUES (2), (20);

--let $c= query_get_value(SELECT GROUP_CONCAT(a) AS c FROM t1 WHERE a = '2.0.0', c, 1)

if ($c != 2)

{

  --die a = '2.0.0' finds $c and not 2

}

DROP TABLE t1;

Leads to:

mysqltest: At line 6: a = '2.0.0' finds 20 and not 2

Without the key, the query finds 2. With the rows 2, 2 and 20 and the key, a >= '1.2.3.4' finds only 20, and a BETWEEN '1.2.3.4' AND 10 finds no row. With sql_mode='', an INT or BIGINT column stores 20 for '2.0.0', 1234 for '12.34.56', 12 for '1.2.' and 5 for '0.5.5'. A DECIMAL or DOUBLE column stores 2, 12.34, 1.2 and 0.5. A YEAR column stores 2020 for '2.0.0', and 2002 for '2.0'. A ucs2 string gives the same. CAST('2.0.0' AS SIGNED) and '2.0.0' + 0 give 2. Optimized builds give the same result.

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

| MySQL  | Finds  | Engine |

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

| 5.5.62 | no row | MyISAM |

| 5.6.51 | 20     | MyISAM |

| 5.7.44 | 20     | InnoDB |

| 8.0.36 | 2      | InnoDB |

| 9.1.0  | 2      | InnoDB |

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

From the code it seems that my_strntoull10rnd_8bit() in strings/ctype-simple.c reads the digits of '2.0.0' with the first dot as the decimal point. At the second dot it goes to the exponent part with addon= 0, but it does not set shift for the digits after the first dot. The end of the loop sets it with shift= dot ? (int)(dot - str) : 0. So '2.0' gives 2, and '2.0.0' gives 20. The integer and YEAR fields use this function to store a string, and the key lookup stores the constant in the key field. Without the key, the string is read as a DECIMAL value, with a warning, and gives 2. The general difference between the key lookup and the scan for an INT and a string is MDEV-29259: with the patch, a = '2.5.0' finds 3 with the key and no row without it, as a = '2.5' does.