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

Wrong result (missing row) with use_stat_tables, stale statistics, STRAIGHT_JOIN

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.0.4
    • Fix Version/s: 10.0.7
    • Component/s: None
    • Labels:
      None

      Description

      In the following test case the first query produces an empty result set, and the second query returns a row. 1 row is the correct result.

      SET use_stat_tables = 'PREFERABLY';
       
      CREATE TABLE t1 (id INT PRIMARY KEY) ENGINE=MyISAM;
      INSERT INTO t1 VALUES (1),(2);
      ANALYZE TABLE t1;
       
      CREATE TABLE t2 (name CHAR(3)) ENGINE=MyISAM;
      ANALYZE TABLE t2;
      INSERT INTO t2 VALUES ('USA'),('AUS');
       
      SELECT * FROM t1 STRAIGHT_JOIN t2 WHERE name IN ( 'AUS','YEM' ) AND id = 1;
       
      ANALYZE TABLE t2;
       
      SELECT * FROM t1 STRAIGHT_JOIN t2 WHERE name IN ( 'AUS','YEM' ) AND id = 1;

      Results:

      INSERT INTO t2 VALUES ('USA'),('AUS');
      SELECT * FROM t1 STRAIGHT_JOIN t2 WHERE name IN ( 'AUS','YEM' ) AND id = 1;
      id	name
      ANALYZE TABLE t2;
      Table	Op	Msg_type	Msg_text
      test.t2	analyze	status	OK
      SELECT * FROM t1 STRAIGHT_JOIN t2 WHERE name IN ( 'AUS','YEM' ) AND id = 1;
      id	name
      1	AUS

      revision-id: sergii@pisem.net-20131028064617-ferc2dktspd2rylj
      revno: 3907
      branch-nick: 10.0-base

      First EXPLAIN:

      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
      Warnings:
      Note	1003	select 1 AS `id`,'USA' AS `name` from `test`.`t1` straight_join `test`.`t2` where (('USA' in ('AUS','YEM')))

      Second EXPLAIN:

      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	100.00	Using index
      1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
      Warnings:
      Note	1003	select 1 AS `id`,`test`.`t2`.`name` AS `name` from `test`.`t1` straight_join `test`.`t2` where ((`test`.`t2`.`name` in ('AUS','YEM')))

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              igor Igor Babaev
              Reporter:
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration