bzr commit into mysql-6.0-codebase-bugfixing branch (tor.didriksen:3729) Bug#48073

[Tor Didriksen]

#At file:///export/home/didrik/mysqldev-6.0-codebase/6.0-codebase-bf-valgrind/ based on revid:epotemkin@stripped

 3729 Tor Didriksen	2009-11-24
      Bug #48073  	Subquery on char columns from view crashes Mysql
      
      Server crashed because of a wrong C-style cast.
     @ mysql-test/r/subselect_sj.result
        Add test case.
     @ mysql-test/r/subselect_sj_jcl6.result
        Add test case.
     @ mysql-test/t/subselect_sj.test
        Add test case.
     @ sql/sql_select.cc
        When executing the view-based query, we actually iterate through an 
        object which is an Item_ref, rather than an Item_field.
        Ask the item for its real_item() first, and then do the down-cast.

    modified:
      mysql-test/r/subselect_sj.result
      mysql-test/r/subselect_sj_jcl6.result
      mysql-test/t/subselect_sj.test
      sql/sql_select.cc
=== modified file 'mysql-test/r/subselect_sj.result'
--- a/mysql-test/r/subselect_sj.result	2009-11-24 11:26:13 +0000
+++ b/mysql-test/r/subselect_sj.result	2009-11-24 16:31:06 +0000
@@ -528,3 +528,50 @@ FROM t1 RIGHT JOIN t2 ON t1.varchar_key 
 );
 varchar_key
 DROP TABLE t1, t2, t3;
+
+Bug #48073 Subquery on char columns from view crashes Mysql
+
+DROP TABLE IF EXISTS t1, t2;
+DROP VIEW IF EXISTS v1;
+CREATE TABLE t1 (
+city VARCHAR(50) NOT NULL,
+country_id SMALLINT UNSIGNED NOT NULL
+);
+INSERT INTO t1 VALUES 
+('Batna',2),
+('Bchar',2),
+('Skikda',2),
+('Tafuna',3),
+('Algeria',2) ;
+CREATE TABLE t2 (
+country_id SMALLINT UNSIGNED NOT NULL,
+country VARCHAR(50) NOT NULL
+);
+INSERT INTO t2 VALUES
+(2,'Algeria'),
+(3,'American Samoa') ;
+CREATE VIEW v1 AS 
+SELECT country_id, country 
+FROM t2
+WHERE LEFT(country,1) = "A" 
+;
+SELECT city, country_id
+FROM t1
+WHERE city IN (
+SELECT country 
+FROM t2
+WHERE LEFT(country, 1) = "A" 
+);
+city	country_id
+Algeria	2
+SELECT city, country_id
+FROM t1
+WHERE city IN (
+SELECT country 
+FROM v1
+);
+city	country_id
+Algeria	2
+drop table t1, t2;
+drop view v1;
+# End of bug#48073

=== modified file 'mysql-test/r/subselect_sj_jcl6.result'
--- a/mysql-test/r/subselect_sj_jcl6.result	2009-11-24 11:26:13 +0000
+++ b/mysql-test/r/subselect_sj_jcl6.result	2009-11-24 16:31:06 +0000
@@ -532,6 +532,53 @@ FROM t1 RIGHT JOIN t2 ON t1.varchar_key 
 );
 varchar_key
 DROP TABLE t1, t2, t3;
+
+Bug #48073 Subquery on char columns from view crashes Mysql
+
+DROP TABLE IF EXISTS t1, t2;
+DROP VIEW IF EXISTS v1;
+CREATE TABLE t1 (
+city VARCHAR(50) NOT NULL,
+country_id SMALLINT UNSIGNED NOT NULL
+);
+INSERT INTO t1 VALUES 
+('Batna',2),
+('Bchar',2),
+('Skikda',2),
+('Tafuna',3),
+('Algeria',2) ;
+CREATE TABLE t2 (
+country_id SMALLINT UNSIGNED NOT NULL,
+country VARCHAR(50) NOT NULL
+);
+INSERT INTO t2 VALUES
+(2,'Algeria'),
+(3,'American Samoa') ;
+CREATE VIEW v1 AS 
+SELECT country_id, country 
+FROM t2
+WHERE LEFT(country,1) = "A" 
+;
+SELECT city, country_id
+FROM t1
+WHERE city IN (
+SELECT country 
+FROM t2
+WHERE LEFT(country, 1) = "A" 
+);
+city	country_id
+Algeria	2
+SELECT city, country_id
+FROM t1
+WHERE city IN (
+SELECT country 
+FROM v1
+);
+city	country_id
+Algeria	2
+drop table t1, t2;
+drop view v1;
+# End of bug#48073
 set join_cache_level=default;
 show variables like 'join_cache_level';
 Variable_name	Value

=== modified file 'mysql-test/t/subselect_sj.test'
--- a/mysql-test/t/subselect_sj.test	2009-11-23 10:27:34 +0000
+++ b/mysql-test/t/subselect_sj.test	2009-11-24 16:31:06 +0000
@@ -434,3 +434,59 @@ WHERE (SELECT varchar_key FROM t3 
       );
 
 DROP TABLE t1, t2, t3;
+
+--echo
+--echo Bug #48073 Subquery on char columns from view crashes Mysql
+--echo
+
+--disable_warnings
+DROP TABLE IF EXISTS t1, t2;
+DROP VIEW IF EXISTS v1;
+--enable_warnings
+
+CREATE TABLE t1 (
+  city VARCHAR(50) NOT NULL,
+  country_id SMALLINT UNSIGNED NOT NULL
+);
+
+INSERT INTO t1 VALUES 
+('Batna',2),
+('Bchar',2),
+('Skikda',2),
+('Tafuna',3),
+('Algeria',2) ;
+
+CREATE TABLE t2 (
+  country_id SMALLINT UNSIGNED NOT NULL,
+  country VARCHAR(50) NOT NULL
+);
+
+INSERT INTO t2 VALUES
+(2,'Algeria'),
+(3,'American Samoa') ;
+
+CREATE VIEW v1 AS 
+SELECT country_id, country 
+FROM t2
+WHERE LEFT(country,1) = "A" 
+; 
+
+SELECT city, country_id
+FROM t1
+WHERE city IN (
+  SELECT country 
+  FROM t2
+  WHERE LEFT(country, 1) = "A" 
+);
+
+SELECT city, country_id
+FROM t1
+WHERE city IN (
+  SELECT country 
+  FROM v1
+);
+
+drop table t1, t2;
+drop view v1;
+
+--echo # End of bug#48073

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2009-11-24 11:26:13 +0000
+++ b/sql/sql_select.cc	2009-11-24 16:31:06 +0000
@@ -3457,9 +3457,8 @@ bool convert_subq_to_sj(JOIN *parent_joi
   if (subq_pred->left_expr->cols() == 1)
   {
     nested_join->sj_outer_expr_list.push_back(subq_pred->left_expr);
-
-    Item_func_eq *item_eq= new Item_func_eq(subq_pred->left_expr, 
-                                    subq_lex->ref_pointer_array[0]);
+    Item_func_eq *item_eq=
+      new Item_func_eq(subq_pred->left_expr, subq_lex->ref_pointer_array[0]);
     item_eq->in_equality_no= 0;
     sj_nest->sj_on_expr= and_items(sj_nest->sj_on_expr, item_eq);
   }
@@ -9969,7 +9968,9 @@ bool setup_sj_materialization(JOIN_TAB *
     {
       bool dummy;
       Item_equal *item_eq;
-      Field *copy_to=((Item_field*)it++)->field; 
+      Item *item= (it++)->real_item();
+      DBUG_ASSERT(item->type() == Item::FIELD_ITEM);
+      Field *copy_to= ((Item_field*)item)->field;
       /*
         Tricks with Item_equal are due to the following: suppose we have a
         query:


Attachment: [text/bzr-bundle] bzr/[e-mail]