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
• Status: Closed
• 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 [e-mail] +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 [e-mail] +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.