Description:
I have two tables and a query that returns 24 rows with the attached test data:
select couponPoolId
from coupons
where couponId not in (select couponId from couponListItems)
group by couponPoolId;
This is wrong; there are 31 records that satisfy the condition.
If I add any aggregate function, the query starts returning correct rows.
Disabling semijoin resolves the issue.
How to repeat:
1. Load attached dump
2. Run queries.
mysql> select couponPoolId from coupons where couponId not in (select couponId from couponListItems) group by couponPoolId;
+--------------+
| couponPoolId |
+--------------+
| 4 |
| 5 |
...
| 1710 |
+--------------+
24 rows in set (0,01 sec)
mysql> select couponPoolId, count(*) cnt from coupons where couponId not in (select couponId from couponListItems) group by couponPoolId;
+--------------+-------+
| couponPoolId | cnt |
+--------------+-------+
| 4 | 1 |
| 5 | 1 |
...
| 1710 | 1 |
+--------------+-------+
31 rows in set (2,86 sec)
mysql> select couponPoolId from coupons where couponId not in (select /*+ NO_SEMIJOIN() */ couponId from couponListItems) group by couponPoolId;
+--------------+
| couponPoolId |
+--------------+
| 4 |
| 5 |
...
| 1710 |
+--------------+
31 rows in set (2,85 sec)
mysql> \W
Show warnings enabled.
mysql> explain select couponPoolId from coupons where couponId not in (select couponId from couponListItems) group by couponPoolId\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: coupons
partitions: NULL
type: range
possible_keys: coupons_FKIndex1
key: coupons_FKIndex1
key_len: 4
ref: NULL
rows: 1387
filtered: 100.00
Extra: Using where; Using index for group-by
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: couponListItems
partitions: NULL
type: ref
possible_keys: couponId
key: couponId
key_len: 4
ref: infis.coupons.couponId
rows: 1
filtered: 100.00
Extra: Using where; Not exists; Using index
2 rows in set, 1 warning (0,00 sec)
Note (Code 1003): /* select#1 */ select `infis`.`coupons`.`couponPoolId` AS `couponPoolId` from `infis`.`coupons` anti join (`infis`.`couponListItems`) on((`infis`.`coupons`.`couponId` = `infis`.`couponListItems`.`couponId`)) where true group by `infis`.`coupons`.`couponPoolId`
mysql> explain select couponPoolId, count(*) cnt from coupons where couponId not in (select couponId from couponListItems) group by couponPoolId\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: coupons
partitions: NULL
type: index
possible_keys: coupons_FKIndex1
key: coupons_FKIndex1
key_len: 4
ref: NULL
rows: 2478798
filtered: 100.00
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: couponListItems
partitions: NULL
type: ref
possible_keys: couponId
key: couponId
key_len: 4
ref: infis.coupons.couponId
rows: 1
filtered: 100.00
Extra: Using where; Not exists; Using index
2 rows in set, 1 warning (0,00 sec)
Note (Code 1003): /* select#1 */ select `infis`.`coupons`.`couponPoolId` AS `couponPoolId`,count(0) AS `cnt` from `infis`.`coupons` anti join (`infis`.`couponListItems`) on((`infis`.`coupons`.`couponId` = `infis`.`couponListItems`.`couponId`)) where true group by `infis`.`coupons`.`couponPoolId`
Suggested fix:
Return the correct result.
Description: I have two tables and a query that returns 24 rows with the attached test data: select couponPoolId from coupons where couponId not in (select couponId from couponListItems) group by couponPoolId; This is wrong; there are 31 records that satisfy the condition. If I add any aggregate function, the query starts returning correct rows. Disabling semijoin resolves the issue. How to repeat: 1. Load attached dump 2. Run queries. mysql> select couponPoolId from coupons where couponId not in (select couponId from couponListItems) group by couponPoolId; +--------------+ | couponPoolId | +--------------+ | 4 | | 5 | ... | 1710 | +--------------+ 24 rows in set (0,01 sec) mysql> select couponPoolId, count(*) cnt from coupons where couponId not in (select couponId from couponListItems) group by couponPoolId; +--------------+-------+ | couponPoolId | cnt | +--------------+-------+ | 4 | 1 | | 5 | 1 | ... | 1710 | 1 | +--------------+-------+ 31 rows in set (2,86 sec) mysql> select couponPoolId from coupons where couponId not in (select /*+ NO_SEMIJOIN() */ couponId from couponListItems) group by couponPoolId; +--------------+ | couponPoolId | +--------------+ | 4 | | 5 | ... | 1710 | +--------------+ 31 rows in set (2,85 sec) mysql> \W Show warnings enabled. mysql> explain select couponPoolId from coupons where couponId not in (select couponId from couponListItems) group by couponPoolId\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: coupons partitions: NULL type: range possible_keys: coupons_FKIndex1 key: coupons_FKIndex1 key_len: 4 ref: NULL rows: 1387 filtered: 100.00 Extra: Using where; Using index for group-by *************************** 2. row *************************** id: 1 select_type: SIMPLE table: couponListItems partitions: NULL type: ref possible_keys: couponId key: couponId key_len: 4 ref: infis.coupons.couponId rows: 1 filtered: 100.00 Extra: Using where; Not exists; Using index 2 rows in set, 1 warning (0,00 sec) Note (Code 1003): /* select#1 */ select `infis`.`coupons`.`couponPoolId` AS `couponPoolId` from `infis`.`coupons` anti join (`infis`.`couponListItems`) on((`infis`.`coupons`.`couponId` = `infis`.`couponListItems`.`couponId`)) where true group by `infis`.`coupons`.`couponPoolId` mysql> explain select couponPoolId, count(*) cnt from coupons where couponId not in (select couponId from couponListItems) group by couponPoolId\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: coupons partitions: NULL type: index possible_keys: coupons_FKIndex1 key: coupons_FKIndex1 key_len: 4 ref: NULL rows: 2478798 filtered: 100.00 Extra: Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: couponListItems partitions: NULL type: ref possible_keys: couponId key: couponId key_len: 4 ref: infis.coupons.couponId rows: 1 filtered: 100.00 Extra: Using where; Not exists; Using index 2 rows in set, 1 warning (0,00 sec) Note (Code 1003): /* select#1 */ select `infis`.`coupons`.`couponPoolId` AS `couponPoolId`,count(0) AS `cnt` from `infis`.`coupons` anti join (`infis`.`couponListItems`) on((`infis`.`coupons`.`couponId` = `infis`.`couponListItems`.`couponId`)) where true group by `infis`.`coupons`.`couponPoolId` Suggested fix: Return the correct result.