Wrong empty result in subselect_sj.test:Bug#49097 when all subquery related optimizer switches are ON

[Timour Katchaounov]

The following test case for Bug#49097, extracted from subselect_sj
produces an incorrect empty result for all tree statements, when all
three optimizer siwtches are ON: materialization, in_to_exists, semijoin.
This is the only combination of switches that exposes the wrong
result.

--echo
--echo Bug#49097 subquery with view generates wrong result with
--echo non-prepared statement
--echo

--disable_warnings
DROP TABLE IF EXISTS t1, t2;
DROP VIEW IF EXISTS v1;
--enable_warnings

CREATE TABLE t1 (
  city VARCHAR(50) NOT NULL,
  country_id SMALLINT UNSIGNED NOT NULL
);

INSERT INTO t1 VALUES
('Batna',2),
('Bchar',2),
('Skikda',2),
('Tafuna',3),
('Algeria',2) ;

CREATE TABLE t2 (
  country_id SMALLINT UNSIGNED NOT NULL,
  country VARCHAR(50) NOT NULL
);

INSERT INTO t2 VALUES
(2,'Algeria'),
(3,'XAmerican Samoa') ;

CREATE VIEW v1 AS
SELECT country_id, country
FROM t2
WHERE LEFT(country,1) = "A"
;

set @@optimizer_switch='materialization=on,in_to_exists=on,semijoin=on';

SELECT city, country_id
FROM t1
WHERE country_id IN (
  SELECT country_id
  FROM t2
  WHERE LEFT(country,1) = "A"
);

SELECT city, country_id
FROM t1
WHERE country_id IN (
  SELECT country_id
  FROM v1
);

PREPARE stmt FROM
"
SELECT city, country_id
FROM t1
WHERE country_id IN (
  SELECT country_id
  FROM v1
);
";

execute stmt;

deallocate prepare stmt;
drop table t1, t2;
drop view v1;

[Timour Katchaounov (timour)]

The bug is fixed after Monty's patch for 5.3-mwl89:

revno: 2877
committer: Michael Widenius <email address hidden>
branch nick: maria-5.3-mwl89
timestamp: Fri 2011-01-14 01:26:20 +0200
message:
  Don't recalculate conditions that have already been checked.
  This fixes the wrong result in tests like compress, join, join_cache, greedy_optimizer and select_pkeycache