diff options
Diffstat (limited to 'mysql-test/t/view.test')
| -rw-r--r-- | mysql-test/t/view.test | 302 |
1 files changed, 300 insertions, 2 deletions
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index 0c4680eb5d2..97359a2d9b1 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -1,3 +1,4 @@ +--source include/have_partition.inc --disable_warnings drop table if exists t1,t2,t3,t4,t5,t6,t9,`t1a``b`,v1,v2,v3,v4,v5,v6; @@ -141,7 +142,7 @@ drop view v100; drop view t1; # try to drop VIEW with DROP TABLE --- error ER_BAD_TABLE_ERROR +-- error ER_IT_IS_A_VIEW drop table v1; # try to drop table with DROP VIEW @@ -2866,7 +2867,7 @@ DROP VIEW IF EXISTS v2; CREATE TABLE t1(a INT, b INT); --error ER_WRONG_STRING_LENGTH -CREATE DEFINER=1234567890abcdefGHIKL@localhost +CREATE DEFINER=longer_than_80_456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789@localhost VIEW v1 AS SELECT a FROM t1; --error ER_WRONG_STRING_LENGTH @@ -5698,4 +5699,301 @@ set big_tables=@save_big_tables; --echo # -- End of 5.5 tests. --echo # ----------------------------------------------------------------- +--echo # some subqueries in SELECT list test +create table t1 (a int, b int); +create table t2 (a int, b int); +insert into t1 values (1,2), (3,4), (3,3), (5,6), (7,8), (9,10); +insert into t2 values (1,2), (3,4), (3,3), (5,6), (7,8), (9,10); +create algorithm=merge view v1 as select t1.a as a, (select max(b) from t2 where t1.a=t2.a) as c from t1; +explain extended +select * from v1; +select * from v1; +explain extended +select * from t2, v1 where t2.a=v1.a; +select * from t2, v1 where t2.a=v1.a; +explain extended +select * from t1, v1 where t1.a=v1.a; +select * from t1, v1 where t1.a=v1.a; +explain extended +select * from t1, v1 where t1.b=v1.c; +select * from t1, v1 where t1.b=v1.c; +explain extended +select * from t2, t1, v1 where t1.a=t2.a and t1.a=v1.a; +select * from t2, t1, v1 where t1.a=t2.a and t1.a=v1.a; + +drop view v1; +drop table t1,t2; + +# +# MDEV-6785 Wrong result on 2nd execution of PS with aggregate function, FROM SQ or MERGE view +# + +create table t1 (i int not null); +insert into t1 values (1),(2); +create table t2 (j int not null); +insert into t2 values (11),(12); +create algorithm=merge view v3 as select t1.* from t2 left join t1 on (t2.j = t1.i); +prepare stmt from 'select count(v3.i) from t1, v3'; +execute stmt; +execute stmt; +drop table t1, t2; +drop view v3; + +--echo # +--echo # MDEV-8525: mariadb 10.0.20 crashing when data is read by Kodi +--echo # media center (http://kodi.tv). +--echo # + +CREATE TABLE `t1` ( + `idSong` int(11) NOT NULL AUTO_INCREMENT, + `idAlbum` int(11) DEFAULT NULL, + `idPath` int(11) DEFAULT NULL, + `strArtists` text, + `strGenres` text, + `strTitle` varchar(512) DEFAULT NULL, + `iTrack` int(11) DEFAULT NULL, + `iDuration` int(11) DEFAULT NULL, + `iYear` int(11) DEFAULT NULL, + `dwFileNameCRC` text, + `strFileName` text, + `strMusicBrainzTrackID` text, + `iTimesPlayed` int(11) DEFAULT NULL, + `iStartOffset` int(11) DEFAULT NULL, + `iEndOffset` int(11) DEFAULT NULL, + `idThumb` int(11) DEFAULT NULL, + `lastplayed` varchar(20) DEFAULT NULL, + `rating` char(1) DEFAULT '0', + `comment` text, + `mood` text, + PRIMARY KEY (`idSong`), + UNIQUE KEY `idxSong7` (`idAlbum`,`strMusicBrainzTrackID`(36)), + KEY `idxSong` (`strTitle`(255)), + KEY `idxSong1` (`iTimesPlayed`), + KEY `idxSong2` (`lastplayed`), + KEY `idxSong3` (`idAlbum`), + KEY `idxSong6` (`idPath`,`strFileName`(255)) +) DEFAULT CHARSET=utf8; + +INSERT INTO `t1` VALUES (1,1,1,'strArtists1','strGenres1','strTitle1',1,100,2000,NULL,'strFileName1','strMusicBrainzTrackID1',0,0,0,NULL,NULL,'0','',''),(2,2,2,'strArtists2','strGenres2','strTitle2',2,200,2001,NULL,'strFileName2','strMusicBrainzTrackID2',0,0,0,NULL,NULL,'0','',''); + +CREATE TABLE `t2` ( + `idAlbum` int(11) NOT NULL AUTO_INCREMENT, + `strAlbum` varchar(256) DEFAULT NULL, + `strMusicBrainzAlbumID` text, + `strArtists` text, + `strGenres` text, + `iYear` int(11) DEFAULT NULL, + `idThumb` int(11) DEFAULT NULL, + `bCompilation` int(11) NOT NULL DEFAULT '0', + `strMoods` text, + `strStyles` text, + `strThemes` text, + `strReview` text, + `strImage` text, + `strLabel` text, + `strType` text, + `iRating` int(11) DEFAULT NULL, + `lastScraped` varchar(20) DEFAULT NULL, + `dateAdded` varchar(20) DEFAULT NULL, + `strReleaseType` text, + PRIMARY KEY (`idAlbum`), + UNIQUE KEY `idxAlbum_2` (`strMusicBrainzAlbumID`(36)), + KEY `idxAlbum` (`strAlbum`(255)), + KEY `idxAlbum_1` (`bCompilation`) +) DEFAULT CHARSET=utf8; + +INSERT INTO `t2` VALUES (1,'strAlbum1','strMusicBrainzAlbumID1','strArtists1','strGenres1',2000,NULL,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'album'); + +CREATE TABLE `t3` ( + `idArtist` int(11) DEFAULT NULL, + `idAlbum` int(11) DEFAULT NULL, + `strJoinPhrase` text, + `boolFeatured` int(11) DEFAULT NULL, + `iOrder` int(11) DEFAULT NULL, + `strArtist` text, + UNIQUE KEY `idxAlbumArtist_1` (`idAlbum`,`idArtist`), + UNIQUE KEY `idxAlbumArtist_2` (`idArtist`,`idAlbum`), + KEY `idxAlbumArtist_3` (`boolFeatured`) +) DEFAULT CHARSET=utf8; + +INSERT INTO `t3` VALUES (1,1,'',0,0,'strArtist1'); + +CREATE TABLE `t4` ( + `idArtist` int(11) NOT NULL AUTO_INCREMENT, + `strArtist` varchar(256) DEFAULT NULL, + `strMusicBrainzArtistID` text, + `strBorn` text, + `strFormed` text, + `strGenres` text, + `strMoods` text, + `strStyles` text, + `strInstruments` text, + `strBiography` text, + `strDied` text, + `strDisbanded` text, + `strYearsActive` text, + `strImage` text, + `strFanart` text, + `lastScraped` varchar(20) DEFAULT NULL, + `dateAdded` varchar(20) DEFAULT NULL, + PRIMARY KEY (`idArtist`), + UNIQUE KEY `idxArtist1` (`strMusicBrainzArtistID`(36)), + KEY `idxArtist` (`strArtist`(255)) +) DEFAULT CHARSET=utf8; + +INSERT INTO `t4` VALUES (1,'strArtist1','strMusicBrainzArtistID',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); + +CREATE VIEW `v1` AS select `t2`.`idAlbum` AS `idAlbum`,`t2`.`strAlbum` AS `strAlbum`,`t2`.`strMusicBrainzAlbumID` AS `strMusicBrainzAlbumID`,`t2`.`strArtists` AS `strArtists`,`t2`.`strGenres` AS `strGenres`,`t2`.`iYear` AS `iYear`,`t2`.`strMoods` AS `strMoods`,`t2`.`strStyles` AS `strStyles`,`t2`.`strThemes` AS `strThemes`,`t2`.`strReview` AS `strReview`,`t2`.`strLabel` AS `strLabel`,`t2`.`strType` AS `strType`,`t2`.`strImage` AS `strImage`,`t2`.`iRating` AS `iRating`,`t2`.`bCompilation` AS `bCompilation`,(select min(`t1`.`iTimesPlayed`) from `t1` where (`t1`.`idAlbum` = `t2`.`idAlbum`)) AS `iTimesPlayed`,`t2`.`strReleaseType` AS `strReleaseType` from `t2`; + +CREATE VIEW `v2` AS select `t3`.`idAlbum` AS `idAlbum`,`t3`.`idArtist` AS `idArtist`,`t4`.`strArtist` AS `strArtist`,`t4`.`strMusicBrainzArtistID` AS `strMusicBrainzArtistID`,`t3`.`boolFeatured` AS `boolFeatured`,`t3`.`strJoinPhrase` AS `strJoinPhrase`,`t3`.`iOrder` AS `iOrder` from (`t3` join `t4` on((`t3`.`idArtist` = `t4`.`idArtist`))); + +SELECT v1.*,v2.* FROM v1 LEFT JOIN v2 ON v1.idAlbum = v2.idAlbum WHERE v1.idAlbum = 1 ORDER BY v2.iOrder; + +drop view v1,v2; +drop table t1,t2,t3,t4; + +--echo # +--echo # MDEV-8913: Derived queries with same column names as final +--echo # projection causes issues when using Order By +--echo # +create table t1 (field int); +insert into t1 values (10),(5),(3),(8),(20); + +SELECT sq.f2 AS f1, sq.f1 AS f2 +FROM ( SELECT field AS f1, 1 AS f2 FROM t1) AS sq +ORDER BY sq.f1; + +create view v1 as SELECT field AS f1, 1 AS f2 FROM t1; + +SELECT sq.f2 AS f1, sq.f1 AS f2 +FROM v1 AS sq +ORDER BY sq.f1; + +drop view v1; + +create table t2 SELECT field AS f1, 1 AS f2 FROM t1; + +SELECT + sq.f2 AS f1, + sq.f1 AS f2 +FROM t2 AS sq +ORDER BY sq.f1; + +drop table t1, t2; + +--error ER_BAD_FIELD_ERROR +SELECT 1 FROM (SELECT 1 as a) AS b HAVING (SELECT `SOME_GARBAGE`.b.a)=1; + + +--echo # +--echo # MDEV-10035: DBUG_ASSERT on CREATE VIEW v1 AS SELECT * FROM t1 +--echo # FOR UPDATE +--echo # + +CREATE TABLE t1 (a INT); +insert into t1 values (1),(2); + +CREATE VIEW v1 AS SELECT * FROM t1 FOR UPDATE; +SHOW CREATE VIEW v1; +select * from v1; +DROP VIEW v1; + +CREATE VIEW v1 AS SELECT * FROM t1 LOCK IN SHARE MODE; +SHOW CREATE VIEW v1; +select * from v1; +DROP VIEW v1; + +DROP TABLE t1; + +--echo # +--echo # MDEV-8642: WHERE Clause not applied on View - Empty result set returned +--echo # + +CREATE TABLE `t1` ( + `id` int(20) NOT NULL AUTO_INCREMENT, + `use_case` int(11) DEFAULT NULL, + `current_deadline` date DEFAULT NULL, + `ts_create` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY (`id`), + UNIQUE KEY `id_UNIQUE` (`id`) +) ENGINE=MyISAM AUTO_INCREMENT=13976 DEFAULT CHARSET=latin1; +INSERT INTO `t1` VALUES (1,10,'2015-12-18','2015-08-18 08:38:16'); +INSERT INTO `t1` VALUES (2,20,'2015-10-18','2015-08-18 08:43:30'); +CREATE VIEW v1 AS SELECT + use_case as use_case_id, + ( + SELECT + deadline_sub.current_deadline + FROM + t1 deadline_sub + WHERE + deadline_sub.use_case = use_case_id + AND ts_create = (SELECT + MIN(ts_create) + FROM + t1 startdate_sub + WHERE + startdate_sub.use_case = use_case_id + ) + ) AS InitialDeadline +FROM + t1; + +SELECT * FROM v1 where use_case_id = 10; + +drop view v1; +drop table t1; + +--echo # +--echo # MDEV-12666: CURRENT_ROLE() and DATABASE() does not work in a view +--echo # +--echo # DATABASE() fails only when the initial view creation features a NULL +--echo # default database. +--echo # +--echo # CREATE, USE and DROP database so that we have no "default" database. +--echo # +CREATE DATABASE temporary; +USE temporary; +DROP DATABASE temporary; +SELECT DATABASE(); + +CREATE VIEW test.v_no_db AS SELECT DATABASE() = 'temporary_two'; +SHOW CREATE VIEW test.v_no_db; +PREPARE prepared_no_database FROM "SELECT DATABASE() = 'temporary_two'"; + +--echo # +--echo # All statements should return NULL +--echo # +EXECUTE prepared_no_database; +SELECT DATABASE() = 'temporary_two'; +SELECT * FROM test.v_no_db; + +CREATE DATABASE temporary_two; +USE temporary_two; +CREATE VIEW test.v_with_db AS SELECT DATABASE() = 'temporary_two'; +PREPARE prepared_with_database FROM "SELECT DATABASE() = 'temporary_two'"; + +--echo # +--echo # All statements should return 1; +--echo # +SELECT DATABASE() = 'temporary_two'; +SELECT * FROM test.v_no_db; +SELECT * FROM test.v_with_db; +EXECUTE prepared_with_database; + +--echo # +--echo # Prepared statements maintain default database to be the same +--echo # during on creation so this should return NULL still. +--echo # See MySQL bug #25843 +--echo # +EXECUTE prepared_no_database; + +DROP DATABASE temporary_two; +DROP VIEW test.v_no_db; +DROP VIEW test.v_with_db; +USE test; + +--echo # ----------------------------------------------------------------- +--echo # -- End of 10.0 tests. +--echo # ----------------------------------------------------------------- SET optimizer_switch=@save_optimizer_switch; |