Bug #26832 ENUM types not optimized

Description:
enum types not optimized when i search a value that don't bellow to enum values, like
enum('a','b','c')
select * from table where enum_field='d'

this make an table scan or index scan, see the screnshots from my query

How to repeat:
create a table with enum and search with a non enum value

Suggested fix:
when using enum fields check it and make a 0 rows optimization before start table scan optimizations

Thank you for a problem report, and sorry for a delay with its processing. Please, check with a newer version, 5.1.17, and, in case of the same problem, send a complete test case that demonstrates the behaviour described.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Roberto,

Please, check with a newer version, 5.1.17, and, in case of the same problem,
send a complete test case that demonstrates the behaviour described.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

the problem continue
create table with a ENUM field and select with a value that´s not in enum values

ok it´s nice to read raw values of myisam tables when crashed, but maybe a optimization could help us here, i don´t know if we will have problems, since a enum not null field with a value diferent from enum values isn´t a NULL value, it´s just a NOVALUE?! i don´t know what to do here hehe, maybe a warning when INSERT or UPDATE? i don´t know what to do with this

select * from mov_contacorrente where lote_tipo='beto' (2 seconds, 700000rows in table, full table scan)

create table:
CREATE TABLE `mov_contacorrente` (
  `cc_hash_key` varchar(75) NOT NULL DEFAULT '',
  `origem_tipo` enum('cob','doc','bc') NOT NULL DEFAULT 'doc',
  `hash_origem` varchar(75) NOT NULL DEFAULT '',
  `linha_digitavel` varchar(75) NOT NULL,
  `plano_cobranca` int(10) unsigned NOT NULL DEFAULT '0',
  `tipo_operacao` enum('tr','mp','cv','ie','ma','') NOT NULL DEFAULT '',
  `tipo_cobranca` enum('dv','cd','cs') NOT NULL DEFAULT 'dv',
  `evento_id` int(10) unsigned NOT NULL DEFAULT '0',
  `sacador_tipo` enum('f','j') NOT NULL DEFAULT 'f',
  `sacador_id` bigint(20) NOT NULL DEFAULT '0',
  `vendedor_tipo` enum('f','j') NOT NULL DEFAULT 'f',
  `vendedor_id` int(10) unsigned NOT NULL DEFAULT '0',
  `credor_tipo` enum('f','j') NOT NULL DEFAULT 'f',
  `credor_id` bigint(20) NOT NULL DEFAULT '0',
  `credor_ca` bigint(20) NOT NULL DEFAULT '0',
  `credor_co` int(11) NOT NULL DEFAULT '0',
  `credor_centro` bigint(20) NOT NULL DEFAULT '0',
  `credor_conta` bigint(20) unsigned NOT NULL DEFAULT '0',
  `credor_carteira` bigint(20) unsigned NOT NULL DEFAULT '0',
  `credor_convenio` bigint(20) unsigned NOT NULL DEFAULT '0',
  `devedor_tipo` enum('f','j') NOT NULL DEFAULT 'f',
  `devedor_id` bigint(20) NOT NULL DEFAULT '0',
  `devedor_ca` bigint(20) NOT NULL DEFAULT '0',
  `devedor_co` int(11) NOT NULL DEFAULT '0',
  `devedor_centro` bigint(20) NOT NULL DEFAULT '0',
  `devedor_conta` bigint(20) unsigned NOT NULL DEFAULT '0',
  `devedor_carteira` bigint(20) unsigned NOT NULL DEFAULT '0',
  `praca_pagamento_tipo` enum('f','j') NOT NULL DEFAULT 'f',
  `praca_pagamento_id` bigint(20) NOT NULL DEFAULT '0',
  `liquidez` float(5,2) NOT NULL DEFAULT '0.00',
  `prioridade_pgto` mediumint(3) unsigned NOT NULL DEFAULT '0',
  `moeda` char(3) NOT NULL DEFAULT 'R$',
  `valor_face` decimal(17,5) NOT NULL DEFAULT '0.00000',
  `valor_residual` decimal(17,5) NOT NULL DEFAULT '0.00000',
  `valor_residual_compensado` decimal(17,5) NOT NULL DEFAULT '0.00000',
  `valor_emdigitacao` decimal(17,5) NOT NULL DEFAULT '0.00000',
  `valor_pago_moeda_default` decimal(17,5) NOT NULL DEFAULT '0.00000',
  `valor_pago_dinheiro_default` decimal(17,5) NOT NULL DEFAULT '0.00000',
  `sit_cobranca` enum('n','s','l','lb') NOT NULL DEFAULT 'n',
  `data_vencimento_face` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `data_emissao_face` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `data_vencimento_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `data_vencimento_credor` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `data_vencimento_devedor` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `data_vencimento_auto` enum('Y','N') NOT NULL DEFAULT 'N',
  `data_entrada_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `data_entrada_credor` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `data_entrada_devedor` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `data_liquidacao_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `data_compensacao_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `data_compensacao_credor` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `data_compensacao_devedor` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `data_ultima_cobranca` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `data_proxima_cobranca` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `negativado` enum('Y','N') NOT NULL DEFAULT 'N',
  `protestado` enum('Y','N') NOT NULL DEFAULT 'N',
  `protesto_data` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `tabeliao_tipo` enum('f','j') NOT NULL DEFAULT 'f',
  `tabeliao_id` bigint(20) NOT NULL DEFAULT '0',
  `multa_data_prevista` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `multa_data` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `multa_valor_tipo` enum('%','$','%rs') NOT NULL DEFAULT '%',
  `multa_valor` decimal(17,5) NOT NULL DEFAULT '0.00000',
  `multa_valor_cobrado` decimal(17,5) NOT NULL DEFAULT '0.00000',
  `juros_data_prevista` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `juros_tipo` enum('$','%fs','%fc','%rs') NOT NULL DEFAULT '$',
  `juros_valor` decimal(17,5) NOT NULL DEFAULT '0.00000',
  `juros_periodo` mediumint(3) unsigned NOT NULL DEFAULT '1',
  `juros_periodo_tipo` enum('dia','semana','mes','ano') NOT NULL DEFAULT 'dia',
  `repasse_por` double NOT NULL DEFAULT '0',
  `repasse_pessoa_tipo` enum('f','j') NOT NULL DEFAULT 'f',
  `repasse_pessoa_id` bigint(20) NOT NULL DEFAULT '0',
  `contador_relacionamentos` int(10) unsigned NOT NULL DEFAULT '0',
  `contador_repasse` int(10) unsigned NOT NULL DEFAULT '0',
  `contador_ocorrencia` int(10) unsigned NOT NULL DEFAULT '0',
  `contador_juros` int(10) unsigned NOT NULL DEFAULT '0',
  `bordero` decimal(10,1) NOT NULL DEFAULT '0.0',
  `lote_unidade` int(11) NOT NULL DEFAULT '0',
  `lote_data` date NOT NULL DEFAULT '0000-00-00',
  `lote_tipo` char(1) NOT NULL DEFAULT 'r',
  `lote_numero` int(10) NOT NULL DEFAULT '0',
  `digitador_tipo` enum('f','j') NOT NULL DEFAULT 'f',
  `digitador_id` bigint(20) NOT NULL DEFAULT '0',
  `sit_spa` enum('d','s','p','a','mov') NOT NULL DEFAULT 'd',
  `data_spa_d` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `data_spa_s` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `data_spa_p` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `data_spa_a` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `data_spa_mov` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `repasse_tipo` enum('total','cob') NOT NULL DEFAULT 'total',
  `importado` varchar(25) NOT NULL,
  `rodado` enum('Y','N') NOT NULL DEFAULT 'N',
  `desconto_data_prevista` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `desconto_tipo` enum('$','%fs','%fc','%rs') NOT NULL DEFAULT '%fs',
  `desconto_valor` decimal(17,5) NOT NULL DEFAULT '0.00000',
  `pess_mov_tipo` enum('f','j') NOT NULL DEFAULT 'f',
  `pess_mov_id` int(10) unsigned NOT NULL DEFAULT '0',
  `desconto_financeiro` decimal(17,5) NOT NULL DEFAULT '0.00000',
  `imposto_retido` decimal(17,5) NOT NULL DEFAULT '0.00000',
  `devedor_ca_descfin` int(11) NOT NULL DEFAULT '0',
  `credor_ca_descfin` int(11) NOT NULL DEFAULT '0',
  `devedor_ca_impret` int(11) NOT NULL DEFAULT '0',
  `credor_ca_impret` int(11) NOT NULL DEFAULT '0',
  `recompra_Valor` decimal(17,5) NOT NULL DEFAULT '0.00000',
  `sit_venda_titulo` enum('','d','r','l') NOT NULL DEFAULT '',
  `agente_financeiro_tipo` enum('f','j') NOT NULL DEFAULT 'f',
  `agente_financeiro_id` int(11) NOT NULL DEFAULT '0',
  `data_venda_bordero` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `data_recompra` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`cc_hash_key`),
  KEY `origem` (`hash_origem`,`cc_hash_key`),
  KEY `bordero` (`sacador_tipo`,`sacador_id`,`bordero`),
  KEY `lote` (`lote_unidade`,`lote_data`,`lote_numero`),
  KEY `devedor` (`devedor_tipo`,`devedor_id`,`data_vencimento_gmt`),
  KEY `residual_devedor` (`valor_residual`,`devedor_tipo`,`devedor_id`),
  KEY `credor_emi` (`credor_tipo`,`credor_id`,`data_emissao_face`),
  KEY `devedor_emi` (`devedor_tipo`,`devedor_id`,`data_emissao_face`),
  KEY `credor` (`credor_tipo`,`credor_id`,`data_vencimento_gmt`,`credor_convenio`),
  KEY `vencimento` (`data_vencimento_gmt`),
  KEY `venc` (`data_vencimento_gmt`,`cc_hash_key`),
  KEY `emi` (`data_emissao_face`,`cc_hash_key`),
  KEY `t_bol` (`evento_id`,`lote_tipo`,`importado`,`cc_hash_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
PARTITIONS 4 */

stay with this problem in mysql 5.5
changing to performace not serious

I had never seen complete, correct and repeatable test case in this bug report. In your last table the column you refer to is NOT ENUM:

 `lote_tipo` char(1) NOT NULL DEFAULT 'r',

and is NOT indexed. Why do you expect anything else bug full table scan is beyond my understanding.

In my test simple test case:

mysql> show create table te\G
*************************** 1. row ***************************
       Table: te
Create Table: CREATE TABLE `te` (
  `c1` enum('a','b','c') DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  KEY `c1` (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> insert into te values('1',1),('2',2);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into te select * from te;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into te select * from te;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into te select * from te;
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> insert into te select * from te;
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql> insert into te select * from te;
Query OK, 32 rows affected (0.01 sec)
Records: 32  Duplicates: 0  Warnings: 0

mysql> insert into te select * from te;
Query OK, 64 rows affected (0.01 sec)
Records: 64  Duplicates: 0  Warnings: 0

mysql> insert into te select * from te;
Query OK, 128 rows affected (0.02 sec)
Records: 128  Duplicates: 0  Warnings: 0

mysql> insert into te values('3',3);
Query OK, 1 row affected (0.00 sec)

mysql> explain select * from te where c1 = '3';
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | te    | ref  | c1            | c1   | 2       | const |    1 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.02 sec)

mysql> explain select * from te where c1 = '1';
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | te    | ref  | c1            | c1   | 2       | const |  128 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from te where c1 = '4';
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | te    | ref  | c1            | c1   | 2       | const |    1 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)

I see expected results. Even though '4' is not a valid value for ENUM, index is used to search for it, and will aloow to find out fast enough zero rows with this value exist.

Is this just a feature request to check if value for ENUM column is valid one even before optimization? Please, clarify.

sorry about no test case, i will try to explain with your example

------
... all insert´s / create table on your example was done ... continuing ...

insert into te values('g',1),('f',2);
/* 2 rows affected, 0 rows found. Duration for 2 queries: 0,093 sec. */
sql_mode: PIPES_AS_CONCAT,ALLOW_INVALID_DATES

show warnings;
"Level","Code","Message"
"Warning","1265","Dado truncado para coluna 'c1' na linha 1"
"Warning","1265","Dado truncado para coluna 'c1' na linha 2"
------

no problems... it´s what i want... but there´s a 'default' value when truncate occur (i don´t know if it´s a null value, or '' or 0 index of enum field, but we can select it with where c1=0 or c1='' sometimes, i didn´t checked this in mysql docs) check:

------
select * from te ;
"c1","c2"
... some values are repeated....
"a","1"
"b","2"
"c","3"
... last two rows: ...
"","1"
"","2"
------

"" -> the 'default' value for truncated field (ok no problems)

the problem is:
------
select * from te where c1 = 'g';
the result: 
/* 0 rows affected, 0 rows found. Duration for 1 query: 0,093 sec. */

but check that no optimization was done

explain select * from te where c1 = 'g';
"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
"1","SIMPLE","te","ALL",NULL,NULL,NULL,NULL,"261","Using where"
------
full table scan!? 261 rows!

i was thinking about something like this result:

------
delete from te;
explain select * from te where c1 = 'g';
"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
"1","SIMPLE",NULL,NULL,NULL,NULL,NULL,NULL,NULL,"Impossible WHERE noticed after reading const tables"
------
no row scan since c1='g' don´t exists and it´s not a default value for truncated values c1=0 or c1='' for example

but check that this works: (without deleting before...)

------
this: select * from te where c1=0
and this: select * from te where c1=''
result the same values for truncated values (i think it´s ok):

"c1","c2"
"","1"
"","2"
------
nice, it´s what i want too select with 'default' truncated value for enum

.............................
the optimization idea:
select * from te where c1=0 or select * from te where c1=''
use the default 'truncated' value like today implementation, no changes/feature request here

but if it´s a truncated value where
select * from te where c1='abc' (c1='abc' will return 0 rows)

check if enum('abc') exists (default truncated values are alowed like c1=0 or c1='')
if not c1='abc' should be rewrite as something like '0=1' 
like this:

------
explain select * from te  where 0=1
"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
"1","SIMPLE",NULL,NULL,NULL,NULL,NULL,NULL,NULL,"Impossible WHERE"
------
no full table scan! :)

c1='abc' is a impossible WHERE
but c1='' or c1=0 isn´t a impossible WHERE for truncated values (nice!)

result: no full table scan for enum fields
this is good for some alter table, and not optimized app
i don´t know if it´s easy to implement, but it´s a good optimization for 'bad' coded apps

don´t need index here

OK, we have a reasonable feature request here for new kind of "Impossible WHERE" check for ENUMs.

nice! :)
any help contact me =)

this apply to SET() type too?
i never used SET i preffer two tables (1:N tables with foreign key for this, or app serialization) could you test it? i don´t know how to use SET fields

changed to any OS
need some test with SET field type but ENUM type optimizer 'work', is near to set type 'work'