Workarounds

If the previous sections did not help to solve your problem, you can try creating a workaround yourself. Rewrite your query to exclude the parts that cause the problem, and break the query down into smaller queries that execute correctly.

The following example, which is based on bug #47650, now fixed, illustrates the concept. First, we’ll look at a simple version of the incorrect behavior that triggered the bug report:

mysql> CREATE TABLE `t1` (
    ->   `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE `t2` (
    ->   `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    ->   `t1_id` BIGINT(20) DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO `t1` VALUES
(1),(2),(3),(4),(5),(6),(7),(8);
Query OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> INSERT INTO `t2` VALUES
(1,1),(2,1),(3,1),(4,2),(5,2),(6,2),(7,3),(8,3);
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0

mysql> SELECT t1.id AS t1_id, COUNT(DISTINCT t2.id) AS cnt FROM t1
LEFT JOIN t2 ON t1.id = t2.t1_id
    -> WHERE t1.id = 1 GROUP BY t1.id WITH ROLLUP LIMIT 100;
+-------+-----+
| t1_id | cnt |
+-------+-----+
|     1 |   8 |
|  NULL |   8 |
+-------+-----+
2 rows in set (0.01 sec)