summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rwxr-xr-xBUILD/compile-pentium-valgrind-max2
-rw-r--r--acinclude.m44
-rw-r--r--client/mysqltest.c98
-rw-r--r--mysql-test/r/auto_increment.result7
-rw-r--r--mysql-test/r/distinct.result6
-rw-r--r--mysql-test/r/merge.result48
-rw-r--r--mysql-test/r/null_key.result110
-rw-r--r--mysql-test/r/subselect.result2
-rw-r--r--mysql-test/t/auto_increment.test4
-rw-r--r--mysql-test/t/innodb.test4
-rw-r--r--mysql-test/t/merge.test29
-rw-r--r--mysql-test/t/null_key.test35
-rw-r--r--mysys/mf_loadpath.c6
-rw-r--r--mysys/my_symlink.c12
-rw-r--r--sql/ha_myisammrg.cc91
-rw-r--r--sql/handler.cc2
-rw-r--r--sql/mysql_priv.h2
-rw-r--r--sql/protocol.cc2
-rw-r--r--sql/sql_class.h19
-rw-r--r--sql/sql_parse.cc6
-rw-r--r--sql/sql_select.cc601
-rw-r--r--sql/sql_select.h7
-rw-r--r--sql/sql_show.cc52
-rw-r--r--sql/sql_yacc.yy1
-rw-r--r--sql/table.cc4
-rw-r--r--sql/unireg.cc4
26 files changed, 779 insertions, 379 deletions
diff --git a/BUILD/compile-pentium-valgrind-max b/BUILD/compile-pentium-valgrind-max
index c6d8e4f21e8..20876d67472 100755
--- a/BUILD/compile-pentium-valgrind-max
+++ b/BUILD/compile-pentium-valgrind-max
@@ -3,7 +3,7 @@
path=`dirname $0`
. "$path/SETUP.sh"
-extra_flags="$pentium_cflags $debug_cflags -USAFEMALLOC -DHAVE_purify"
+extra_flags="$pentium_cflags $debug_cflags -USAFEMALLOC -UFORCE_INIT_OF_VARS -DHAVE_purify"
c_warnings="$c_warnings $debug_extra_warnings"
cxx_warnings="$cxx_warnings $debug_extra_warnings"
extra_configs="$pentium_configs $debug_configs"
diff --git a/acinclude.m4 b/acinclude.m4
index 5a48e9840d2..94e70f2b892 100644
--- a/acinclude.m4
+++ b/acinclude.m4
@@ -868,9 +868,9 @@ AC_SUBST(orbit_idl)
AC_DEFUN([MYSQL_CHECK_ISAM], [
AC_ARG_WITH([isam], [
- --without-isam Disable the ISAM table type],
+ --with-isam Enable the ISAM table type],
[with_isam="$withval"],
- [with_isam=yes])
+ [with_isam=no])
isam_libs=
if test X"$with_isam" = X"yes"
diff --git a/client/mysqltest.c b/client/mysqltest.c
index 03a8206276f..18dd7a26f2d 100644
--- a/client/mysqltest.c
+++ b/client/mysqltest.c
@@ -42,7 +42,7 @@
**********************************************************************/
-#define MTEST_VERSION "1.28"
+#define MTEST_VERSION "1.29"
#include <my_global.h>
#include <mysql_embed.h>
@@ -63,9 +63,10 @@
#include <errno.h>
#include <violite.h>
-#define MAX_QUERY 65536
+#define MAX_QUERY 65536
+#define MAX_COLUMNS 256
#define PAD_SIZE 128
-#define MAX_CONS 1024
+#define MAX_CONS 128
#define MAX_INCLUDE_DEPTH 16
#define LAZY_GUESS_BUF_SIZE 8192
#define INIT_Q_LINES 1024
@@ -192,7 +193,7 @@ Q_SYNC_WITH_MASTER,
Q_SYNC_SLAVE_WITH_MASTER,
Q_ERROR,
Q_SEND, Q_REAP,
-Q_DIRTY_CLOSE, Q_REPLACE,
+Q_DIRTY_CLOSE, Q_REPLACE, Q_REPLACE_COLUMN,
Q_PING, Q_EVAL,
Q_RPL_PROBE, Q_ENABLE_RPL_PARSE,
Q_DISABLE_RPL_PARSE, Q_EVAL_RESULT,
@@ -246,6 +247,7 @@ const char *command_names[]=
"reap",
"dirty_close",
"replace_result",
+ "replace_column",
"ping",
"eval",
"rpl_probe",
@@ -290,7 +292,7 @@ VAR* var_get(const char *var_name, const char** var_name_end, my_bool raw,
int eval_expr(VAR* v, const char *p, const char** p_end);
static int read_server_arguments(const char *name);
-/* Definitions for replace */
+/* Definitions for replace result */
typedef struct st_pointer_array { /* when using array-strings */
TYPELIB typelib; /* Pointer to strings */
@@ -318,6 +320,13 @@ static char *out_buff;
static uint out_length;
static int eval_result = 0;
+/* For column replace */
+char *replace_column[MAX_COLUMNS];
+uint max_replace_column= 0;
+
+static void get_replace_column(struct st_query *q);
+static void free_replace_column();
+
/* Disable functions that only exist in MySQL 4.0 */
#if MYSQL_VERSION_ID < 40000 || defined(EMBEDDED_LIBRARY)
void mysql_enable_rpl_parse(MYSQL* mysql __attribute__((unused))) {}
@@ -338,7 +347,6 @@ static const char *embedded_server_groups[] = {
NullS
};
-
static void do_eval(DYNAMIC_STRING* query_eval, const char* query)
{
const char* p;
@@ -433,6 +441,7 @@ static void free_used_memory()
delete_dynamic(&q_lines);
dynstr_free(&ds_res);
free_replace();
+ free_replace_column();
my_free(pass,MYF(MY_ALLOW_ZERO_PTR));
free_defaults(default_argv);
mysql_server_end();
@@ -2048,27 +2057,35 @@ static void replace_dynstr_append_mem(DYNAMIC_STRING *ds, const char *val,
dynstr_append_mem(ds, val, len);
}
+
/*
Append all results to the dynamic string separated with '\t'
+ Values may be converted with 'replace_column'
*/
static void append_result(DYNAMIC_STRING *ds, MYSQL_RES *res)
{
MYSQL_ROW row;
- int num_fields= mysql_num_fields(res);
+ uint num_fields= mysql_num_fields(res);
unsigned long *lengths;
while ((row = mysql_fetch_row(res)))
{
- int i;
+ uint i;
lengths = mysql_fetch_lengths(res);
for (i = 0; i < num_fields; i++)
{
const char *val= row[i];
ulonglong len= lengths[i];
+
+ if (i < max_replace_column && replace_column[i])
+ {
+ val= replace_column[i];
+ len= strlen(val);
+ }
if (!val)
{
- val = "NULL";
- len = 4;
+ val= "NULL";
+ len= 4;
}
if (i)
dynstr_append_mem(ds, "\t", 1);
@@ -2076,6 +2093,7 @@ static void append_result(DYNAMIC_STRING *ds, MYSQL_RES *res)
}
dynstr_append_mem(ds, "\n", 1);
}
+ free_replace_column();
}
@@ -2539,6 +2557,9 @@ int main(int argc, char **argv)
case Q_REPLACE:
get_replace(q);
break;
+ case Q_REPLACE_COLUMN:
+ get_replace_column(q);
+ break;
case Q_SAVE_MASTER_POS: do_save_master_pos(); break;
case Q_SYNC_WITH_MASTER: do_sync_with_master(q); break;
case Q_SYNC_SLAVE_WITH_MASTER:
@@ -3357,3 +3378,60 @@ static void free_replace_buffer(void)
{
my_free(out_buff,MYF(MY_WME));
}
+
+
+/****************************************************************************
+ Replace results for a column
+*****************************************************************************/
+
+static void free_replace_column()
+{
+ uint i;
+ for (i=0 ; i < max_replace_column ; i++)
+ {
+ if (replace_column[i])
+ {
+ my_free(replace_column[i], 0);
+ replace_column[i]= 0;
+ }
+ }
+ max_replace_column= 0;
+}
+
+/*
+ Get arguments for replace_columns. The syntax is:
+ replace-column column_number to_string [column_number to_string ...]
+ Where each argument may be quoted with ' or "
+ A argument may also be a variable, in which case the value of the
+ variable is replaced.
+*/
+
+static void get_replace_column(struct st_query *q)
+{
+ char *from=q->first_argument;
+ char *buff,*start;
+ DBUG_ENTER("get_replace_columns");
+
+ free_replace_column();
+ if (!*from)
+ die("Missing argument in %s\n", q->query);
+
+ /* Allocate a buffer for results */
+ start=buff=my_malloc(strlen(from)+1,MYF(MY_WME | MY_FAE));
+ while (*from)
+ {
+ char *to;
+ uint column_number;
+
+ to= get_string(&buff, &from, q);
+ if (!(column_number= atoi(to)) || column_number > MAX_COLUMNS)
+ die("Wrong column number to replace_columns in %s\n", q->query);
+ if (!*from)
+ die("Wrong number of arguments to replace in %s\n", q->query);
+ to= get_string(&buff, &from, q);
+ my_free(replace_column[column_number-1], MY_ALLOW_ZERO_PTR);
+ replace_column[column_number-1]= my_strdup(to, MYF(MY_WME | MY_FAE));
+ set_if_bigger(max_replace_column, column_number);
+ }
+ my_free(start, MYF(0));
+}
diff --git a/mysql-test/r/auto_increment.result b/mysql-test/r/auto_increment.result
index 5553f718799..19f8ffa84d4 100644
--- a/mysql-test/r/auto_increment.result
+++ b/mysql-test/r/auto_increment.result
@@ -111,11 +111,16 @@ insert into t1 set i = null;
select last_insert_id();
last_insert_id()
255
+insert into t1 set i = 254;
+ERROR 23000: Duplicate entry '254' for key 1
+select last_insert_id();
+last_insert_id()
+255
insert into t1 set i = null;
ERROR 23000: Duplicate entry '255' for key 1
select last_insert_id();
last_insert_id()
-255
+0
drop table t1;
create table t1 (i tinyint unsigned not null auto_increment, key (i));
insert into t1 set i = 254;
diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result
index 9ebcd1fb915..c5841f28830 100644
--- a/mysql-test/r/distinct.result
+++ b/mysql-test/r/distinct.result
@@ -173,9 +173,9 @@ INSERT INTO t2 values (1),(2),(3);
INSERT INTO t3 VALUES (1,'1'),(2,'2'),(1,'1'),(2,'2');
explain SELECT distinct t3.a FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t3 index a a 5 NULL 6 Using index; Using temporary
-1 SIMPLE t2 index a a 4 NULL 5 Using index; Distinct
-1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1 Using where; Distinct
+1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 2 Using temporary
+1 SIMPLE t2 ref a a 4 test.t1.a 2 Using index
+1 SIMPLE t3 ref a a 5 test.t1.b 2 Using where; Using index
SELECT distinct t3.a FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a;
a
1
diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result
index 323931ae3eb..413277fee43 100644
--- a/mysql-test/r/merge.result
+++ b/mysql-test/r/merge.result
@@ -1,4 +1,5 @@
drop table if exists t1,t2,t3,t4,t5,t6;
+drop database if exists mysqltest;
create table t1 (a int not null primary key auto_increment, message char(20));
create table t2 (a int not null primary key auto_increment, message char(20));
INSERT INTO t1 (message) VALUES ("Testing"),("table"),("t1");
@@ -174,15 +175,26 @@ t3 CREATE TABLE `t3` (
`a` int(11) NOT NULL default '0',
`b` char(20) default NULL,
KEY `a` (`a`)
-) TYPE=MRG_MyISAM CHARSET=latin1 UNION=(t1,t2)
+) TYPE=MRG_MyISAM CHARSET=latin1 UNION=(`t1`,`t2`)
create table t4 (a int not null, b char(10), key(a)) type=MERGE UNION=(t1,t2);
select * from t4;
ERROR HY000: Can't open file: 't4.MRG'. (errno: 143)
-create table t5 (a int not null, b char(10), key(a)) type=MERGE UNION=(test.t1,test_2.t2);
-ERROR HY000: Incorrect table definition; All MERGE tables must be in the same database
-drop table if exists t5,t4,t3,t1,t2;
-Warnings:
-Note 1051 Unknown table 't5'
+alter table t4 add column c int;
+ERROR HY000: Can't open file: 't4.MRG'. (errno: 143)
+create database mysqltest;
+create table mysqltest.t6 (a int not null primary key auto_increment, message char(20));
+create table t5 (a int not null, b char(20), key(a)) type=MERGE UNION=(test.t1,mysqltest.t6);
+show create table t5;
+Table Create Table
+t5 CREATE TABLE `t5` (
+ `a` int(11) NOT NULL default '0',
+ `b` char(20) default NULL,
+ KEY `a` (`a`)
+) TYPE=MRG_MyISAM CHARSET=latin1 UNION=(`t1`,`mysqltest`.`t6`)
+alter table t5 type=myisam;
+drop table t5, mysqltest.t6;
+drop database mysqltest;
+drop table t4,t3,t1,t2;
create table t1 (c char(10)) type=myisam;
create table t2 (c char(10)) type=myisam;
create table t3 (c char(10)) union=(t1,t2) type=merge;
@@ -251,14 +263,14 @@ t3 CREATE TABLE `t3` (
`incr` int(11) NOT NULL default '0',
`othr` int(11) NOT NULL default '0',
PRIMARY KEY (`incr`)
-) TYPE=MRG_MyISAM CHARSET=latin1 UNION=(t1,t2)
+) TYPE=MRG_MyISAM CHARSET=latin1 UNION=(`t1`,`t2`)
alter table t3 drop primary key;
show create table t3;
Table Create Table
t3 CREATE TABLE `t3` (
`incr` int(11) NOT NULL default '0',
`othr` int(11) NOT NULL default '0'
-) TYPE=MRG_MyISAM CHARSET=latin1 UNION=(t1,t2)
+) TYPE=MRG_MyISAM CHARSET=latin1 UNION=(`t1`,`t2`)
drop table t3,t2,t1;
create table t1 (a int not null, key(a)) type=merge;
select * from t1;
@@ -294,21 +306,21 @@ t4 CREATE TABLE `t4` (
`a` int(11) NOT NULL default '0',
`b` int(11) NOT NULL default '0',
KEY `a` (`a`,`b`)
-) TYPE=MRG_MyISAM CHARSET=latin1 UNION=(t1,t2)
+) TYPE=MRG_MyISAM CHARSET=latin1 UNION=(`t1`,`t2`)
show create table t5;
Table Create Table
t5 CREATE TABLE `t5` (
`a` int(11) NOT NULL default '0',
`b` int(11) NOT NULL auto_increment,
PRIMARY KEY (`a`,`b`)
-) TYPE=MRG_MyISAM CHARSET=latin1 INSERT_METHOD=FIRST UNION=(t1,t2)
+) TYPE=MRG_MyISAM CHARSET=latin1 INSERT_METHOD=FIRST UNION=(`t1`,`t2`)
show create table t6;
Table Create Table
t6 CREATE TABLE `t6` (
`a` int(11) NOT NULL default '0',
`b` int(11) NOT NULL auto_increment,
PRIMARY KEY (`a`,`b`)
-) TYPE=MRG_MyISAM CHARSET=latin1 INSERT_METHOD=LAST UNION=(t1,t2)
+) TYPE=MRG_MyISAM CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`)
insert into t1 values (1,NULL),(1,NULL),(1,NULL),(1,NULL);
insert into t2 values (2,NULL),(2,NULL),(2,NULL),(2,NULL);
select * from t3 order by b,a limit 3;
@@ -373,7 +385,7 @@ t4 CREATE TABLE `t4` (
`a` int(11) NOT NULL default '0',
`b` int(11) NOT NULL default '0',
KEY `a` (`a`,`b`)
-) TYPE=MRG_MyISAM CHARSET=latin1 UNION=(t1,t2,t3)
+) TYPE=MRG_MyISAM CHARSET=latin1 UNION=(`t1`,`t2`,`t3`)
select * from t4 order by a,b;
a b
1 1
@@ -399,7 +411,7 @@ t4 CREATE TABLE `t4` (
`a` int(11) NOT NULL default '0',
`b` int(11) NOT NULL default '0',
KEY `a` (`a`,`b`)
-) TYPE=MRG_MyISAM CHARSET=latin1 INSERT_METHOD=FIRST UNION=(t1,t2,t3)
+) TYPE=MRG_MyISAM CHARSET=latin1 INSERT_METHOD=FIRST UNION=(`t1`,`t2`,`t3`)
insert into t4 values (4,1),(4,2);
select * from t1 order by a,b;
a b
@@ -528,7 +540,11 @@ a b
6 1
6 2
6 3
-drop table if exists t6, t5, t4, t3, t2, t1;
+insert into t1 values (99,NULL);
+select * from t4 where a+0 > 90;
+a b
+99 1
+drop table t6, t5, t4, t3, t2, t1;
CREATE TABLE t1 ( a int(11) NOT NULL default '0', b int(11) NOT NULL default '0', PRIMARY KEY (a,b)) TYPE=MyISAM;
INSERT INTO t1 VALUES (1,1), (2,1);
CREATE TABLE t2 ( a int(11) NOT NULL default '0', b int(11) NOT NULL default '0', PRIMARY KEY (a,b)) TYPE=MyISAM;
@@ -540,7 +556,7 @@ max(b)
select max(b) from t1 where a = 2;
max(b)
1
-drop table if exists t3,t1,t2;
+drop table t3,t1,t2;
create table t1 (a int not null);
create table t2 (a int not null);
insert into t1 values (1);
@@ -559,7 +575,7 @@ select * from t6;
a
1
2
-drop table if exists t6, t3, t1, t2, t4, t5;
+drop table t6, t3, t1, t2, t4, t5;
CREATE TABLE t1 (
fileset_id tinyint(3) unsigned NOT NULL default '0',
file_code varchar(32) NOT NULL default '',
diff --git a/mysql-test/r/null_key.result b/mysql-test/r/null_key.result
index 289290ba08c..7dc0b4bfdd3 100644
--- a/mysql-test/r/null_key.result
+++ b/mysql-test/r/null_key.result
@@ -21,7 +21,10 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range a,b a 9 NULL 3 Using where; Using index
explain select * from t1 where (a is null or a = 7) and b=7;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref a,b b 4 const 2 Using where
+1 SIMPLE t1 ref_or_null a,b a 9 const,const 2 Using where; Using index
+explain select * from t1 where (a is null or a = 7) and b=7 order by a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref_or_null a,b a 9 const,const 2 Using where; Using index; Using filesort
explain select * from t1 where (a is null and b>a) or a is null and b=7 limit 2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref a,b a 5 const 3 Using where; Using index
@@ -56,13 +59,15 @@ NULL 9
NULL 9
select * from t1 where (a is null or a = 7) and b=7;
a b
-NULL 7
7 7
+NULL 7
select * from t1 where a is null and b=9 or a is null and b=7 limit 3;
a b
NULL 7
NULL 9
NULL 9
+create table t2 like t1;
+insert into t2 select * from t1;
alter table t1 modify b blob not null, add c int not null, drop key a, add unique key (a,b(20),c), drop key b, add key (b(10));
explain select * from t1 where a is null and b = 2;
id select_type table type possible_keys key key_len ref rows Extra
@@ -84,7 +89,7 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range a,b a 5 NULL 5 Using where
explain select * from t1 where (a is null or a = 7) and b=7 and c=0;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL a,b NULL NULL NULL 12 Using where
+1 SIMPLE t1 ref_or_null a,b a 5 const 4 Using where
explain select * from t1 where (a is null and b>a) or a is null and b=7 limit 2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref a,b a 5 const 3 Using where
@@ -125,8 +130,8 @@ NULL 9 0
NULL 9 0
select * from t1 where (a is null or a = 7) and b=7 and c=0;
a b c
-NULL 7 0
7 7 0
+NULL 7 0
select * from t1 where a is null and b=9 or a is null and b=7 limit 3;
a b c
NULL 7 0
@@ -136,6 +141,103 @@ select * from t1 where b like "6%";
a b c
6 6 0
drop table t1;
+rename table t2 to t1;
+alter table t1 modify b int null;
+insert into t1 values (7,null), (8,null), (8,7);
+explain select * from t1 where a = 7 and (b=7 or b is null);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref_or_null a,b a 10 const,const 2 Using where; Using index
+select * from t1 where a = 7 and (b=7 or b is null);
+a b
+7 7
+7 NULL
+explain select * from t1 where (a = 7 or a is null) and (b=7 or b is null);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range a,b a 10 NULL 4 Using where; Using index
+select * from t1 where (a = 7 or a is null) and (b=7 or b is null);
+a b
+NULL 7
+7 NULL
+7 7
+explain select * from t1 where (a = 7 or a is null) and (a = 7 or a is null);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref_or_null a a 5 const 5 Using where; Using index
+select * from t1 where (a = 7 or a is null) and (a = 7 or a is null);
+a b
+7 NULL
+7 7
+NULL 7
+NULL 9
+NULL 9
+create table t2 (a int);
+insert into t2 values (7),(8);
+explain select * from t2 straight_join t1 where t1.a=t2.a and b is null;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2
+1 SIMPLE t1 ref a,b a 10 test.t2.a,const 2 Using where; Using index
+drop index b on t1;
+explain select * from t2,t1 where t1.a=t2.a and b is null;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2
+1 SIMPLE t1 ref a a 10 test.t2.a,const 2 Using where; Using index
+select * from t2,t1 where t1.a=t2.a and b is null;
+a a b
+7 7 NULL
+8 8 NULL
+explain select * from t2,t1 where t1.a=t2.a and (b= 7 or b is null);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2
+1 SIMPLE t1 ref_or_null a a 10 test.t2.a,const 4 Using where; Using index
+select * from t2,t1 where t1.a=t2.a and (b= 7 or b is null);
+a a b
+7 7 7
+7 7 NULL
+8 8 7
+8 8 NULL
+explain select * from t2,t1 where (t1.a=t2.a or t1.a is null) and b= 7;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2
+1 SIMPLE t1 ref_or_null a a 10 test.t2.a,const 4 Using where; Using index
+select * from t2,t1 where (t1.a=t2.a or t1.a is null) and b= 7;
+a a b
+7 7 7
+7 NULL 7
+8 8 7
+8 NULL 7
+explain select * from t2,t1 where (t1.a=t2.a or t1.a is null) and (b= 7 or b is null);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2
+1 SIMPLE t1 ref_or_null a a 5 test.t2.a 4 Using where; Using index
+select * from t2,t1 where (t1.a=t2.a or t1.a is null) and (b= 7 or b is null);
+a a b
+7 7 NULL
+7 7 7
+7 NULL 7
+8 8 NULL
+8 8 7
+8 NULL 7
+insert into t2 values (null),(6);
+delete from t1 where a=8;
+explain select * from t2,t1 where t1.a=t2.a or t1.a is null;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 4
+1 SIMPLE t1 ref_or_null a a 5 test.t2.a 4 Using where; Using index
+explain select * from t2,t1 where t1.a<=>t2.a or (t1.a is null and t1.b <> 9);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 4
+1 SIMPLE t1 ref_or_null a a 5 test.t2.a 4 Using where; Using index
+select * from t2,t1 where t1.a<=>t2.a or (t1.a is null and t1.b <> 9);
+a a b
+7 7 NULL
+7 7 7
+7 NULL 7
+8 NULL 7
+NULL NULL 7
+NULL NULL 9
+NULL NULL 9
+6 6 6
+6 NULL 7
+drop table t1,t2;
CREATE TABLE t1 (
id int(10) unsigned NOT NULL auto_increment,
uniq_id int(10) unsigned default NULL,
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index ee24ef445fb..444aa064522 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -829,7 +829,7 @@ a t1.a in (select t2.a from t2)
explain SELECT t1.a, t1.a in (select t2.a from t2) FROM t1;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 Using index
-2 DEPENDENT SUBQUERY t2 index a a 5 NULL 3 Using where; Using index
+2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 const 2 Using where; Using index
drop table t1,t2;
create table t1 (a float);
select 10.5 IN (SELECT * from t1 LIMIT 1);
diff --git a/mysql-test/t/auto_increment.test b/mysql-test/t/auto_increment.test
index 63fdfded6d0..189320a8dcb 100644
--- a/mysql-test/t/auto_increment.test
+++ b/mysql-test/t/auto_increment.test
@@ -80,6 +80,9 @@ insert into t1 set i = 254;
insert into t1 set i = null;
select last_insert_id();
--error 1062
+insert into t1 set i = 254;
+select last_insert_id();
+--error 1062
insert into t1 set i = null;
select last_insert_id();
drop table t1;
@@ -100,5 +103,6 @@ select last_insert_id();
--error 1062
insert into t1 values (NULL, 10);
select last_insert_id();
+
drop table t1;
diff --git a/mysql-test/t/innodb.test b/mysql-test/t/innodb.test
index 3736f4a2ddc..17df79a69fa 100644
--- a/mysql-test/t/innodb.test
+++ b/mysql-test/t/innodb.test
@@ -22,7 +22,7 @@ drop table t1;
#
# A bit bigger test
-# The 'replace_result' statements are needed because the cardinality calculated
+# The 'replace_column' statements are needed because the cardinality calculated
# by innodb is not always the same between runs
#
@@ -51,7 +51,7 @@ select * from t1 where parent_id=102;
select level,id from t1 where level=1;
select level,id,parent_id from t1 where level=1;
optimize table t1;
---replace_result 87 # 50 # 48 # 43 # 25 # 24 # 6 # 3 #
+--replace_column 7 #
show keys from t1;
drop table t1;
diff --git a/mysql-test/t/merge.test b/mysql-test/t/merge.test
index 7a7678afca1..01ba8986474 100644
--- a/mysql-test/t/merge.test
+++ b/mysql-test/t/merge.test
@@ -4,6 +4,7 @@
--disable_warnings
drop table if exists t1,t2,t3,t4,t5,t6;
+drop database if exists mysqltest;
--enable_warnings
create table t1 (a int not null primary key auto_increment, message char(20));
@@ -48,13 +49,23 @@ show create table t3;
create table t4 (a int not null, b char(10), key(a)) type=MERGE UNION=(t1,t2);
--error 1016
select * from t4;
---error 1212
-create table t5 (a int not null, b char(10), key(a)) type=MERGE UNION=(test.t1,test_2.t2);
+--error 1016
+alter table t4 add column c int;
-# Because of windows, it's important that we drop the merge tables first!
-# This should give a warning on table t5
-drop table if exists t5,t4,t3,t1,t2;
+#
+# Test tables in different databases
+#
+create database mysqltest;
+create table mysqltest.t6 (a int not null primary key auto_increment, message char(20));
+create table t5 (a int not null, b char(20), key(a)) type=MERGE UNION=(test.t1,mysqltest.t6);
+show create table t5;
+alter table t5 type=myisam;
+drop table t5, mysqltest.t6;
+drop database mysqltest;
+# Because of windows, it's important that we drop the merge tables first!
+drop table t4,t3,t1,t2;
+
create table t1 (c char(10)) type=myisam;
create table t2 (c char(10)) type=myisam;
create table t3 (c char(10)) union=(t1,t2) type=merge;
@@ -177,7 +188,9 @@ select * from t1 order by a,b;
select * from t2 order by a,b;
select * from t5 order by a,b;
select * from t6 order by a,b;
-drop table if exists t6, t5, t4, t3, t2, t1;
+insert into t1 values (99,NULL);
+select * from t4 where a+0 > 90;
+drop table t6, t5, t4, t3, t2, t1;
CREATE TABLE t1 ( a int(11) NOT NULL default '0', b int(11) NOT NULL default '0', PRIMARY KEY (a,b)) TYPE=MyISAM;
INSERT INTO t1 VALUES (1,1), (2,1);
@@ -186,7 +199,7 @@ INSERT INTO t2 VALUES (1,2), (2,2);
CREATE TABLE t3 ( a int(11) NOT NULL default '0', b int(11) NOT NULL default '0', KEY a (a,b)) TYPE=MRG_MyISAM UNION=(t1,t2);
select max(b) from t3 where a = 2;
select max(b) from t1 where a = 2;
-drop table if exists t3,t1,t2;
+drop table t3,t1,t2;
#
# temporary merge tables
@@ -203,7 +216,7 @@ insert into t4 values (1);
insert into t5 values (2);
create temporary table t6 (a int not null) TYPE=MERGE UNION=(t4,t5);
select * from t6;
-drop table if exists t6, t3, t1, t2, t4, t5;
+drop table t6, t3, t1, t2, t4, t5;
#
# testing merge::records_in_range and optimizer
diff --git a/mysql-test/t/null_key.test b/mysql-test/t/null_key.test
index 18d0d368891..7d9500e90dd 100644
--- a/mysql-test/t/null_key.test
+++ b/mysql-test/t/null_key.test
@@ -14,6 +14,7 @@ explain select * from t1 where a=2 and b = 2;
explain select * from t1 where a<=>b limit 2;
explain select * from t1 where (a is null or a > 0 and a < 3) and b < 5 limit 3;
explain select * from t1 where (a is null or a = 7) and b=7;
+explain select * from t1 where (a is null or a = 7) and b=7 order by a;
explain select * from t1 where (a is null and b>a) or a is null and b=7 limit 2;
explain select * from t1 where a is null and b=9 or a is null and b=7 limit 3;
explain select * from t1 where a > 1 and a < 3 limit 1;
@@ -25,6 +26,8 @@ select * from t1 where (a is null or a > 0 and a < 3) and b < 5 limit 3;
select * from t1 where (a is null or a > 0 and a < 3) and b > 7 limit 3;
select * from t1 where (a is null or a = 7) and b=7;
select * from t1 where a is null and b=9 or a is null and b=7 limit 3;
+create table t2 like t1;
+insert into t2 select * from t1;
alter table t1 modify b blob not null, add c int not null, drop key a, add unique key (a,b(20),c), drop key b, add key (b(10));
explain select * from t1 where a is null and b = 2;
explain select * from t1 where a is null and b = 2 and c=0;
@@ -47,8 +50,38 @@ select * from t1 where (a is null or a > 0 and a < 3) and b > 7 limit 3;
select * from t1 where (a is null or a = 7) and b=7 and c=0;
select * from t1 where a is null and b=9 or a is null and b=7 limit 3;
select * from t1 where b like "6%";
-drop table t1;
+#
+# Test ref_or_null optimization
+#
+drop table t1;
+rename table t2 to t1;
+alter table t1 modify b int null;
+insert into t1 values (7,null), (8,null), (8,7);
+explain select * from t1 where a = 7 and (b=7 or b is null);
+select * from t1 where a = 7 and (b=7 or b is null);
+explain select * from t1 where (a = 7 or a is null) and (b=7 or b is null);
+select * from t1 where (a = 7 or a is null) and (b=7 or b is null);
+explain select * from t1 where (a = 7 or a is null) and (a = 7 or a is null);
+select * from t1 where (a = 7 or a is null) and (a = 7 or a is null);
+create table t2 (a int);
+insert into t2 values (7),(8);
+explain select * from t2 straight_join t1 where t1.a=t2.a and b is null;
+drop index b on t1;
+explain select * from t2,t1 where t1.a=t2.a and b is null;
+select * from t2,t1 where t1.a=t2.a and b is null;
+explain select * from t2,t1 where t1.a=t2.a and (b= 7 or b is null);
+select * from t2,t1 where t1.a=t2.a and (b= 7 or b is null);
+explain select * from t2,t1 where (t1.a=t2.a or t1.a is null) and b= 7;
+select * from t2,t1 where (t1.a=t2.a or t1.a is null) and b= 7;
+explain select * from t2,t1 where (t1.a=t2.a or t1.a is null) and (b= 7 or b is null);
+select * from t2,t1 where (t1.a=t2.a or t1.a is null) and (b= 7 or b is null);
+insert into t2 values (null),(6);
+delete from t1 where a=8;
+explain select * from t2,t1 where t1.a=t2.a or t1.a is null;
+explain select * from t2,t1 where t1.a<=>t2.a or (t1.a is null and t1.b <> 9);
+select * from t2,t1 where t1.a<=>t2.a or (t1.a is null and t1.b <> 9);
+drop table t1,t2;
#
# The following failed for Matt Loschert
diff --git a/mysys/mf_loadpath.c b/mysys/mf_loadpath.c
index 291ad62e297..2c90d9f90a6 100644
--- a/mysys/mf_loadpath.c
+++ b/mysys/mf_loadpath.c
@@ -39,10 +39,10 @@ my_string my_load_path(my_string to, const char *path,
(is_prefix((gptr) path,FN_PARENTDIR)) ||
! own_path_prefix)
{
- if (! my_getwd(buff,(uint) (FN_REFLEN-strlen(path)),MYF(0)))
- VOID(strcat(buff,path));
+ if (! my_getwd(buff,(uint) (FN_REFLEN+2-strlen(path)),MYF(0)))
+ VOID(strcat(buff,path+2));
else
- VOID(strmov(buff,path));
+ VOID(strmov(buff,path)); /* Return org file name */
}
else
VOID(strxmov(buff,own_path_prefix,path,NullS));
diff --git a/mysys/my_symlink.c b/mysys/my_symlink.c
index abef0096e28..b9468d42cfc 100644
--- a/mysys/my_symlink.c
+++ b/mysys/my_symlink.c
@@ -123,20 +123,22 @@ int my_realpath(char *to, const char *filename,
}
else
{
- /* Realpath didn't work; Use original name */
+ /*
+ Realpath didn't work; Use my_load_path() which is a poor substitute
+ original name but will at least be able to resolve paths that starts
+ with '.'.
+ */
DBUG_PRINT("error",("realpath failed with errno: %d", errno));
my_errno=errno;
if (MyFlags & MY_WME)
my_error(EE_REALPATH, MYF(0), filename, my_errno);
- if (to != filename)
- strmov(to,filename);
+ my_load_path(to, filename, NullS);
result= -1;
}
}
DBUG_RETURN(result);
#else
- if (to != filename)
- strmov(to,filename);
+ my_load_path(to, filename, NullS);
return 0;
#endif
}
diff --git a/sql/ha_myisammrg.cc b/sql/ha_myisammrg.cc
index 5f07bbc4140..a0449e83222 100644
--- a/sql/ha_myisammrg.cc
+++ b/sql/ha_myisammrg.cc
@@ -303,14 +303,40 @@ THR_LOCK_DATA **ha_myisammrg::store_lock(THD *thd,
return to;
}
+
+/* Find out database name and table name from a filename */
+
+static void split_file_name(const char *file_name,
+ LEX_STRING *db, LEX_STRING *name)
+{
+ uint name_length, dir_length, prefix_length;
+ char buff[FN_REFLEN];
+
+ db->length= 0;
+ name_length= (uint) (strmake(buff, file_name, sizeof(buff)-1) - buff);
+ dir_length= dirname_length(buff);
+ if (dir_length > 1)
+ {
+ /* Get database */
+ buff[dir_length-1]= 0; // Remove end '/'
+ prefix_length= dirname_length(buff);
+ db->str= (char*) file_name+ prefix_length;
+ db->length= dir_length - prefix_length -1;
+ }
+ name->str= (char*) file_name+ dir_length;
+ name->length= (uint) (fn_ext(name->str) - name->str);
+}
+
+
void ha_myisammrg::update_create_info(HA_CREATE_INFO *create_info)
{
- // [phi] auto_increment stuff is missing (but currently not needed)
DBUG_ENTER("ha_myisammrg::update_create_info");
+
if (!(create_info->used_fields & HA_CREATE_USED_UNION))
{
MYRG_TABLE *open_table;
THD *thd=current_thd;
+
create_info->merge_list.next= &create_info->merge_list.first;
create_info->merge_list.elements=0;
@@ -318,14 +344,17 @@ void ha_myisammrg::update_create_info(HA_CREATE_INFO *create_info)
open_table != file->end_table ;
open_table++)
{
- char *name=open_table->table->filename;
- char buff[FN_REFLEN];
TABLE_LIST *ptr;
+ LEX_STRING db, name;
+
if (!(ptr = (TABLE_LIST *) thd->calloc(sizeof(TABLE_LIST))))
goto err;
- fn_format(buff,name,"","",3);
- if (!(ptr->real_name=thd->strdup(buff)))
+ split_file_name(open_table->table->filename, &db, &name);
+ if (!(ptr->real_name= thd->strmake(name.str, name.length)))
+ goto err;
+ if (db.length && !(ptr->db= thd->strmake(db.str, db.length)))
goto err;
+
create_info->merge_list.elements++;
(*create_info->merge_list.next) = (byte*) ptr;
create_info->merge_list.next= (byte**) &ptr->next;
@@ -344,37 +373,34 @@ err:
DBUG_VOID_RETURN;
}
+
int ha_myisammrg::create(const char *name, register TABLE *form,
HA_CREATE_INFO *create_info)
{
char buff[FN_REFLEN],**table_names,**pos;
TABLE_LIST *tables= (TABLE_LIST*) create_info->merge_list.first;
+ THD *thd= current_thd;
DBUG_ENTER("ha_myisammrg::create");
- if (!(table_names= (char**) sql_alloc((create_info->merge_list.elements+1)*
- sizeof(char*))))
+ if (!(table_names= (char**) thd->alloc((create_info->merge_list.elements+1)*
+ sizeof(char*))))
DBUG_RETURN(1);
for (pos=table_names ; tables ; tables=tables->next)
{
char *table_name;
+ TABLE **tbl= 0;
if (create_info->options & HA_LEX_CREATE_TMP_TABLE)
+ tbl= find_temporary_table(thd, tables->db, tables->real_name);
+ if (!tbl)
{
- TABLE **tbl=find_temporary_table(current_thd,
- tables->db, tables->real_name);
- if (!tbl)
- {
- table_name=sql_alloc(1+
- my_snprintf(buff,FN_REFLEN,"%s/%s/%s",mysql_real_data_home,
- tables->db, tables->real_name));
- if (!table_name)
- DBUG_RETURN(1);
- strcpy(table_name, buff);
- }
- else
- table_name=(*tbl)->path;
+ uint length= my_snprintf(buff,FN_REFLEN,"%s%s/%s",
+ mysql_real_data_home,
+ tables->db, tables->real_name);
+ if (!(table_name= thd->strmake(buff, length)))
+ DBUG_RETURN(1);
}
else
- table_name=tables->real_name;
+ table_name=(*tbl)->path;
*pos++= table_name;
}
*pos=0;
@@ -384,9 +410,13 @@ int ha_myisammrg::create(const char *name, register TABLE *form,
(my_bool) 0));
}
+
void ha_myisammrg::append_create_info(String *packet)
{
- char buff[FN_REFLEN];
+ const char *current_db;
+ uint db_length;
+ THD *thd= current_thd;
+
if (file->merge_insert_method != MERGE_INSERT_DISABLED)
{
packet->append(" INSERT_METHOD=",15);
@@ -395,15 +425,26 @@ void ha_myisammrg::append_create_info(String *packet)
packet->append(" UNION=(",8);
MYRG_TABLE *open_table,*first;
+ current_db= table->table_cache_key;
+ db_length= strlen(current_db);
+
for (first=open_table=file->open_tables ;
open_table != file->end_table ;
open_table++)
{
- char *name= open_table->table->filename;
- fn_format(buff,name,"","",3);
+ LEX_STRING db, name;
+ split_file_name(open_table->table->filename, &db, &name);
if (open_table != first)
packet->append(',');
- packet->append(buff,(uint) strlen(buff));
+ /* Report database for mapped table if it isn't in current database */
+ if (db.length &&
+ (db_length != db.length ||
+ strncmp(current_db, db.str, db.length)))
+ {
+ append_identifier(thd, packet, db.str, db.length);
+ packet->append('.');
+ }
+ append_identifier(thd, packet, name.str, name.length);
}
packet->append(')');
}
diff --git a/sql/handler.cc b/sql/handler.cc
index 56319bcc91c..150a0d5329e 100644
--- a/sql/handler.cc
+++ b/sql/handler.cc
@@ -709,6 +709,8 @@ void handler::update_auto_increment()
nr=get_auto_increment();
if (!table->next_number_field->store(nr))
thd->insert_id((ulonglong) nr);
+ else
+ thd->insert_id(table->next_number_field->val_int());
auto_increment_column_changed=1;
DBUG_VOID_RETURN;
}
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index aca84f1bcb3..5ad2cc56b8c 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -532,6 +532,8 @@ int mysqld_show_fields(THD *thd,TABLE_LIST *table, const char *wild,
bool verbose);
int mysqld_show_keys(THD *thd, TABLE_LIST *table);
int mysqld_show_logs(THD *thd);
+void append_identifier(THD *thd, String *packet, const char *name,
+ uint length);
void mysqld_list_fields(THD *thd,TABLE_LIST *table, const char *wild);
int mysqld_dump_create_info(THD *thd, TABLE *table, int fd = -1);
int mysqld_show_create(THD *thd, TABLE_LIST *table_list);
diff --git a/sql/protocol.cc b/sql/protocol.cc
index 1d730836d6e..1a1d1f0a585 100644
--- a/sql/protocol.cc
+++ b/sql/protocol.cc
@@ -572,7 +572,7 @@ bool Protocol::send_fields(List<Item> *list, uint flag)
#endif
}
- send_eof(thd);
+ send_eof(thd, 1);
DBUG_RETURN(prepare_for_send(list));
err:
diff --git a/sql/sql_class.h b/sql/sql_class.h
index ccfe2555518..f6336cb7dd9 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -503,8 +503,23 @@ public:
#ifdef SIGNAL_WITH_VIO_CLOSE
Vio* active_vio;
#endif
- ulonglong next_insert_id,last_insert_id,current_insert_id,
- limit_found_rows;
+ /*
+ next_insert_id is set on SET INSERT_ID= #. This is used as the next
+ generated auto_increment value in handler.cc
+ */
+ ulonglong next_insert_id;
+ /*
+ The insert_id used for the last statement or set by SET LAST_INSERT_ID=#
+ or SELECT LAST_INSERT_ID(#). Used for binary log and returned by
+ LAST_INSERT_ID()
+ */
+ ulonglong last_insert_id;
+ /*
+ Set to the first value that LAST_INSERT_ID() returned for the last
+ statement. When this is set, last_insert_id_used is set to true.
+ */
+ ulonglong current_insert_id;
+ ulonglong limit_found_rows;
ha_rows select_limit, offset_limit, cuted_fields,
sent_row_count, examined_row_count;
table_map used_tables;
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 5070466007e..3428b9805a0 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -3388,11 +3388,6 @@ static bool check_merge_table_access(THD *thd, char *db,
{
if (!tmp->db || !tmp->db[0])
tmp->db=db;
- else if (strcmp(tmp->db,db))
- {
- send_error(thd,ER_UNION_TABLES_IN_DIFFERENT_DIR);
- return 1;
- }
}
error=check_table_access(thd, SELECT_ACL | UPDATE_ACL | DELETE_ACL,
table_list);
@@ -4425,6 +4420,7 @@ static bool append_file_to_dir(THD *thd, char **filename_ptr, char *table_name)
return 0;
}
+
/*
Check if the select is a simple select (not an union)
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 31bb8ffc032..881386c9031 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -31,8 +31,11 @@
#include <ft_global.h>
const char *join_type_str[]={ "UNKNOWN","system","const","eq_ref","ref",
- "MAYBE_REF","ALL","range","index","fulltext" };
+ "MAYBE_REF","ALL","range","index","fulltext",
+ "ref_or_null"
+};
+static void optimize_keyuse(JOIN *join, DYNAMIC_ARRAY *keyuse_array);
static bool make_join_statistics(JOIN *join,TABLE_LIST *tables,COND *conds,
DYNAMIC_ARRAY *keyuse);
static bool update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,
@@ -106,6 +109,8 @@ static int join_read_prev_same(READ_RECORD *info);
static int join_read_prev(READ_RECORD *info);
static int join_ft_read_first(JOIN_TAB *tab);
static int join_ft_read_next(READ_RECORD *info);
+static int join_read_always_key_or_null(JOIN_TAB *tab);
+static int join_read_next_same_or_null(READ_RECORD *info);
static COND *make_cond_for_table(COND *cond,table_map table,
table_map used_table);
static Item* part_of_refkey(TABLE *form,Field *field);
@@ -1456,8 +1461,9 @@ make_join_statistics(JOIN *join,TABLE_LIST *tables,COND *conds,
DYNAMIC_ARRAY *keyuse_array)
{
int error;
- uint i,table_count,const_count,found_ref,refs,key,const_ref,eq_part;
- table_map found_const_table_map,all_table_map;
+ uint i,table_count,const_count,key;
+ table_map found_const_table_map, all_table_map, found_ref, refs;
+ key_map const_ref, eq_part;
TABLE **table_vector;
JOIN_TAB *stat,*stat_end,*s,**stat_ref;
KEYUSE *keyuse,*start_keyuse;
@@ -1475,7 +1481,7 @@ make_join_statistics(JOIN *join,TABLE_LIST *tables,COND *conds,
join->best_ref=stat_vector;
stat_end=stat+table_count;
- found_const_table_map=all_table_map=0;
+ found_const_table_map= all_table_map=0;
const_count=0;
for (s=stat,i=0 ; tables ; s++,tables=tables->next,i++)
@@ -1632,16 +1638,17 @@ make_join_statistics(JOIN *join,TABLE_LIST *tables,COND *conds,
key=keyuse->key;
s->keys|= (key_map) 1 << key; // QQ: remove this ?
- refs=const_ref=eq_part=0;
+ refs=const_ref=0;
+ eq_part=0;
do
{
- if (keyuse->val->type() != Item::NULL_ITEM)
+ if (keyuse->val->type() != Item::NULL_ITEM && !keyuse->optimize)
{
if (!((~found_const_table_map) & keyuse->used_tables))
const_ref|= (key_map) 1 << keyuse->keypart;
else
refs|=keyuse->used_tables;
- eq_part|= (uint) 1 << keyuse->keypart;
+ eq_part|= (key_map) 1 << keyuse->keypart;
}
keyuse++;
} while (keyuse->table == table && keyuse->key == key);
@@ -1700,8 +1707,6 @@ make_join_statistics(JOIN *join,TABLE_LIST *tables,COND *conds,
if (s->worst_seeks < 2.0) // Fix for small tables
s->worst_seeks=2.0;
- /* if (s->type == JT_EQ_REF)
- continue; */
if (s->const_keys)
{
ha_rows records;
@@ -1752,7 +1757,10 @@ make_join_statistics(JOIN *join,TABLE_LIST *tables,COND *conds,
join->found_const_table_map=found_const_table_map;
if (join->const_tables != join->tables)
+ {
+ optimize_keyuse(join, keyuse_array);
find_best_combination(join,all_table_map & ~join->const_table_map);
+ }
else
{
memcpy((gptr) join->best_positions,(gptr) join->positions,
@@ -1774,13 +1782,26 @@ make_join_statistics(JOIN *join,TABLE_LIST *tables,COND *conds,
typedef struct key_field_t { // Used when finding key fields
Field *field;
Item *val; // May be empty if diff constant
- uint level,const_level; // QQ: Remove const_level
+ uint level;
+ uint optimize;
bool eq_func;
- bool exists_optimize;
} KEY_FIELD;
+/* Values in optimize */
+#define KEY_OPTIMIZE_EXISTS 1
+#define KEY_OPTIMIZE_REF_OR_NULL 2
+
+/*
+ Merge new key definitions to old ones, remove those not used in both
+
+ This is called for OR between different levels
-/* merge new key definitions to old ones, remove those not used in both */
+ To be able to do 'ref_or_null' we merge a comparison of a column
+ and 'column IS NULL' to one test. This is useful for sub select queries
+ that are internally transformed to something like:
+
+ SELECT * FROM t1 WHERE t1.key=outer_ref_field or t1.key IS NULL
+*/
static KEY_FIELD *
merge_key_fields(KEY_FIELD *start,KEY_FIELD *new_fields,KEY_FIELD *end,
@@ -1802,20 +1823,46 @@ merge_key_fields(KEY_FIELD *start,KEY_FIELD *new_fields,KEY_FIELD *end,
{
if (new_fields->val->used_tables())
{
+ /*
+ If the value matches, we can use the key reference.
+ If not, we keep it until we have examined all new values
+ */
if (old->val->eq(new_fields->val, old->field->binary()))
{
- old->level=old->const_level=and_level;
- old->exists_optimize&=new_fields->exists_optimize;
+ old->level= and_level;
+ old->optimize= ((old->optimize & new_fields->optimize &
+ KEY_OPTIMIZE_EXISTS) |
+ ((old->optimize | new_fields->optimize) &
+ KEY_OPTIMIZE_REF_OR_NULL));
}
}
- else if (old->val->eq(new_fields->val, old->field->binary()) &&
- old->eq_func && new_fields->eq_func)
+ else if (old->eq_func && new_fields->eq_func &&
+ old->val->eq(new_fields->val, old->field->binary()))
+
+ {
+ old->level= and_level;
+ old->optimize= ((old->optimize & new_fields->optimize &
+ KEY_OPTIMIZE_EXISTS) |
+ ((old->optimize | new_fields->optimize) &
+ KEY_OPTIMIZE_REF_OR_NULL));
+ }
+ else if (old->eq_func && new_fields->eq_func &&
+ (old->val->is_null() || new_fields->val->is_null()))
{
- old->level=old->const_level=and_level;
- old->exists_optimize&=new_fields->exists_optimize;
+ /* field = expression OR field IS NULL */
+ old->level= and_level;
+ old->optimize= KEY_OPTIMIZE_REF_OR_NULL;
+ /* Remember the NOT NULL value */
+ if (old->val->is_null())
+ old->val= new_fields->val;
}
- else // Impossible; remove it
+ else
{
+ /*
+ We are comparing two different const. In this case we can't
+ use a key-lookup on this so it's better to remove the value
+ and let the range optimzier handle it
+ */
if (old == --first_free) // If last item
break;
*old= *first_free; // Remove old value
@@ -1827,7 +1874,7 @@ merge_key_fields(KEY_FIELD *start,KEY_FIELD *new_fields,KEY_FIELD *end,
/* Remove all not used items */
for (KEY_FIELD *old=start ; old != first_free ;)
{
- if (old->level != and_level && old->const_level != and_level)
+ if (old->level != and_level)
{ // Not used in all levels
if (old == --first_free)
break;
@@ -1840,32 +1887,53 @@ merge_key_fields(KEY_FIELD *start,KEY_FIELD *new_fields,KEY_FIELD *end,
}
+/*
+ Add a possible key to array of possible keys if it's usable as a key
+
+ SYNPOSIS
+ add_key_field()
+ key_fields Pointer to add key, if usable
+ and_level And level, to be stored in KEY_FIELD
+ field Field used in comparision
+ eq_func True if we used =, <=> or IS NULL
+ value Value used for comparison with field
+ Is NULL for BETWEEN and IN
+ usable_tables Tables which can be used for key optimization
+
+ NOTES
+ If we are doing a NOT NULL comparison on a NOT NULL field in a outer join
+ table, we store this to be able to do not exists optimization later.
+
+ RETURN
+ *key_fields is incremented if we stored a key in the array
+*/
+
static void
add_key_field(KEY_FIELD **key_fields,uint and_level,
Field *field,bool eq_func,Item *value,
table_map usable_tables)
{
- bool exists_optimize=0;
+ uint exists_optimize= 0;
if (!(field->flags & PART_KEY_FLAG))
{
// Don't remove column IS NULL on a LEFT JOIN table
if (!eq_func || !value || value->type() != Item::NULL_ITEM ||
!field->table->maybe_null || field->null_ptr)
return; // Not a key. Skip it
- exists_optimize=1;
+ exists_optimize= KEY_OPTIMIZE_EXISTS;
}
else
{
table_map used_tables=0;
- if (value && (used_tables=value->used_tables()) &
- (field->table->map | RAND_TABLE_BIT))
+ if (value && ((used_tables=value->used_tables()) &
+ (field->table->map | RAND_TABLE_BIT)))
return;
if (!(usable_tables & field->table->map))
{
if (!eq_func || !value || value->type() != Item::NULL_ITEM ||
!field->table->maybe_null || field->null_ptr)
return; // Can't use left join optimize
- exists_optimize=1;
+ exists_optimize= KEY_OPTIMIZE_EXISTS;
}
else
{
@@ -1880,20 +1948,23 @@ add_key_field(KEY_FIELD **key_fields,uint and_level,
return; // Can't be used as eq key
}
- /* Save the following cases:
- Field op constant
- Field LIKE constant where constant doesn't start with a wildcard
- Field = field2 where field2 is in a different table
- Field op formula
- Field IS NULL
- Field IS NOT NULL
+ /*
+ Save the following cases:
+ Field op constant
+ Field LIKE constant where constant doesn't start with a wildcard
+ Field = field2 where field2 is in a different table
+ Field op formula
+ Field IS NULL
+ Field IS NOT NULL
*/
stat[0].key_dependent|=used_tables;
if (value->const_item())
stat[0].const_keys |= possible_keys;
- /* We can't always use indexes when comparing a string index to a
- number. cmp_type() is checked to allow compare of dates to numbers */
+ /*
+ We can't always use indexes when comparing a string index to a
+ number. cmp_type() is checked to allow compare of dates to numbers
+ */
if (!eq_func ||
field->result_type() == STRING_RESULT &&
value->result_type() != STRING_RESULT &&
@@ -1902,11 +1973,11 @@ add_key_field(KEY_FIELD **key_fields,uint and_level,
}
}
/* Store possible eq field */
- (*key_fields)->field=field;
- (*key_fields)->eq_func=eq_func;
- (*key_fields)->val=value;
- (*key_fields)->level=(*key_fields)->const_level=and_level;
- (*key_fields)->exists_optimize=exists_optimize;
+ (*key_fields)->field= field;
+ (*key_fields)->eq_func= eq_func;
+ (*key_fields)->val= value;
+ (*key_fields)->level= and_level;
+ (*key_fields)->optimize= exists_optimize;
(*key_fields)++;
}
@@ -1926,12 +1997,7 @@ add_key_fields(JOIN_TAB *stat,KEY_FIELD **key_fields,uint *and_level,
while ((item=li++))
add_key_fields(stat,key_fields,and_level,item,usable_tables);
for (; org_key_fields != *key_fields ; org_key_fields++)
- {
- if (org_key_fields->const_level == org_key_fields->level)
- org_key_fields->const_level=org_key_fields->level= *and_level;
- else
- org_key_fields->const_level= *and_level;
- }
+ org_key_fields->level= *and_level;
}
else
{
@@ -2036,7 +2102,7 @@ add_key_part(DYNAMIC_ARRAY *keyuse_array,KEY_FIELD *key_field)
TABLE *form= field->table;
KEYUSE keyuse;
- if (key_field->eq_func && !key_field->exists_optimize)
+ if (key_field->eq_func && !(key_field->optimize & KEY_OPTIMIZE_EXISTS))
{
for (uint key=0 ; key < form->keys ; key++)
{
@@ -2054,7 +2120,9 @@ add_key_part(DYNAMIC_ARRAY *keyuse_array,KEY_FIELD *key_field)
keyuse.val = key_field->val;
keyuse.key = key;
keyuse.keypart=part;
+ keyuse.keypart_map= (key_part_map) 1 << part;
keyuse.used_tables=key_field->val->used_tables();
+ keyuse.optimize= key_field->optimize & KEY_OPTIMIZE_REF_OR_NULL;
VOID(insert_dynamic(keyuse_array,(gptr) &keyuse));
}
}
@@ -2138,16 +2206,23 @@ add_ft_keys(DYNAMIC_ARRAY *keyuse_array,
VOID(insert_dynamic(keyuse_array,(gptr) &keyuse));
}
+
static int
sort_keyuse(KEYUSE *a,KEYUSE *b)
{
+ int res;
if (a->table->tablenr != b->table->tablenr)
return (int) (a->table->tablenr - b->table->tablenr);
if (a->key != b->key)
return (int) (a->key - b->key);
if (a->keypart != b->keypart)
return (int) (a->keypart - b->keypart);
- return test(a->used_tables) - test(b->used_tables); // Place const first
+ // Place const values before other ones
+ if ((res= test(a->used_tables) - test(b->used_tables)))
+ return res;
+ /* Place rows that are not 'OPTIMIZE_REF_OR_NULL' first */
+ return (int) ((a->optimize & KEY_OPTIMIZE_REF_OR_NULL) -
+ (b->optimize & KEY_OPTIMIZE_REF_OR_NULL));
}
@@ -2163,31 +2238,28 @@ update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,JOIN_TAB *join_tab,
SELECT_LEX *select_lex)
{
uint and_level,i,found_eq_constant;
+ KEY_FIELD *key_fields,*end;
+ if (!(key_fields=(KEY_FIELD*)
+ thd->alloc(sizeof(key_fields[0])*
+ (thd->lex.current_select->cond_count+1)*2)))
+ return TRUE; /* purecov: inspected */
+ and_level=0; end=key_fields;
+ if (cond)
+ add_key_fields(join_tab,&end,&and_level,cond,normal_tables);
+ for (i=0 ; i < tables ; i++)
{
- KEY_FIELD *key_fields,*end;
-
- if (!(key_fields=(KEY_FIELD*)
- thd->alloc(sizeof(key_fields[0])*
- (thd->lex.current_select->cond_count+1)*2)))
- return TRUE; /* purecov: inspected */
- and_level=0; end=key_fields;
- if (cond)
- add_key_fields(join_tab,&end,&and_level,cond,normal_tables);
- for (i=0 ; i < tables ; i++)
+ if (join_tab[i].on_expr)
{
- if (join_tab[i].on_expr)
- {
- add_key_fields(join_tab,&end,&and_level,join_tab[i].on_expr,
- join_tab[i].table->map);
- }
+ add_key_fields(join_tab,&end,&and_level,join_tab[i].on_expr,
+ join_tab[i].table->map);
}
- if (my_init_dynamic_array(keyuse,sizeof(KEYUSE),20,64))
- return TRUE;
- /* fill keyuse with found key parts */
- for (KEY_FIELD *field=key_fields ; field != end ; field++)
- add_key_part(keyuse,field);
}
+ if (my_init_dynamic_array(keyuse,sizeof(KEYUSE),20,64))
+ return TRUE;
+ /* fill keyuse with found key parts */
+ for (KEY_FIELD *field=key_fields ; field != end ; field++)
+ add_key_part(keyuse,field);
if (select_lex->ftfunc_list->elements)
{
@@ -2195,9 +2267,10 @@ update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,JOIN_TAB *join_tab,
}
/*
- Remove ref if there is a keypart which is a ref and a const.
- Remove keyparts without previous keyparts.
Special treatment for ft-keys.
+ Remove the following things from KEYUSE:
+ - ref if there is a keypart which is a ref and a const.
+ - keyparts without previous keyparts.
*/
if (keyuse->elements)
{
@@ -2215,8 +2288,7 @@ update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,JOIN_TAB *join_tab,
for (i=0 ; i < keyuse->elements-1 ; i++,use++)
{
if (!use->used_tables)
- use->table->const_key_parts[use->key] |=
- (key_part_map) 1 << use->keypart;
+ use->table->const_key_parts[use->key]|= use->keypart_map;
if (use->keypart != FT_KEYPART)
{
if (use->key == prev->key && use->table == prev->table)
@@ -2245,6 +2317,41 @@ update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,JOIN_TAB *join_tab,
return FALSE;
}
+/*
+ Update some values in keyuse for faster find_best_combination() loop
+*/
+
+static void optimize_keyuse(JOIN *join, DYNAMIC_ARRAY *keyuse_array)
+{
+ KEYUSE *end,*keyuse= dynamic_element(keyuse_array, 0, KEYUSE*);
+
+ for (end= keyuse+ keyuse_array->elements ; keyuse < end ; keyuse++)
+ {
+ table_map map;
+ /*
+ If we find a ref, assume this table matches a proportional
+ part of this table.
+ For example 100 records matching a table with 5000 records
+ gives 5000/100 = 50 records per key
+ Constant tables are ignored.
+ To avoid bad matches, we don't make ref_table_rows less than 100.
+ */
+ keyuse->ref_table_rows= ~(table_map) 0; // If no ref
+ if (keyuse->used_tables &
+ (map= (keyuse->used_tables & ~join->const_table_map &
+ ~OUTER_REF_TABLE_BIT)))
+ {
+ uint tablenr;
+ for (tablenr=0 ; ! (map & 1) ; map>>=1, tablenr++) ;
+ if (map == 1) // Only one table
+ {
+ TABLE *tmp_table=join->all_tables[tablenr];
+ keyuse->ref_table_rows= max(tmp_table->file->records, 100);
+ }
+ }
+ }
+}
+
/*****************************************************************************
Go through all combinations of not marked tables and find the one
@@ -2331,7 +2438,7 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count,
uint max_key_part=0;
/* Test how we can use keys */
- rec= s->records/MATCHING_ROWS_IN_OTHER_TABLE; /* Assumed records/key */
+ rec= s->records/MATCHING_ROWS_IN_OTHER_TABLE; // Assumed records/key
for (keyuse=s->keyuse ; keyuse->table == table ;)
{
key_map found_part=0;
@@ -2339,44 +2446,27 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count,
uint key=keyuse->key;
KEY *keyinfo=table->key_info+key;
bool ft_key=(keyuse->keypart == FT_KEYPART);
+ uint found_ref_or_null= 0;
+ /* Calculate how many key segments of the current key we can use */
start_key=keyuse;
do
{
uint keypart=keyuse->keypart;
+ uint found_part_ref_or_null= KEY_OPTIMIZE_REF_OR_NULL;
do
{
- if (!ft_key)
- {
- table_map map;
- if (!(rest_tables & keyuse->used_tables))
- {
- found_part|= (key_part_map) 1 << keypart;
- found_ref|= keyuse->used_tables;
- }
- /*
- If we find a ref, assume this table matches a proportional
- part of this table.
- For example 100 records matching a table with 5000 records
- gives 5000/100 = 50 records per key
- Constant tables are ignored and to avoid bad matches,
- we don't make rec less than 100.
- */
- if (keyuse->used_tables &
- (map=(keyuse->used_tables & ~join->const_table_map &
- ~OUTER_REF_TABLE_BIT)))
- {
- uint tablenr;
- for (tablenr=0 ; ! (map & 1) ; map>>=1, tablenr++) ;
- if (map == 1) // Only one table
- {
- TABLE *tmp_table=join->all_tables[tablenr];
- if (rec > tmp_table->file->records && rec > 100)
- rec=max(tmp_table->file->records,100);
- }
- }
+ if (!(rest_tables & keyuse->used_tables) &&
+ !(found_ref_or_null & keyuse->optimize))
+ {
+ found_part|=keyuse->keypart_map;
+ found_ref|= keyuse->used_tables;
+ if (rec > keyuse->ref_table_rows)
+ rec= keyuse->ref_table_rows;
+ found_part_ref_or_null&= keyuse->optimize;
}
keyuse++;
+ found_ref_or_null|= found_part_ref_or_null;
} while (keyuse->table == table && keyuse->key == key &&
keyuse->keypart == keypart);
} while (keyuse->table == table && keyuse->key == key);
@@ -2386,8 +2476,8 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count,
*/
if (!found_part && !ft_key)
continue; // Nothing usable found
- if (rec == 0)
- rec=1L; // Fix for small tables
+ if (rec < MATCHING_ROWS_IN_OTHER_TABLE)
+ rec= MATCHING_ROWS_IN_OTHER_TABLE; // Fix for small tables
/*
ft-keys require special treatment
@@ -2406,7 +2496,8 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count,
/*
Check if we found full key
*/
- if (found_part == PREV_BITS(uint,keyinfo->key_parts))
+ if (found_part == PREV_BITS(uint,keyinfo->key_parts) &&
+ !found_ref_or_null)
{ /* use eq key */
max_key_part= (uint) ~0;
if ((keyinfo->flags & (HA_NOSAME | HA_NULL_PART_KEY)) == HA_NOSAME)
@@ -2459,7 +2550,8 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count,
Set tmp to (previous record count) * (records / combination)
*/
if ((found_part & 1) &&
- !(table->file->index_flags(key) & HA_ONLY_WHOLE_INDEX))
+ (!(table->file->index_flags(key) & HA_ONLY_WHOLE_INDEX) ||
+ found_part == PREV_BITS(uint,keyinfo->key_parts)))
{
max_key_part=max_part_bit(found_part);
/*
@@ -2509,6 +2601,12 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count,
}
records=(ulong) tmp;
}
+ if (found_ref_or_null)
+ {
+ /* We need to do two key searches to find key */
+ tmp*= 2.0;
+ records*= 2.0;
+ }
}
if (table->used_keys & ((key_map) 1 << key))
{
@@ -2758,9 +2856,7 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, KEYUSE *org_keyuse,
TABLE *table;
KEY *keyinfo;
- /*
- Use best key from find_best
- */
+ /* Use best key from find_best */
table=j->table;
key=keyuse->key;
keyinfo=table->key_info+key;
@@ -2776,14 +2872,22 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, KEYUSE *org_keyuse,
else
{
keyparts=length=0;
+ uint found_part_ref_or_null= 0;
+ /*
+ Calculate length for the used key
+ Stop if there is a missing key part or when we find second key_part
+ with KEY_OPTIMIZE_REF_OR_NULL
+ */
do
{
- if (!((~used_tables) & keyuse->used_tables))
+ if (!(~used_tables & keyuse->used_tables))
{
- if (keyparts == keyuse->keypart)
+ if (keyparts == keyuse->keypart &&
+ !(found_part_ref_or_null & keyuse->optimize))
{
keyparts++;
- length+=keyinfo->key_part[keyuse->keypart].store_length;
+ length+= keyinfo->key_part[keyuse->keypart].store_length;
+ found_part_ref_or_null|= keyuse->optimize;
}
}
keyuse++;
@@ -2806,8 +2910,8 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, KEYUSE *org_keyuse,
j->ref.key_err=1;
keyuse=org_keyuse;
- store_key **ref_key=j->ref.key_copy;
- byte *key_buff=j->ref.key_buff;
+ store_key **ref_key= j->ref.key_copy;
+ byte *key_buff=j->ref.key_buff, *null_ref_key= 0;
if (ftkey)
{
j->ref.items[0]=((Item_func*)(keyuse->val))->key_item();
@@ -2835,9 +2939,7 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, KEYUSE *org_keyuse,
maybe_null ? (char*) key_buff : 0,
keyinfo->key_part[i].length, keyuse->val);
if (thd->is_fatal_error)
- {
return TRUE;
- }
tmp.copy();
}
else
@@ -2845,17 +2947,25 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, KEYUSE *org_keyuse,
keyuse,join->const_table_map,
&keyinfo->key_part[i],
(char*) key_buff,maybe_null);
+ /* Remmeber if we are going to use REF_OR_NULL */
+ if (keyuse->optimize & KEY_OPTIMIZE_REF_OR_NULL)
+ null_ref_key= key_buff;
key_buff+=keyinfo->key_part[i].store_length;
}
} /* not ftkey */
*ref_key=0; // end_marker
- if (j->type == JT_FT) /* no-op */;
- else if (j->type == JT_CONST)
- j->table->const_table=1;
+ if (j->type == JT_FT)
+ return 0;
+ if (j->type == JT_CONST)
+ j->table->const_table= 1;
else if (((keyinfo->flags & (HA_NOSAME | HA_NULL_PART_KEY))
- != HA_NOSAME) ||
- keyparts != keyinfo->key_parts)
- j->type=JT_REF; /* Must read with repeat */
+ != HA_NOSAME) || keyparts != keyinfo->key_parts ||
+ null_ref_key)
+ {
+ /* Must read with repeat */
+ j->type= null_ref_key ? JT_REF_OR_NULL : JT_REF;
+ j->null_ref_key= null_ref_key;
+ }
else if (ref_key == j->ref.key_copy)
{
/*
@@ -3166,6 +3276,7 @@ make_join_readinfo(JOIN *join, uint options)
table->file->extra(HA_EXTRA_KEYREAD);
}
break;
+ case JT_REF_OR_NULL:
case JT_REF:
table->status=STATUS_NO_RECORD;
if (tab->select)
@@ -3176,14 +3287,22 @@ make_join_readinfo(JOIN *join, uint options)
delete tab->quick;
tab->quick=0;
table->file->index_init(tab->ref.key);
- tab->read_first_record= join_read_always_key;
- tab->read_record.read_record= join_read_next_same;
if (table->used_keys & ((key_map) 1 << tab->ref.key) &&
!table->no_keyread)
{
table->key_read=1;
table->file->extra(HA_EXTRA_KEYREAD);
}
+ if (tab->type == JT_REF)
+ {
+ tab->read_first_record= join_read_always_key;
+ tab->read_record.read_record= join_read_next_same;
+ }
+ else
+ {
+ tab->read_first_record= join_read_always_key_or_null;
+ tab->read_record.read_record= join_read_next_same_or_null;
+ }
break;
case JT_FT:
table->status=STATUS_NO_RECORD;
@@ -5194,6 +5313,40 @@ flush_cached_records(JOIN *join,JOIN_TAB *join_tab,bool skipp_last)
The different ways to read a record
Returns -1 if row was not found, 0 if row was found and 1 on errors
*****************************************************************************/
+
+/* Help function when we get some an error from the table handler */
+
+static int report_error(TABLE *table, int error)
+{
+ if (error == HA_ERR_END_OF_FILE || error == HA_ERR_KEY_NOT_FOUND)
+ {
+ table->status= STATUS_GARBAGE;
+ return -1; // key not found; ok
+ }
+ /*
+ Locking reads can legally return also these errors, do not
+ print them to the .err log
+ */
+ if (error != HA_ERR_LOCK_DEADLOCK && error != HA_ERR_LOCK_WAIT_TIMEOUT)
+ sql_print_error("Got error %d when reading table '%s'",
+ error, table->path);
+ table->file->print_error(error,MYF(0));
+ return 1;
+}
+
+
+static int safe_index_read(JOIN_TAB *tab)
+{
+ int error;
+ TABLE *table= tab->table;
+ if ((error=table->file->index_read(table->record[0],
+ tab->ref.key_buff,
+ tab->ref.key_length, HA_READ_KEY_EXACT)))
+ return report_error(table, error);
+ return 0;
+}
+
+
static int
join_read_const_table(JOIN_TAB *tab, POSITION *pos)
{
@@ -5248,10 +5401,7 @@ join_read_system(JOIN_TAB *tab)
table->primary_key)))
{
if (error != HA_ERR_END_OF_FILE)
- {
- table->file->print_error(error,MYF(0));
- return 1;
- }
+ return report_error(table, error);
table->null_row=1; // This is ok.
empty_record(table); // Make empty record
return -1;
@@ -5285,15 +5435,7 @@ join_read_const(JOIN_TAB *tab)
table->null_row=1;
empty_record(table);
if (error != HA_ERR_KEY_NOT_FOUND)
- {
- /* Locking reads can legally return also these errors, do not
- print them to the .err log */
- if (error != HA_ERR_LOCK_DEADLOCK && error != HA_ERR_LOCK_WAIT_TIMEOUT)
- sql_print_error("read_const: Got error %d when reading table %s",
- error, table->path);
- table->file->print_error(error,MYF(0));
- return 1;
- }
+ return report_error(table, error);
return -1;
}
store_record(table,record[1]);
@@ -5326,12 +5468,7 @@ join_read_key(JOIN_TAB *tab)
tab->ref.key_buff,
tab->ref.key_length,HA_READ_KEY_EXACT);
if (error && error != HA_ERR_KEY_NOT_FOUND)
- {
- sql_print_error("read_key: Got error %d when reading table '%s'",error,
- table->path);
- table->file->print_error(error,MYF(0));
- return 1;
- }
+ return report_error(table, error);
}
table->null_row=0;
return table->status ? -1 : 0;
@@ -5351,18 +5488,13 @@ join_read_always_key(JOIN_TAB *tab)
tab->ref.key_length,HA_READ_KEY_EXACT)))
{
if (error != HA_ERR_KEY_NOT_FOUND)
- {
- if (error != HA_ERR_LOCK_DEADLOCK && error != HA_ERR_LOCK_WAIT_TIMEOUT)
- sql_print_error("read_const: Got error %d when reading table %s",error,
- table->path);
- table->file->print_error(error,MYF(0));
- return 1;
- }
+ return report_error(table, error);
return -1; /* purecov: inspected */
}
return 0;
}
+
/*
This function is used when optimizing away ORDER BY in
SELECT * FROM t1 WHERE a=1 ORDER BY a DESC,b DESC
@@ -5381,13 +5513,7 @@ join_read_last_key(JOIN_TAB *tab)
tab->ref.key_length)))
{
if (error != HA_ERR_KEY_NOT_FOUND)
- {
- if (error != HA_ERR_LOCK_DEADLOCK && error != HA_ERR_LOCK_WAIT_TIMEOUT)
- sql_print_error("read_const: Got error %d when reading table %s",error,
- table->path);
- table->file->print_error(error,MYF(0));
- return 1;
- }
+ return report_error(table, error);
return -1; /* purecov: inspected */
}
return 0;
@@ -5414,19 +5540,14 @@ join_read_next_same(READ_RECORD *info)
tab->ref.key_length)))
{
if (error != HA_ERR_END_OF_FILE)
- {
- if (error != HA_ERR_LOCK_DEADLOCK && error != HA_ERR_LOCK_WAIT_TIMEOUT)
- sql_print_error("read_next: Got error %d when reading table %s",error,
- table->path);
- table->file->print_error(error,MYF(0));
- return 1;
- }
+ return report_error(table, error);
table->status= STATUS_GARBAGE;
return -1;
}
return 0;
}
+
static int
join_read_prev_same(READ_RECORD *info)
{
@@ -5435,23 +5556,9 @@ join_read_prev_same(READ_RECORD *info)
JOIN_TAB *tab=table->reginfo.join_tab;
if ((error=table->file->index_prev(table->record[0])))
- {
- if (error != HA_ERR_END_OF_FILE)
- {
- if (error != HA_ERR_LOCK_DEADLOCK && error != HA_ERR_LOCK_WAIT_TIMEOUT)
- sql_print_error("read_next: Got error %d when reading table %s",error,
- table->path);
- table->file->print_error(error,MYF(0));
- error= 1;
- }
- else
- {
- table->status= STATUS_GARBAGE;
- error= -1;
- }
- }
- else if (key_cmp(table, tab->ref.key_buff, tab->ref.key,
- tab->ref.key_length))
+ return report_error(table, error);
+ if (key_cmp(table, tab->ref.key_buff, tab->ref.key,
+ tab->ref.key_length))
{
table->status=STATUS_NOT_FOUND;
error= -1;
@@ -5488,6 +5595,7 @@ join_init_read_record(JOIN_TAB *tab)
return (*tab->read_record.read_record)(&tab->read_record);
}
+
static int
join_read_first(JOIN_TAB *tab)
{
@@ -5505,17 +5613,10 @@ join_read_first(JOIN_TAB *tab)
tab->read_record.file=table->file;
tab->read_record.index=tab->index;
tab->read_record.record=table->record[0];
- error=tab->table->file->index_first(tab->table->record[0]);
- if (error)
+ if ((error=tab->table->file->index_first(tab->table->record[0])))
{
if (error != HA_ERR_KEY_NOT_FOUND && error != HA_ERR_END_OF_FILE)
- {
- if (error != HA_ERR_LOCK_DEADLOCK && error != HA_ERR_LOCK_WAIT_TIMEOUT)
- sql_print_error("read_first_with_key: Got error %d when reading table",
- error);
- table->file->print_error(error,MYF(0));
- return 1;
- }
+ report_error(table, error);
return -1;
}
return 0;
@@ -5525,23 +5626,13 @@ join_read_first(JOIN_TAB *tab)
static int
join_read_next(READ_RECORD *info)
{
- int error=info->file->index_next(info->record);
- if (error)
- {
- if (error != HA_ERR_END_OF_FILE)
- {
- if (error != HA_ERR_LOCK_DEADLOCK && error != HA_ERR_LOCK_WAIT_TIMEOUT)
- sql_print_error(
- "read_next_with_key: Got error %d when reading table %s",
- error, info->table->path);
- info->file->print_error(error,MYF(0));
- return 1;
- }
- return -1;
- }
+ int error;
+ if ((error=info->file->index_next(info->record)))
+ return report_error(info->table, error);
return 0;
}
+
static int
join_read_last(JOIN_TAB *tab)
{
@@ -5559,19 +5650,8 @@ join_read_last(JOIN_TAB *tab)
tab->read_record.file=table->file;
tab->read_record.index=tab->index;
tab->read_record.record=table->record[0];
- error=tab->table->file->index_last(tab->table->record[0]);
- if (error)
- {
- if (error != HA_ERR_END_OF_FILE)
- {
- if (error != HA_ERR_LOCK_DEADLOCK && error != HA_ERR_LOCK_WAIT_TIMEOUT)
- sql_print_error("read_last_with_key: Got error %d when reading table",
- error, table->path);
- table->file->print_error(error,MYF(0));
- return 1;
- }
- return -1;
- }
+ if ((error= tab->table->file->index_last(tab->table->record[0])))
+ return report_error(table, error);
return 0;
}
@@ -5579,20 +5659,9 @@ join_read_last(JOIN_TAB *tab)
static int
join_read_prev(READ_RECORD *info)
{
- int error=info->file->index_prev(info->record);
- if (error)
- {
- if (error != HA_ERR_END_OF_FILE)
- {
- if (error != HA_ERR_LOCK_DEADLOCK && error != HA_ERR_LOCK_WAIT_TIMEOUT)
- sql_print_error(
- "read_prev_with_key: Got error %d when reading table: %s",
- error,info->table->path);
- info->file->print_error(error,MYF(0));
- return 1;
- }
- return -1;
- }
+ int error;
+ if ((error= info->file->index_prev(info->record)))
+ return report_error(info->table, error);
return 0;
}
@@ -5609,42 +5678,57 @@ join_ft_read_first(JOIN_TAB *tab)
#endif
table->file->ft_init();
- error=table->file->ft_read(table->record[0]);
- if (error)
- {
- if (error != HA_ERR_END_OF_FILE)
- {
- if (error != HA_ERR_LOCK_DEADLOCK && error != HA_ERR_LOCK_WAIT_TIMEOUT)
- sql_print_error("ft_read_first: Got error %d when reading table %s",
- error, table->path);
- table->file->print_error(error,MYF(0));
- return 1;
- }
- return -1;
- }
+ if ((error= table->file->ft_read(table->record[0])))
+ return report_error(table, error);
return 0;
}
static int
join_ft_read_next(READ_RECORD *info)
{
- int error=info->file->ft_read(info->table->record[0]);
- if (error)
- {
- if (error != HA_ERR_END_OF_FILE)
- {
- if (error != HA_ERR_LOCK_DEADLOCK && error != HA_ERR_LOCK_WAIT_TIMEOUT)
- sql_print_error("ft_read_next: Got error %d when reading table %s",
- error, info->table->path);
- info->file->print_error(error,MYF(0));
- return 1;
- }
- return -1;
- }
+ int error;
+ if ((error= info->file->ft_read(info->table->record[0])))
+ return report_error(info->table, error);
return 0;
}
+/*
+ Reading of key with key reference and one part that may be NULL
+*/
+
+static int
+join_read_always_key_or_null(JOIN_TAB *tab)
+{
+ int res;
+
+ /* First read according to key which is NOT NULL */
+ *tab->null_ref_key=0;
+ if ((res= join_read_always_key(tab)) >= 0)
+ return res;
+
+ /* Then read key with null value */
+ *tab->null_ref_key= 1;
+ return safe_index_read(tab);
+}
+
+
+static int
+join_read_next_same_or_null(READ_RECORD *info)
+{
+ int error;
+ if ((error= join_read_next_same(info)) >= 0)
+ return error;
+ JOIN_TAB *tab= info->table->reginfo.join_tab;
+
+ /* Test if we have already done a read after null key */
+ if (*tab->null_ref_key)
+ return -1; // All keys read
+ *tab->null_ref_key= 1; // Read null key
+ return safe_index_read(tab);
+}
+
+
/*****************************************************************************
The different end of select functions
These functions returns < 0 when end is reached, 0 on ok and > 0 if a
@@ -6371,10 +6455,13 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit,
}
ref_key= -1;
- if (tab->ref.key >= 0) // Constant range in WHERE
+ /* Test if constant range in WHERE */
+ if (tab->ref.key >= 0)
{
ref_key= tab->ref.key;
ref_key_parts= tab->ref.key_parts;
+ if (tab->type == JT_REF_OR_NULL)
+ DBUG_RETURN(0);
}
else if (select && select->quick) // Range found by opt_range
{
diff --git a/sql/sql_select.h b/sql/sql_select.h
index df21d337b54..f29729fb5b3 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -27,8 +27,10 @@
typedef struct keyuse_t {
TABLE *table;
Item *val; /* or value if no field */
- uint key,keypart;
table_map used_tables;
+ uint key, keypart, optimize;
+ key_map keypart_map;
+ ha_rows ref_table_rows;
} KEYUSE;
class store_key;
@@ -73,7 +75,7 @@ typedef struct st_join_cache {
*/
enum join_type { JT_UNKNOWN,JT_SYSTEM,JT_CONST,JT_EQ_REF,JT_REF,JT_MAYBE_REF,
- JT_ALL, JT_RANGE, JT_NEXT, JT_FT};
+ JT_ALL, JT_RANGE, JT_NEXT, JT_FT, JT_REF_OR_NULL};
class JOIN;
@@ -85,6 +87,7 @@ typedef struct st_join_table {
QUICK_SELECT *quick;
Item *on_expr;
const char *info;
+ byte *null_ref_key;
int (*read_first_record)(struct st_join_table *tab);
int (*next_select)(JOIN *,struct st_join_table *,bool);
READ_RECORD read_record;
diff --git a/sql/sql_show.cc b/sql/sql_show.cc
index 1c4954e0276..fae01936357 100644
--- a/sql/sql_show.cc
+++ b/sql/sql_show.cc
@@ -27,8 +27,6 @@
#include "ha_berkeley.h" // For berkeley_show_logs
#endif
-/* extern "C" pthread_mutex_t THR_LOCK_keycache; */
-
static const char *grant_names[]={
"select","insert","update","delete","create","drop","reload","shutdown",
"process","file","grant","references","index","alter"};
@@ -43,15 +41,11 @@ static int mysql_find_files(THD *thd,List<char> *files, const char *db,
static int
store_create_info(THD *thd, TABLE *table, String *packet);
-static void
-append_identifier(THD *thd, String *packet, const char *name);
-
-extern struct st_VioSSLAcceptorFd * ssl_acceptor_fd;
-/****************************************************************************
-** Send list of databases
-** A database is a directory in the mysql_data_home directory
-****************************************************************************/
+/*
+ Report list of databases
+ A database is a directory in the mysql_data_home directory
+*/
int
mysqld_show_dbs(THD *thd,const char *wild)
@@ -1002,8 +996,8 @@ mysqld_dump_create_info(THD *thd, TABLE *table, int fd)
}
-static void
-append_identifier(THD *thd, String *packet, const char *name)
+void
+append_identifier(THD *thd, String *packet, const char *name, uint length)
{
char qtype;
if (thd->variables.sql_mode & MODE_ANSI_QUOTES)
@@ -1014,12 +1008,12 @@ append_identifier(THD *thd, String *packet, const char *name)
if (thd->options & OPTION_QUOTE_SHOW_CREATE)
{
packet->append(&qtype, 1);
- packet->append(name, 0, system_charset_info);
+ packet->append(name, length, system_charset_info);
packet->append(&qtype, 1);
}
else
{
- packet->append(name, 0, system_charset_info);
+ packet->append(name, length, system_charset_info);
}
}
@@ -1050,7 +1044,7 @@ store_create_info(THD *thd, TABLE *table, String *packet)
packet->append("CREATE TEMPORARY TABLE ", 23);
else
packet->append("CREATE TABLE ", 13);
- append_identifier(thd,packet,table->real_name);
+ append_identifier(thd,packet, table->real_name, strlen(table->real_name));
packet->append(" (\n", 3);
Field **ptr,*field;
@@ -1061,7 +1055,7 @@ store_create_info(THD *thd, TABLE *table, String *packet)
uint flags = field->flags;
packet->append(" ", 2);
- append_identifier(thd,packet,field->field_name);
+ append_identifier(thd,packet,field->field_name, strlen(field->field_name));
packet->append(' ');
// check for surprises from the previous call to Field::sql_type()
if (type.ptr() != tmp)
@@ -1152,7 +1146,7 @@ store_create_info(THD *thd, TABLE *table, String *packet)
packet->append("KEY ", 4);
if (!found_primary)
- append_identifier(thd, packet, key_info->name);
+ append_identifier(thd, packet, key_info->name, strlen(key_info->name));
if (!(thd->variables.sql_mode & MODE_NO_KEY_OPTIONS) &&
!limited_mysql_mode && !foreign_db_mode)
@@ -1174,7 +1168,8 @@ store_create_info(THD *thd, TABLE *table, String *packet)
packet->append(',');
if (key_part->field)
- append_identifier(thd,packet,key_part->field->field_name);
+ append_identifier(thd,packet,key_part->field->field_name,
+ strlen(key_part->field->field_name));
if (!key_part->field ||
(key_part->length !=
table->field[key_part->fieldnr-1]->key_length() &&
@@ -1190,17 +1185,17 @@ store_create_info(THD *thd, TABLE *table, String *packet)
packet->append(')');
}
+ /*
+ Get possible foreign key definitions stored in InnoDB and append them
+ to the CREATE TABLE statement
+ */
handler *file = table->file;
+ char* for_str= file->get_foreign_key_create_info();
- /* Get possible foreign key definitions stored in InnoDB and append them
- to the CREATE TABLE statement */
-
- char* for_str = file->get_foreign_key_create_info();
-
- if (for_str) {
- packet->append(for_str, strlen(for_str));
-
- file->free_foreign_key_create_info(for_str);
+ if (for_str)
+ {
+ packet->append(for_str, strlen(for_str));
+ file->free_foreign_key_create_info(for_str);
}
packet->append("\n)", 2);
@@ -1267,7 +1262,8 @@ store_create_info(THD *thd, TABLE *table, String *packet)
{
char buff[100];
sprintf(buff," RAID_TYPE=%s RAID_CHUNKS=%d RAID_CHUNKSIZE=%ld",
- my_raid_type(file->raid_type), file->raid_chunks, file->raid_chunksize/RAID_BLOCK_SIZE);
+ my_raid_type(file->raid_type), file->raid_chunks,
+ file->raid_chunksize/RAID_BLOCK_SIZE);
packet->append(buff);
}
}
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 1d605abe8a3..6072be28dca 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -1597,6 +1597,7 @@ alter_list_item:
LEX *lex=Lex;
lex->length=lex->dec=0; lex->type=0; lex->interval=0;
lex->default_value=lex->comment=0;
+ lex->charset= NULL;
lex->simple_alter=0;
}
type opt_attribute
diff --git a/sql/table.cc b/sql/table.cc
index 908d6807450..0fc2a09f749 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -395,7 +395,9 @@ int openfrm(const char *name, const char *alias, uint db_stat, uint prgflag,
}
else
{
- if (!(charset=get_charset((uint) strpos[14], MYF(0))))
+ if (!strpos[14])
+ charset= &my_charset_bin;
+ else if (!(charset=get_charset((uint) strpos[14], MYF(0))))
charset= (outparam->table_charset ? outparam->table_charset:
default_charset_info);
}
diff --git a/sql/unireg.cc b/sql/unireg.cc
index 5e723281d3f..3e634f54b4f 100644
--- a/sql/unireg.cc
+++ b/sql/unireg.cc
@@ -460,8 +460,10 @@ static bool pack_fields(File file,List<create_field> &create_fields)
buff[13]= (uchar) field->sql_type;
if (field->sql_type == FIELD_TYPE_GEOMETRY)
buff[14]= (uchar) field->geom_type;
- else
+ else if (field->charset)
buff[14]= (uchar) field->charset->number;
+ else
+ buff[14]= 0; // Numerical
int2store(buff+15, field->comment.length);
comment_length+= field->comment.length;
set_if_bigger(int_count,field->interval_id);
View Lorry Controller Status