Fixed AUTO_INCREMENT handling in MyISAM (last auto_increment patch broke things)

Some after merge fixes


mysql-test/r/ctype_latin1_de.result:
  Changed test to have more relevant results
mysql-test/r/delete.result:
  Updated results after merge
mysql-test/r/select.result:
  Remove explicit database usage
mysql-test/r/type_datetime.result:
  Updated results after merge
mysql-test/t/ctype_latin1_de.test:
  Changed test to have more relevant results
mysql-test/t/select.test:
  Remove explicit database usage
sql/ha_myisam.cc:
  Fixed AUTO_INCREMENT handling in MyISAM (last auto_increment patch broke things)

7 files changed, 131 insertions, 130 deletions

diff --git a/mysql-test/r/ctype_latin1_de.result b/mysql-test/r/ctype_latin1_de.result
index e5ae6f249ee..b79bc67138c 100644
--- a/mysql-test/r/ctype_latin1_de.result
+++ b/mysql-test/r/ctype_latin1_de.result
@@ -1,93 +1,95 @@
 drop table if exists t1;
-create table t1 (a char (20) not null, b int not null auto_increment, index (a,b),index(b));
+create table t1 (a char (20) not null, b int not null auto_increment, index (a,b));
 insert into t1 (a) values ('ä'),('ac'),('ae'),('ad'),('Äc'),('aeb');
 insert into t1 (a) values ('üc'),('uc'),('ue'),('ud'),('Ü'),('ueb'),('uf');
 insert into t1 (a) values ('ö'),('oc'),('Öa'),('oe'),('od'),('Öc'),('oeb');
 insert into t1 (a) values ('s'),('ss'),('ß'),('ßb'),('ssa'),('ssc'),('ßa');
 insert into t1 (a) values ('eä'),('uü'),('öo'),('ää'),('ääa'),('aeae');
-insert into t1 (a) values ('q'),('a'),('u'),('o'),('é'),('É');
+insert into t1 (a) values ('q'),('a'),('u'),('o'),('é'),('É'),('a');
 select a,b from t1 order by a,b;
 a	b
-a	35
-ac	2
-ad	4
+a	1
+a	2
+ac	1
+ad	1
 ä	1
-ae	3
-ää	31
-aeae	33
-ääa	32
-aeb	6
-Äc	5
-é	38
-É	39
-eä	28
-o	37
-oc	15
-od	18
-ö	14
-oe	17
-Öa	16
-oeb	20
-Öc	19
-öo	30
-q	34
-s	21
-ss	22
-ß	23
-ssa	25
-ßa	27
-ßb	24
-ssc	26
-u	36
-uc	8
-ud	10
-ue	9
-Ü	11
-ueb	12
-üc	7
-uf	13
-uü	29
+ae	2
+ää	1
+aeae	2
+ääa	1
+aeb	1
+Äc	1
+é	1
+É	2
+eä	1
+o	1
+oc	1
+od	1
+ö	1
+oe	2
+Öa	1
+oeb	1
+Öc	1
+öo	1
+q	1
+s	1
+ss	1
+ß	2
+ssa	1
+ßa	2
+ßb	1
+ssc	1
+u	1
+uc	1
+ud	1
+ue	1
+Ü	2
+ueb	1
+üc	1
+uf	1
+uü	1
 select a,b from t1 order by upper(a),b;
 a	b
-a	35
-ac	2
-ad	4
+a	1
+a	2
+ac	1
+ad	1
 ä	1
-ae	3
-ää	31
-aeae	33
-ääa	32
-aeb	6
-Äc	5
-é	38
-É	39
-eä	28
-o	37
-oc	15
-od	18
-ö	14
-oe	17
-Öa	16
-oeb	20
-Öc	19
-öo	30
-q	34
-s	21
-ss	22
-ß	23
-ssa	25
-ßa	27
-ßb	24
-ssc	26
-u	36
-uc	8
-ud	10
-ue	9
-Ü	11
-ueb	12
-üc	7
-uf	13
-uü	29
+ae	2
+ää	1
+aeae	2
+ääa	1
+aeb	1
+Äc	1
+é	1
+É	2
+eä	1
+o	1
+oc	1
+od	1
+ö	1
+oe	2
+Öa	1
+oeb	1
+Öc	1
+öo	1
+q	1
+s	1
+ss	1
+ß	2
+ssa	1
+ßa	2
+ßb	1
+ssc	1
+u	1
+uc	1
+ud	1
+ue	1
+Ü	2
+ueb	1
+üc	1
+uf	1
+uü	1
 select a from t1 order by a desc;
 a
 uü
@@ -129,44 +131,46 @@ ae
 ad
 ac
 a
+a
 check table t1;
 Table	Op	Msg_type	Msg_text
 test.t1	check	status	OK
 select * from t1 where a like "ö%";
 a	b
-ö	14
-Öa	16
-Öc	19
-öo	30
+ö	1
+Öa	1
+Öc	1
+öo	1
 select * from t1 where a like binary "%É%";
 a	b
-É	39
+É	2
 select * from t1 where a like "%Á%";
 a	b
-a	35
-ac	2
-ad	4
-ae	3
-aeae	33
-ääa	32
-aeb	6
-Öa	16
-ssa	25
-ßa	27
+a	1
+a	2
+ac	1
+ad	1
+ae	2
+aeae	2
+ääa	1
+aeb	1
+Öa	1
+ssa	1
+ßa	2
 select * from t1 where a like "%U%";
 a	b
-u	36
-uc	8
-ud	10
-ue	9
-ueb	12
-uf	13
-uü	29
+u	1
+uc	1
+ud	1
+ue	1
+ueb	1
+uf	1
+uü	1
 select * from t1 where a like "%ss%";
 a	b
-ss	22
-ssa	25
-ssc	26
+ss	1
+ssa	1
+ssc	1
 drop table t1;
 select strcmp('ä','ae'),strcmp('ae','ä'),strcmp('aeq','äq'),strcmp('äq','aeq');
 strcmp('ä','ae')	strcmp('ae','ä')	strcmp('aeq','äq')	strcmp('äq','aeq')
diff --git a/mysql-test/r/delete.result b/mysql-test/r/delete.result
index c2230722aa6..582ab894233 100644
--- a/mysql-test/r/delete.result
+++ b/mysql-test/r/delete.result
@@ -24,3 +24,18 @@ create table t1 (a bigint not null, primary key (a,a,a,a,a,a,a,a,a,a));
 insert into t1 values (2),(4),(6),(8),(10),(12),(14),(16),(18),(20),(22),(24),(26),(23),(27);
 delete from t1 where a=27;
 drop table t1;
+CREATE TABLE t1 (
+bool     char(0) default NULL,
+not_null varchar(20) binary NOT NULL default '',
+misc     integer not null,
+PRIMARY KEY  (not_null)
+) TYPE=MyISAM;
+INSERT INTO t1 VALUES (NULL,'a',4), (NULL,'b',5), (NULL,'c',6), (NULL,'d',7);
+select * from t1 where misc > 5 and bool is null;
+bool	not_null	misc
+NULL	c	6
+NULL	d	7
+delete   from t1 where misc > 5 and bool is null;
+select * from t1 where misc > 5 and bool is null;
+bool	not_null	misc
+drop table t1;
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index a921d75f20a..b71c6ada03f 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -1,4 +1,3 @@
-use test;
 drop table if exists t1,t2,t3,t4;
 CREATE TABLE t1 (
 Period smallint(4) unsigned zerofill DEFAULT '0000' NOT NULL,
diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result
index b71884f8563..4785f790069 100644
--- a/mysql-test/r/type_datetime.result
+++ b/mysql-test/r/type_datetime.result
@@ -78,21 +78,6 @@ EXPLAIN SELECT * FROM t1 WHERE expedition='0001-00-00 00:00:00';
 table	type	possible_keys	key	key_len	ref	rows	Extra
 t1	ref	expedition	expedition	8	const	1	Using where
 drop table t1;
-CREATE TABLE `t1` (
-  `date` datetime NOT NULL default '0000-00-00 00:00:00',
-  `numfacture` int(6) unsigned NOT NULL default '0',
-  `expedition` datetime NOT NULL default '0000-00-00 00:00:00',
-  PRIMARY KEY  (`numfacture`),
-  KEY `date` (`date`),
-  KEY `expedition` (`expedition`)
-) TYPE=MyISAM;
-
-INSERT INTO t1 (expedition) VALUES ('0001-00-00 00:00:00');
-SELECT * FROM t1 WHERE expedition='0001-00-00 00:00:00';
-INSERT INTO t1 (numfacture,expedition) VALUES ('1212','0001-00-00 00:00:00');
-SELECT * FROM t1 WHERE expedition='0001-00-00 00:00:00';
-EXPLAIN SELECT * FROM t1 WHERE expedition='0001-00-00 00:00:00';
-drop table t1;
 create table t1 (a datetime not null, b datetime not null);
 insert into t1 values (now(), now());
 insert into t1 values (now(), now());
diff --git a/mysql-test/t/ctype_latin1_de.test b/mysql-test/t/ctype_latin1_de.test
index e829005a229..4b96f5f5867 100644
--- a/mysql-test/t/ctype_latin1_de.test
+++ b/mysql-test/t/ctype_latin1_de.test
@@ -2,13 +2,13 @@
 # Test latin_de character set
 #
 drop table if exists t1;
-create table t1 (a char (20) not null, b int not null auto_increment, index (a,b),index(b));
+create table t1 (a char (20) not null, b int not null auto_increment, index (a,b));
 insert into t1 (a) values ('ä'),('ac'),('ae'),('ad'),('Äc'),('aeb');
 insert into t1 (a) values ('üc'),('uc'),('ue'),('ud'),('Ü'),('ueb'),('uf');
 insert into t1 (a) values ('ö'),('oc'),('Öa'),('oe'),('od'),('Öc'),('oeb');
 insert into t1 (a) values ('s'),('ss'),('ß'),('ßb'),('ssa'),('ssc'),('ßa');
 insert into t1 (a) values ('eä'),('uü'),('öo'),('ää'),('ääa'),('aeae');
-insert into t1 (a) values ('q'),('a'),('u'),('o'),('é'),('É');
+insert into t1 (a) values ('q'),('a'),('u'),('o'),('é'),('É'),('a');
 select a,b from t1 order by a,b;
 select a,b from t1 order by upper(a),b;
 select a from t1 order by a desc;
diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test
index d9b75fca362..89cc2a57b30 100644
--- a/mysql-test/t/select.test
+++ b/mysql-test/t/select.test
@@ -6,8 +6,6 @@
 # Simple select test
 #
 
-use test;
-
 drop table if exists t1,t2,t3,t4;
 
 CREATE TABLE t1 (
diff --git a/sql/ha_myisam.cc b/sql/ha_myisam.cc
index 6e055f57c83..749a3eba5e4 100644
--- a/sql/ha_myisam.cc
+++ b/sql/ha_myisam.cc
@@ -1021,7 +1021,7 @@ int ha_myisam::create(const char *name, register TABLE *table_arg,
 {
   int error;
   uint i,j,recpos,minpos,fieldpos,temp_length,length;
-  bool found_auto_increment=0, found_real_auto_increment=0;
+  bool found_real_auto_increment=0;
   enum ha_base_keytype type;
   char buff[FN_REFLEN];
   KEY *pos;
@@ -1091,12 +1091,6 @@ int ha_myisam::create(const char *name, register TABLE *table_arg,
 	keydef[i].seg[j].null_bit=0;
 	keydef[i].seg[j].null_pos=0;
       }
-      if (field->flags & AUTO_INCREMENT_FLAG && !found_auto_increment)
-      {
-	keydef[i].flag|=HA_AUTO_KEY;
-	found_auto_increment=1;
-        found_real_auto_increment=(j==0);
-      }
       if (field->type() == FIELD_TYPE_BLOB)
       {
 	keydef[i].seg[j].flag|=HA_BLOB_PART;
@@ -1108,6 +1102,12 @@ int ha_myisam::create(const char *name, register TABLE *table_arg,
     keyseg+=pos->key_parts;
   }
 
+  if (table_arg->found_next_number_field)
+  {
+    keydef[table_arg->next_number_index].flag|= HA_AUTO_KEY;
+    found_real_auto_increment= table_arg->next_number_key_offset == 0;
+  }
+
   recpos=0; recinfo_pos=recinfo;
   while (recpos < (uint) table_arg->reclength)
   {