linux

analyze table nf_erro_20130620 ;
don't help here...

SELECT
*
FROM nf_erro_20130620 WHERE
emitente_tipo='j' AND emitente_id=1 AND emitente_propriedade=0 AND
nf_serie="3nfe" AND
nf>-100000000000000 and nf<0
with a nf> -100000000000000000 worked
maybe nf<0 have some problem in MIN_LIMIT inside opt_range.cc?

Hi Roberto,

Did you attempt to edit the dump manually?
It looks tampered with.

Please send SHOW CREATE TABLE exactly like it is in the client, without any editing.

Roberto,

If you paste the EXPLAIN (as you did updating the description), please paste ALL of it, not just fragments you find surprising.
And please provide the information requested above, otherwise I'll have to close the bug as incomplete, because your dump won't even load.

Please also clarify what you mean by 'affected version 10.0.4' – where you got it, how you built it.

sorry about version, i was editing the jira report but it didn't allowed the 10.0.1 and there's no 10.0.3 anymore, i put 10.0.4, i'm testing with 10.0.3 now, but i think it will have the same error
how to reproduce:
download the sql.tgz version (2GB sql file)
run the script
and run the query with explain
it will return impossible WHERE
and the extended explain show a query with WHERE 0

it should result in one row, the same using nf=-5
but if you do
nf<0 and nf >-1000 it works
i think that nf<0 isn't setting the lower limit and maybe thinking that the field is a unsigned value?! i don't know why it return no rows yet

Roberto,

Once again, please provide full output of SHOW CREATE TABLE.
And please answer the question, Did you attempt to edit the dump manually?

just one minute i will get the create table, the server is down now, or the internet link is slow

show create table:
CREATE TABLE `nf_erro_20130620` (
`emitente_tipo` enum('f','j') NOT NULL DEFAULT 'f',
`emitente_id` int(10) unsigned NOT NULL DEFAULT '0',
`emitente_propriedade` tinyint(3) unsigned NOT NULL DEFAULT '0',
`nf_serie` char(5) NOT NULL DEFAULT 'A',
`nf` int(11) NOT NULL DEFAULT '0',
`id_item` int(11) NOT NULL DEFAULT '0',
`status` enum('ok','c','d','nf','i') NOT NULL DEFAULT 'ok',
`unidade_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
`lote_tipo` enum('v','c') NOT NULL DEFAULT 'v',
`lote_spa` decimal(12,1) NOT NULL DEFAULT '0.0',
`oe_tipo` enum('oe','fec') NOT NULL DEFAULT 'oe',
`oe` bigint(20) NOT NULL DEFAULT '0',
`oe_seq` mediumint(9) NOT NULL DEFAULT '0',
`item_id` smallint(5) unsigned NOT NULL DEFAULT '0',
`item_id_red` mediumint(9) NOT NULL DEFAULT '0',
`item_codigo` char(30) NOT NULL DEFAULT '0',
`item_desc` char(100) NOT NULL DEFAULT '',
`servico` enum('Y','N') NOT NULL DEFAULT 'N',
`documento_auxiliar` enum('Y','N') NOT NULL DEFAULT 'N',
`cfop_id` tinyint(3) unsigned NOT NULL DEFAULT '0',
`cfop_id_red` int(11) NOT NULL DEFAULT '0',
`cfop_numero` varchar(10) NOT NULL DEFAULT '',
`class_fiscal` char(20) NOT NULL DEFAULT '',
`sit_trib` char(3) NOT NULL DEFAULT '',
`sit_trib_ipi` char(3) NOT NULL DEFAULT '',
`sit_trib_pis` char(3) NOT NULL DEFAULT '',
`sit_trib_cofins` char(3) NOT NULL DEFAULT '',
`sit_trib_iss` varchar(1) NOT NULL DEFAULT '',
`sit_trib_sn` varchar(4) NOT NULL DEFAULT '',
`icms_incluir_bc` varchar(5) NOT NULL DEFAULT '',
`icms_subst_incluir_bc` varchar(5) NOT NULL DEFAULT '',
`ipi_incluir_bc` varchar(5) NOT NULL DEFAULT '',
`pis_inclur_bc` varchar(5) NOT NULL DEFAULT '',
`cofins_incluir_bc` varchar(5) NOT NULL DEFAULT '',
`ii_incluir_bc` varchar(5) NOT NULL DEFAULT '',
`sn_incluir_bc` varchar(5) NOT NULL DEFAULT '',
`un_fat` char(5) NOT NULL DEFAULT '',
`quant` decimal(17,5) NOT NULL DEFAULT '0.00000',
`pecas` decimal(17,5) NOT NULL DEFAULT '0.00000',
`pbruto` decimal(17,5) NOT NULL DEFAULT '0.00000',
`pliq` decimal(17,5) NOT NULL DEFAULT '0.00000',
`vliq` decimal(17,5) NOT NULL DEFAULT '0.00000',
`vbruto` decimal(17,5) NOT NULL DEFAULT '0.00000',
`valor_pauta` decimal(17,5) NOT NULL DEFAULT '0.00000',
`valor_pauta_grandeza` enum('un','up','pl','pb','@pl','@pb','vl','vb') NOT NULL DEFAULT 'un',
`valor_pauta_grandeza_quant` decimal(17,5) NOT NULL DEFAULT '1.00000',
`valor_produto_un` decimal(17,5) NOT NULL DEFAULT '0.00000',
`valor_produto_grandeza` enum('un','up','pl','pb','@pl','@pb','vl','vb') NOT NULL DEFAULT 'un',
`valor_produto_grandeza_quant` decimal(10,5) NOT NULL DEFAULT '1.00000',
`valor_produto_tabela_un` decimal(17,5) NOT NULL DEFAULT '0.00000',
`valor_produto_total` decimal(17,5) NOT NULL DEFAULT '0.00000',
`valor_frete` decimal(17,5) NOT NULL DEFAULT '0.00000',
`valor_seguro` decimal(17,5) unsigned NOT NULL DEFAULT '0.00000',
`valor_outras` decimal(17,5) unsigned NOT NULL DEFAULT '0.00000',
`valor_estimado_impostos` decimal(17,5) unsigned NOT NULL DEFAULT '0.00000' COMMENT 'lei da transparencia',
`valor_estimado_impostos_por` decimal(17,5) unsigned NOT NULL DEFAULT '0.00000',
`desconto` decimal(17,5) NOT NULL DEFAULT '0.00000',
`icms_de_aliq` decimal(10,7) NOT NULL DEFAULT '0.0000000',
`icms_de_red` decimal(10,7) NOT NULL DEFAULT '0.0000000',
`icms_fe_aliq` decimal(10,7) NOT NULL DEFAULT '0.0000000',
`icms_fe_red` decimal(10,7) NOT NULL DEFAULT '0.0000000',
`icms_basecalculo` decimal(17,5) NOT NULL DEFAULT '0.00000',
`icms_valor` decimal(17,5) NOT NULL DEFAULT '0.00000',
`icms_subst_ind_convenio` decimal(10,7) NOT NULL DEFAULT '0.0000000',
`icms_subst_basecalculo` decimal(17,5) NOT NULL DEFAULT '0.00000',
`icms_subst_valor` decimal(17,5) NOT NULL DEFAULT '0.00000',
`ipi_aliq` decimal(10,7) NOT NULL DEFAULT '0.0000000',
`ipi_red` decimal(10,7) NOT NULL DEFAULT '0.0000000',
`ipi_basecalculo` decimal(17,5) NOT NULL DEFAULT '0.00000',
`ipi_valor` decimal(17,5) NOT NULL DEFAULT '0.00000',
`iss_aliq` decimal(10,7) NOT NULL DEFAULT '0.0000000',
`iss_red` decimal(10,7) NOT NULL DEFAULT '0.0000000',
`iss_basecalculo` decimal(17,5) NOT NULL DEFAULT '0.00000',
`iss_valor` decimal(17,5) NOT NULL DEFAULT '0.00000',
`pis_aliq` decimal(10,5) NOT NULL DEFAULT '0.00000',
`pis_red` decimal(10,5) NOT NULL DEFAULT '0.00000',
`pis_basecalculo` decimal(17,5) NOT NULL DEFAULT '0.00000',
`pis_valor` decimal(17,5) NOT NULL DEFAULT '0.00000',
`cofins_aliq` decimal(10,5) NOT NULL DEFAULT '0.00000',
`cofins_red` decimal(10,5) NOT NULL DEFAULT '0.00000',
`cofins_basecalculo` decimal(17,5) NOT NULL DEFAULT '0.00000',
`cofins_valor` decimal(17,5) NOT NULL DEFAULT '0.00000',
`funrural_aliq` decimal(10,5) NOT NULL DEFAULT '0.00000',
`funrural_red` decimal(10,5) NOT NULL DEFAULT '0.00000',
`funrural_basecalculo` decimal(17,5) NOT NULL DEFAULT '0.00000',
`funrural_valor` decimal(17,5) NOT NULL DEFAULT '0.00000',
`sn_aliq` decimal(10,5) NOT NULL DEFAULT '0.00000',
`sn_red` decimal(10,5) NOT NULL DEFAULT '0.00000',
`sn_basecalculo` decimal(17,5) NOT NULL DEFAULT '0.00000',
`sn_valor` decimal(17,5) NOT NULL DEFAULT '0.00000',
`cide_aliq` decimal(10,7) NOT NULL DEFAULT '0.0000000',
`cide_red` decimal(10,7) NOT NULL DEFAULT '0.0000000',
`cide_basecalculo` decimal(17,5) NOT NULL DEFAULT '0.00000',
`cide_valor` decimal(17,5) NOT NULL DEFAULT '0.00000',
`csll_aliq` decimal(10,7) NOT NULL DEFAULT '0.0000000',
`csll_red` decimal(10,7) NOT NULL DEFAULT '0.0000000',
`csll_basecalculo` decimal(17,5) NOT NULL DEFAULT '0.00000',
`csll_valor` decimal(17,5) NOT NULL DEFAULT '0.00000',
`sit_trib_csll` varchar(4) NOT NULL DEFAULT '',
`inss_aliq` decimal(10,7) NOT NULL DEFAULT '0.0000000',
`inss_red` decimal(10,7) NOT NULL DEFAULT '0.0000000',
`inss_basecalculo` decimal(17,5) NOT NULL DEFAULT '0.00000',
`inss_valor` decimal(17,5) NOT NULL DEFAULT '0.00000',
`sit_trib_inss` varchar(4) NOT NULL DEFAULT '',
`ir_aliq` decimal(10,7) NOT NULL DEFAULT '0.0000000',
`ir_red` decimal(10,7) NOT NULL DEFAULT '0.0000000',
`ir_basecalculo` decimal(17,5) NOT NULL DEFAULT '0.00000',
`ir_valor` decimal(17,5) NOT NULL DEFAULT '0.00000',
`sit_trib_ir` varchar(4) NOT NULL DEFAULT '',
`ii_aliq` decimal(10,7) NOT NULL DEFAULT '0.0000000',
`ii_red` decimal(10,7) NOT NULL DEFAULT '0.0000000',
`ii_basecalculo` decimal(17,5) NOT NULL DEFAULT '0.00000',
`ii_valor` decimal(17,5) NOT NULL DEFAULT '0.00000',
`sit_trib_ii` varchar(4) NOT NULL DEFAULT '',
`numero_pedido_compra` varchar(25) NOT NULL DEFAULT '',
`numero_item_pedido` varchar(25) NOT NULL DEFAULT '',
`obs_item` varchar(255) NOT NULL DEFAULT '',
`quant_devolvida` decimal(17,5) unsigned NOT NULL DEFAULT '0.00000',
`pecas_devolvida` decimal(17,5) unsigned NOT NULL DEFAULT '0.00000',
`pbruto_devolvida` decimal(17,5) unsigned NOT NULL DEFAULT '0.00000',
`pliq_devolvida` decimal(17,5) unsigned NOT NULL DEFAULT '0.00000',
`vliq_devolvida` decimal(17,5) unsigned NOT NULL DEFAULT '0.00000',
`vbruto_devolvida` decimal(17,5) unsigned NOT NULL DEFAULT '0.00000',
`ii_anterior_un` decimal(17,5) unsigned NOT NULL DEFAULT '0.00000',
`ipi_anterior_un` decimal(17,5) unsigned NOT NULL DEFAULT '0.00000',
`pis_anterior_un` decimal(17,5) unsigned NOT NULL DEFAULT '0.00000',
`cofins_anterior_un` decimal(17,5) unsigned NOT NULL DEFAULT '0.00000',
PRIMARY KEY (`emitente_tipo`,`emitente_id`,`emitente_propriedade`,`nf_serie`,`nf`,`id_item`),
KEY `spamov` (`unidade_id`,`lote_tipo`,`lote_spa`,`item_id`,`item_id_red`),
KEY `item_id` (`emitente_tipo`,`emitente_id`,`emitente_propriedade`,`nf_serie`,`nf`,`item_id`,`item_id_red`)
) ENGINE=Aria DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (nf)
(PARTITION s0 VALUES LESS THAN (100000) ENGINE = Aria,
PARTITION s1 VALUES LESS THAN (450000) ENGINE = Aria,
PARTITION s2 VALUES LESS THAN (800000) ENGINE = Aria,
PARTITION s3 VALUES LESS THAN MAXVALUE ENGINE = Aria) */

no i didn't changed the dump

hum the dump shows:
)/*! engine=Aria partitioned */;
i will check mysql_dump verision

sorry server was using a old version of mysql 5.0.60 mysqldump
/usr/bin/mysqldump --version
mysqldump Ver 10.11 Distrib 5.0.60, for unknown-linux-gnu (x86_64)

elena, i updated the mysqldump and add a new file to download
i'm running in 10.0.3 and it didn't return the row too

Thank you.

i will do some other works, but i think it's something in opt_range at partition prune, i will turn off partition prune and test

no partition prune switch in optimizer_switch?

Attached a reduced dump.
Partitioning is irrelevant.
Aria seems important (not reproducible with same dump, but MyISAM).

To reproduce, load the dump and execute

SELECT * FROM test.t7 WHERE emitente_tipo='j' AND emitente_id=1 AND emitente_propriedade=0 AND nf_serie='3nfe' AND nf < 0 ;

nice elena, thanks a lot!

any idea about a patch? this make aria useless to me

Let's see...

1. Try on Aria table
   MariaDB [j81]> explain SELECT * FROM t7 WHERE emitente_tipo='j' AND emitente_id=1 AND emitente_propriedade=0 AND nf_serie='3nfe' AND nf < 0 ;
   -------------------------------------------------------------------------------------------------------------+

   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

   -------------------------------------------------------------------------------------------------------------+

1. Try on a MyISAM table with the same structure/data:
   MariaDB [j81]> explain SELECT * FROM t7m WHERE emitente_tipo='j' AND emitente_id=1 AND emitente_propriedade=0 AND nf_serie='3nfe' AND nf < 0 ;
   -------------------------------------------------------------------------------------+

   id

   select_type

   table

   type

   possible_keys

   key

   key_len

   ref

   rows

   Extra

   -------------------------------------------------------------------------------------+

   1

   SIMPLE

   t7m

   range

   PRIMARY,item_id

   item_id

   15

   NULL

   1

   Using index condition

   -------------------------------------------------------------------------------------+
   1 row in set (0.00 sec)

Debugging, I see that in case of Aria, range analyzer has returned that "impossible range".
ha_maria::records_in_range (this=0x7fff9402f868, inx=0, min_key=0x7fffc87c41b0, max_key=0x7fffc87c41d0)= 0

Looking at key#0 definition and WHERE:

PRIMARY KEY (`emitente_tipo`,`emitente_id`,`emitente_propriedade`,`nf_serie`,`nf`,`id_item`),

emitente_tipo='j' – pk1
AND emitente_id=1 – pk2
AND emitente_propriedade=0 – pk3
AND nf_serie="3nfe" – pk4
AND nf<0 – pk5

1. Note that there is no restriction for the last PK component, id_item.

The problem seems to be in the ha_maria::records_in_range(). If make that function return 10 (in gdb), the query returns correct result.

i didn't tested aria without page file format... (similar to myisam format right?)

after a:
alter table t7 engine=aria row_format=page

or after a:
repair table t7

the query runs ok with two rows

(I'm using the elena dump file)

Using create table with " row_format=dynamic" give the "same error" (maybe the statistics of aria is wrong)
after a repair or alter table, results get back again...
i think it's something at index creation and statistics update when we run inserts

running create table without index, it works nice (but don't use index...)
getting back to my last comment, i'm near sure that index statistics is corrupted while running insert
example of create table without index:

CREATE TABLE `t7` (
`emitente_tipo` enum('f','j') NOT NULL DEFAULT 'f',
`emitente_id` int(10) unsigned NOT NULL DEFAULT '0',
`emitente_propriedade` tinyint(3) unsigned NOT NULL DEFAULT '0',
`nf_serie` char(5) NOT NULL DEFAULT 'A',
`nf` int(11) NOT NULL DEFAULT '0',
`id_item` int(11) NOT NULL DEFAULT '0',
`status` enum('ok','c','d','nf','i') NOT NULL DEFAULT 'ok',
`unidade_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
`lote_tipo` enum('v','c') NOT NULL DEFAULT 'v',
`lote_spa` decimal(12,1) NOT NULL DEFAULT '0.0',
`oe_tipo` enum('oe','fec') NOT NULL DEFAULT 'oe',
`oe` bigint(20) NOT NULL DEFAULT '0',
`oe_seq` mediumint(9) NOT NULL DEFAULT '0',
`item_id` smallint(5) unsigned NOT NULL DEFAULT '0',
`item_id_red` mediumint(9) NOT NULL DEFAULT '0',
`item_codigo` char(30) NOT NULL DEFAULT '0',
`item_desc` char(100) NOT NULL DEFAULT '',
`servico` enum('Y','N') NOT NULL DEFAULT 'N',
`documento_auxiliar` enum('Y','N') NOT NULL DEFAULT 'N',
`cfop_id` tinyint(3) unsigned NOT NULL DEFAULT '0',
`cfop_id_red` int(11) NOT NULL DEFAULT '0',
`cfop_numero` varchar(10) NOT NULL DEFAULT '',
`class_fiscal` char(20) NOT NULL DEFAULT '',
`sit_trib` char(3) NOT NULL DEFAULT '',
`sit_trib_ipi` char(3) NOT NULL DEFAULT '',
`sit_trib_pis` char(3) NOT NULL DEFAULT '',
`sit_trib_cofins` char(3) NOT NULL DEFAULT '',
`sit_trib_iss` varchar(1) NOT NULL DEFAULT '',
`sit_trib_sn` varchar(4) NOT NULL DEFAULT '',
`icms_incluir_bc` varchar(5) NOT NULL DEFAULT '',
`icms_subst_incluir_bc` varchar(5) NOT NULL DEFAULT '',
`ipi_incluir_bc` varchar(5) NOT NULL DEFAULT '',
`pis_inclur_bc` varchar(5) NOT NULL DEFAULT '',
`cofins_incluir_bc` varchar(5) NOT NULL DEFAULT '',
`ii_incluir_bc` varchar(5) NOT NULL DEFAULT '',
`sn_incluir_bc` varchar(5) NOT NULL DEFAULT '',
`un_fat` char(5) NOT NULL DEFAULT '',
`quant` decimal(17,5) NOT NULL DEFAULT '0.00000',
`pecas` decimal(17,5) NOT NULL DEFAULT '0.00000',
`pbruto` decimal(17,5) NOT NULL DEFAULT '0.00000',
`pliq` decimal(17,5) NOT NULL DEFAULT '0.00000',
`vliq` decimal(17,5) NOT NULL DEFAULT '0.00000',
`vbruto` decimal(17,5) NOT NULL DEFAULT '0.00000',
`valor_pauta` decimal(17,5) NOT NULL DEFAULT '0.00000',
`valor_pauta_grandeza` enum('un','up','pl','pb','@pl','@pb','vl','vb') NOT NULL DEFAULT 'un',
`valor_pauta_grandeza_quant` decimal(17,5) NOT NULL DEFAULT '1.00000',
`valor_produto_un` decimal(17,5) NOT NULL DEFAULT '0.00000',
`valor_produto_grandeza` enum('un','up','pl','pb','@pl','@pb','vl','vb') NOT NULL DEFAULT 'un',
`valor_produto_grandeza_quant` decimal(10,5) NOT NULL DEFAULT '1.00000',
`valor_produto_tabela_un` decimal(17,5) NOT NULL DEFAULT '0.00000',
`valor_produto_total` decimal(17,5) NOT NULL DEFAULT '0.00000',
`valor_frete` decimal(17,5) NOT NULL DEFAULT '0.00000',
`valor_seguro` decimal(17,5) unsigned NOT NULL DEFAULT '0.00000',
`valor_outras` decimal(17,5) unsigned NOT NULL DEFAULT '0.00000',
`valor_estimado_impostos` decimal(17,5) unsigned NOT NULL DEFAULT '0.00000' COMMENT 'lei da transparencia',
`valor_estimado_impostos_por` decimal(17,5) unsigned NOT NULL DEFAULT '0.00000',
`desconto` decimal(17,5) NOT NULL DEFAULT '0.00000',
`icms_de_aliq` decimal(10,7) NOT NULL DEFAULT '0.0000000',
`icms_de_red` decimal(10,7) NOT NULL DEFAULT '0.0000000',
`icms_fe_aliq` decimal(10,7) NOT NULL DEFAULT '0.0000000',
`icms_fe_red` decimal(10,7) NOT NULL DEFAULT '0.0000000',
`icms_basecalculo` decimal(17,5) NOT NULL DEFAULT '0.00000',
`icms_valor` decimal(17,5) NOT NULL DEFAULT '0.00000',
`icms_subst_ind_convenio` decimal(10,7) NOT NULL DEFAULT '0.0000000',
`icms_subst_basecalculo` decimal(17,5) NOT NULL DEFAULT '0.00000',
`icms_subst_valor` decimal(17,5) NOT NULL DEFAULT '0.00000',
`ipi_aliq` decimal(10,7) NOT NULL DEFAULT '0.0000000',
`ipi_red` decimal(10,7) NOT NULL DEFAULT '0.0000000',
`ipi_basecalculo` decimal(17,5) NOT NULL DEFAULT '0.00000',
`ipi_valor` decimal(17,5) NOT NULL DEFAULT '0.00000',
`iss_aliq` decimal(10,7) NOT NULL DEFAULT '0.0000000',
`iss_red` decimal(10,7) NOT NULL DEFAULT '0.0000000',
`iss_basecalculo` decimal(17,5) NOT NULL DEFAULT '0.00000',
`iss_valor` decimal(17,5) NOT NULL DEFAULT '0.00000',
`pis_aliq` decimal(10,5) NOT NULL DEFAULT '0.00000',
`pis_red` decimal(10,5) NOT NULL DEFAULT '0.00000',
`pis_basecalculo` decimal(17,5) NOT NULL DEFAULT '0.00000',
`pis_valor` decimal(17,5) NOT NULL DEFAULT '0.00000',
`cofins_aliq` decimal(10,5) NOT NULL DEFAULT '0.00000',
`cofins_red` decimal(10,5) NOT NULL DEFAULT '0.00000',
`cofins_basecalculo` decimal(17,5) NOT NULL DEFAULT '0.00000',
`cofins_valor` decimal(17,5) NOT NULL DEFAULT '0.00000',
`funrural_aliq` decimal(10,5) NOT NULL DEFAULT '0.00000',
`funrural_red` decimal(10,5) NOT NULL DEFAULT '0.00000',
`funrural_basecalculo` decimal(17,5) NOT NULL DEFAULT '0.00000',
`funrural_valor` decimal(17,5) NOT NULL DEFAULT '0.00000',
`sn_aliq` decimal(10,5) NOT NULL DEFAULT '0.00000',
`sn_red` decimal(10,5) NOT NULL DEFAULT '0.00000',
`sn_basecalculo` decimal(17,5) NOT NULL DEFAULT '0.00000',
`sn_valor` decimal(17,5) NOT NULL DEFAULT '0.00000',
`cide_aliq` decimal(10,7) NOT NULL DEFAULT '0.0000000',
`cide_red` decimal(10,7) NOT NULL DEFAULT '0.0000000',
`cide_basecalculo` decimal(17,5) NOT NULL DEFAULT '0.00000',
`cide_valor` decimal(17,5) NOT NULL DEFAULT '0.00000',
`csll_aliq` decimal(10,7) NOT NULL DEFAULT '0.0000000',
`csll_red` decimal(10,7) NOT NULL DEFAULT '0.0000000',
`csll_basecalculo` decimal(17,5) NOT NULL DEFAULT '0.00000',
`csll_valor` decimal(17,5) NOT NULL DEFAULT '0.00000',
`sit_trib_csll` varchar(4) NOT NULL DEFAULT '',
`inss_aliq` decimal(10,7) NOT NULL DEFAULT '0.0000000',
`inss_red` decimal(10,7) NOT NULL DEFAULT '0.0000000',
`inss_basecalculo` decimal(17,5) NOT NULL DEFAULT '0.00000',
`inss_valor` decimal(17,5) NOT NULL DEFAULT '0.00000',
`sit_trib_inss` varchar(4) NOT NULL DEFAULT '',
`ir_aliq` decimal(10,7) NOT NULL DEFAULT '0.0000000',
`ir_red` decimal(10,7) NOT NULL DEFAULT '0.0000000',
`ir_basecalculo` decimal(17,5) NOT NULL DEFAULT '0.00000',
`ir_valor` decimal(17,5) NOT NULL DEFAULT '0.00000',
`sit_trib_ir` varchar(4) NOT NULL DEFAULT '',
`ii_aliq` decimal(10,7) NOT NULL DEFAULT '0.0000000',
`ii_red` decimal(10,7) NOT NULL DEFAULT '0.0000000',
`ii_basecalculo` decimal(17,5) NOT NULL DEFAULT '0.00000',
`ii_valor` decimal(17,5) NOT NULL DEFAULT '0.00000',
`sit_trib_ii` varchar(4) NOT NULL DEFAULT '',
`numero_pedido_compra` varchar(25) NOT NULL DEFAULT '',
`numero_item_pedido` varchar(25) NOT NULL DEFAULT '',
`obs_item` varchar(255) NOT NULL DEFAULT '',
`quant_devolvida` decimal(17,5) unsigned NOT NULL DEFAULT '0.00000',
`pecas_devolvida` decimal(17,5) unsigned NOT NULL DEFAULT '0.00000',
`pbruto_devolvida` decimal(17,5) unsigned NOT NULL DEFAULT '0.00000',
`pliq_devolvida` decimal(17,5) unsigned NOT NULL DEFAULT '0.00000',
`vliq_devolvida` decimal(17,5) unsigned NOT NULL DEFAULT '0.00000',
`vbruto_devolvida` decimal(17,5) unsigned NOT NULL DEFAULT '0.00000',
`ii_anterior_un` decimal(17,5) unsigned NOT NULL DEFAULT '0.00000',
`ipi_anterior_un` decimal(17,5) unsigned NOT NULL DEFAULT '0.00000',
`pis_anterior_un` decimal(17,5) unsigned NOT NULL DEFAULT '0.00000',
`cofins_anterior_un` decimal(17,5) unsigned NOT NULL DEFAULT '0.00000'
) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1;

Inside maria_records_in_range (info=0x7fff94021cb0, inx=0, min_key=0x7fffc87c41c0, max_key=0x7fffc87c41e0) at /home/psergey/dev2/5.5/storage/maria/ma_range.c:101

we have:

(gdb) print start_pos
$122 = 652
(gdb) print end_pos
$123 = 578

That is, it thinks that "start_pos > end_pos" , in other words, lower_endpoint > upper_endpoint, and returns 0.

is incorrect.

Relevant part of DDL and query:

CREATE TABLE `t7` (
`emitente_tipo` enum('f','j') NOT NULL DEFAULT 'f',
`emitente_id` int(10) unsigned NOT NULL DEFAULT '0',
`emitente_propriedade` tinyint(3) unsigned NOT NULL DEFAULT '0',
`nf_serie` char(5) NOT NULL DEFAULT 'A',
`nf` int(11) NOT NULL DEFAULT '0',
`id_item` int(11) NOT NULL DEFAULT '0',
...
PRIMARY KEY (`emitente_tipo`,`emitente_id`,`emitente_propriedade`,`nf_serie`,`nf`,`id_item`),
) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1

select nf,id_item from t7 where emitente_tipo='j' AND emitente_id=1 AND emitente_propriedade=0 AND nf_serie="3nfe" AND nf<0;

in mysqld.trace, _ma_record_pos() prints range endpoints. They are

Key: "2-1-0-3nfe "

Key: "2-1-0-3nfe -0"

The endpoints seem to be correct.

I am debugging _ma_search_pos() for both start_pos and end_pos.

In both cases, they determine key position on the root page here:

flag= (*keyinfo->bin_search)(key, &page, nextflag, &keypos,
info->lastkey_buff, &after_key);
keynr= _ma_keynr(&page, keypos, &max_keynr);

for start_pos:
keynr=2 max_keynr=5 page.node=5 flag=0
for end_pos:
keynr=2 max_keynr=5 page.node=5 flag=1

for start_pos:
we calculate offset=1.0
andd return (keynr+1)/(max_keynr+1) = 0.5

for end_pos:
_ma_search_pos calls _ma_search_pos() for a leaf node.
the second call returns offset=0.65900383141762453

This way, we get the situationm where start_pos > end_pos.

Because they've found the same key on the root node and then
start_pos got offset=1.0, while end_pos got offset=0.65900383141762453

Committed a patch.

http://lists.askmonty.org/pipermail/commits/2013-July/005132.html

sergei, a doubt ...
why a repair or an alter make this same query work? repair rewrite the index? and the optimizer runs differntly?

SAME PROBLEM BUT WITH UPDATE AND DELETE...

DELETE FROM t7
WHERE emitente_tipo='j' AND emitente_id=1 AND emitente_propriedade=0 AND nf_serie='3nfe' AND nf < 0;
/* Affected rows: 0 Registros encontrados: 0 Avisos: 0 Duração de 1 query: 0,047 sec. */

update t7
set emitente_propriedade=1
WHERE emitente_tipo='j' AND emitente_id=1 AND emitente_propriedade=0 AND nf_serie='3nfe' AND nf < 0;
/* Affected rows: 0 Registros encontrados: 0 Avisos: 0 Duração de 1 query: 0,046 sec. */

with patch, UPDATE/SELECT/DELETE works without problems

could this be implemented in near next releases?

Roberto,

You have reported that the fix worked for you (thanks!)
Elena also ran tests and found no problem.

We intend to include the fix in the next 5.5 release.

Pushed into 5.5 tree

nice sergei! i tested with 10.0.3 and this worked fine too
i don't use <5.5 maybe someone could test...

thanks again =]

PLEASE PUT THIS IN 10.0.4 VERSION =]

I'm afraid it's too late to do a new 5.5->10.0 merge now. Remember, 10.0.4 is an alpha release, it's main feature is 5.6 merge. The goal of 10.0.4 release is to get the new features out so that users could try them out.

In about a month we expect to have 10.0.5 release that will include this bug fix.

ok no problem, i used the same patch for 10.0.3 without changes, and it worked, if possible put this as soon as possible, recompiling sometimes is a problem, the binary is easier to install =] thanks !