CREATE TABLE t1 (a datetime, KEY (a));

INSERT INTO t1 VALUES

  (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),

  ('1900-01-01 00:00:00'),('1900-01-01 00:00:00'),('1900-01-01 00:00:00'),

  ('1900-01-01 00:00:00'),('1900-01-01 00:00:00'),('1900-01-01 00:00:00'),

  ('1900-01-01 00:00:00'),('1900-01-01 00:00:00'),('1900-01-01 00:00:00'),

  ('1900-01-01 00:00:00'),('1900-01-01 00:00:00'),('1900-01-01 00:00:00'),

  ('1900-01-01 00:00:00'),('1903-09-19 08:11:00'),('1904-11-11 16:03:37'),

  ('1908-05-09 17:44:46'),('1908-06-13 00:00:00'),('1908-11-13 07:14:00'),

  ('1909-10-16 09:04:52'),('1912-09-24 13:32:52'),('1912-12-30 12:55:35'),

  ('1915-07-29 05:28:30'),('1915-09-21 06:31:21'),('1916-09-17 14:41:40'),

  ('1917-03-18 00:00:00'),('1917-09-10 04:01:39'),('1918-01-28 19:23:18'),

  ('1919-04-09 02:49:46'),('1919-08-06 16:22:26'),('1920-07-19 00:48:12'),

  ('1921-11-25 20:07:24'),('1922-01-27 00:00:00'),('1922-11-26 23:40:46'),

  ('1925-12-22 19:09:05'),('1926-01-10 13:01:16'),('1929-09-09 00:00:00'),

  ('1932-03-02 11:06:11'),('1932-11-06 00:00:00'),('1933-04-09 00:00:00'),

  ('1934-06-15 00:00:00'),('1935-10-11 21:05:43'),('1936-05-07 00:00:00'),

  ('1938-10-29 19:13:28'),('1939-11-28 17:40:13'),('1939-12-25 11:58:59'),

  ('1940-02-12 00:00:00'),('1940-05-26 18:05:37'),('1940-09-01 23:48:25'),

  ('1940-10-08 15:47:55'),('1943-01-25 05:23:53'),('1943-08-28 21:39:35'),

  ('1945-11-02 00:00:00'),('1946-11-24 16:12:20'),('1947-02-20 14:59:25'),

  ('1952-06-04 17:26:05'),('1953-01-25 00:00:00'),('1954-03-09 00:00:00'),

  ('1954-11-10 14:23:08'),('1956-07-22 00:00:00'),('1956-10-18 00:00:00'),

  ('1957-04-20 15:50:31'),('1958-01-07 21:17:27'),('1959-01-23 00:00:00'),

  ('1961-05-30 19:29:26'),('1961-08-20 06:48:59'),('1962-06-27 07:17:42'),

  ('1963-11-25 05:06:58'),('1965-10-16 21:03:14'),('1969-05-04 20:16:54'),

  ('1970-02-08 17:49:51'),('1977-05-17 00:18:11'),('1979-11-27 00:00:00'),

  ('1980-02-01 00:00:00'),('1980-07-19 04:12:45'),('1982-11-11 19:43:37'),

  ('1985-09-27 00:00:00'),('1988-01-05 00:33:22'),('1988-07-05 00:00:00'),

  ('1990-07-23 08:59:41'),('1992-05-14 20:22:32'),('1992-08-15 00:32:32'),

  ('1995-06-21 21:21:58'),('1995-12-12 23:42:55'),('1995-12-19 00:44:49'),

  ('1997-03-28 22:37:04'),('1999-03-06 10:02:13'),('2001-03-26 17:57:34'),

  ('2002-01-31 14:19:40'),('2007-09-05 23:04:00'),('2009-08-12 07:44:26'),

  ('2009-10-06 02:46:15'),('2012-01-25 10:16:48'),('2014-01-26 00:00:00'),

  ('2019-04-26 00:00:00'),('2019-09-12 09:00:40'),('2019-11-26 21:58:10'),

  ('2020-09-01 00:00:00'),('2020-09-10 10:19:14'),('2023-05-22 15:30:34'),

  ('2024-02-05 23:21:06'),('2024-02-19 17:29:43'),('2025-12-23 00:00:00'),

  ('2026-05-26 15:37:17'),('2026-10-25 08:24:30'),('2027-06-04 16:34:39'),

  ('2027-10-06 20:22:36'),('2030-03-06 04:18:32'),('2030-08-14 11:02:30'),

  ('2030-08-15 00:00:00'),('2031-06-16 20:40:16'),('2031-11-26 21:42:42'),

  ('2032-01-07 20:05:18'),('2034-01-04 02:50:15'),('2034-10-27 00:00:00'),

  ('2036-06-04 16:34:59'),('2037-02-28 00:00:00'),('2039-11-06 03:46:19');

CREATE TABLE t2 (b int);

INSERT t2 VALUES (1),(2);

SET join_cache_level=3;

SELECT COUNT(*) FROM t1, t2 WHERE t1.a IS NULL;

# Cleanup

DROP TABLE t1, t2;

SET join_cache_level=3;

SELECT COUNT(*) FROM t1, t2 WHERE t1.a IS NULL;

COUNT(*)

0

EXPLAIN EXTENDED SELECT COUNT(*) FROM t1, t2 WHERE t1.a IS NULL;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	

1	SIMPLE	t1	hash_range	a	#hash#a:a	6:6	const	7	100.00	Using where; Using index; Using join buffer (flat, BNLH join)

Warnings:

Note	1003	select count(0) AS `COUNT(*)` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` is null

set join_cache_level=2;

SELECT COUNT(*) FROM t1, t2 WHERE t1.a IS NULL;

COUNT(*)

18