|
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;
|
|
10.4 f5dceafd
|
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
|
Expected result is 18:
set join_cache_level=2;
|
SELECT COUNT(*) FROM t1, t2 WHERE t1.a IS NULL;
|
COUNT(*)
|
18
|
Reproducible on all existing versions, including earlier minor releases, with at least MyISAM, InnoDB, Aria.
|