From fdf789a7eadf864ecc0e617f25f795fafda55026 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Sat, 22 Oct 2011 00:14:27 -0700 Subject: Fixed LP bug #874378. This bug happened for the queries over multi-table mergeable views because the bitmap TABLE::read_set of the underlying tables were not updated after the views had been merged into the query. Now this bitmaps are updated properly. Also the bitmap TABLE::merge_keys now is updated in prevention of future bugs. --- mysql-test/t/derived_view.test | 43 ++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 43 insertions(+) (limited to 'mysql-test/t/derived_view.test') diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test index ff7491b38fb..dc25e1765e9 100644 --- a/mysql-test/t/derived_view.test +++ b/mysql-test/t/derived_view.test @@ -1016,5 +1016,48 @@ SELECT v1.a FROM v1,v2 WHERE v2.b = v1.b ORDER BY 1; DROP VIEW v1,v2; DROP TABLE t1,t2; + +--echo # +--echo # Bug #743378: join over merged view employing BNL +--echo # + +CREATE TABLE t1 ( d varchar(1) NOT NULL) ; +INSERT INTO t1 VALUES ('j'),('v'),('c'); + +CREATE TABLE t2 (h time NOT NULL, d varchar(1) NOT NULL) ; +INSERT INTO t2 VALUES ('05:03:03','w'),('02:59:24','d'),('00:01:58','e'); + +CREATE TABLE t3 ( + b int NOT NULL, e varchar(1) NOT NULL, d varchar(1) NOT NULL, KEY (e,b) +); +INSERT INTO t3 VALUES (4,'x','x'),(9,'w','w'),(4,'d','d'),(8,'e','e'); + +CREATE TABLE t4 (i int NOT NULL, m varchar(1) NOT NULL) ; +INSERT INTO t4 VALUES (8,'m'),(9,'d'),(2,'s'),(4,'r'),(8,'m'); + +CREATE TABLE t5 ( + a int NOT NULL, c int NOT NULL, b int NOT NULL, f date NOT NULL, + g date NOT NULL, h time NOT NULL, j time NOT NULL, k datetime NOT NULL +); + +INSERT INTO t5 VALUES + (1,4,0,'0000-00-00','0000-00-00','21:22:34','21:22:34','2002-02-13 17:30'), + (2,6,8,'2004-09-18','2004-09-18','10:50:38','10:50:38','2008-09-27 00:34'); + +CREATE VIEW v3 AS SELECT t3.*, t4.i FROM t3, t4, t5; + +SET SESSION join_cache_level = 1; +SET SESSION join_buffer_size = 512; + +EXPLAIN +SELECT t2.d FROM t1,t2,v3 WHERE v3.e = t2.d AND v3.i < 3; +SELECT t2.d FROM t1,t2,v3 WHERE v3.e = t2.d AND v3.i < 3; + +SET SESSION join_cache_level = DEFAULT; +SET SESSION join_buffer_size = DEFAULT; + +DROP VIEW v3; +DROP TABLE t1,t2,t3,t4,t5; + # The following command must be the last one the file set optimizer_switch=@exit_optimizer_switch; -- cgit v1.2.1