bk commit into 5.0 tree (konstantin:1.1964) BUG#11901

[Konstantin Osipov]

Below is the list of changes that have just been committed into a local
5.0 repository of kostja. When kostja does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet
  1.1964 05/08/09 21:58:29 konstantin@stripped +3 -0
  A fix for Bug#11901 "mysql_stmt_attr_set CURSOR_TYPE_READ_ONLY join in
  subqry order by server crash": failing DBUG_ASSERT(curr_join == this)
  when opening a cursor.
  Ensure that for top-level join curr_join == join (always), 
  and thus fix the failing assert.
  curr_join is a hack to ensure that uncacheable subqueries can be
  re-evaluated safely, and should be never different from main join
  in case of top-level join.

  tests/mysql_client_test.c
    1.149 05/08/09 21:58:21 konstantin@stripped +123 -0
    A test case for Bug#11901 "mysql_stmt_attr_set CURSOR_TYPE_READ_ONLY
     join in subqry order by server crash"

  sql/sql_select.h
    1.96 05/08/09 21:58:21 konstantin@stripped +5 -0
    - implement JOIN::is_top_level_join()

  sql/sql_select.cc
    1.354 05/08/09 21:58:21 konstantin@stripped +4 -5
    - utilize tmp_join only if we evaluate an uncacheable subquery.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	konstantin
# Host:	oak.local
# Root:	/home/kostja/mysql/mysql-5.0-11901

--- 1.353/sql/sql_select.cc	2005-08-08 19:26:11 +04:00
+++ 1.354/sql/sql_select.cc	2005-08-09 21:58:21 +04:00
@@ -1090,9 +1090,10 @@
 	  order=0;
       }
     }
-    
-    if (thd->lex->subqueries)
+
+    if (select_lex->uncacheable && !is_top_level_join())
     {
+      /* If this join belongs to an uncacheable subquery */
       if (!(tmp_join= (JOIN*)thd->alloc(sizeof(JOIN))))
 	DBUG_RETURN(-1);
       error= 0;				// Ensure that tmp_join.error= 0
@@ -1636,9 +1637,7 @@
   curr_join->fields= curr_fields_list;
   curr_join->procedure= procedure;
 
-  if (unit == &thd->lex->unit &&
-      (unit->fake_select_lex == 0 || select_lex == unit->fake_select_lex)
&&
-      thd->cursor && tables != const_tables)
+  if (is_top_level_join() && thd->cursor && tables != const_tables)
   {
     /*
       We are here if this is JOIN::exec for the last select of the main unit

--- 1.95/sql/sql_select.h	2005-08-08 19:24:47 +04:00
+++ 1.96/sql/sql_select.h	2005-08-09 21:58:21 +04:00
@@ -366,6 +366,11 @@
 	    !group_list);
   }
   bool change_result(select_result *result);
+  bool is_top_level_join() const
+  {
+    return (unit == &thd->lex->unit && (unit->fake_select_lex == 0
||
+                                        select_lex == unit->fake_select_lex));
+  }
 };
 
 

--- 1.148/tests/mysql_client_test.c	2005-08-08 19:24:48 +04:00
+++ 1.149/tests/mysql_client_test.c	2005-08-09 21:58:21 +04:00
@@ -14051,6 +14051,128 @@
   myquery(rc);
 }
 
+/* Cursors: opening a cursor to a compilicated query with ORDER BY */
+
+static void test_bug11901()
+{
+  MYSQL_STMT *stmt;
+  MYSQL_BIND bind[2];
+  int rc;
+  char workdept[20];
+  ulong workdept_len;
+  uint32 empno;
+  const char *stmt_text;
+
+  myheader("test_bug11901");
+
+  stmt_text= "drop table if exists t1, t2";
+  rc= mysql_real_query(mysql, stmt_text, strlen(stmt_text));
+  myquery(rc);
+
+  stmt_text= "create table t1 ("
+    "  empno int(11) not null, firstname varchar(20) not null,"
+    "  midinit varchar(20) not null, lastname varchar(20) not null,"
+    "  workdept varchar(6) not null, salary double not null,"
+    "  bonus float not null, primary key (empno), "
+    " unique key (workdept, empno) "
+    ") default charset=latin1 collate=latin1_bin";
+  rc= mysql_real_query(mysql, stmt_text, strlen(stmt_text));
+  myquery(rc);
+
+  stmt_text= "insert into t1 values "
+     "(10,  'CHRISTINE', 'I', 'HAAS',      'A00', 52750, 1000),"
+     "(20,  'MICHAEL',   'L', 'THOMPSON',  'B01', 41250, 800), "
+     "(30,  'SALLY',     'A', 'KWAN',      'C01', 38250, 800), "
+     "(50,  'JOHN',      'B', 'GEYER',     'E01', 40175, 800), "
+     "(60,  'IRVING',    'F', 'STERN',     'D11', 32250, 500), "
+     "(70,  'EVA',       'D', 'PULASKI',   'D21', 36170, 700), "
+     "(90,  'EILEEN',    'W', 'HENDERSON', 'E11', 29750, 600), "
+     "(100, 'THEODORE',  'Q', 'SPENSER',   'E21', 26150, 500), "
+     "(110, 'VINCENZO',  'G', 'LUCCHESSI', 'A00', 46500, 900), "
+     "(120, 'SEAN',      '',  'O\\'CONNELL', 'A00', 29250, 600), "
+     "(130, 'DOLORES',   'M', 'QUINTANA',  'C01', 23800, 500), "
+     "(140, 'HEATHER',   'A', 'NICHOLLS',  'C01', 28420, 600), "
+     "(150, 'BRUCE',     '',  'ADAMSON',   'D11', 25280, 500), "
+     "(160, 'ELIZABETH', 'R', 'PIANKA',    'D11', 22250, 400), "
+     "(170, 'MASATOSHI', 'J', 'YOSHIMURA', 'D11', 24680, 500), "
+     "(180, 'MARILYN',   'S', 'SCOUTTEN',  'D11', 21340, 500), "
+     "(190, 'JAMES',     'H', 'WALKER',    'D11', 20450, 400), "
+     "(200, 'DAVID',     '',  'BROWN',     'D11', 27740, 600), "
+     "(210, 'WILLIAM',   'T', 'JONES',     'D11', 18270, 400), "
+     "(220, 'JENNIFER',  'K', 'LUTZ',      'D11', 29840, 600), "
+     "(230, 'JAMES',     'J', 'JEFFERSON', 'D21', 22180, 400), "
+     "(240, 'SALVATORE', 'M', 'MARINO',    'D21', 28760, 600), "
+     "(250, 'DANIEL',    'S', 'SMITH',     'D21', 19180, 400), "
+     "(260, 'SYBIL',     'P', 'JOHNSON',   'D21', 17250, 300), "
+     "(270, 'MARIA',     'L', 'PEREZ',     'D21', 27380, 500), "
+     "(280, 'ETHEL',     'R', 'SCHNEIDER', 'E11', 26250, 500), "
+     "(290, 'JOHN',      'R', 'PARKER',    'E11', 15340, 300), "
+     "(300, 'PHILIP',    'X', 'SMITH',     'E11', 17750, 400), "
+     "(310, 'MAUDE',     'F', 'SETRIGHT',  'E11', 15900, 300), "
+     "(320, 'RAMLAL',    'V', 'MEHTA',     'E21', 19950, 400), "
+     "(330, 'WING',      '',  'LEE',       'E21', 25370, 500), "
+     "(340, 'JASON',     'R', 'GOUNOT',    'E21', 23840, 500)";
+
+  rc= mysql_real_query(mysql, stmt_text, strlen(stmt_text));
+  myquery(rc);
+
+  stmt_text= "create table t2 ("
+    " deptno varchar(6) not null, deptname varchar(20) not null,"
+    " mgrno int(11) not null, location varchar(20) not null,"
+    " admrdept varchar(6) not null, refcntd int(11) not null,"
+    " refcntu int(11) not null, primary key (deptno)"
+    ") default charset=latin1 collate=latin1_bin";
+  rc= mysql_real_query(mysql, stmt_text, strlen(stmt_text));
+  myquery(rc);
+
+  stmt_text= "insert into t2 values "
+    "('A00', 'SPIFFY COMPUTER SERV', 10, '', 'A00', 0, 0), "
+    "('B01', 'PLANNING',             20, '', 'A00', 0, 0), "
+    "('C01', 'INFORMATION CENTER',   30, '', 'A00', 0, 0), "
+    "('D01', 'DEVELOPMENT CENTER',   0,  '', 'A00', 0, 0),"
+    "('D11', 'MANUFACTURING SYSTEM', 60, '', 'D01', 0, 0), "
+    "('D21', 'ADMINISTRATION SYSTE', 70, '', 'D01', 0, 0), "
+    "('E01', 'SUPPORT SERVICES',     50, '', 'A00', 0, 0), "
+    "('E11', 'OPERATIONS',           90, '', 'E01', 0, 0), "
+    "('E21', 'SOFTWARE SUPPORT',     100,'', 'E01', 0, 0)";
+  rc= mysql_real_query(mysql, stmt_text, strlen(stmt_text));
+  myquery(rc);
+
+  /* ****** Begin of trace ****** */
+
+  stmt= open_cursor("select t1.empno, t1.workdept "
+                    "from (t1 left join t2 on t2.deptno = t1.workdept) "
+                    "where t2.deptno in "
+                    "   (select t2.deptno "
+                    "    from (t1 left join t2 on t2.deptno = t1.workdept) "
+                    "    where t1.empno = ?) "
+                    "order by 1");
+  bzero(bind, sizeof(bind));
+
+  bind[0].buffer_type= MYSQL_TYPE_LONG;
+  bind[0].buffer= &empno;
+  rc= mysql_stmt_bind_param(stmt, bind);
+  check_execute(stmt, rc);
+
+  bind[1].buffer_type= MYSQL_TYPE_VAR_STRING;
+  bind[1].buffer= (void*) workdept;
+  bind[1].buffer_length= sizeof(workdept);
+  bind[1].length= &workdept_len;
+
+  rc= mysql_stmt_bind_result(stmt, bind);
+  check_execute(stmt, rc);
+
+  empno= 10;
+  /* ERROR: next statement causes a server crash */
+  rc= mysql_stmt_execute(stmt);
+  check_execute(stmt, rc);
+
+  mysql_stmt_close(stmt);
+
+  rc= mysql_query(mysql, "drop table t1, t2");
+  myquery(rc);
+}
+
 /*
   Read and parse arguments and MySQL options from my.cnf
 */
@@ -14298,6 +14420,7 @@
   { "test_bug10760", test_bug10760 },
   { "test_bug12001", test_bug12001 },
   { "test_bug11909", test_bug11909 },
+  { "test_bug11901", test_bug11901 },
   { 0, 0 }
 };