Impossible row counts in MySQL

Question

I don't know how does MySQL works internally, but I'm sure there are some problem with indexes or some metainformation about table counts:

mysql> select count(*) from Event;
+----------+
| count(*) |
+----------+
|     5925 |
+----------+
1 row in set (0,01 sec)

mysql> select count(*) from Event where event_id in (select discount_event_id from Discount);
+----------+
| count(*) |
+----------+
|     5901 |
+----------+
1 row in set (0,12 sec)

mysql> select count(*) from Event where event_id not in (select discount_event_id from Discount);
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0,11 sec)

These 24 missing event_ids makes no sense. It's just logically impossible from my point of view. There cannot be 24 rows that both are and aren't in another set. Or they are, or they aren't.

Also, as suggested from some of the answers and comments, there are no NULL event_ids, since these are the rowids:

mysql> select count(*) from Event where event_id is null;
+----------+
| count(*) |
+----------+
|        0 |
+----------+

What is going on?

https://stackoverflow.com/questions/129077/not-in-clause-and-null-values (though that's about SQL Server it's very likely to fit here too) — sticky bit, Jan 31 '19 at 23:55
Try `SELECT COUNT(*), event_id FROM Event GROUP BY event_id WITH ROLLUP` to get a breakdown. — tadman, Feb 01 '19 at 00:37
Looks like you're checking the wrong table/column for `NULL`s. Try: `SELECT discount_event_id FROM discount WHERE discount_event_id IS NULL;` — sticky bit, Feb 01 '19 at 00:39
@stickybit Yeah, I just found the same. Write it as answer and I'll mark it as the valid one. — Peregring-lk, Feb 01 '19 at 00:43

score 4 · Answer 1 · answered Jan 31 '19 at 23:55

4

It means there are 24 event_id that are NULL

select count(*) from Event where event_id IS NULL

both in and not in operators return NULL when you compare with NULL value, which is coerced to FALSE hence omitted from both result sets.

answered Jan 31 '19 at 23:55

zerkms

240,587
65
429
525

There'are no `NULL` `event_id`s since that field is the rowid of the `Event` table. See the update on the question. – Peregring-lk Feb 01 '19 at 00:28

Impossible row counts in MySQL

1 Answers1