[Commits] Rev 3736: MDEV-5414: RAND() in a subselect : different behavior in MariaDB and MySQL in file:///home/bell/maria/bzr/work-maria-5.3-MDEV-5414/

sanja at askmonty.org sanja at askmonty.org
Tue Dec 17 14:21:44 EET 2013


At file:///home/bell/maria/bzr/work-maria-5.3-MDEV-5414/

------------------------------------------------------------
revno: 3736
revision-id: sanja at askmonty.org-20131217122129-fz6tqzmwlxjfihc9
parent: igor at askmonty.org-20131211181308-k5nwfqsl8pfolwcc
committer: sanja at askmonty.org
branch nick: work-maria-5.3-MDEV-5414
timestamp: Tue 2013-12-17 14:21:29 +0200
message:
  MDEV-5414: RAND() in a subselect : different behavior in MariaDB and MySQL
  
  Materialization forced in case if rand() used in view or derived table to avoud several calls of rand for gting value of a field.
-------------- next part --------------
=== modified file 'mysql-test/r/derived_view.result'
--- a/mysql-test/r/derived_view.result	2013-11-14 21:25:05 +0000
+++ b/mysql-test/r/derived_view.result	2013-12-17 12:21:29 +0000
@@ -2246,6 +2246,115 @@ Warnings:
 Note	1003	select 4 AS `a` from `test`.`t1` 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 table 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 table 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
 #

=== modified file 'mysql-test/r/view.result'
--- a/mysql-test/r/view.result	2013-11-11 15:28:14 +0000
+++ b/mysql-test/r/view.result	2013-12-17 12:21:29 +0000
@@ -4740,6 +4740,16 @@ 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;
 # -----------------------------------------------------------------
 # -- End of 5.3 tests.
 # -----------------------------------------------------------------

=== modified file 'mysql-test/t/derived_view.test'
--- a/mysql-test/t/derived_view.test	2013-11-14 21:25:05 +0000
+++ b/mysql-test/t/derived_view.test	2013-12-17 12:21:29 +0000
@@ -1591,6 +1591,118 @@ EXPLAIN EXTENDED SELECT a FROM v1 WHERE
 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 #

=== modified file 'mysql-test/t/view.test'
--- a/mysql-test/t/view.test	2013-11-11 15:28:14 +0000
+++ b/mysql-test/t/view.test	2013-12-17 12:21:29 +0000
@@ -4675,6 +4675,22 @@ 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 # -- End of 5.3 tests.
 --echo # -----------------------------------------------------------------

=== modified file 'sql/sql_derived.cc'
--- a/sql/sql_derived.cc	2013-10-21 10:45:49 +0000
+++ b/sql/sql_derived.cc	2013-12-17 12:21:29 +0000
@@ -357,6 +357,14 @@ bool mysql_derived_merge(THD *thd, LEX *
   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;

=== modified file 'sql/sql_lex.cc'
--- a/sql/sql_lex.cc	2013-11-14 21:25:05 +0000
+++ b/sql/sql_lex.cc	2013-12-17 12:21:29 +0000
@@ -2340,7 +2340,9 @@ bool st_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();

=== modified file 'sql/sql_lex.h'
--- a/sql/sql_lex.h	2013-05-03 13:07:13 +0000
+++ b/sql/sql_lex.h	2013-12-17 12:21:29 +0000
@@ -2002,6 +2002,7 @@ typedef struct st_lex : public Query_tab
       sl->uncacheable|= cause;
       un->uncacheable|= cause;
     }
+    select_lex.uncacheable|= cause;
   }
   void set_trg_event_type_for_tables();
 



More information about the commits mailing list