vyHTwBTF

1SELECT "A" AS x, i.n FROM mta_info.integers AS i
2    UNION ALL SELECT "B" AS x, i.n FROM mta_info.integers AS i
3	
4SELECT * FROM thatview WHERE thatview.x = "A"
5	
6DROP TABLE IF EXISTS integers;
7CREATE TABLE `integers` (
8  `n` int(10) unsigned NOT NULL,
9  PRIMARY KEY (`n`)
10) ENGINE=InnoDB DEFAULT CHARSET=utf8;
11
12INSERT INTO integers (n) VALUES (0),(1),(2),(3),(4),(5);
13INSERT INTO integers (n) (SELECT i.n+(SELECT MAX(n+1) FROM integers)*j.n FROM integers AS i INNER JOIN integers AS j ON j.n > 0);
14INSERT INTO integers (n) (SELECT i.n+(SELECT MAX(n+1) FROM integers)*j.n FROM integers AS i INNER JOIN integers AS j ON j.n > 0);
15INSERT INTO integers (n) (SELECT i.n+(SELECT MAX(n+1) FROM integers)*j.n FROM integers AS i INNER JOIN integers AS j ON j.n > 0);
16
17CREATE ALGORITHM=UNDEFINED VIEW thatview AS 
18     SELECT "A" AS x, i.n FROM mta_info.integers AS i
19    UNION ALL SELECT "B" AS x, i.n FROM mta_info.integers AS i;
20
21 -- Query I do    
22SELECT * FROM thatview WHERE thatview.x = "A";
23
24-- Which is actually seen by MySQL as:
25SELECT * FROM (
26    SELECT "A" AS x, i.n FROM mta_info.integers AS i
27    UNION ALL SELECT "B" AS x, i.n FROM mta_info.integers AS i) AS thatview 
28WHERE thatview.x = "A";
29
30-- While is should be simplified to this, if the Merge algorithm was usable
31SELECT * FROM (SELECT "A" AS x, i.n FROM mta_info.integers AS i) AS thatview 
32WHERE thatview.x = "A";