Bug #109449 functional index could produce wrong result

• Submitted: 21 Dec 2022 9:48
• Modified: 21 Dec 2022 12:15
• Reporter: x j
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.26, 8.0.31
• OS: Any
• CPU Architecture: Any

[21 Dec 2022 9:48] x j

Description:
when defining a functional index with unsigned type on a signed column, it will not give an error nor warning when inserting a value out of range

How to repeat:
create table t(a int, index idx((cast(a-10 as unsigned))));
insert into t values (1);
mysql> select * from t where a-10 = 18446744073709551607;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> select * from t ignore index(idx) where a-10 = 18446744073709551607;
Empty set (0.00 sec)

also it is wrong if your condition is a-10=-9

[21 Dec 2022 10:05] x j

also for generate column with an index on it
create table t(a int, b int unsigned as (a-10) virtual, index idx(b));
set @@sql_mode='';
mysql> insert into t(a) values (1);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from t where a - 10 = 0;
+------+------+
| a    | b    |
+------+------+
|    1 |    0 |
+------+------+
1 row in set (0.00 sec)

[21 Dec 2022 12:15] MySQL Verification Team

Hello x j,

Thank you for the report and test case.

regards,
Umesh

[3 Jan 2023 8:04] huahua xu

test case 1:

mysql> drop table t1;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t1(a int, b int unsigned as (cast(a-10 as unsigned)) virtual, index idx(b));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 (a) values (1);
ERROR 1264 (22003): Out of range value for column 'b' at row 1

test case 2:

mysql> drop table t1;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t1(a int, b bigint unsigned as (cast(a-10 as unsigned)) virtual, index idx(b));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 (a) values (1);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+------+----------------------+
| a    | b                    |
+------+----------------------+
|    1 | 18446744073709551607 |
+------+----------------------+

In the case 1, the type of column `b` is defined as Field_long, but it is Field_longlong in the case 2. Obviously, their internal processing logic is different, and I think that it is a bug about `Field_longlong::store`:

diff --git a/sql/field.cc b/sql/field.cc
index fb03f3ae1a1..b38f96c244e 100644
--- a/sql/field.cc
+++ b/sql/field.cc
@@ -3913,7 +3913,8 @@ type_conversion_status Field_longlong::store(longlong nr, bool unsigned_val) {
       if field is unsigned and value is signed (< 0) or
       if field is signed and value is unsigned we have an overflow
     */
-    if (is_unsigned() != unsigned_val) {
+    if (is_unsigned()
+          || is_unsigned() != unsigned_val) {
       nr = is_unsigned() ? (ulonglong)0 : (ulonglong)LLONG_MAX;
       set_warning(Sql_condition::SL_WARNING, ER_WARN_DATA_OUT_OF_RANGE, 1);
       error = TYPE_WARN_OUT_OF_RANGE;