Bug #6394 Views: Crash if select a view created with EXISTS clause
Submitted: 3 Nov 2004 2:37 Modified: 25 Feb 2005 19:25
Reporter: Shuichi Tamagawa Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.2 OS:Linux (SuSE Linux 9.0)
Assigned to: Oleksandr Byelkin CPU Architecture:Any

[3 Nov 2004 2:37] Shuichi Tamagawa
Description:
MySQL crushes if you SELECT a view which was created with EXISTS(or NOT EXISTS) clause.

It crushes only if the SELECT statement specifies the column name. If the SELECT statement uses '*' , it doesn't crush but returns "ERROR 1356 (HY000): View 'test.v2' references invalid table(s) or column(s)"  instead.

Please see the attached files to see
 - Messages after crus
 - Result of stack trace

How to repeat:
mysql> create table t1(c1 int);
Query OK, 0 rows affected (0.11 sec)

mysql> create table t2(c2 int);
Query OK, 0 rows affected (0.10 sec)

mysql> insert into t1 values (1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into t2 values (1);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT c1 FROM t1 WHERE c1 IN (SELECT c2 FROM t2);
+------+
| c1   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> SELECT c1 FROM t1 WHERE EXISTS (SELECT c2 FROM t2 WHERE c2 = c1);
+------+
| c1   |
+------+
|    1 |
+------+
1 row in set (0.01 sec)

mysql> create view v1 as SELECT c1 FROM t1 WHERE c1 IN (SELECT c2 FROM t2);
Query OK, 0 rows affected (0.00 sec)

mysql> create view v2 as SELECT c1 FROM t1 WHERE EXISTS (SELECT c2 FROM t2 WHERE c2 = c1);
Query OK, 0 rows affected (0.00 sec)
mysql> select * from v1;
+------+
| c1   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> select * from v2;
ERROR 1356 (HY000): View 'test.v2' references invalid table(s) or column(s)
mysql> select c1 from v2;
mysqld got signal 11;
...
...
...
[3 Nov 2004 2:44] Shuichi Tamagawa
messages after crash

Attachment: error.txt (text/plain), 3.75 KiB.

[3 Nov 2004 2:45] Shuichi Tamagawa
stack trace

Attachment: stack_trace.txt (text/plain), 1.35 KiB.

[18 Jan 2005 20:00] Oleksandr Byelkin
ChangeSet
  1.1800 05/01/18 21:58:12 bell@sanja.is.com.ua +3 -0
  fixed problem in resolving items of outer query in subqueries in view (BUG#6394)
[10 Feb 2005 21:10] Oleksandr Byelkin
ChangeSet
  1.1821 05/02/10 23:01:59 bell@sanja.is.com.ua +3 -0
  Better emulation correct SELECT tree for fields expressions of merging view during name resolving (BUG#6394)
[24 Feb 2005 2:16] Oleksandr Byelkin
Thank you for bugreport!
Bugfix is pushed into 5.0.3 source repository.
[25 Feb 2005 19:25] Paul Dubois
Noted in 5.0.3 changelog.