Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-30601

Wrong result with hash_range and join_cache_level=3 or 4

    XMLWordPrintable

Details

    Description

      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.

      Attachments

        Activity

          People

            psergei Sergei Petrunia
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.