summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/derived_view.result109
-rw-r--r--mysql-test/r/view.result50
-rw-r--r--mysql-test/t/derived_view.test112
-rw-r--r--mysql-test/t/view.test45
-rw-r--r--sql/sql_derived.cc8
-rw-r--r--sql/sql_lex.cc4
-rw-r--r--sql/sql_lex.h1
-rw-r--r--sql/sql_select.cc3
-rw-r--r--sql/sql_union.cc5
-rw-r--r--sql/sql_yacc.yy4
10 files changed, 336 insertions, 5 deletions
diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result
index edb0aefe163..9a70bffff6f 100644
--- a/mysql-test/r/derived_view.result
+++ b/mysql-test/r/derived_view.result
@@ -2256,6 +2256,115 @@ Warnings:
Note 1003 select 4 AS `a` from dual where (4 > 100) order by 1
DROP VIEW v1;
DROP TABLE t1;
+CREATE TABLE IF NOT EXISTS `galleries` (
+`id` int(11) NOT NULL AUTO_INCREMENT,
+`name` varchar(100) NOT NULL,
+`year` int(11) DEFAULT NULL,
+PRIMARY KEY (`id`),
+UNIQUE KEY `name` (`name`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+Warnings:
+Warning 1286 Unknown storage engine 'InnoDB'
+Warning 1266 Using storage engine MyISAM for table 'galleries'
+CREATE TABLE IF NOT EXISTS `pictures` (
+`id` int(11) NOT NULL AUTO_INCREMENT,
+`name` varchar(100) NOT NULL,
+`width` float DEFAULT NULL,
+`height` float DEFAULT NULL,
+`year` int(4) DEFAULT NULL,
+`technique` varchar(50) DEFAULT NULL,
+`comment` varchar(2000) DEFAULT NULL,
+`gallery_id` int(11) NOT NULL,
+`type` int(11) NOT NULL,
+PRIMARY KEY (`id`),
+KEY `gallery_id` (`gallery_id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
+Warnings:
+Warning 1286 Unknown storage engine 'InnoDB'
+Warning 1266 Using storage engine MyISAM for table 'pictures'
+ALTER TABLE `pictures`
+ADD CONSTRAINT `pictures_ibfk_1` FOREIGN KEY (`gallery_id`) REFERENCES `galleries` (`id`);
+INSERT INTO `galleries` (`id`, `name`, `year`) VALUES
+(1, 'Quand le noir et blanc invite le taupe', 2013),
+(2, 'Une touche de couleur', 2012),
+(3, 'Éclats', 2011),
+(4, 'Gris béton', 2010),
+(5, 'Expression du spalter', 2010),
+(6, 'Zénitude', 2009),
+(7, 'La force du rouge', 2008),
+(8, 'Sphères', NULL),
+(9, 'Centre', 2009),
+(10, 'Nébuleuse', NULL);
+INSERT INTO `pictures` (`id`, `name`, `width`, `height`, `year`, `technique`, `comment`, `gallery_id`, `type`) VALUES
+(1, 'Éclaircie', 72.5, 100, NULL, NULL, NULL, 1, 1),
+(2, 'Architecture', 81, 100, NULL, NULL, NULL, 1, 1),
+(3, 'Nouveau souffle', 72.5, 100, NULL, NULL, NULL, 1, 1),
+(4, 'Échanges (2)', 89, 116, NULL, NULL, NULL, 1, 1),
+(5, 'Échanges', 89, 116, NULL, NULL, NULL, 1, 1),
+(6, 'Fenêtre de vie', 81, 116, NULL, NULL, NULL, 1, 1),
+(7, 'Architecture', 81, 100, NULL, NULL, NULL, 1, 1),
+(8, 'Nouveau souffle (2)', 72.5, 100, NULL, NULL, NULL, 1, 1),
+(9, 'Fluidité', 89, 116, NULL, NULL, NULL, 1, 1),
+(10, 'Nouveau Monde', 89, 125, NULL, NULL, NULL, 1, 1),
+(11, 'Mirage', 73, 100, NULL, NULL, NULL, 1, 1),
+(12, 'Équilibre', 72.5, 116, NULL, NULL, NULL, 2, 1),
+(13, 'Fusion', 72.5, 116, NULL, NULL, NULL, 2, 1),
+(14, 'Étincelles', NULL, NULL, NULL, NULL, NULL, 3, 1),
+(15, 'Régénérescence', NULL, NULL, NULL, NULL, NULL, 3, 1),
+(16, 'Chaleur', 80, 80, NULL, NULL, NULL, 4, 1),
+(17, 'Création', 90, 90, NULL, NULL, NULL, 4, 1),
+(18, 'Horizon', 92, 73, NULL, NULL, NULL, 4, 1),
+(19, 'Labyrinthe', 81, 100, NULL, NULL, NULL, 4, 1),
+(20, 'Miroir', 80, 116, NULL, NULL, NULL, 5, 1),
+(21, 'Libération', 81, 116, NULL, NULL, NULL, 5, 1),
+(22, 'Éclats', 81, 116, NULL, NULL, NULL, 5, 1),
+(23, 'Zénitude', 116, 89, NULL, NULL, NULL, 6, 1),
+(24, 'Écritures lointaines', 90, 90, NULL, NULL, NULL, 7, 1),
+(25, 'Émergence', 80, 80, NULL, NULL, NULL, 7, 1),
+(26, 'Liberté', 50, 50, NULL, NULL, NULL, 7, 1),
+(27, 'Silhouettes amérindiennes', 701, 70, NULL, NULL, NULL, 7, 1),
+(28, 'Puissance', 81, 100, NULL, NULL, NULL, 8, 1),
+(29, 'Source', 73, 116, NULL, NULL, NULL, 8, 1),
+(30, 'Comme une ville qui prend vie', 50, 100, 2008, NULL, NULL, 9, 1),
+(31, 'Suspension azur', 80, 80, NULL, NULL, NULL, 9, 1),
+(32, 'Nébuleuse', 70, 70, NULL, NULL, NULL, 10, 1),
+(33, 'Œuvre commandée 120 P', 114, 195, NULL, NULL, NULL, 1, 2),
+(34, 'Œuvre commandée 120 P', 114, 195, NULL, NULL, NULL, 1, 2),
+(35, 'Œuvre commandée 120 P', 114, 195, NULL, NULL, NULL, 1, 2),
+(36, 'Œuvre commandée 120 P', 114, 195, NULL, NULL, NULL, 1, 2),
+(37, 'Œuvre commandée 120 P', 114, 195, NULL, NULL, NULL, 1, 2),
+(38, 'Œuvre commandée 120 P', 114, 195, NULL, NULL, NULL, 1, 2);
+explain
+SELECT g.id AS gallery_id,
+g.name AS gallery_name,
+p.id AS picture_id,
+p.name AS picture_name,
+g.p_random AS r1,
+g.p_random AS r2,
+g.p_random AS r3
+FROM
+(
+SELECT gal.id,
+gal.name,
+(
+SELECT pi.id
+FROM pictures pi
+WHERE pi.gallery_id = gal.id
+ORDER BY RAND()
+LIMIT 1
+) AS p_random
+FROM galleries gal
+) g
+LEFT JOIN pictures p
+ON p.id = g.p_random
+ORDER BY gallery_name ASC
+;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE <derived2> ALL NULL NULL NULL NULL 2 Using filesort
+1 SIMPLE p eq_ref PRIMARY PRIMARY 4 g.p_random 1 Using where
+2 DERIVED gal ALL NULL NULL NULL NULL 10
+3 DEPENDENT SUBQUERY pi ref gallery_id gallery_id 4 test.gal.id 4 Using temporary; Using filesort
+drop table galleries, pictures;
#
# end of 5.3 tests
#
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 36716fb2a30..ee43b3a3fcd 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -4952,6 +4952,56 @@ i1 c1 i1 c1
deallocate prepare stmt;
drop view v1;
drop table t1,t2;
+create table t1 (a int);
+insert into t1 values (1),(2);
+create view v1 (a,r) as select a,rand() from t1;
+create table t2 select a, r as r1, r as r2, r as r3 from v1;
+select a, r1 = r2, r2 = r3 from t2;
+a r1 = r2 r2 = r3
+1 1 1
+2 1 1
+drop view v1;
+drop table t1,t2;
+#
+# MDEV-5515: 2nd execution of a prepared statement returns wrong results
+#
+CREATE TABLE t1 (i1 INT, j1 INT NOT NULL, PRIMARY KEY (i1));
+INSERT INTO t1 VALUES (30,300),(40,400);
+CREATE TABLE t2 (i2 INT);
+INSERT INTO t2 VALUES (50),(60);
+CREATE TABLE t3 (c3 VARCHAR(20), i3 INT);
+INSERT INTO t3 VALUES ('a',10),('b',2);
+CREATE TABLE t4 (i4 INT);
+INSERT INTO t4 VALUES (1),(2);
+DROP VIEW IF EXISTS v1;
+Warnings:
+Note 1051 Unknown table 'test.v1'
+CREATE VIEW v1 AS select coalesce(j1,i3) AS v1_field1 from t2 join t3 left join t1 on ( i1 = i2 );
+CREATE VIEW v2 AS select v1_field1 from t4 join v1;
+prepare my_stmt from "select v1_field1 from v2";
+execute my_stmt;
+v1_field1
+10
+10
+10
+10
+2
+2
+2
+2
+execute my_stmt;
+v1_field1
+10
+10
+10
+10
+2
+2
+2
+2
+deallocate prepare my_stmt;
+DROP VIEW v1,v2;
+DROP TABLE t1,t2,t3,t4;
# -----------------------------------------------------------------
# -- End of 5.3 tests.
# -----------------------------------------------------------------
diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test
index 3da58d8ae23..61e11cebad4 100644
--- a/mysql-test/t/derived_view.test
+++ b/mysql-test/t/derived_view.test
@@ -1591,6 +1591,118 @@ EXPLAIN EXTENDED SELECT a FROM v1 WHERE a > 100 ORDER BY b;
DROP VIEW v1;
DROP TABLE t1;
+#
+# MDEV-5414: RAND() in a subselect : different behavior in MariaDB and MySQL
+#
+CREATE TABLE IF NOT EXISTS `galleries` (
+ `id` int(11) NOT NULL AUTO_INCREMENT,
+ `name` varchar(100) NOT NULL,
+ `year` int(11) DEFAULT NULL,
+ PRIMARY KEY (`id`),
+ UNIQUE KEY `name` (`name`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+CREATE TABLE IF NOT EXISTS `pictures` (
+ `id` int(11) NOT NULL AUTO_INCREMENT,
+ `name` varchar(100) NOT NULL,
+ `width` float DEFAULT NULL,
+ `height` float DEFAULT NULL,
+ `year` int(4) DEFAULT NULL,
+ `technique` varchar(50) DEFAULT NULL,
+ `comment` varchar(2000) DEFAULT NULL,
+ `gallery_id` int(11) NOT NULL,
+ `type` int(11) NOT NULL,
+ PRIMARY KEY (`id`),
+ KEY `gallery_id` (`gallery_id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
+
+ALTER TABLE `pictures`
+ ADD CONSTRAINT `pictures_ibfk_1` FOREIGN KEY (`gallery_id`) REFERENCES `galleries` (`id`);
+
+INSERT INTO `galleries` (`id`, `name`, `year`) VALUES
+(1, 'Quand le noir et blanc invite le taupe', 2013),
+(2, 'Une touche de couleur', 2012),
+(3, 'Éclats', 2011),
+(4, 'Gris béton', 2010),
+(5, 'Expression du spalter', 2010),
+(6, 'Zénitude', 2009),
+(7, 'La force du rouge', 2008),
+(8, 'Sphères', NULL),
+(9, 'Centre', 2009),
+(10, 'Nébuleuse', NULL);
+
+INSERT INTO `pictures` (`id`, `name`, `width`, `height`, `year`, `technique`, `comment`, `gallery_id`, `type`) VALUES
+(1, 'Éclaircie', 72.5, 100, NULL, NULL, NULL, 1, 1),
+(2, 'Architecture', 81, 100, NULL, NULL, NULL, 1, 1),
+(3, 'Nouveau souffle', 72.5, 100, NULL, NULL, NULL, 1, 1),
+(4, 'Échanges (2)', 89, 116, NULL, NULL, NULL, 1, 1),
+(5, 'Échanges', 89, 116, NULL, NULL, NULL, 1, 1),
+(6, 'Fenêtre de vie', 81, 116, NULL, NULL, NULL, 1, 1),
+(7, 'Architecture', 81, 100, NULL, NULL, NULL, 1, 1),
+(8, 'Nouveau souffle (2)', 72.5, 100, NULL, NULL, NULL, 1, 1),
+(9, 'Fluidité', 89, 116, NULL, NULL, NULL, 1, 1),
+(10, 'Nouveau Monde', 89, 125, NULL, NULL, NULL, 1, 1),
+(11, 'Mirage', 73, 100, NULL, NULL, NULL, 1, 1),
+(12, 'Équilibre', 72.5, 116, NULL, NULL, NULL, 2, 1),
+(13, 'Fusion', 72.5, 116, NULL, NULL, NULL, 2, 1),
+(14, 'Étincelles', NULL, NULL, NULL, NULL, NULL, 3, 1),
+(15, 'Régénérescence', NULL, NULL, NULL, NULL, NULL, 3, 1),
+(16, 'Chaleur', 80, 80, NULL, NULL, NULL, 4, 1),
+(17, 'Création', 90, 90, NULL, NULL, NULL, 4, 1),
+(18, 'Horizon', 92, 73, NULL, NULL, NULL, 4, 1),
+(19, 'Labyrinthe', 81, 100, NULL, NULL, NULL, 4, 1),
+(20, 'Miroir', 80, 116, NULL, NULL, NULL, 5, 1),
+(21, 'Libération', 81, 116, NULL, NULL, NULL, 5, 1),
+(22, 'Éclats', 81, 116, NULL, NULL, NULL, 5, 1),
+(23, 'Zénitude', 116, 89, NULL, NULL, NULL, 6, 1),
+(24, 'Écritures lointaines', 90, 90, NULL, NULL, NULL, 7, 1),
+(25, 'Émergence', 80, 80, NULL, NULL, NULL, 7, 1),
+(26, 'Liberté', 50, 50, NULL, NULL, NULL, 7, 1),
+(27, 'Silhouettes amérindiennes', 701, 70, NULL, NULL, NULL, 7, 1),
+(28, 'Puissance', 81, 100, NULL, NULL, NULL, 8, 1),
+(29, 'Source', 73, 116, NULL, NULL, NULL, 8, 1),
+(30, 'Comme une ville qui prend vie', 50, 100, 2008, NULL, NULL, 9, 1),
+(31, 'Suspension azur', 80, 80, NULL, NULL, NULL, 9, 1),
+(32, 'Nébuleuse', 70, 70, NULL, NULL, NULL, 10, 1),
+(33, 'Œuvre commandée 120 P', 114, 195, NULL, NULL, NULL, 1, 2),
+(34, 'Œuvre commandée 120 P', 114, 195, NULL, NULL, NULL, 1, 2),
+(35, 'Œuvre commandée 120 P', 114, 195, NULL, NULL, NULL, 1, 2),
+(36, 'Œuvre commandée 120 P', 114, 195, NULL, NULL, NULL, 1, 2),
+(37, 'Œuvre commandée 120 P', 114, 195, NULL, NULL, NULL, 1, 2),
+(38, 'Œuvre commandée 120 P', 114, 195, NULL, NULL, NULL, 1, 2);
+
+# Now we only lest explain to be sure that table materialized. If
+# in the future merged derived table will be processed in a way that
+# rand() can be called only once then other way of testing correctness
+# of this query should be put here.
+explain
+SELECT g.id AS gallery_id,
+ g.name AS gallery_name,
+ p.id AS picture_id,
+ p.name AS picture_name,
+ g.p_random AS r1,
+ g.p_random AS r2,
+ g.p_random AS r3
+FROM
+(
+ SELECT gal.id,
+ gal.name,
+ (
+ SELECT pi.id
+ FROM pictures pi
+ WHERE pi.gallery_id = gal.id
+ ORDER BY RAND()
+ LIMIT 1
+ ) AS p_random
+ FROM galleries gal
+) g
+LEFT JOIN pictures p
+ ON p.id = g.p_random
+ORDER BY gallery_name ASC
+;
+
+drop table galleries, pictures;
+
--echo #
--echo # end of 5.3 tests
--echo #
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index 6424841e017..a01a8a4d815 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -4873,6 +4873,51 @@ deallocate prepare stmt;
drop view v1;
drop table t1,t2;
+#
+# MDEV-5414: RAND() in a subselect : different behavior in MariaDB and MySQL
+#
+create table t1 (a int);
+insert into t1 values (1),(2);
+
+create view v1 (a,r) as select a,rand() from t1;
+
+
+create table t2 select a, r as r1, r as r2, r as r3 from v1;
+
+select a, r1 = r2, r2 = r3 from t2;
+
+drop view v1;
+drop table t1,t2;
+
+--echo #
+--echo # MDEV-5515: 2nd execution of a prepared statement returns wrong results
+--echo #
+CREATE TABLE t1 (i1 INT, j1 INT NOT NULL, PRIMARY KEY (i1));
+
+INSERT INTO t1 VALUES (30,300),(40,400);
+
+CREATE TABLE t2 (i2 INT);
+INSERT INTO t2 VALUES (50),(60);
+
+CREATE TABLE t3 (c3 VARCHAR(20), i3 INT);
+INSERT INTO t3 VALUES ('a',10),('b',2);
+
+CREATE TABLE t4 (i4 INT);
+INSERT INTO t4 VALUES (1),(2);
+
+DROP VIEW IF EXISTS v1;
+CREATE VIEW v1 AS select coalesce(j1,i3) AS v1_field1 from t2 join t3 left join t1 on ( i1 = i2 );
+
+CREATE VIEW v2 AS select v1_field1 from t4 join v1;
+
+prepare my_stmt from "select v1_field1 from v2";
+execute my_stmt;
+execute my_stmt;
+deallocate prepare my_stmt;
+
+DROP VIEW v1,v2;
+DROP TABLE t1,t2,t3,t4;
+
--echo # -----------------------------------------------------------------
--echo # -- End of 5.3 tests.
--echo # -----------------------------------------------------------------
diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc
index b584ed6de93..accc3012e9e 100644
--- a/sql/sql_derived.cc
+++ b/sql/sql_derived.cc
@@ -358,6 +358,14 @@ bool mysql_derived_merge(THD *thd, LEX *lex, TABLE_LIST *derived)
if (derived->merged)
return FALSE;
+ if (dt_select->uncacheable & UNCACHEABLE_RAND)
+ {
+ /* There is random function => fall back to materialization. */
+ derived->change_refs_to_fields();
+ derived->set_materialized_derived();
+ return FALSE;
+ }
+
if (thd->lex->sql_command == SQLCOM_UPDATE_MULTI ||
thd->lex->sql_command == SQLCOM_DELETE_MULTI)
thd->save_prep_leaf_list= TRUE;
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 6645147bd4f..0ce09e3fcd0 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -2589,7 +2589,9 @@ bool LEX::can_be_merged()
// TODO: do not forget implement case when select_lex.table_list.elements==0
/* find non VIEW subqueries/unions */
- bool selects_allow_merge= select_lex.next_select() == 0;
+ bool selects_allow_merge= (select_lex.next_select() == 0 &&
+ !(select_lex.uncacheable &
+ UNCACHEABLE_RAND));
if (selects_allow_merge)
{
for (SELECT_LEX_UNIT *tmp_unit= select_lex.first_inner_unit();
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 4af06eec658..458e990f043 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -2665,6 +2665,7 @@ struct LEX: public Query_tables_list
sl->uncacheable|= cause;
un->uncacheable|= cause;
}
+ select_lex.uncacheable|= cause;
}
void set_trg_event_type_for_tables();
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 08da695a12f..d4cf72ede42 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -988,8 +988,9 @@ JOIN::optimize()
if (select_lex->handle_derived(thd->lex, DT_MERGE))
DBUG_RETURN(TRUE);
table_count= select_lex->leaf_tables.elements;
- select_lex->update_used_tables();
}
+ // Update used tables after all handling derived table procedures
+ select_lex->update_used_tables();
if (transform_max_min_subquery())
DBUG_RETURN(1); /* purecov: inspected */
diff --git a/sql/sql_union.cc b/sql/sql_union.cc
index 72f1df2f4ae..82fe7f2936a 100644
--- a/sql/sql_union.cc
+++ b/sql/sql_union.cc
@@ -647,7 +647,10 @@ bool st_select_lex_unit::exec()
{
ha_rows records_at_start= 0;
thd->lex->current_select= sl;
- fake_select_lex->uncacheable|= sl->uncacheable;
+ if (sl != &thd->lex->select_lex)
+ fake_select_lex->uncacheable|= sl->uncacheable;
+ else
+ fake_select_lex->uncacheable= 0;
{
set_limit(sl);
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 1c1cd0958f1..de6bd59cf7e 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -9369,7 +9369,7 @@ variable_aux:
if ($$ == NULL)
MYSQL_YYABORT;
LEX *lex= Lex;
- lex->uncacheable(UNCACHEABLE_RAND);
+ lex->uncacheable(UNCACHEABLE_SIDEEFFECT);
lex->set_var_list.push_back(item);
}
| ident_or_text
@@ -9378,7 +9378,7 @@ variable_aux:
if ($$ == NULL)
MYSQL_YYABORT;
LEX *lex= Lex;
- lex->uncacheable(UNCACHEABLE_RAND);
+ lex->uncacheable(UNCACHEABLE_SIDEEFFECT);
}
| '@' opt_var_ident_type ident_or_text opt_component
{
View Lorry Controller Status