diff options
Diffstat (limited to 'mysql-test/r/view.result')
| -rw-r--r-- | mysql-test/r/view.result | 94 |
1 files changed, 94 insertions, 0 deletions
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 64b329e9e01..ca08c53cabe 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -4789,6 +4789,45 @@ DROP DATABASE IF EXISTS nodb; CREATE VIEW nodb.a AS SELECT 1; ERROR 42000: Unknown database 'nodb' # +# BUG#14117018 - MYSQL SERVER CREATES INVALID VIEW DEFINITION +# BUG#18405221 - SHOW CREATE VIEW OUTPUT INCORRECT +# +CREATE VIEW v1 AS (SELECT '' FROM DUAL); +CREATE VIEW v2 AS (SELECT 'BUG#14117018' AS col1 FROM DUAL) UNION ALL +(SELECT '' FROM DUAL); +CREATE VIEW v3 AS (SELECT 'BUG#14117018' AS col1 FROM DUAL) UNION ALL +(SELECT '' FROM DUAL) UNION ALL +(SELECT '' FROM DUAL); +CREATE VIEW v4 AS (SELECT 'BUG#14117018' AS col1 FROM DUAL) UNION ALL +(SELECT '' AS col2 FROM DUAL) UNION ALL +(SELECT '' FROM DUAL); +CREATE VIEW v5 AS (SELECT 'buggy' AS col1, 'fix' as col2 FROM DUAL) UNION ALL +(SELECT 'buggy' as a, 'fix' as a FROM DUAL); +# Name for the column in select1 is set properly with or +# without this fix. +SHOW CREATE VIEW v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (select '' AS `Name_exp_1`) latin1 latin1_swedish_ci +# Name for the column in select2 is set with this fix. +# Without this fix, name would not have set for the +# columns in select2. +SHOW CREATE VIEW v2; +View Create View character_set_client collation_connection +v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS (select 'BUG#14117018' AS `col1`) union all (select '' AS `Name_exp_1`) latin1 latin1_swedish_ci +# Name for the field item in select2 & select3 is set with this fix. +# Without this fix, name would not have set for the +# columns in select2 & select3. +SHOW CREATE VIEW v3; +View Create View character_set_client collation_connection +v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS (select 'BUG#14117018' AS `col1`) union all (select '' AS `Name_exp_1`) union all (select '' AS `Name_exp_1`) latin1 latin1_swedish_ci +# Name for the field item in select3 is set with this fix. +# Without this fix, name would not have set for the +# columns in select3. +SHOW CREATE VIEW v4; +View Create View character_set_client collation_connection +v4 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v4` AS (select 'BUG#14117018' AS `col1`) union all (select '' AS `col2`) union all (select '' AS `Name_exp_1`) latin1 latin1_swedish_ci +DROP VIEW v1, v2, v3, v4, v5; +# # lp:833600 Wrong result with view + outer join + uncorrelated subquery (non-semijoin) # CREATE TABLE t1 ( a int, b int ); @@ -5300,6 +5339,61 @@ NULL 8 drop view v1; drop table t1,t2,t3; SET optimizer_switch=@save_optimizer_switch_MDEV_3874; +CREATE TABLE `t1` ( +`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, +`f0` int(11) unsigned NOT NULL DEFAULT '0', +`f1` int(11) unsigned NOT NULL DEFAULT '0', +PRIMARY KEY (`id`), +UNIQUE KEY `id` (`id`) +); +CREATE TABLE `t2` ( +`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, +`f02` bigint(20) unsigned NOT NULL DEFAULT '0', +`f03` int(11) unsigned NOT NULL DEFAULT '0', +PRIMARY KEY (`id`), +UNIQUE KEY `id` (`id`) +); +CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `v1` AS +SELECT +`t1`.`f0` AS `f0`, +`t1`.`f1` AS `f1`, +`t2`.`f02` AS `f02`, +`t2`.`f03` AS `f03` +FROM +(`t1` LEFT JOIN `t2` ON((`t1`.`id` = `t2`.`f02`))); +CREATE FUNCTION `f1`( +p0 BIGINT(20) UNSIGNED +) +RETURNS bigint(20) unsigned +DETERMINISTIC +CONTAINS SQL +SQL SECURITY DEFINER +COMMENT '' +BEGIN +DECLARE k0 INTEGER UNSIGNED DEFAULT 0; +DECLARE lResult INTEGER UNSIGNED DEFAULT 0; +SET k0 = 0; +WHILE k0 < 1 DO +SELECT COUNT(*) as `f00` INTO lResult FROM `v1` WHERE `v1`.`f0` = p0; -- BUG +SET k0 = k0 + 1; +END WHILE; +RETURN(k0); +END| +SELECT `f1`(1); +`f1`(1) +1 +SELECT `f1`(1); +`f1`(1) +1 +SELECT `f1`(1); +`f1`(1) +1 +SELECT `f1`(1); +`f1`(1) +1 +DROP FUNCTION f1; +DROP VIEW v1; +DROP TABLE t1, t2; # ----------------------------------------------------------------- # -- End of 5.5 tests. # ----------------------------------------------------------------- |