Bug #11700 Wrong query result when table is used firsts time
Submitted: 2 Jul 2005 4:13 Modified: 14 Jul 2005 16:10
Reporter: Peter Zaitsev (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:4.1.13-bk OS:Any (all)
Assigned to: Sergey Petrunya CPU Architecture:Any

[2 Jul 2005 4:13] Peter Zaitsev
Description:
Query returns wrong results after tables are populated, however after flush tables result
becomes right.   The same applies to MySQL restart:

mysql> SELECT COUNT(*) FROM profile2queue pq, accounts a, queues q WHERE (pq.profile_id = a.profile_id) AND (a.acct_id = 132) AND (pq.queue_id = q.id) AND (q.rrobin_enabled <> 1);
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT COUNT(*) FROM profile2queue pq, accounts a, queues q WHERE (pq.profile_id = a.profile_id) AND (a.acct_id = 132) AND (pq.queue_id = q.id) AND (q.rrobin_enabled <> 1);
+----------+
| COUNT(*) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)

How to repeat:
See above
[13 Jul 2005 23:36] 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/27036
[14 Jul 2005 11:10] 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/27061
[14 Jul 2005 13:09] Sergey Petrunya
Fix pushed into 4.1.13 tree
[14 Jul 2005 16:10] Paul Dubois
Noted in 4.1.13 changelog.