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

[17 Jul 2006 0:19] Erica Moss
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;
[25 Jul 2006 15:43] Bugs System
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
[2 Aug 2006 19:03] Evgeny Potemkin
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
[7 Aug 2006 6:34] Jon Stephens
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.