John Jove added a comment - 2025-03-07 06:11 Hi, shall we change this bug status as confirmed? I also try the BLOB data type with NOT NULL, in which a correct result is returned. CREATE TABLE t1 (c1 TINYBLOB NOT NULL ); INSERT INTO t1 VALUES (1); SELECT c1 FROM t1 WHERE 'a' BETWEEN 0 AND (c1); -- {0x31} It seems that the NOT NULL constraint causes a different calculation from the UNIQUE constraint against the BLOB data type.

Sergei Golubchik added a comment - 2025-03-07 16:53 CREATE TABLE t1 (c1 TINYBLOB, UNIQUE (c1(2))) engine=innodb; INSERT INTO t1 (c1) VALUES (1); SELECT c1 FROM t1 WHERE 'a' BETWEEN 0 AND (c1); # no rows alter table t1 engine=myisam; SELECT c1 FROM t1 WHERE 'a' BETWEEN 0 AND (c1); # 1 row drop table t1;

John Jove added a comment - 2025-03-14 15:36 I observe that if we specify the index without the length of column c1 like follows, a correct result is returned. CREATE TABLE t1 (c1 TINYBLOB, UNIQUE (c1)) engine=innodb; INSERT INTO t1 (c1) VALUES (1); SELECT c1 FROM t1 WHERE 'a' BETWEEN 0 AND (c1); -- {1}

Dave Gosselin added a comment - 1 week ago I tried the following on MySQL 8.3.0: mysql> select @@version; +-------------+ | @@version | +-------------+ | 8.3.0-debug | +-------------+ 1 row in set (0.00 sec)   mysql> CREATE TABLE t1 (c1 TINYBLOB, UNIQUE (c1(2))) engine=innodb; Query OK, 0 rows affected (0.04 sec)   mysql> INSERT INTO t1 (c1) VALUES (1); Query OK, 1 row affected (0.01 sec)   mysql> SELECT c1 FROM t1 WHERE 'a' BETWEEN 0 AND (c1); # no rows Empty set (0.00 sec)   mysql> alter table t1 engine=myisam; Query OK, 1 row affected (0.04 sec) Records: 1 Duplicates: 0 Warnings: 0   mysql> SELECT c1 FROM t1 WHERE 'a' BETWEEN 0 AND (c1); # 1 row +------------+ | c1 | +------------+ | 0x31 | +------------+ 1 row in set, 1 warning (0.00 sec)   mysql> drop table t1; Query OK, 0 rows affected (0.01 sec) and mysql> CREATE TABLE t1 (c1 TINYBLOB NOT NULL); Query OK, 0 rows affected (0.04 sec)   mysql> INSERT INTO t1 VALUES (1); Query OK, 1 row affected (0.00 sec)   mysql> SELECT c1 FROM t1 WHERE 'a' BETWEEN 0 AND (c1); +--------+ | c1 | +--------+ | 0x31 | +--------+ 1 row in set, 1 warning (0.00 sec) The following is not supported on MySQL 8.3.0: mysql> CREATE TABLE t1 (c1 TINYBLOB, UNIQUE (c1)) engine=innodb; ERROR 1170 (42000): BLOB/TEXT column 'c1' used in key specification without a key length

Dave Gosselin added a comment - 2 days ago This is a problem with how range optimization is performed during make_join_statistics . The following is NOT a solution for this bug; rather it illustrates the point. If we disable range analysis for all queries, then we will return a correct result. This can be done by changing the following conditional in make_join_statistics such that it always evaluates to false (in fact range analysis is disallowed for materialized subqueries, derived tables, and derived views as noted). 6208 /* 6209 Perform range analysis if there are keys it could use (1). 6210 Don't do range analysis for materialized subqueries (2). 6211 Don't do range analysis for materialized derived tables/views (3) 6212 */ 6213 const bool DEBUG_RANGE_ENABLED= false; 6214 if (DEBUG_RANGE_ENABLED && (!s->const_keys.is_clear_all() || 6215 !bitmap_is_clear_all(map: &s->table->cond_set)) && // (1) 6216 !s->table->is_filled_at_execution() && // (2) 6217 !(s->table->pos_in_table_list->derived && // (3) 6218 s->table->pos_in_table_list->is_materialized_derived())) // (3) 6219 { 6220 bool impossible_range= FALSE; 6221 ha_rows records= HA_ROWS_MAX; 6222 SQL_SELECT *select= 0; 6223 Item **sargable_cond= NULL; 6224 if (!s->const_keys.is_clear_all()) I will continue to debug the issue to a root cause from this point.