MySQL Bugs: #21086: server crashes when VIEW defined with a SELECT with COLLATE clause is called

Bug #21086	server crashes when VIEW defined with a SELECT with COLLATE clause is called
Submitted:	17 Jul 2006 0:19	Modified:	7 Aug 2006 6:34
Reporter:	Erica Moss	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S1 (Critical)
Version:	5.1.12	OS:	Linux (Fedora core 5)
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
The code below demonstrates the problem simply.  If you specify a collation for a SELECT and use this in a view definition, and include either an AS clause, or use the (col_name) syntax to specify an alias for the column, the server crashes when you INSERT INTO the view.

RESULTS:
 At line 19: query 'INSERT INTO v1 (col) VALUES ('b')' failed: 2013: Lost connection to MySQL server during query

How to repeat:
DROP DATABASE IF EXISTS fooDB;
CREATE DATABASE fooDB;
use fooDB;
CREATE TABLE t1 (s1 char);
INSERT INTO t1 VALUES ('Z');

CREATE VIEW v1 AS
       SELECT s1 collate latin1_german1_ci AS col FROM t1;

CREATE VIEW v2 (col) AS
       SELECT s1 collate latin1_german1_ci FROM t1;

# these will work
INSERT INTO v1 VALUES ('a');
SELECT col FROM v1;
UPDATE v2 SET col='n' WHERE col='Z';

# either of these statements will cause crash
INSERT INTO v1 (col) VALUES ('b');
INSERT INTO v2 (col) VALUES ('c');

DROP VIEW v1, v2;
DROP TABLE t1;
DROP DATABASE fooDB;

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9540

When executing INSERT over a view with calculated columns it was assuming all
elements of the fields collection are actually Item_field instances.
This may not be true when inserting into a view and that view has columns that are 
such expressions that allow updating (like setting a collation for example).

Fixed in 5.0.25, 5.1.12

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://www.mysql.com/doc/en/Installing_source_tree.html

Documented in 5.0.25 & 5.1.12 changelogs.