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;