Bug #13356 server crash
Submitted: 20 Sep 2005 18:49 Modified: 30 Sep 2005 17:10
Reporter: Roberto Spadim (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:5.0.10-beta/5.0 BK source OS:Linux (linux)
Assigned to: Evgeny Potemkin CPU Architecture:Any

[20 Sep 2005 18:49] Roberto Spadim 
Description:
this query make server crash

SELECT a.item_id,a.item_id_red,a.quant_mov_un,a.quant_mov,
(
	SELECT SUM(quantidade-quant_embarcado) FROM spamov_itens WHERE unidade_id=4 AND lote_tipo='v' AND lote_spa=5 AND item_id=a.item_id AND item_id_red=a.item_id_red
) AS restante_1,(
	SELECT SUM(quant_oe) FROM spamov_itens WHERE unidade_id=4 AND lote_tipo='v' AND lote_spa=5 AND item_id=a.item_id AND item_id_red=a.item_id_red
) AS emoe_1 

FROM spamov_itens AS a,spamov AS b 
WHERE (a.unidade_id,a.lote_tipo,a.lote_spa) IN ((4,'v',5)) AND b.sit_spa='mov' AND b.unidade_id=a.unidade_id AND b.lote_spa=a.lote_spa AND b.lote_tipo=a.lote_tipo AND b.producao='Y' 
GROUP BY a.item_id,a.item_id_red

result:
Lost connection to MySQL server during query

without WHERE and GROUP BY works!

How to repeat:
SELECT a.item_id,a.item_id_red,a.quant_mov_un,a.quant_mov,
(
	SELECT SUM(quantidade-quant_embarcado) FROM spamov_itens WHERE unidade_id=4 AND lote_tipo='v' AND lote_spa=5 AND item_id=a.item_id AND item_id_red=a.item_id_red
) AS restante_1,(
	SELECT SUM(quant_oe) FROM spamov_itens WHERE unidade_id=4 AND lote_tipo='v' AND lote_spa=5 AND item_id=a.item_id AND item_id_red=a.item_id_red
) AS emoe_1 

FROM spamov_itens AS a,spamov AS b 
WHERE (a.unidade_id,a.lote_tipo,a.lote_spa) IN ((4,'v',5)) AND b.sit_spa='mov' AND b.unidade_id=a.unidade_id AND b.lote_spa=a.lote_spa AND b.lote_tipo=a.lote_tipo AND b.producao='Y' 
GROUP BY a.item_id,a.item_id_red

Suggested fix:
???
[20 Sep 2005 18:50] Roberto Spadim 
i upload dump in files: tmp.sql
[20 Sep 2005 19:05] John David Duncan 
Verified in 5.0.12 on Mac OS X
[20 Sep 2005 19:45] MySQL Verification Team 
miguel@hegel:~/dbs/5.0> libexec/mysqld
050920 16:35:47  InnoDB: Started; log sequence number 0 52348
050920 16:35:47 [Note] libexec/mysqld: ready for connections.
Version: '5.0.14-rc-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
mysqld: item.cc:5252: void resolve_const_item(THD*, Item**, Item*): Assertion `0' failed.
mysqld got signal 6;
[20 Sep 2005 19:51] Roberto Spadim 
i'm using myisam table!
[20 Sep 2005 19:52] Roberto Spadim 
config file

Attachment: my.cnf (application/octet-stream, text), 4.93 KiB.
[27 Sep 2005 15:58] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/30393
[27 Sep 2005 19:12] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/30402
[27 Sep 2005 20:57] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/30406
[27 Sep 2005 21:07] Evgeny Potemkin 
This bug can be reproduced by simplier query:
select f1 from t1,t2 where f1=f2 and (f1,f2) = ((1,1));

Note that 4.1 is also affected by this bug.
[27 Sep 2005 21:09] Evgeny Potemkin 
resolve_const_item() assumed to be not called for Item_row items. For
ensuring that DBUG_ASSERT(0) was set there. In this case assert is failed.

Fixed in 4.1, cset 1.2456.1.1
[28 Sep 2005 17:30] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/30454
[28 Sep 2005 17:31] Evgeny Potemkin 
Fixed in 5.0.14
[30 Sep 2005 17:10] Paul DuBois 
Noted in 4.1.15, 5.0.14 changelogs.