bk commit into 4.1 tree (sergefp:1.2346) BUG#11700

[Sergey Petrunia]

Below is the list of changes that have just been committed into a local
4.1 repository of psergey. When psergey 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.2346 05/07/14 15:13:36 sergefp@stripped +3 -0
  Fix for BUG#11700: in add_not_null_conds(), call full fix_fields() for the created NOT
NULL.
    This is needed because in some cases range optimization is performed twice and added 
    NOT NULL item must have correct const_table_map() value. 

  sql/sql_select.cc
    1.419 05/07/14 15:13:33 sergefp@stripped +9 -3
    Fix for BUG#11700: in add_not_null_conds(), call full fix_fields() for the created NOT
NULL.
      This is needed because in some cases range optimization is performed twice and added

      NOT NULL item must have correct const_table_map() value. 

  mysql-test/t/select.test
    1.41 05/07/14 15:13:33 sergefp@stripped +41 -0
    Testcase for BUG#11700

  mysql-test/r/select.result
    1.58 05/07/14 15:13:33 sergefp@stripped +35 -0
    Testcase for BUG#11700

# 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:	sergefp
# Host:	newbox.mylan
# Root:	/home/psergey/mysql-4.1-build-tree

--- 1.418/sql/sql_select.cc	2005-07-02 13:13:34 +00:00
+++ 1.419/sql/sql_select.cc	2005-07-14 15:13:33 +00:00
@@ -3539,11 +3539,17 @@
           DBUG_ASSERT(item->type() == Item::FIELD_ITEM);
           Item_field *not_null_item= (Item_field*)item;
           JOIN_TAB *referred_tab= not_null_item->field->table->reginfo.join_tab;
-          Item_func_isnotnull *notnull;
+          Item *notnull;
           if (!(notnull= new Item_func_isnotnull(not_null_item)))
             DBUG_VOID_RETURN;
-
-          notnull->quick_fix_field();
+          /*
+            We need to do full fix_fields() call here in order to have correct
+            notnull->const_item(). This is needed e.g. by test_quick_select 
+            when it is called from make_join_select after this function is 
+            called.
+          */
+          if (notnull->fix_fields(join->thd, join->tables_list, &notnull))
+            DBUG_VOID_RETURN;
           DBUG_EXECUTE("where",print_where(notnull,
                                            referred_tab->table->table_name););
           add_cond_and_fix(&referred_tab->select_cond, notnull);

--- 1.57/mysql-test/r/select.result	2005-06-28 19:20:22 +00:00
+++ 1.58/mysql-test/r/select.result	2005-07-14 15:13:33 +00:00
@@ -2524,3 +2524,38 @@
 id	name	gid	uid	ident	level
 1	fs	NULL	NULL	0	READ
 drop table t1,t2,t3;
+CREATE TABLE t1 (
+acct_id int(11) NOT NULL default '0',
+profile_id smallint(6) default NULL,
+UNIQUE KEY t1$acct_id (acct_id),
+KEY t1$profile_id (profile_id)
+);
+INSERT INTO t1 VALUES (132,17),(133,18);
+CREATE TABLE t2 (
+profile_id smallint(6) default NULL,
+queue_id int(11) default NULL,
+seq int(11) default NULL,
+KEY t2$queue_id (queue_id)
+);
+INSERT INTO t2 VALUES (17,31,4),(17,30,3),(17,36,2),(17,37,1);
+CREATE TABLE t3 (
+id int(11) NOT NULL default '0',
+qtype int(11) default NULL,
+seq int(11) default NULL,
+warn_lvl int(11) default NULL,
+crit_lvl int(11) default NULL,
+rr1 tinyint(4) NOT NULL default '0',
+rr2 int(11) default NULL,
+default_queue tinyint(4) NOT NULL default '0',
+KEY t3$qtype (qtype),
+KEY t3$id (id)
+);
+INSERT INTO t3 VALUES (30,1,29,NULL,NULL,0,NULL,0),(31,1,28,NULL,NULL,0,NULL,0),
+(36,1,34,NULL,NULL,0,NULL,0),(37,1,35,NULL,NULL,0,121,0);
+SELECT COUNT(*) FROM t1 a STRAIGHT_JOIN t2 pq STRAIGHT_JOIN t3 q 
+WHERE 
+(pq.profile_id = a.profile_id) AND (a.acct_id = 132) AND 
+(pq.queue_id = q.id) AND (q.rr1 <> 1);
+COUNT(*)
+4
+drop table t1,t2,t3;

--- 1.40/mysql-test/t/select.test	2005-06-28 19:20:22 +00:00
+++ 1.41/mysql-test/t/select.test	2005-07-14 15:13:33 +00:00
@@ -2075,3 +2075,44 @@
 select * from t3 left join t1 on t3.id = t1.uid, t2 where t2.ident in (0, t1.gid, t3.id,
0);
 
 drop table t1,t2,t3;
+
+# Test for BUG#11700
+CREATE TABLE t1 (
+  acct_id int(11) NOT NULL default '0',
+  profile_id smallint(6) default NULL,
+  UNIQUE KEY t1$acct_id (acct_id),
+  KEY t1$profile_id (profile_id)
+);
+INSERT INTO t1 VALUES (132,17),(133,18);
+
+CREATE TABLE t2 (
+  profile_id smallint(6) default NULL,
+  queue_id int(11) default NULL,
+  seq int(11) default NULL,
+  KEY t2$queue_id (queue_id)
+);
+INSERT INTO t2 VALUES (17,31,4),(17,30,3),(17,36,2),(17,37,1);
+
+CREATE TABLE t3 (
+  id int(11) NOT NULL default '0',
+  qtype int(11) default NULL,
+  seq int(11) default NULL,
+  warn_lvl int(11) default NULL,
+  crit_lvl int(11) default NULL,
+  rr1 tinyint(4) NOT NULL default '0',
+  rr2 int(11) default NULL,
+  default_queue tinyint(4) NOT NULL default '0',
+  KEY t3$qtype (qtype),
+  KEY t3$id (id)
+);
+
+INSERT INTO t3 VALUES (30,1,29,NULL,NULL,0,NULL,0),(31,1,28,NULL,NULL,0,NULL,0),
+  (36,1,34,NULL,NULL,0,NULL,0),(37,1,35,NULL,NULL,0,121,0);
+
+SELECT COUNT(*) FROM t1 a STRAIGHT_JOIN t2 pq STRAIGHT_JOIN t3 q 
+WHERE 
+  (pq.profile_id = a.profile_id) AND (a.acct_id = 132) AND 
+  (pq.queue_id = q.id) AND (q.rr1 <> 1);
+
+drop table t1,t2,t3;
+