Partitioning expression that depends on NO_UNSIGNED_SUBTRACTION is accepted, and the table cannot be read with the default sql_mode

Likely cause: old code, not a recent change. The partitioning code does not check whether its expression depends on the sql_mode, as the server does for a generated column. MySQL 5.5 to 9.1 give the same result.

--source include/have_partition.inc

SET sql_mode= 'NO_UNSIGNED_SUBTRACTION';

--error 0,ER_PARTITION_FUNCTION_IS_NOT_ALLOWED

CREATE TABLE t1 (a INT UNSIGNED, b INT UNSIGNED) PARTITION BY HASH (a - b) PARTITIONS 5;

if (!$mysql_errno)

{

  INSERT INTO t1 VALUES (1, 2);

  FLUSH TABLES;

  SET sql_mode= DEFAULT;

  --error 0,ER_DATA_OUT_OF_RANGE

  SELECT * FROM t1 WHERE a = 1 AND b = 2;

  if ($mysql_errno)

  {

    --die A table partitioned by a - b on unsigned columns and created with sql_mode NO_UNSIGNED_SUBTRACTION cannot be read with the default sql_mode

  }

  DROP TABLE t1;

}

Leads to:

mysqltest: At line 14: A table partitioned by a - b on unsigned columns and created with sql_mode NO_UNSIGNED_SUBTRACTION cannot be read with the default sql_mode

The SELECT gives ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '`test`.`t1`.`a` - `test`.`t1`.`b`', and CHECK TABLE reports "Found a misplaced row". The result also depends on the sql_mode of the session that opens the table first. With the default sql_mode, a STORED generated column a - b is refused with ERROR 1901 ("Expression depends on the @@sql_mode value NO_UNSIGNED_SUBTRACTION"), while the partitioned table is accepted with any sql_mode. Debug builds give the same result.

From the code it seems that fix_partition_func() fixes the expression with the sql_mode of the session that opens the table, and that the partitioning code does not call value_depends_on_sql_mode(), which Field::check_vcol_sql_mode_dependency() does for generated columns.