| Bug #5594 | SQL_CALC_FOUND_ROWS always returns 0 with Full Text search | ||
|---|---|---|---|
| Submitted: | 15 Sep 2004 14:24 | Modified: | 16 Sep 2004 18:04 |
| Reporter: | [ name withheld ] | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: MyISAM storage engine | Severity: | S2 (Serious) |
| Version: | MySQL 4.0.18-max | OS: | Slack 10 |
| Assigned to: | CPU Architecture: | Any | |
[15 Sep 2004 14:49]
[ name withheld ]
Verified with MySQL 4.0.21-standard - still occurs
[15 Sep 2004 15:04]
MySQL Verification Team
Hi,
Thank you for the report, but I wasn't able to repeat it on the latest BK 4.0 tree.
mysql> select SQL_CALC_FOUND_ROWS str from t1 where match(str) against ('MySQL') limit 1;
+------------------------------------------------+
| str |
+------------------------------------------------+
| This is the first row that contains word MySQL |
+------------------------------------------------+
1 row in set (0.04 sec)
mysql> SELECT FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
| 2 |
+--------------+
1 row in set (0.00 sec)
[15 Sep 2004 20:38]
MySQL Verification Team
If you get the same result on version 4.0.21, please, provide the repeatable test case, i.e. dump of the table with structure and data.
[16 Sep 2004 8:42]
[ name withheld ]
Doing some more testing using a fresh database that did work, I decided to transfer the content of the original table to a new one, and that worked as well. Then, I retried the original table - and it worked as well. This was all from the shell, but within PHP it still returned 0. I've found that 4.0.18 compiled in PHP has the error, and 4.0.21 does not. What happens with 4.0.18, is that if you do mysql_query(), then mysql_fetch_array() thru the resultset - and then mysql_query() the FOUND_ROWS() 0 is returned. Doing the FOUND_ROWS() *before* the mysql_fetch_array(), it does return the correct value. I don't know who is to blame; me, PHP, MySQL - or just a weird combination. Anyways, for now - it works.
[16 Sep 2004 18:04]
MySQL Verification Team
According with your last comment: now it works.
Description: Using SQL_CALC_FOUND_ROWS on a query with a MATCH AGAINST and LIMIT always returns 0, even when records are found How to repeat: SELECT SQL_CALC_FOUND_ROWS table1.PK FROM table1 WHERE MATCH table1.Content AGAINST ('test') LIMIT 10; SELECT FOUND_ROWS(); Result: 0