[MDEV-19045] Change in behavior upon creation of unlimited non-unique indexes Created: 2019-03-25  Updated: 2019-03-26  Resolved: 2019-03-26

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table, Data types
Affects Version/s: 10.4
Fix Version/s: 10.4.4

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Problem/Incident
is caused by MDEV-371 Unique indexes for blobs Closed

 Description   

Before MDEV-371, an attempt to add an unlimited (non-unique) index on a blob column caused an error, regardless the strictness of SQL_MODE:

create table t1 (f text);
 
alter table t1 add index ind1 (f);

10.3 dcdeb394

 
mysqltest: At line 2: query 'alter table t1 add index ind1 (f)' failed: 1170: BLOB/TEXT column 'f' used in key specification without a key length

Now instead it causes a warning, also regardless the strictness of SQL_MODE, and an index is created with automatically specified length:

10.4 c0ba036b

 
alter table t1 add index ind1 (f);
 
Warnings:
 
Note	1071	Specified key was too long; max key length is 1000 bytes
 
show create table t1;
 
Table	Create Table
 
t1	CREATE TABLE `t1` (
 
  `f` text DEFAULT NULL,
 
  KEY `ind1` (`f`(1000))
 
) ENGINE=MyISAM DEFAULT CHARSET=latin1

If the change is intentional, it needs to be documented somewhere.

 Comments   
Comment by Sachin Setiya (Inactive) [ 2019-03-26 ]

In 10.4

MariaDB [d]> create table t1(a blob , index(a)); show create table t1;                                                              
Query OK, 0 rows affected, 1 warning (0.002 sec)
 
 
 
+-------+-----------------------------------------------------------------------------------------------------------+                                         
| Table | Create Table                                                                                              |                                         
+-------+-----------------------------------------------------------------------------------------------------------+                                         
| t1    | CREATE TABLE `t1` (
 
  `a` blob DEFAULT NULL,
 
  KEY `a` (`a`(1000))
 
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
 
+-------+-----------------------------------------------------------------------------------------------------------+                                         
1 row in set (0.002 sec)

In 10.3

MariaDB [d]> create table t1(a blob , index(a));
 
ERROR 1170 (42000): BLOB/TEXT column 'a' used in key specification without a key length

Comment by Elena Stepanova [ 2019-03-26 ]

The change was intentional, introduced by commit https://github.com/MariaDB/server/commit/0477e80522adb28 , with the goal to make the behavior consistent with limited but too long indexes which would be shortened with a warning before, e.g.

create table t1 (a text);
 
--error 1170
 
alter table t1 add index (a);
 
alter table t1 add index (a(2000));

Generated at Thu Feb 08 08:48:39 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.