MySQL Bugs: #14496: Crash or strange results with prepared statement, MATCH and FULLTEXT

Bug #14496	Crash or strange results with prepared statement, MATCH and FULLTEXT
Submitted:	30 Oct 2005 22:45	Modified:	14 Feb 2006 2:50
Reporter:	Michael Wallner (Candidate Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S2 (Serious)
Version:	5.0.15-nt, 5.0.16-BK, 4.1.16-BK, 4.1.14	OS:	Windows (Windows, Linux)
Assigned to:	Sergey Vojtovich	CPU Architecture:	Any

Description:
Some MySQL people on IRC necessitated me to try some code.

Obviously the didn't tell me that it may cause my mysqld to restart, because I wouldn't have to make this report otherwise ;)

0x815460b handle_segfault + 523
0x4005ffe1 _end + 933956433
0x4030b409 _end + 936755065
0x80f3f81 _ZN4Item4sendEP8ProtocolP6String + 433
0x81492d7 _ZN11select_send9send_dataER4ListI4ItemE + 167
0x8187976 _Z8end_sendP4JOINP13st_join_tableb + 182
0x818dca7 _Z10sub_selectP4JOINP13st_join_tableb + 263
0x81913ed _Z9do_selectP4JOINP4ListI4ItemEP8st_tableP9Procedure + 781
0x819b52b _ZN4JOIN4execEv + 3931
0x819c597 _Z12mysql_selectP3THDPPP4ItemP13st_table_listjR4ListIS1_ES2_jP8st_orderSB_S2_SB_mP13select_resultP18st_select_lex_unitP13st_sel + 375
0x819ccc4 _Z13handle_selectP3THDP6st_lexP13select_result + 196
0x8168354 _Z21mysql_execute_commandP3THD + 9620
0x81a2856 _Z12execute_stmtP3THDP18Prepared_statementP6Stringb + 406
0x81a2d65 _Z18mysql_stmt_executeP3THDPcj + 485
0x816b14e _Z16dispatch_command19enum_server_commandP3THDPcj + 3166
0x816b606 _Z10do_commandP3THD + 118
0x816be6b handle_one_connection + 1899
0x40058f3c _end + 933927596
0x402ad8ba _end + 936371242

How to repeat:
<?php
$dbh = new PDO('mysql:host=localhost;dbname=', '', '');
$dbh->exec('create table foo ( bar text NULL, FULLTEXT KEY bar (bar) )');
/* init some data */
$dbh->exec('insert into foo (bar) values ( "This is row sample")');
$dbh->exec('insert into foo (bar) values ( "This is row foobar" )');
$dbh->exec('insert into foo (bar) values ( "This is row sample")');
$dbh->exec('insert into foo (bar) values ( "This is row another" )');
$dbh->exec('insert into foo (bar) values ( "This is row wordlist" )');
$dbh->exec('insert into foo (bar) values ( "This is row random" )');

$match =  "MATCH(bar) AGAINST ('foobar sample')";
$sql = "select bar, $match as afoo from foo where $match";
$stmt = $dbh->prepare($sql)
  or print_r($dbh->errorInfo());

print "First Exec\n";
$stmt->execute() or print_r($stmt->errorInfo());

while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
  print "{$row['afoo']}: {$row['bar']}\n";
}

print "\nSecond Exec\n";
$stmt->execute() or print_r($stmt->errorInfo());
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
  print "{$row['afoo']}: {$row['bar']}\n";
}

$dbh->exec('drop table foo');
?>

Thank you for a bug report. I was able to repeat this crash om 5.0.15-nt using mysql client:

mysql> create table foo ( bar text NULL, FULLTEXT KEY bar (bar) ) engine=MyISAM;

Query OK, 0 rows affected (0.05 sec)

mysql> insert into foo (bar) values ( "This is row sample");
Query OK, 1 row affected (0.02 sec)

mysql> insert into foo (bar) values ( "This is row foobar");
Query OK, 1 row affected (0.00 sec)

mysql> insert into foo (bar) values ( "This is row sample");
Query OK, 1 row affected (0.00 sec)

mysql> insert into foo (bar) values ( "This is row another" );
Query OK, 1 row affected (0.00 sec)

mysql> insert into foo (bar) values ( "This is row wordlist" );
Query OK, 1 row affected (0.04 sec)

mysql> insert into foo (bar) values ( "This is row random" );
Query OK, 1 row affected (0.01 sec)

mysql> select bar, MATCH(bar) AGAINST ('foobar sample') as afoo
    -> from foo where MATCH(bar) AGAINST ('foobar sample');
+--------------------+------------------+
| bar                | afoo             |
+--------------------+------------------+
| This is row foobar |  1.5911398452715 |
| This is row sample | 0.68526663197496 |
| This is row sample | 0.68526663197496 |
+--------------------+------------------+
3 rows in set (0.03 sec)

mysql> select bar, MATCH(bar) AGAINST ('foobar sample') as afoo
    -> from foo where MATCH(bar) AGAINST ('foobar sample');
+--------------------+------------------+
| bar                | afoo             |
+--------------------+------------------+
| This is row foobar |  1.5911398452715 |
| This is row sample | 0.68526663197496 |
| This is row sample | 0.68526663197496 |
+--------------------+------------------+
3 rows in set (0.03 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.15-nt |
+-----------+
1 row in set (0.00 sec)

So, it works OK two times whne PS is not used. Let's try to prepare it:

mysql> prepare stmt2 from "select bar, MATCH(bar) AGAINST ('foobar sample') as afoo
    "> from foo where MATCH(bar) AGAINST ('foobar sample')";
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> execute stmt2;
+--------------------+------------------+
| bar                | afoo             |
+--------------------+------------------+
| This is row foobar |  1.5911398452715 |
| This is row sample | 0.68526663197496 |
| This is row sample | 0.68526663197496 |
+--------------------+------------------+
3 rows in set (0.00 sec)

mysql> execute stmt2;
ERROR 2013 (HY000): Lost connection to MySQL server during query

This is a crash on Windows. 

Now let's come to Linux. Both 5.0.16-BK (ChangeSet@1.1957, 2005-10-29 13:11:34+04:00, konstantin@mysql.com) and 4.1.16-BK-debug (ChangeSet@1.2450, 2005-10-27 10:20:38-07:00, jimw@mysql.com) gave me the following results (with the same table and data) on Fedora Core 1:

mysql> prepare stmt2 from "select bar, MATCH(bar) AGAINST ('foobar sample') as afoo
from foo where MATCH(bar) AGAINST ('foobar sample')";
Query OK, 0 rows affected (0,00 sec)
Statement prepared

mysql> execute stmt2;
+--------------------+------------------+
| bar                | afoo             |
+--------------------+------------------+
| This is row foobar |   1.591139793396 |
| This is row sample | 0.68526661396027 |
| This is row sample | 0.68526661396027 |
+--------------------+------------------+
3 rows in set (0,00 sec)

mysql> execute stmt2;
+--------------------+------------------+
| bar                | afoo             |
+--------------------+------------------+
| This is row foobar |                0 |
| This is row sample | 0.68526661396027 |
| This is row sample | 0.68526661396027 |
+--------------------+------------------+
3 rows in set (0,00 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 4.1.16-debug |
+--------------+
1 row in set (0,00 sec)

This different results is a bug also. Without PS we get the consistent results:

mysql> select bar, MATCH(bar) AGAINST ('foobar sample') as afoo
from foo where MATCH(bar) AGAINST ('foobar sample');
+--------------------+------------------+
| bar                | afoo             |
+--------------------+------------------+
| This is row foobar |   1.591139793396 |
| This is row sample | 0.68526661396027 |
| This is row sample | 0.68526661396027 |
+--------------------+------------------+
3 rows in set (0,00 sec)

mysql> select bar, MATCH(bar) AGAINST ('foobar sample') as afoo  from foo where
MATCH(bar) AGAINST ('foobar sample');
+--------------------+------------------+
| bar                | afoo             |
+--------------------+------------------+
| This is row foobar |   1.591139793396 |
| This is row sample | 0.68526661396027 |
| This is row sample | 0.68526661396027 |
+--------------------+------------------+
3 rows in set (0,00 sec)

Your PHP script also works and give some strange results for me on 4.1.16 and 5.0.16, but no crash. But I think that simple mysql is enough to demonstrate the erronious behaviour and crash.

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/2015

Fixed that fulltext query using PS results in unexpected behaviour
when executed 2 or more times.

Fixed in 4.1.19, 5.0.19. Wasn't able to push into 5.1 yet.

Noted in 4.1.19, 5.0.19, 5.1.6 changelogs.

        <para>
          A <literal>FULLTEXT</literal> query in a prepared statement
          could result in unexpected behavior. (Bug #14496)
        </para>