Bug #48270 mysql-test view.test fails
Submitted: 23 Oct 2009 16:18 Modified: 23 Oct 2009 18:36
Reporter: Martin Dimitrov Email Updates:
Status: Can't repeat Impact on me:
None 
Category:Tests Severity:S3 (Non-critical)
Version:5.0.85 OS:Linux
Assigned to: CPU Architecture:Any

[23 Oct 2009 16:18] Martin Dimitrov
Description:
I just installed mysql from source and kicked off a mysql-test-run. 
Then test view.test failed. 
I configured mysql using: 

CC=gcc CFLAGS="-g -O2" CXX=gcc CXXFLAGS="-g -O2 -felide-constructors
-fno-exceptions -fno-rtti" ./configure --prefix=/home/dimitrov/Research/mysql-server/mysql-5.0.85-install  --with-debug --with-extra-charsets=complex --enable-assembler --with-mysqld-ldflags=-all-static

I am running it on Linux: 
Linux version 2.6.26.8-57.fc8 (mockbuild@x86-3.fedora.phx.redhat.com) (gcc version 4.1.2 20070925 (Red Hat 4.1.2-33))

Below is the part of mysql-test-run that failed. 

variables                      [ pass ]            119
view                           [ fail ]

--- /home/dimitrov/Research/mysql-server/mysql-5.0.85-install/mysql-test/r/view.result  2009-10-23 18:22:14.000000000 +0300
+++ /home/dimitrov/Research/mysql-server/mysql-5.0.85-install/mysql-test/r/view.reject  2009-10-23 18:57:18.000000000 +0300
@@ -3659,6 +3659,51 @@

 # -- End of test case for Bug#34337.

+# -----------------------------------------------------------------
+# -- Bug#35193 VIEW query is rewritten without "FROM DUAL",
+# --           causing syntax error
+# -----------------------------------------------------------------
+
+CREATE VIEW v1 AS SELECT 1 FROM DUAL WHERE 1;
+
+SELECT * FROM v1;
+1
+1
+SHOW CREATE TABLE v1;
+View   Create View
+v1     CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 AS `1` from DUAL  where 1
+
+DROP VIEW v1;
+
+# -- End of test case for Bug#35193.
+
+CREATE VIEW v1 AS SELECT 1;
+DROP VIEW v1;
+CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT, INDEX (c2));
+INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
+SELECT * FROM t1 USE INDEX (PRIMARY) WHERE c1=2;
+c1     c2
+2      2
+SELECT * FROM t1 USE INDEX (c2) WHERE c2=2;
+c1     c2
+2      2
+CREATE VIEW v1 AS SELECT c1, c2 FROM t1;
+SHOW INDEX FROM v1;
+Table  Non_unique      Key_name        Seq_in_index    Column_name     Collation       Cardinality     Sub_part        Packed  Null    Index_type       Comment
+SELECT * FROM v1 USE INDEX (PRIMARY) WHERE c1=2;
+ERROR HY000: Key 'PRIMARY' doesn't exist in table 'v1'
+SELECT * FROM v1 FORCE INDEX (PRIMARY) WHERE c1=2;
+ERROR HY000: Key 'PRIMARY' doesn't exist in table 'v1'
+SELECT * FROM v1 IGNORE INDEX (PRIMARY) WHERE c1=2;
+ERROR HY000: Key 'PRIMARY' doesn't exist in table 'v1'
+SELECT * FROM v1 USE INDEX (c2) WHERE c2=2;
+ERROR HY000: Key 'c2' doesn't exist in table 'v1'
+SELECT * FROM v1 FORCE INDEX (c2) WHERE c2=2;
+ERROR HY000: Key 'c2' doesn't exist in table 'v1'
+SELECT * FROM v1 IGNORE INDEX (c2) WHERE c2=2;
+ERROR HY000: Key 'c2' doesn't exist in table 'v1'
+DROP VIEW v1;
+DROP TABLE t1;
 #
 # Bug #45806 crash when replacing into a view with a join!
 #
@@ -3771,51 +3816,6 @@
 DROP TABLE t1;
 # -- End of test case for Bug#45806
 # -----------------------------------------------------------------
-# -- Bug#35193 VIEW query is rewritten without "FROM DUAL",
-# --           causing syntax error
-# -----------------------------------------------------------------
-
-CREATE VIEW v1 AS SELECT 1 FROM DUAL WHERE 1;
-
-SELECT * FROM v1;
-1
-1
-SHOW CREATE TABLE v1;
-View   Create View
-v1     CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 AS `1` from DUAL  where 1
-
-DROP VIEW v1;
-
-# -- End of test case for Bug#35193.
-
-CREATE VIEW v1 AS SELECT 1;
-DROP VIEW v1;
-CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT, INDEX (c2));
-INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
-SELECT * FROM t1 USE INDEX (PRIMARY) WHERE c1=2;
-c1     c2
-2      2
-SELECT * FROM t1 USE INDEX (c2) WHERE c2=2;
-c1     c2
-2      2
-CREATE VIEW v1 AS SELECT c1, c2 FROM t1;
-SHOW INDEX FROM v1;
-Table  Non_unique      Key_name        Seq_in_index    Column_name     Collation       Cardinality     Sub_part        Packed  Null    Index_type       Comment
-SELECT * FROM v1 USE INDEX (PRIMARY) WHERE c1=2;
-ERROR HY000: Key 'PRIMARY' doesn't exist in table 'v1'
-SELECT * FROM v1 FORCE INDEX (PRIMARY) WHERE c1=2;
-ERROR HY000: Key 'PRIMARY' doesn't exist in table 'v1'
-SELECT * FROM v1 IGNORE INDEX (PRIMARY) WHERE c1=2;
-ERROR HY000: Key 'PRIMARY' doesn't exist in table 'v1'
-SELECT * FROM v1 USE INDEX (c2) WHERE c2=2;
-ERROR HY000: Key 'c2' doesn't exist in table 'v1'
-SELECT * FROM v1 FORCE INDEX (c2) WHERE c2=2;
-ERROR HY000: Key 'c2' doesn't exist in table 'v1'
-SELECT * FROM v1 IGNORE INDEX (c2) WHERE c2=2;
-ERROR HY000: Key 'c2' doesn't exist in table 'v1'
-DROP VIEW v1;
-DROP TABLE t1;
-# -----------------------------------------------------------------
 # -- Bug#40825: Error 1356 while selecting from a view
 # --            with a "HAVING" clause though query works
 # -----------------------------------------------------------------

mysqltest: Result content mismatch

Aborting: view failed in default mode.
To continue, re-run with '--force'.
Stopping All Servers

How to repeat:
mysql-test-run view

Suggested fix:
...
[23 Oct 2009 16:28] MySQL Verification Team
I couldn't repeat with current source on Ubuntu 9.10 64-bit:

Installing Master Database
=======================================================
Starting Tests in the 'main' suite

TEST                           RESULT         TIME (ms)
-------------------------------------------------------

view                           [ pass ]          24534
-------------------------------------------------------
Stopping All Servers
skipped 9 bytes from file: socket (3)
All 1 tests were successful.
The servers were restarted 1 times
Spent 24.534 of 28 seconds executing testcases
[23 Oct 2009 16:32] Valeriy Kravchuk
Please, try to repeat with a newer version, 5.0.86, and inform about the results. Looks like a test case problem really, and is not repeatable for me with recent 5.0.88:

77-52-222-60:mysql-test openxs$ ./mysql-test-run.pl view
Logging: ./mysql-test-run.pl view
MySQL Version 5.0.88
Using ndbcluster when necessary, mysqld supports it
Setting mysqld to support SSL connections
Binaries are debug compiled
mysql-test-run: WARNING: Could not find all required ndb binaries, all ndb tests will fail, use --skip-ndbcluster to skip testing it.
Using MTR_BUILD_THREAD      = 0
Using MASTER_MYPORT         = 9306
Using MASTER_MYPORT1        = 9307
Using SLAVE_MYPORT          = 9308
Using SLAVE_MYPORT1         = 9309
Using SLAVE_MYPORT2         = 9310
Using NDBCLUSTER_PORT       = 9311
Using IM_PORT               = 9313
Using IM_MYSQLD1_PORT       = 9314
Using IM_MYSQLD2_PORT       = 9315
Killing Possible Leftover Processes
Removing Stale Files
Creating Directories
Installing Master Database
=======================================================
Starting Tests in the 'main' suite

TEST                           RESULT         TIME (ms)
-------------------------------------------------------

view                           [ pass ]           2583
-------------------------------------------------------
Stopping All Servers
skipped 9 bytes from file: socket (3)
All 1 tests were successful.
The servers were restarted 1 times
Spent 2.583 of 6 seconds executing testcases
[23 Oct 2009 17:22] Martin Dimitrov
Using mysql-5.0.86 succeeds on this test. 
I think the issue was with the test view.test. For some reason it had included a test for bug 45806 two times. (probably copy and paste error .. ) 
The diff below, shows the extra test for Bug 45806. 

I guess the issue is fixed now. 
Thanks

[dimitrov@localhost mysql-test]$ diff t/view.test ../../mysql-5.0.85-install/mysql-test/t/view.test
3682a3683,3714
> --echo #
> --echo # Bug #45806 crash when replacing into a view with a join!
> --echo #
> CREATE TABLE t1(a INT UNIQUE);
> CREATE VIEW v1 AS SELECT t1.a FROM t1, t1 AS a;
> INSERT INTO t1 VALUES (1), (2);
>
> REPLACE INTO v1(a) SELECT 1 FROM t1,t1 AS c;
> SELECT * FROM v1;
> REPLACE INTO v1(a) SELECT 3 FROM t1,t1 AS c;
> SELECT * FROM v1;
> DELETE FROM t1 WHERE a=3;
> INSERT INTO v1(a) SELECT 1 FROM t1,t1 AS c
> ON DUPLICATE KEY UPDATE `v1`.`a`= 1;
> SELECT * FROM v1;
>
> CREATE VIEW v2 AS SELECT t1.a FROM t1, v1 AS a;
>
> REPLACE INTO v2(a) SELECT 1 FROM t1,t1 AS c;
> SELECT * FROM v2;
> REPLACE INTO v2(a) SELECT 3 FROM t1,t1 AS c;
> SELECT * FROM v2;
> INSERT INTO v2(a) SELECT 1 FROM t1,t1 AS c
> ON DUPLICATE KEY UPDATE `v2`.`a`= 1;
> SELECT * FROM v2;
>
> DROP VIEW v1;
> DROP VIEW v2;
> DROP TABLE t1;
>
> --echo # -- End of test case for Bug#45806
>
[23 Oct 2009 18:36] Valeriy Kravchuk
Not repeatable with current versions.