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

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

        Details

        • Type: Bug
        • Status: Closed
        • 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')))

            Gliffy Diagrams

              Attachments

                Issue Links

                relates to

                Task - A task that needs to be done. MDEV-3806 Engine independent statistics

                • Major - Major loss of function.
                • Closed

                  Activity

                  Hide
                  Permalink
                  igor Igor Babaev added a comment -

                  The fix for this bug was pushed into 10.0-base.

                  Show
                  igor Igor Babaev added a comment - The fix for this bug was pushed into 10.0-base.

                    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: