MySQL Bugs: #76710: myisam

Bug #76710	myisam_views-big fails
Submitted:	15 Apr 2015 14:48	Modified:	16 Apr 2015 7:35
Reporter:	Laurynas Biveinis (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Tests	Severity:	S3 (Non-critical)
Version:	5.7.7	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	big-test, mtr

Description:
5.7.7:

./mtr myisam_views-big --big-test
...
funcs_1.myisam_views-big                 [ fail ]
        Test ended at 2015-04-15 07:28:29

CURRENT_TEST: funcs_1.myisam_views-big
--- /Users/laurynas/percona/mysql-server/mysql-test/suite/funcs_1/r/myisam_views-big.result	2015-04-15 10:20:58.000000000 +0300
+++ /Users/laurynas/percona/obj-5.7-debug/mysql-test/var/log/myisam_views-big.reject	2015-04-15 17:28:28.000000000 +0300
@@ -4006,7 +4006,7 @@
 0
 CREATE or REPLACE VIEW v1 AS Select 1
 FROM (SELECT 1 FROM t1) my_table;
-ERROR HY000: View's SELECT contains a subquery in the FROM clause
+DROP VIEW v1;
 CREATE VIEW v1 AS SELECT f1 FROM t1;
 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1 ;
 SET @a:=0 ;
@@ -8356,10 +8356,29 @@
 Drop view if exists test.v1 ;
 CREATE VIEW test.v1
 AS Select f59 from (Select * FROM tb2 limit 20) tx ;
-ERROR HY000: View's SELECT contains a subquery in the FROM clause
 SELECT * FROM test.v1 order by f59 ;
-ERROR 42S02: Table 'test.v1' doesn't exist
-Drop view if exists test.v1 ;
+f59
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+250
+340
+441
+660
+2550
+2760
+3330
+3410
+76710
+569300
+DROP VIEW test.v1 ;
 
 Testcase 3.3.1.40 
 --------------------------------------------------------------------------------
@@ -24060,10 +24079,9 @@
 SET @variant3= 'CREATE VIEW v1 AS SELECT SUM(f59) AS f61 FROM t1';
 SET @variant4= 'CREATE VIEW v1 AS SELECT f61 FROM t1 GROUP BY f61';
 SET @variant5= 'CREATE VIEW v1 AS SELECT f61 FROM t1 HAVING f61 > 0';
-SET @variant6= 'CREATE VIEW v1 AS SELECT (SELECT f60 FROM t2 WHERE f59=19) AS f61 FROM t1';
-SET @variant7= 'CREATE VIEW v1 AS SELECT f61 FROM v2';
-SET @variant8= 'CREATE VIEW v1 AS SELECT f59 AS f61 FROM t1 WHERE f60 IN (SELECT f59 FROM t1)';
-SET @variant9= 'CREATE ALGORITHM = TEMPTABLE VIEW v1 (f61) AS select f60 from t1';
+SET @variant6= 'CREATE VIEW v1 AS SELECT f61 FROM v2';
+SET @variant7= 'CREATE VIEW v1 AS SELECT f59 AS f61 FROM t1 WHERE f60 IN (SELECT f59 FROM t1)';
+SET @variant8= 'CREATE ALGORITHM = TEMPTABLE VIEW v1 (f61) AS select f60 from t1';
 CREATE ALGORITHM = TEMPTABLE VIEW v1 (f61) AS select f60 from t1;
 INSERT INTO v1 VALUES (1002);
 ERROR HY000: The target table v1 of the INSERT is not insertable-into
@@ -24088,14 +24106,6 @@
 DELETE FROM v1;
 ERROR HY000: The target table v1 of the DELETE is not updatable
 DROP VIEW v1;
-CREATE VIEW v1 AS SELECT (SELECT f60 FROM t2 WHERE f59=19) AS f61 FROM t1;
-INSERT INTO v1 VALUES (1002);
-ERROR HY000: The target table v1 of the INSERT is not insertable-into
-UPDATE v1 SET f61=1007;
-ERROR HY000: The target table v1 of the UPDATE is not updatable
-DELETE FROM v1;
-ERROR HY000: The target table v1 of the DELETE is not updatable
-DROP VIEW v1;
 CREATE VIEW v1 AS SELECT f61 FROM t1 HAVING f61 > 0;
 INSERT INTO v1 VALUES (1002);
 ERROR HY000: The target table v1 of the INSERT is not insertable-into

mysqltest: Result content mismatch

How to repeat:
See above

Suggested fix:
Haven't analysed

Hello Laurynas Biveinis,

Thank you for the report.

Thanks,
Umesh

// with 5.7.7 source build

[umshastr@hod03]/export/umesh/server/source/mysql-5.7.7-rc: cmake -DCMAKE_INSTALL_PREFIX=/export/umesh/server/source/mysql-5.7.7-rc -DDOWNLOAD_BOOST=1 -DWITH_BOOST=../boost577
-- Running cmake version 2.8.11
-- Configuring with MAX_INDEXES = 64U
-- SIZEOF_VOIDP 8
-- MySQL 5.7.7-rc
-- Packaging as: mysql-5.7.7-rc-Linux-x86_64
-- Downloading boost_1_57_0.tar.gz to /export/umesh/server/source/boost577
..
-- Build files have been written to: /export/umesh/server/source/mysql-5.7.7-rc
[umshastr@hod03]/export/umesh/server/source/mysql-5.7.7-rc: make
Scanning dependencies of target INFO_BIN
[  0%] Built target INFO_BIN
Scanning dependencies of target INFO_SRC
[  0%] Built target INFO_SRC
Scanning dependencies of target abi_check
[  0%] Built target abi_check
Scanning dependencies of target zlib
[  0%] Building C object zlib/CMakeFiles/zlib.dir/adler32.c.o
[  0%] Building C object zlib/CMakeFiles/zlib.dir/compress.c.o
[  0%] Building C object zlib/CMakeFiles/zlib.dir/crc32.c.o
[  0%] Building C object zlib/CMakeFiles/zlib.dir/deflate.c.o
[  0%] Building C object zlib/CMakeFiles/zlib.dir/gzio.c.o
[  0%] Building C object zlib/CMakeFiles/zlib.dir/infback.c.o
[  0%] Building C object zlib/CMakeFiles/zlib.dir/inffast.c.o
[  0%] Building C object zlib/CMakeFiles/zlib.dir/inflate.c.o
[  0%] Building C object zlib/CMakeFiles/zlib.dir/inftrees.c.o
[  0%] Building C object zlib/CMakeFiles/zlib.dir/trees.c.o
[  0%] Building C object zlib/CMakeFiles/zlib.dir/uncompr.c.o
[  1%] Building C object zlib/CMakeFiles/zlib.dir/zutil.c.o
Linking C static library libzlib.a
[  1%] Built target zlib
..
[100%] Built target my_safe_process
[umshastr@hod03]/export/umesh/server/source/mysql-5.7.7-rc: make install
[  0%] Built target INFO_BIN
..
[umshastr@hod03]/export/umesh/server/source/mysql-5.7.7-rc: cd mysql-test/
[umshastr@hod03]/export/umesh/server/source/mysql-5.7.7-rc/mysql-test: ./mtr myisam_views-big --big-test
Logging: ./mtr  myisam_views-big --big-test
MySQL Version 5.7.7
Checking supported features...
 - SSL connections supported
Collecting tests...
Removing old var directory...
Creating var directory '/export/umesh/server/source/mysql-5.7.7-rc/mysql-test/var'...
Installing system database...

==============================================================================

TEST                                      RESULT   TIME (ms) or COMMENT
--------------------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
funcs_1.myisam_views-big                 [ fail ]
        Test ended at 2015-04-16 09:29:34

CURRENT_TEST: funcs_1.myisam_views-big
--- /export/umesh/server/source/mysql-5.7.7-rc/mysql-test/suite/funcs_1/r/myisam_views-big.result       2015-03-30 15:16:29.000000000 +0300
+++ /export/umesh/server/source/mysql-5.7.7-rc/mysql-test/var/log/myisam_views-big.reject       2015-04-16 10:29:34.565969950 +0300
@@ -4006,7 +4006,7 @@
 0
 CREATE or REPLACE VIEW v1 AS Select 1
 FROM (SELECT 1 FROM t1) my_table;
-ERROR HY000: View's SELECT contains a subquery in the FROM clause
+DROP VIEW v1;
 CREATE VIEW v1 AS SELECT f1 FROM t1;
 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1 ;
 SET @a:=0 ;
@@ -8356,10 +8356,29 @@
 Drop view if exists test.v1 ;
 CREATE VIEW test.v1
 AS Select f59 from (Select * FROM tb2 limit 20) tx ;
-ERROR HY000: View's SELECT contains a subquery in the FROM clause
 SELECT * FROM test.v1 order by f59 ;
-ERROR 42S02: Table 'test.v1' doesn't exist
-Drop view if exists test.v1 ;
+f59
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+250
+340
+441
+660
+2550
+2760
+3330
+3410
+76710
+569300
+DROP VIEW test.v1 ;

 Testcase 3.3.1.40
 --------------------------------------------------------------------------------
@@ -24060,10 +24079,9 @@
 SET @variant3= 'CREATE VIEW v1 AS SELECT SUM(f59) AS f61 FROM t1';
 SET @variant4= 'CREATE VIEW v1 AS SELECT f61 FROM t1 GROUP BY f61';
 SET @variant5= 'CREATE VIEW v1 AS SELECT f61 FROM t1 HAVING f61 > 0';
-SET @variant6= 'CREATE VIEW v1 AS SELECT (SELECT f60 FROM t2 WHERE f59=19) AS f61 FROM t1';
-SET @variant7= 'CREATE VIEW v1 AS SELECT f61 FROM v2';
-SET @variant8= 'CREATE VIEW v1 AS SELECT f59 AS f61 FROM t1 WHERE f60 IN (SELECT f59 FROM t1)';
-SET @variant9= 'CREATE ALGORITHM = TEMPTABLE VIEW v1 (f61) AS select f60 from t1';
+SET @variant6= 'CREATE VIEW v1 AS SELECT f61 FROM v2';
+SET @variant7= 'CREATE VIEW v1 AS SELECT f59 AS f61 FROM t1 WHERE f60 IN (SELECT f59 FROM t1)';
+SET @variant8= 'CREATE ALGORITHM = TEMPTABLE VIEW v1 (f61) AS select f60 from t1';
 CREATE ALGORITHM = TEMPTABLE VIEW v1 (f61) AS select f60 from t1;
 INSERT INTO v1 VALUES (1002);
 ERROR HY000: The target table v1 of the INSERT is not insertable-into
@@ -24084,14 +24102,6 @@
 INSERT INTO v1 VALUES (1002);
 ERROR HY000: The target table v1 of the INSERT is not insertable-into
 UPDATE v1 SET f61=1007;
-ERROR HY000: The target table v1 of the UPDATE is not updatable
-DELETE FROM v1;
-ERROR HY000: The target table v1 of the DELETE is not updatable
-DROP VIEW v1;
-CREATE VIEW v1 AS SELECT (SELECT f60 FROM t2 WHERE f59=19) AS f61 FROM t1;
-INSERT INTO v1 VALUES (1002);
-ERROR HY000: The target table v1 of the INSERT is not insertable-into
-UPDATE v1 SET f61=1007;
 ERROR HY000: The target table v1 of the UPDATE is not updatable
 DELETE FROM v1;
 ERROR HY000: The target table v1 of the DELETE is not updatable

mysqltest: Result content mismatch

 - the logfile can be found in '/export/umesh/server/source/mysql-5.7.7-rc/mysql-test/var/log/funcs_1.myisam_views-big/myisam_views-big.log'
--------------------------------------------------------------------------
The servers were restarted 0 times
Spent 0.000 of 23 seconds executing testcases

Completed: Failed 1/1 tests, 0.00% were successful.

Failing test(s): funcs_1.myisam_views-big

The log files in var/log may give you some hint of what went wrong.

If you want to report this error, please read first the documentation
at http://dev.mysql.com/doc/mysql/en/mysql-test-suite.html

mysql-test-run: *** ERROR: there were failing test cases
[umshastr@hod03]/export/umesh/server/source/mysql-5.7.7-rc/mysql-test: