MySQL Bugs: #39844: Query Crash Mysql Server 5.0.67

Bug #39844	Query Crash Mysql Server 5.0.67
Submitted:	3 Oct 2008 19:36	Modified:	1 Dec 2008 20:10
Reporter:	giovanni silva	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	5.0+	OS:	Any (MS Windows SP2 - XP 5.1.2600, Linux)
Assigned to:	Gleb Shchepa	CPU Architecture:	Any

Description:
Select Query Crash Mysql Server 5.0.67.

Select:
SELECT 
codigo AS codigo, 
(SELECT nota_fiscal.codigo FROM nota_fiscal INNER JOIN detalhe_nota_fiscal ON
nota_fiscal.codigo = detalhe_nota_fiscal.nota_fiscal WHERE detalhe_nota_fiscal.codigo =
movimentacao_estoque.detalhe_nota_fiscal AND nota_fiscal.tipo = 'PDV' GROUP BY
nota_fiscal.codigo) AS codigonotapdv, 
produto AS produto, 
(select descricao from produto where produto.codigo = movimentacao_estoque.produto) AS
descproduto, 
data_validade AS datadevalidade, 
lote AS lote, 
(select unidade_medida.descricao from unidade_medida where unidade_medida.codigo =
unidade_medida) AS descunidadedemedida, 
quantidade AS quantidade, 
data_operacao AS dataoperacao, 
tipo AS tipooperacao,
ativo AS ativo
FROM movimentacao_estoque ORDER BY codigo DESC  LIMIT 0 , 200;

Direct Link for SQL SOURCE
 - http://www.codesoftwares.com.br/request.php?1
Link for SQL SOURCE
 - http://www.codesoftwares.com.br/download.php?list.2

Log:
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=9437184
read_buffer_size=65536
max_used_connections=3
max_connections=100
threads_connected=3
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 41216 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 033FA8F8=SELECT 
(SELECT nota_fiscal.codigo FROM nota_fiscal INNER JOIN detalhe_nota_fiscal ON
nota_fiscal.codigo = detalhe_nota_fiscal.nota_fiscal WHERE detalhe_nota_fiscal.codigo =
movimentacao_estoque.detalhe_nota_fiscal AND nota_fiscal.tipo = 'PDV' GROUP BY
nota_fiscal.codigo) AS codigonotapdv, 
(select descricao from produto where produto.codigo = movimentacao_estoque.produto) AS
descproduto, 
(select unidade_medida.descricao from unidade_medida where unidade_medida.codigo =
unidade_medida) AS descunidadedemedida, 
codigo AS codigo, 
produto AS produto, 
data_validade AS datadevalidade, 
lote AS lote, 
quantidade AS quantidade, 
data_operacao AS dataoperacao, 
tipo AS tipooperacao,
ativo AS ativo
FROM movimentacao_estoque ORDER BY codigo ASC  LIMIT 0 , 200
thd->thread_id=4
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

-My Machine:
Pentium 4  - 2.4 Ghz
1 GB Ram
Windows XP SP2

How to repeat:
It above installs the data base through one of links. It executes the SQL that cause the
error (above).

Mysql.ini Config File

Attachment: my.ini (application/octet-stream, text), 8.99 KiB.

Bug#39730 has been marked as duplicated of this one.

Thank you for the report.

Verified as described. Bug exists only in 5.0 tree.

test case

Attachment: bug39844.tar.gz (application/x-gzip, text), 181.09 KiB.

In version 5.1 this bug exits, in version 6, it exists, but nor always.

Exists some patch or workarround?

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/commits/56402

2707 Gleb Shchepa	2008-10-16
      Bug #39844: Query Crash Mysql Server 5.0.67
      
      Server crashed during a sort order optimization
      of a dependent subquery:
      
      SELECT
          (SELECT t1.a FROM t1, t2
            WHERE t1.a = t2.b AND t2.a = t3.c
            ORDER BY t1.a)
        FROM t3;
      
      
      Bitmap of tables, that the reference to outer table
      column uses, in addition to the regular table bit
      has the OUTER_REF_TABLE_BIT bit set.
      The only_eq_ref_tables function traverses this map
      bit by bit simultaneously with join->map2table list.
      Obviously join->map2table never contains an entry
      for the OUTER_REF_TABLE_BIT pseudo-table, so the
      server crashed there.
      
      
      The only_eq_ref_tables function has been modified
      to traverse regular table bits only like the
      update_depend_map function (resetting of the
      OUTER_REF_TABLE_BIT there is enough, but
      resetting of the whole set of PSEUDO_TABLE_BITS
      is used there for sure).

Pushed into 5.0.72  (revid:gshchepa@mysql.com-20081016163717-ah4x4miph2tgotsy) (version source revid:kgeorge@mysql.com-20081020132607-0xfdc16b9p1xrd83) (pib:5)

Noted in 5.0.72 changelog.

The server could crash during a sort-order optimization of a
dependent subquery.

Setting report to NDI pending push into 5.1.x, 6.0.x.

Pushed into 6.0.8-alpha  (revid:gshchepa@mysql.com-20081016163717-ah4x4miph2tgotsy) (version source revid:gshchepa@mysql.com-20081016181342-093zyzjvqd7yn38l) (pib:5)

Pushed into 5.1.30  (revid:gshchepa@mysql.com-20081016163717-ah4x4miph2tgotsy) (version source revid:gshchepa@mysql.com-20081016180431-oxf0wjcpulgzhytw) (pib:5)

Note in 5.1.31, 6.0.8 changelogs.

6.0.9 changelog, not 6.0.8.

the downloaded version 5.1.30 and managed to repeat the error, just run the query multiple times.

> the downloaded version 5.1.30 and managed to repeat the error, just run the query multiple
times.

Note that the fix is not in 5.1.30 : it's in 5.1.31.

there is a launch date for these versions with the fix?

Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090108105244-8opp3i85jw0uj5ib) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)

Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)

Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)