diff options
| author | unknown <monty@mysql.com> | 2004-09-09 06:59:26 +0300 |
|---|---|---|
| committer | unknown <monty@mysql.com> | 2004-09-09 06:59:26 +0300 |
| commit | 33efc9677d6f6a68d3dba69f4c036856ac4af5fe (patch) | |
| tree | 705389bcc2173e115a186211d2a5890721c9f5c7 | |
| parent | 4ad51359c1b7b5ed854f1af8df06fd6912068d28 (diff) | |
| download | mariadb-git-33efc9677d6f6a68d3dba69f4c036856ac4af5fe.tar.gz | |
After merge fixes of merge with 4.1 that included the new arena code.
Fixed (together with Guilhem) bugs in mysqlbinlog regarding --offset
Prefix addresses with 0x for easier comparisons of debug logs
Fixed problem where MySQL choosed index-read even if there would be a much better range on the same index
This fix changed some 'index' queries to 'range' queries in the test suite
Don't create 'dummy' WHERE clause for trivial WHERE clauses where we can remove the WHERE clause.
This fix removed of a lot of 'Using where' notes in the test suite.
Give NOTE instead of WARNING if table/function doesn't exists when using DROP IF EXISTS
Give NOTE instead of WARNING for safe field-type conversions
Makefile.am:
Don't automaticly update files from bk
client/mysqlbinlog.cc:
Merge with 4.1 (+ apply bug fixes for --offset and --start-position)
include/my_sys.h:
Faster clear_alloc_root()
mysql-test/r/bdb.result:
Updated results after merge
mysql-test/r/create.result:
Updated results after merge
mysql-test/r/func_group.result:
Updated results after merge
mysql-test/r/func_if.result:
Updated results after merge
mysql-test/r/heap_btree.result:
Updated results after merge
mysql-test/r/index_merge.result:
Updated results after merge
mysql-test/r/index_merge_ror.result:
Updated results after merge
mysql-test/r/innodb.result:
Updated results after merge
mysql-test/r/join_outer.result:
Updated results after merge
mysql-test/r/mysqlbinlog2.result:
Updated results after merge
mysql-test/r/negation_elimination.result:
Updated results after merge
mysql-test/r/null.result:
Updated results after merge
Added more tests
mysql-test/r/null_key.result:
Updated results after merge
Added more tests
mysql-test/r/order_by.result:
Updated results after merge
mysql-test/r/range.result:
Updated results after merge
Added more tests
mysql-test/r/rpl_charset.result:
Updated results after merge
mysql-test/r/sp-error.result:
Updated results after merge
mysql-test/r/sp.result:
Updated results after merge
Added delete of some stored procedures in an attempt to be able to re-run test even if it aborts in the middle
mysql-test/r/type_blob.result:
Updated results after merge
(Some warnings are now notes)
mysql-test/r/user_var.result:
Updated results after merge
Added more tests
mysql-test/r/variables.result:
Updated results after merge
mysql-test/r/view.result:
Updated results after merge
mysql-test/t/mysqlbinlog2.test:
Updated tests to use new positions
mysql-test/t/null.test:
More tests
mysql-test/t/null_key.test:
More tests
mysql-test/t/range.test:
More tests
mysql-test/t/rpl_charset.test:
Avoid big diffs in the future if tests changes
mysql-test/t/sp-error.test:
Updated error numbers
mysql-test/t/sp-security.test:
Updated error numbers
mysql-test/t/sp.test:
Updated results after merge
Added delete of some stored procedures in an attempt to be able to re-run test even if it aborts in the middle
mysql-test/t/user_var.test:
More tests
mysql-test/t/view.test:
Updated error numbers
mysys/my_alloc.c:
Write into debug log the address of the allocated area
sql/ha_isam.cc:
Prefix addresses with 0x for easier comparisons of debug logs
sql/ha_myisam.cc:
Prefix addresses with 0x for easier comparisons of debug logs
sql/ha_ndbcluster.cc:
Add missing enum to switch
sql/handler.cc:
remove compiler warning
sql/item.cc:
More debugging
Simple cleanup
sql/item.h:
Move Item::cleanup() to item.cc
sql/item_cmpfunc.cc:
Fix arena code
sql/item_subselect.cc:
After merge fixes
sql/item_subselect.h:
After merge fixes
sql/item_sum.cc:
Updated comment
sql/log_event.cc:
Remove wrong test
sql/mysql_priv.h:
Indentation fixes
sql/mysqld.cc:
After merge fixes
Added 0x to pointers in debug log
sql/opt_range.cc:
Fixed problem where MySQL choosed index-read even if there would be a much better range on the same index
This fix changed some 'index' queries to 'range' queries in the test suite
sql/set_var.cc:
Indentation fixes
sql/sp_head.cc:
Set state to INITIALIZED to make SP work with new arena code
sql/sql_base.cc:
After merge fixes
sql/sql_class.cc:
More debugging
Use clear_alloc_root() instead of init_alloc_root() as the former is faster
sql/sql_class.h:
New method 'only_prepare()'
sql/sql_lex.cc:
After merge fixes
sql/sql_lex.h:
After merge fixes
sql/sql_parse.cc:
Fix for timezone tables. (The old way to add timezone tables to global list in 'create_total_list' doesn't work anymore)
Give NOTE instead of WARNING if table/function doesn't exists when using DROP IF EXISTS
sql/sql_prepare.cc:
After merge fixes
sql/sql_select.cc:
Don't create 'dummy' WHERE clause for trivial WHERE clauses where we can remove the WHERE clause.
This fix removed of a lot of 'Using where' notes in the test suite
sql/sql_table.cc:
Give NOTE instead of WARNING if table/function doesn't exists when using DROP IF EXISTS
sql/sql_union.cc:
After merge fix
sql/sql_view.cc:
After merge fix
sql/table.cc:
After merge fix
sql/tztime.cc:
Update timezone table handling to use new table lists structure
sql/tztime.h:
Update timezone table handling to use new table lists structure
sql/unireg.cc:
Use 0x before pointers
67 files changed, 685 insertions, 480 deletions
diff --git a/Makefile.am b/Makefile.am index 7c2ed820a23..e3fcafd6cf0 100644 --- a/Makefile.am +++ b/Makefile.am @@ -102,3 +102,5 @@ tags: test: cd mysql-test ; ./mysql-test-run +# Don't update the files from bitkeeper +%::SCCS/s.% diff --git a/client/mysqlbinlog.cc b/client/mysqlbinlog.cc index 538ec4982c1..31588d5b013 100644 --- a/client/mysqlbinlog.cc +++ b/client/mysqlbinlog.cc @@ -22,7 +22,8 @@ can read files produced by 3.23, 4.x, 5.0 servers. Can read binlogs from 3.23/4.x/5.0 and relay logs from 4.x/5.0. - Should be able to read any file of these categories, even with --position. + Should be able to read any file of these categories, even with + --start-position. An important fact: the Format_desc event of the log is at most the 3rd event of the log; if it is the 3rd then there is this combination: Format_desc_of_slave, Rotate_of_master, Format_desc_of_master. @@ -323,8 +324,8 @@ int Load_log_processor::process(Append_block_log_event *ae) /* There is no Create_file event (a bad binlog or a big - --position). Assuming it's a big --position, we just do nothing and - print a warning. + --start-position). Assuming it's a big --start-position, we just do + nothing and print a warning. */ fprintf(stderr,"Warning: ignoring Append_block as there is no \ Create_file event for file_id: %u\n",ae->file_id); @@ -356,18 +357,27 @@ int process_event(LAST_EVENT_INFO *last_event_info, Log_event *ev, my_off_t pos) { char ll_buff[21]; + Log_event_type ev_type= ev->get_type_code(); DBUG_ENTER("process_event"); + /* + Format events are not concerned by --offset and such, we always need to + read them to be able to process the wanted events. + */ if ((rec_count >= offset) && - ((my_time_t)(ev->when) >= start_datetime)) + ((my_time_t)(ev->when) >= start_datetime) || + (ev_type == FORMAT_DESCRIPTION_EVENT)) { - /* - We have found an event after start_datetime, from now on print - everything (in case the binlog has timestamps increasing and decreasing, - we do this to avoid cutting the middle). - */ - start_datetime= 0; - offset= 0; // print everything and protect against cycling rec_count + if (ev_type != FORMAT_DESCRIPTION_EVENT) + { + /* + We have found an event after start_datetime, from now on print + everything (in case the binlog has timestamps increasing and + decreasing, we do this to avoid cutting the middle). + */ + start_datetime= 0; + offset= 0; // print everything and protect against cycling rec_count + } if (((my_time_t)(ev->when) >= stop_datetime) || (pos >= stop_position_mot)) { @@ -377,7 +387,7 @@ int process_event(LAST_EVENT_INFO *last_event_info, Log_event *ev, if (!short_form) fprintf(result_file, "# at %s\n",llstr(pos,ll_buff)); - switch (ev->get_type_code()) { + switch (ev_type) { case QUERY_EVENT: if (one_database) { @@ -431,7 +441,7 @@ int process_event(LAST_EVENT_INFO *last_event_info, Log_event *ev, Create_file_log_event *ce= load_processor.grab_event(exv->file_id); /* if ce is 0, it probably means that we have not seen the Create_file - event (a bad binlog, or most probably --position is after the + event (a bad binlog, or most probably --start-position is after the Create_file event). Print a warning comment. */ if (ce) @@ -853,6 +863,8 @@ could be out of memory"); for (;;) { const char *error_msg; + Log_event *ev; + len = net_safe_read(mysql); if (len == packet_error) { @@ -865,10 +877,9 @@ could be out of memory"); break; // end of data DBUG_PRINT("info",( "len= %u, net->read_pos[5] = %d\n", len, net->read_pos[5])); - Log_event *ev = Log_event::read_log_event((const char*) net->read_pos + 1 , - len - 1, &error, - description_event); - if (!ev) + if (!(ev= Log_event::read_log_event((const char*) net->read_pos + 1 , + len - 1, &error_msg, + description_event))) { fprintf(stderr, "Could not construct log event object\n"); error= 1; @@ -970,10 +981,10 @@ static void check_header(IO_CACHE* file, { byte header[BIN_LOG_HEADER_SIZE]; byte buf[PROBE_HEADER_LEN]; + my_off_t tmp_pos, pos; *description_event= new Format_description_log_event(3); - my_off_t tmp_pos; - my_off_t pos = my_b_tell(file); + pos= my_b_tell(file); my_b_seek(file, (my_off_t)0); if (my_b_read(file, header, sizeof(header))) die("Failed reading header; Probably an empty file"); @@ -981,16 +992,17 @@ static void check_header(IO_CACHE* file, die("File is not a binary log file"); /* - Imagine we are running with --position=1000. We still need to know the - binlog format's. So we still need to find, if there is one, the Format_desc - event, or to know if this is a 3.23 binlog. So we need to first read the - first events of the log, those around offset 4. - Even if we are reading a 3.23 binlog from the start (no --position): we need - to know the header length (which is 13 in 3.23, 19 in 4.x) to be able to - successfully print the first event (Start_log_event_v3). So even in this - case, we need to "probe" the first bytes of the log *before* we do a real - read_log_event(). Because read_log_event() needs to know the header's length - to work fine. + Imagine we are running with --start-position=1000. We still need + to know the binlog format's. So we still need to find, if there is + one, the Format_desc event, or to know if this is a 3.23 + binlog. So we need to first read the first events of the log, + those around offset 4. Even if we are reading a 3.23 binlog from + the start (no --start-position): we need to know the header length + (which is 13 in 3.23, 19 in 4.x) to be able to successfully print + the first event (Start_log_event_v3). So even in this case, we + need to "probe" the first bytes of the log *before* we do a real + read_log_event(). Because read_log_event() needs to know the + header's length to work fine. */ for(;;) { @@ -1002,23 +1014,25 @@ static void check_header(IO_CACHE* file, Could not read entry at offset %lu : Error in log format or read error", tmp_pos); /* - Otherwise this is just EOF : this log currently contains 0-2 events. - Maybe it's going to be filled in the next milliseconds; then we are - going to have a problem if this a 3.23 log (imagine we are locally - reading a 3.23 binlog which is being written presently): we won't know - it in read_log_event() and will fail(). - Similar problems could happen with hot relay logs if --position is used - (but a --position which is posterior to the current size of the log). - These are rare problems anyway (reading a hot log + when we read the - first events there are not all there yet + when we read a bit later - there are more events + using a strange --position). + Otherwise this is just EOF : this log currently contains 0-2 + events. Maybe it's going to be filled in the next + milliseconds; then we are going to have a problem if this a + 3.23 log (imagine we are locally reading a 3.23 binlog which + is being written presently): we won't know it in + read_log_event() and will fail(). Similar problems could + happen with hot relay logs if --start-position is used (but a + --start-position which is posterior to the current size of the log). + These are rare problems anyway (reading a hot log + when we + read the first events there are not all there yet + when we + read a bit later there are more events + using a strange + --start-position). */ break; } else { DBUG_PRINT("info",("buf[4]=%d", buf[4])); - /* always test for a Start_v3, even if no --position */ + /* always test for a Start_v3, even if no --start-position */ if (buf[4] == START_EVENT_V3) /* This is 3.23 or 4.x */ { if (uint4korr(buf + EVENT_LEN_OFFSET) < @@ -1030,7 +1044,7 @@ Could not read entry at offset %lu : Error in log format or read error", } break; } - else if (tmp_pos>=position) + else if (tmp_pos >= start_position) break; else if (buf[4] == FORMAT_DESCRIPTION_EVENT) /* This is 5.0 */ { diff --git a/include/my_sys.h b/include/my_sys.h index 54feb73d3e6..4724592aa81 100644 --- a/include/my_sys.h +++ b/include/my_sys.h @@ -703,7 +703,8 @@ File create_temp_file(char *to, const char *dir, const char *pfx, #define my_init_dynamic_array(A,B,C,D) init_dynamic_array(A,B,C,D CALLER_INFO) #define my_init_dynamic_array_ci(A,B,C,D) init_dynamic_array(A,B,C,D ORIG_CALLER_INFO) extern my_bool init_dynamic_array(DYNAMIC_ARRAY *array,uint element_size, - uint init_alloc,uint alloc_increment CALLER_INFO_PROTO); + uint init_alloc,uint alloc_increment + CALLER_INFO_PROTO); extern my_bool insert_dynamic(DYNAMIC_ARRAY *array,gptr element); extern byte *alloc_dynamic(DYNAMIC_ARRAY *array); extern byte *pop_dynamic(DYNAMIC_ARRAY*); @@ -733,7 +734,7 @@ extern void my_free_lock(byte *ptr,myf flags); #define my_free_lock(A,B) my_free((A),(B)) #endif #define alloc_root_inited(A) ((A)->min_malloc != 0) -#define clear_alloc_root(A) bzero((void *) (A), sizeof(MEM_ROOT)) +#define clear_alloc_root(A) { (A)->free= (A)->used= (A)->pre_alloc= 0; } extern void init_alloc_root(MEM_ROOT *mem_root, uint block_size, uint pre_alloc_size); extern gptr alloc_root(MEM_ROOT *mem_root,unsigned int Size); diff --git a/mysql-test/r/bdb.result b/mysql-test/r/bdb.result index d510ec53dfc..6713d90e4f9 100644 --- a/mysql-test/r/bdb.result +++ b/mysql-test/r/bdb.result @@ -146,7 +146,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref level level 1 const 1 Using index explain select level,id,parent_id from t1 where level=1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref level level 1 const 1 Using where +1 SIMPLE t1 ref level level 1 const 1 select level,id from t1 where level=1; level id 1 1002 diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index 92c825f547d..8131399872a 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -298,7 +298,7 @@ select * from t3; id name create table if not exists t3 like t1; Warnings: -Warning 1050 Table 't3' already exists +Note 1050 Table 't3' already exists select @@warning_count; @@warning_count 1 diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index 2ec389032f7..9af8fa8c8cc 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -586,7 +586,7 @@ id select_type table type possible_keys key key_len ref rows Extra explain select max(t1.a3), min(t2.a2) from t1, t2 where t1.a2 = 2 and t1.a3 < 'MIN' and t2.a3 > 'CA'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref k1 k1 3 const 2 Using where; Using index +1 SIMPLE t1 range k1 k1 7 NULL 1 Using where; Using index 1 SIMPLE t2 range k1 k1 3 NULL 4 Using where; Using index explain select min(a4 - 0.01) from t1; diff --git a/mysql-test/r/func_if.result b/mysql-test/r/func_if.result index 307d1021cdb..83e103fe57d 100644 --- a/mysql-test/r/func_if.result +++ b/mysql-test/r/func_if.result @@ -43,7 +43,7 @@ explain extended select if(u=1,st,binary st) s from t1 where st like "%a%" order id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 7 Using where; Using filesort Warnings: -Note 1003 select if((test.t1.u = 1),test.t1.st,cast(test.t1.st as char charset binary)) AS `s` from test.t1 where (test.t1.st like _latin1'%a%') order by if((test.t1.u = 1),test.t1.st,cast(test.t1.st as char charset binary)) +Note 1003 select if((`test`.`t1`.`u` = 1),`test`.`t1`.`st`,cast(`test`.`t1`.`st` as char charset binary)) AS `s` from `test`.`t1` where (`test`.`t1`.`st` like _latin1'%a%') order by if((`test`.`t1`.`u` = 1),`test`.`t1`.`st`,cast(`test`.`t1`.`st` as char charset binary)) select nullif(u=0, 'test') from t1; nullif(u=0, 'test') NULL diff --git a/mysql-test/r/heap_btree.result b/mysql-test/r/heap_btree.result index 03625d19d04..8f9349ce316 100644 --- a/mysql-test/r/heap_btree.result +++ b/mysql-test/r/heap_btree.result @@ -73,7 +73,7 @@ engine=heap; insert into t1 values (1,1),(2,2),(1,3),(2,4),(2,5),(2,6); explain select * from t1 where x=1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref x x 4 const 1 Using where +1 SIMPLE t1 ref x x 4 const 1 select * from t1 where x=1; x y 1 1 @@ -134,7 +134,7 @@ a b 1 1 explain select * from tx where b=x; id select_type table type possible_keys key key_len ref rows Extra -x SIMPLE tx ref b b x const x Using where +x SIMPLE tx ref b b x const x drop table t1; create table t1 (id int unsigned not null, primary key using BTREE (id)) engine=HEAP; insert into t1 values(1); diff --git a/mysql-test/r/index_merge.result b/mysql-test/r/index_merge.result index f3654db030c..cb4b285430c 100644 --- a/mysql-test/r/index_merge.result +++ b/mysql-test/r/index_merge.result @@ -264,11 +264,11 @@ select * from t0,t1 where (t0.key1=t1.key1) and (t0.key1=3 or t0.key2<4) and t1.key1=2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t0 ref i1,i2 i1 4 const 1 Using where -1 SIMPLE t1 ref i1 i1 4 const 1 Using where +1 SIMPLE t1 ref i1 i1 4 const 1 explain select * from t0,t1 where t0.key1 = 5 and (t1.key1 = t0.key1 or t1.key8 = t0.key1); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 ref i1 i1 4 const 1 Using where +1 SIMPLE t0 ref i1 i1 4 const 1 1 SIMPLE t1 index_merge i1,i8 i1,i8 4,4 NULL 2 Using union(i1,i8); Using where explain select * from t0,t1 where t0.key1 < 3 and (t1.key1 = t0.key1 or t1.key8 = t0.key1); diff --git a/mysql-test/r/index_merge_ror.result b/mysql-test/r/index_merge_ror.result index 15ad1026ca0..6fc9dddef88 100644 --- a/mysql-test/r/index_merge_ror.result +++ b/mysql-test/r/index_merge_ror.result @@ -130,7 +130,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,stb_swt1a_2b,stb_swt1b,st_b st_b 4 const 14720 Using where explain select * from t1 where st_a=1 and swt1a=1 and swt2a=1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a sta_swt12a 12 const,const,const 958 Using where +1 SIMPLE t1 ref sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a sta_swt12a 12 const,const,const 958 explain select * from t1 where st_b=1 and swt1b=1 and swt2b=1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref stb_swt1a_2b,stb_swt1b,st_b stb_swt1b 8 const,const 3757 Using where diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result index b407c1537bc..b46626d0322 100644 --- a/mysql-test/r/innodb.result +++ b/mysql-test/r/innodb.result @@ -141,13 +141,13 @@ id parent_id level 1015 102 2 explain select level from t1 where level=1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref level level 1 const # Using where; Using index +1 SIMPLE t1 ref level level 1 const # Using index explain select level,id from t1 where level=1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref level level 1 const # Using where; Using index +1 SIMPLE t1 ref level level 1 const # Using index explain select level,id,parent_id from t1 where level=1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref level level 1 const # Using where +1 SIMPLE t1 ref level level 1 const # select level,id from t1 where level=1; level id 1 1002 @@ -609,7 +609,7 @@ id parent_id level 1016 102 2 explain select level from t1 where level=1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref level level 1 const # Using where; Using index +1 SIMPLE t1 ref level level 1 const # Using index select level,id from t1 where level=1; level id 1 1004 diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index ec377b92371..6bafe533fac 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -628,7 +628,7 @@ insert into t2 values (10,1),(20,2),(30,3); explain select * from t2 left join t1 on t1.fooID = t2.fooID and t1.fooID = 30; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index NULL PRIMARY 4 NULL 3 Using index -1 SIMPLE t1 const PRIMARY PRIMARY 2 const 1 Using where; Using index +1 SIMPLE t1 const PRIMARY PRIMARY 2 const 1 Using index select * from t2 left join t1 on t1.fooID = t2.fooID and t1.fooID = 30; fooID barID fooID 10 1 NULL @@ -703,13 +703,13 @@ explain select s.*, '*', m.*, (s.match_1_h - m.home) UUX from order by m.match_id desc; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE s ALL NULL NULL NULL NULL 10 -1 SIMPLE m const match_id,match_id_2 match_id 1 const 1 Using where +1 SIMPLE m const match_id,match_id_2 match_id 1 const 1 explain select s.*, '*', m.*, (s.match_1_h - m.home) UUX from (t2 s left join t1 m on m.match_id = 1) order by UUX desc; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE s ALL NULL NULL NULL NULL 10 Using temporary; Using filesort -1 SIMPLE m const match_id,match_id_2 match_id 1 const 1 Using where +1 SIMPLE m const match_id,match_id_2 match_id 1 const 1 select s.*, '*', m.*, (s.match_1_h - m.home) UUX from (t2 s left join t1 m on m.match_id = 1) order by UUX desc; @@ -729,7 +729,7 @@ t2 s straight_join t1 m where m.match_id = 1 order by UUX desc; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE s ALL NULL NULL NULL NULL 10 Using temporary; Using filesort -1 SIMPLE m const match_id,match_id_2 match_id 1 const 1 Using where +1 SIMPLE m const match_id,match_id_2 match_id 1 const 1 select s.*, '*', m.*, (s.match_1_h - m.home) UUX from t2 s straight_join t1 m where m.match_id = 1 order by UUX desc; diff --git a/mysql-test/r/mysqlbinlog2.result b/mysql-test/r/mysqlbinlog2.result index 3c1b85e05a1..9fe4edf7b82 100644 --- a/mysql-test/r/mysqlbinlog2.result +++ b/mysql-test/r/mysqlbinlog2.result @@ -18,6 +18,8 @@ insert into t1 values(null, "f"); /*!40019 SET @@session.max_insert_delayed_threads=0*/; use test; SET TIMESTAMP=1579609942; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; create table t1 (a int auto_increment not null primary key, b char(3)); SET INSERT_ID=1; SET TIMESTAMP=1579609942; @@ -40,6 +42,8 @@ insert into t1 values(null, "e"); SET INSERT_ID=1; use test; SET TIMESTAMP=1579609942; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; insert into t1 values(null, "a"); SET INSERT_ID=2; SET TIMESTAMP=1579609942; @@ -58,6 +62,8 @@ insert into t1 values(null, "e"); /*!40019 SET @@session.max_insert_delayed_threads=0*/; use test; SET TIMESTAMP=1579609946; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; insert into t1 values(null, "d"); SET INSERT_ID=5; SET TIMESTAMP=1579609946; @@ -67,6 +73,8 @@ insert into t1 values(null, "e"); /*!40019 SET @@session.max_insert_delayed_threads=0*/; use test; SET TIMESTAMP=1579609942; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; create table t1 (a int auto_increment not null primary key, b char(3)); SET INSERT_ID=1; SET TIMESTAMP=1579609942; @@ -84,6 +92,8 @@ SET INSERT_ID=4; SET INSERT_ID=3; use test; SET TIMESTAMP=1579609944; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; insert into t1 values(null, "c"); SET INSERT_ID=4; SET TIMESTAMP=1579609946; @@ -96,6 +106,8 @@ insert into t1 values(null, "e"); /*!40019 SET @@session.max_insert_delayed_threads=0*/; use test; SET TIMESTAMP=1579609942; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; create table t1 (a int auto_increment not null primary key, b char(3)); SET INSERT_ID=1; SET TIMESTAMP=1579609942; @@ -108,6 +120,8 @@ insert into t1 values(null, "b"); /*!40019 SET @@session.max_insert_delayed_threads=0*/; use test; SET TIMESTAMP=1579609942; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; create table t1 (a int auto_increment not null primary key, b char(3)); SET INSERT_ID=1; SET TIMESTAMP=1579609942; @@ -127,6 +141,8 @@ insert into t1 values(null, "e"); SET INSERT_ID=6; use test; SET TIMESTAMP=1579609943; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; insert into t1 values(null, "f"); --- offset -- @@ -134,6 +150,8 @@ insert into t1 values(null, "f"); SET INSERT_ID=1; use test; SET TIMESTAMP=1579609942; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; insert into t1 values(null, "a"); SET INSERT_ID=2; SET TIMESTAMP=1579609942; @@ -150,12 +168,16 @@ insert into t1 values(null, "e"); SET INSERT_ID=6; use test; SET TIMESTAMP=1579609943; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; insert into t1 values(null, "f"); --- start-position -- /*!40019 SET @@session.max_insert_delayed_threads=0*/; use test; SET TIMESTAMP=1579609946; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; insert into t1 values(null, "d"); SET INSERT_ID=5; SET TIMESTAMP=1579609946; @@ -163,12 +185,16 @@ insert into t1 values(null, "e"); SET INSERT_ID=6; use test; SET TIMESTAMP=1579609943; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; insert into t1 values(null, "f"); --- stop-position -- /*!40019 SET @@session.max_insert_delayed_threads=0*/; use test; SET TIMESTAMP=1579609942; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; create table t1 (a int auto_increment not null primary key, b char(3)); SET INSERT_ID=1; SET TIMESTAMP=1579609942; @@ -192,6 +218,8 @@ SET INSERT_ID=6; SET INSERT_ID=3; use test; SET TIMESTAMP=1579609944; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; insert into t1 values(null, "c"); SET INSERT_ID=4; SET TIMESTAMP=1579609946; @@ -202,12 +230,16 @@ insert into t1 values(null, "e"); SET INSERT_ID=6; use test; SET TIMESTAMP=1579609943; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; insert into t1 values(null, "f"); --- stop-datetime -- /*!40019 SET @@session.max_insert_delayed_threads=0*/; use test; SET TIMESTAMP=1579609942; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; create table t1 (a int auto_increment not null primary key, b char(3)); SET INSERT_ID=1; SET TIMESTAMP=1579609942; @@ -220,6 +252,8 @@ insert into t1 values(null, "b"); /*!40019 SET @@session.max_insert_delayed_threads=0*/; use test; SET TIMESTAMP=1579609942; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; create table t1 (a int auto_increment not null primary key, b char(3)); SET INSERT_ID=1; SET TIMESTAMP=1579609942; @@ -242,6 +276,8 @@ insert into t1 values(null, "e"); SET INSERT_ID=1; use test; SET TIMESTAMP=1579609942; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; insert into t1 values(null, "a"); SET INSERT_ID=2; SET TIMESTAMP=1579609942; @@ -260,6 +296,8 @@ insert into t1 values(null, "e"); /*!40019 SET @@session.max_insert_delayed_threads=0*/; use test; SET TIMESTAMP=1579609946; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; insert into t1 values(null, "d"); SET INSERT_ID=5; SET TIMESTAMP=1579609946; @@ -269,6 +307,8 @@ insert into t1 values(null, "e"); /*!40019 SET @@session.max_insert_delayed_threads=0*/; use test; SET TIMESTAMP=1579609942; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; create table t1 (a int auto_increment not null primary key, b char(3)); SET INSERT_ID=1; SET TIMESTAMP=1579609942; @@ -286,6 +326,8 @@ SET INSERT_ID=4; SET INSERT_ID=3; use test; SET TIMESTAMP=1579609944; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; insert into t1 values(null, "c"); SET INSERT_ID=4; SET TIMESTAMP=1579609946; @@ -298,6 +340,8 @@ insert into t1 values(null, "e"); /*!40019 SET @@session.max_insert_delayed_threads=0*/; use test; SET TIMESTAMP=1579609942; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; create table t1 (a int auto_increment not null primary key, b char(3)); SET INSERT_ID=1; SET TIMESTAMP=1579609942; @@ -310,6 +354,8 @@ insert into t1 values(null, "b"); /*!40019 SET @@session.max_insert_delayed_threads=0*/; use test; SET TIMESTAMP=1579609942; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; create table t1 (a int auto_increment not null primary key, b char(3)); SET INSERT_ID=1; SET TIMESTAMP=1579609942; @@ -329,6 +375,8 @@ insert into t1 values(null, "e"); SET INSERT_ID=6; use test; SET TIMESTAMP=1579609943; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; insert into t1 values(null, "f"); --- offset -- @@ -336,6 +384,8 @@ insert into t1 values(null, "f"); SET INSERT_ID=1; use test; SET TIMESTAMP=1579609942; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; insert into t1 values(null, "a"); SET INSERT_ID=2; SET TIMESTAMP=1579609942; @@ -352,12 +402,16 @@ insert into t1 values(null, "e"); SET INSERT_ID=6; use test; SET TIMESTAMP=1579609943; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; insert into t1 values(null, "f"); --- start-position -- /*!40019 SET @@session.max_insert_delayed_threads=0*/; use test; SET TIMESTAMP=1579609946; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; insert into t1 values(null, "d"); SET INSERT_ID=5; SET TIMESTAMP=1579609946; @@ -365,12 +419,16 @@ insert into t1 values(null, "e"); SET INSERT_ID=6; use test; SET TIMESTAMP=1579609943; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; insert into t1 values(null, "f"); --- stop-position -- /*!40019 SET @@session.max_insert_delayed_threads=0*/; use test; SET TIMESTAMP=1579609942; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; create table t1 (a int auto_increment not null primary key, b char(3)); SET INSERT_ID=1; SET TIMESTAMP=1579609942; @@ -394,6 +452,8 @@ SET INSERT_ID=6; SET INSERT_ID=3; use test; SET TIMESTAMP=1579609944; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; insert into t1 values(null, "c"); SET INSERT_ID=4; SET TIMESTAMP=1579609946; @@ -404,12 +464,16 @@ insert into t1 values(null, "e"); SET INSERT_ID=6; use test; SET TIMESTAMP=1579609943; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; insert into t1 values(null, "f"); --- stop-datetime -- /*!40019 SET @@session.max_insert_delayed_threads=0*/; use test; SET TIMESTAMP=1579609942; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; create table t1 (a int auto_increment not null primary key, b char(3)); SET INSERT_ID=1; SET TIMESTAMP=1579609942; @@ -422,6 +486,8 @@ insert into t1 values(null, "b"); /*!40019 SET @@session.max_insert_delayed_threads=0*/; use test; SET TIMESTAMP=1579609942; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; create table t1 (a int auto_increment not null primary key, b char(3)); SET INSERT_ID=1; SET TIMESTAMP=1579609942; diff --git a/mysql-test/r/negation_elimination.result b/mysql-test/r/negation_elimination.result index 9193a125cd1..a7dbe0d9052 100644 --- a/mysql-test/r/negation_elimination.result +++ b/mysql-test/r/negation_elimination.result @@ -387,5 +387,5 @@ explain extended select a, not(not(a)), not(a <= 2 and not(a)), not(a not like " id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL a 5 NULL 5 Using where; Using index Warnings: -Note 1003 select test.t1.a AS `a`,(test.t1.a <> 0) AS `not(not(a))`,((test.t1.a > 2) or test.t1.a) AS `not(a <= 2 and not(a))`,(test.t1.a like _latin1'1') AS `not(a not like "1")`,(test.t1.a in (1,2)) AS `not (a not in (1,2))`,(test.t1.a = 2) AS `not(a != 2)` from test.t1 where test.t1.a having test.t1.a +Note 1003 select `test`.`t1`.`a` AS `a`,(`test`.`t1`.`a` <> 0) AS `not(not(a))`,((`test`.`t1`.`a` > 2) or `test`.`t1`.`a`) AS `not(a <= 2 and not(a))`,(`test`.`t1`.`a` like _latin1'1') AS `not(a not like "1")`,(`test`.`t1`.`a` in (1,2)) AS `not (a not in (1,2))`,(`test`.`t1`.`a` = 2) AS `not(a != 2)` from `test`.`t1` where `test`.`t1`.`a` having `test`.`t1`.`a` drop table t1; diff --git a/mysql-test/r/null.result b/mysql-test/r/null.result index bd90b3fe3f3..ced80a033e7 100644 --- a/mysql-test/r/null.result +++ b/mysql-test/r/null.result @@ -167,11 +167,20 @@ insert into t1 select i*2 from t1; insert into t1 select i*2 from t1; insert into t1 select i*2 from t1; insert into t1 select i*2 from t1; +insert into t1 values(null); explain select * from t1 where i=2 or i is null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref_or_null i i 5 const 10 Using where; Using index +select count(*) from t1 where i=2 or i is null; +count(*) +10 alter table t1 change i i int not null; +Warnings: +Warning 1265 Data truncated for column 'i' at row 513 explain select * from t1 where i=2 or i is null; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref i i 4 const 7 Using where; Using index +1 SIMPLE t1 ref i i 4 const 8 Using index +select count(*) from t1 where i=2 or i is null; +count(*) +9 drop table t1; diff --git a/mysql-test/r/null_key.result b/mysql-test/r/null_key.result index 3044b188d06..7980c3f7f91 100644 --- a/mysql-test/r/null_key.result +++ b/mysql-test/r/null_key.result @@ -33,10 +33,10 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref a,b a 5 const 2 Using where; Using index explain select * from t1 where a > 1 and a < 3 limit 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index a a 9 NULL 12 Using where; Using index +1 SIMPLE t1 range a a 5 NULL 1 Using where; Using index explain select * from t1 where a > 8 and a < 9; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index a a 9 NULL 12 Using where; Using index +1 SIMPLE t1 range a a 5 NULL 1 Using where; Using index select * from t1 where a is null; a b NULL 7 @@ -66,6 +66,11 @@ a b NULL 7 NULL 9 NULL 9 +select * from t1 where a > 1 and a < 3 limit 1; +a b +2 2 +select * from t1 where a > 8 and a < 9; +a b 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)); @@ -153,12 +158,12 @@ a b 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 3 Using where; Using index +1 SIMPLE t1 ref_or_null a,b a 5 const 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 +NULL 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 diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index a431807a560..e010f4a42e2 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -328,7 +328,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 9 NULL 5 Using where; Using index explain select * from t1 where a = 2 and b < 2 order by a desc,b desc; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref a a 4 const 2 Using where; Using index +1 SIMPLE t1 range a a 9 NULL 2 Using where; Using index explain select * from t1 where a = 1 order by b desc; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref a a 4 const 5 Using where; Using index diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index 01ee018a434..31c65f64256 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -257,6 +257,11 @@ explain select * from t1, t2 where (t1.key1 <t2.keya + 1) and t2.keya=3; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ref j1 j1 4 const 1 Using index 1 SIMPLE t1 ALL i1 NULL NULL NULL 4 Range checked for each record (index map: 0x1) +explain select * from t1 force index(i1), t2 force index(j1) where +(t1.key1 <t2.keya + 1) and t2.keya=3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ref j1 j1 4 const 1 Using index +1 SIMPLE t1 ALL i1 NULL NULL NULL 4 Range checked for each record (index map: 0x1) DROP TABLE t1,t2; CREATE TABLE t1 ( a int(11) default NULL, @@ -533,8 +538,7 @@ count(*) 0 drop table t1; create table t1 (x bigint unsigned not null primary key) engine=innodb; -insert into t1(x) values (0xfffffffffffffff0); -insert into t1(x) values (0xfffffffffffffff1); +insert into t1(x) values (0xfffffffffffffff0),(0xfffffffffffffff1); select * from t1; x 18446744073709551600 @@ -554,6 +558,9 @@ count(*) select count(*) from t1 where x = -16; count(*) 0 +explain select count(*) from t1 where x > -16; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY PRIMARY 8 NULL 1 Using where; Using index select count(*) from t1 where x > -16; count(*) 1 diff --git a/mysql-test/r/rpl_charset.result b/mysql-test/r/rpl_charset.result index d961173f849..a427e77a18d 100644 --- a/mysql-test/r/rpl_charset.result +++ b/mysql-test/r/rpl_charset.result @@ -105,62 +105,62 @@ drop database mysqltest2; drop database mysqltest3; show binlog events from 95; Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 95 Query 1 181 use `test`; drop database if exists mysqltest2 -master-bin.000001 181 Query 1 267 use `test`; drop database if exists mysqltest3 -master-bin.000001 267 Query 1 366 use `test`; create database mysqltest2 character set latin2 -master-bin.000001 366 Query 1 522 use `test`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=8,COLLATION_DATABASE=8,COLLATION_SERVER=30 -master-bin.000001 522 Query 1 600 use `test`; create database mysqltest3 -master-bin.000001 600 Query 1 756 use `test`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=8,COLLATION_DATABASE=8,COLLATION_SERVER=64 -master-bin.000001 756 Query 1 832 use `test`; drop database mysqltest3 -master-bin.000001 832 Query 1 988 use `test`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=8,COLLATION_DATABASE=8,COLLATION_SERVER=64 -master-bin.000001 988 Query 1 1066 use `test`; create database mysqltest3 -master-bin.000001 1066 Query 1 1223 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=8,COLLATION_DATABASE=9,COLLATION_SERVER=64 -master-bin.000001 1223 Query 1 1347 use `mysqltest2`; create table t1 (a int auto_increment primary key, b varchar(100)) -master-bin.000001 1347 Query 1 1505 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=4,COLLATION_CONNECTION=27,COLLATION_DATABASE=9,COLLATION_SERVER=64 -master-bin.000001 1505 Intvar 1 1533 INSERT_ID=1 -master-bin.000001 1533 Query 1 1640 use `mysqltest2`; insert into t1 (b) values(@@character_set_server) -master-bin.000001 1640 Query 1 1798 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=4,COLLATION_CONNECTION=27,COLLATION_DATABASE=9,COLLATION_SERVER=64 -master-bin.000001 1798 Intvar 1 1826 INSERT_ID=2 -master-bin.000001 1826 Query 1 1929 use `mysqltest2`; insert into t1 (b) values(@@collation_server) -master-bin.000001 1929 Query 1 2087 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=4,COLLATION_CONNECTION=27,COLLATION_DATABASE=9,COLLATION_SERVER=64 -master-bin.000001 2087 Intvar 1 2115 INSERT_ID=3 -master-bin.000001 2115 Query 1 2222 use `mysqltest2`; insert into t1 (b) values(@@character_set_client) -master-bin.000001 2222 Query 1 2380 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=4,COLLATION_CONNECTION=27,COLLATION_DATABASE=9,COLLATION_SERVER=64 -master-bin.000001 2380 Intvar 1 2408 INSERT_ID=4 -master-bin.000001 2408 Query 1 2519 use `mysqltest2`; insert into t1 (b) values(@@character_set_connection) -master-bin.000001 2519 Query 1 2677 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=4,COLLATION_CONNECTION=27,COLLATION_DATABASE=9,COLLATION_SERVER=64 -master-bin.000001 2677 Intvar 1 2705 INSERT_ID=5 -master-bin.000001 2705 Query 1 2812 use `mysqltest2`; insert into t1 (b) values(@@collation_connection) -master-bin.000001 2812 Query 1 2969 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=5,COLLATION_DATABASE=9,COLLATION_SERVER=64 -master-bin.000001 2969 Query 1 3044 use `mysqltest2`; truncate table t1 -master-bin.000001 3044 Query 1 3201 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=5,COLLATION_DATABASE=9,COLLATION_SERVER=64 -master-bin.000001 3201 Intvar 1 3229 INSERT_ID=1 -master-bin.000001 3229 Query 1 3336 use `mysqltest2`; insert into t1 (b) values(@@collation_connection) -master-bin.000001 3336 Query 1 3493 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=5,COLLATION_DATABASE=9,COLLATION_SERVER=64 -master-bin.000001 3493 Intvar 1 3521 INSERT_ID=2 -master-bin.000001 3521 Query 1 3631 use `mysqltest2`; insert into t1 (b) values(LEAST("Müller","Muffler")) -master-bin.000001 3631 Query 1 3789 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64 -master-bin.000001 3789 Intvar 1 3817 INSERT_ID=3 -master-bin.000001 3817 Query 1 3924 use `mysqltest2`; insert into t1 (b) values(@@collation_connection) -master-bin.000001 3924 Query 1 4082 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64 -master-bin.000001 4082 Intvar 1 4110 INSERT_ID=4 -master-bin.000001 4110 Query 1 4220 use `mysqltest2`; insert into t1 (b) values(LEAST("Müller","Muffler")) -master-bin.000001 4220 Query 1 4378 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64 -master-bin.000001 4378 Intvar 1 4406 INSERT_ID=74 -master-bin.000001 4406 Create_file 1 5074 db=mysqltest2;table=t1;file_id=1;block_len=581 -master-bin.000001 5074 Query 1 5232 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64 -master-bin.000001 5232 Intvar 1 5260 INSERT_ID=5 -master-bin.000001 5260 Exec_load 1 5283 ;file_id=1 -master-bin.000001 5283 Query 1 5441 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64 -master-bin.000001 5441 Query 1 5516 use `mysqltest2`; truncate table t1 -master-bin.000001 5516 Query 1 5674 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64 -master-bin.000001 5674 Intvar 1 5702 INSERT_ID=1 -master-bin.000001 5702 User var 1 5742 @`a`=_cp850 0x4DFC6C6C6572 COLLATE cp850_general_ci -master-bin.000001 5742 Query 1 5840 use `mysqltest2`; insert into t1 (b) values(collation(@a)) -master-bin.000001 5840 Query 1 5998 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64 -master-bin.000001 5998 Query 1 6075 use `mysqltest2`; drop database mysqltest2 -master-bin.000001 6075 Query 1 6228 SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64 -master-bin.000001 6228 Query 1 6300 drop database mysqltest3 +master-bin.000001 # Query 1 # use `test`; drop database if exists mysqltest2 +master-bin.000001 # Query 1 # use `test`; drop database if exists mysqltest3 +master-bin.000001 # Query 1 # use `test`; create database mysqltest2 character set latin2 +master-bin.000001 # Query 1 # use `test`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=8,COLLATION_DATABASE=8,COLLATION_SERVER=30 +master-bin.000001 # Query 1 # use `test`; create database mysqltest3 +master-bin.000001 # Query 1 # use `test`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=8,COLLATION_DATABASE=8,COLLATION_SERVER=64 +master-bin.000001 # Query 1 # use `test`; drop database mysqltest3 +master-bin.000001 # Query 1 # use `test`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=8,COLLATION_DATABASE=8,COLLATION_SERVER=64 +master-bin.000001 # Query 1 # use `test`; create database mysqltest3 +master-bin.000001 # Query 1 # use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=8,COLLATION_DATABASE=9,COLLATION_SERVER=64 +master-bin.000001 # Query 1 # use `mysqltest2`; create table t1 (a int auto_increment primary key, b varchar(100)) +master-bin.000001 # Query 1 # use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=4,COLLATION_CONNECTION=27,COLLATION_DATABASE=9,COLLATION_SERVER=64 +master-bin.000001 # Intvar 1 # INSERT_ID=1 +master-bin.000001 # Query 1 # use `mysqltest2`; insert into t1 (b) values(@@character_set_server) +master-bin.000001 # Query 1 # use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=4,COLLATION_CONNECTION=27,COLLATION_DATABASE=9,COLLATION_SERVER=64 +master-bin.000001 # Intvar 1 # INSERT_ID=2 +master-bin.000001 # Query 1 # use `mysqltest2`; insert into t1 (b) values(@@collation_server) +master-bin.000001 # Query 1 # use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=4,COLLATION_CONNECTION=27,COLLATION_DATABASE=9,COLLATION_SERVER=64 +master-bin.000001 # Intvar 1 # INSERT_ID=3 +master-bin.000001 # Query 1 # use `mysqltest2`; insert into t1 (b) values(@@character_set_client) +master-bin.000001 # Query 1 # use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=4,COLLATION_CONNECTION=27,COLLATION_DATABASE=9,COLLATION_SERVER=64 +master-bin.000001 # Intvar 1 # INSERT_ID=4 +master-bin.000001 # Query 1 # use `mysqltest2`; insert into t1 (b) values(@@character_set_connection) +master-bin.000001 # Query 1 # use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=4,COLLATION_CONNECTION=27,COLLATION_DATABASE=9,COLLATION_SERVER=64 +master-bin.000001 # Intvar 1 # INSERT_ID=5 +master-bin.000001 # Query 1 # use `mysqltest2`; insert into t1 (b) values(@@collation_connection) +master-bin.000001 # Query 1 # use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=5,COLLATION_DATABASE=9,COLLATION_SERVER=64 +master-bin.000001 # Query 1 # use `mysqltest2`; truncate table t1 +master-bin.000001 # Query 1 # use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=5,COLLATION_DATABASE=9,COLLATION_SERVER=64 +master-bin.000001 # Intvar 1 # INSERT_ID=1 +master-bin.000001 # Query 1 # use `mysqltest2`; insert into t1 (b) values(@@collation_connection) +master-bin.000001 # Query 1 # use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=5,COLLATION_DATABASE=9,COLLATION_SERVER=64 +master-bin.000001 # Intvar 1 # INSERT_ID=2 +master-bin.000001 # Query 1 # use `mysqltest2`; insert into t1 (b) values(LEAST("Müller","Muffler")) +master-bin.000001 # Query 1 # use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64 +master-bin.000001 # Intvar 1 # INSERT_ID=3 +master-bin.000001 # Query 1 # use `mysqltest2`; insert into t1 (b) values(@@collation_connection) +master-bin.000001 # Query 1 # use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64 +master-bin.000001 # Intvar 1 # INSERT_ID=4 +master-bin.000001 # Query 1 # use `mysqltest2`; insert into t1 (b) values(LEAST("Müller","Muffler")) +master-bin.000001 # Query 1 # use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64 +master-bin.000001 # Intvar 1 # INSERT_ID=74 +master-bin.000001 # Create_file 1 # db=mysqltest2;table=t1;file_id=1;block_len=581 +master-bin.000001 # Query 1 # use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64 +master-bin.000001 # Intvar 1 # INSERT_ID=5 +master-bin.000001 # Exec_load 1 # ;file_id=1 +master-bin.000001 # Query 1 # use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64 +master-bin.000001 # Query 1 # use `mysqltest2`; truncate table t1 +master-bin.000001 # Query 1 # use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64 +master-bin.000001 # Intvar 1 # INSERT_ID=1 +master-bin.000001 # User var 1 # @`a`=_cp850 0x4DFC6C6C6572 COLLATE cp850_general_ci +master-bin.000001 # Query 1 # use `mysqltest2`; insert into t1 (b) values(collation(@a)) +master-bin.000001 # Query 1 # use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64 +master-bin.000001 # Query 1 # use `mysqltest2`; drop database mysqltest2 +master-bin.000001 # Query 1 # SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64 +master-bin.000001 # Query 1 # drop database mysqltest3 set global character_set_server=latin2; ERROR HY000: Binary logging and replication forbid changing the global server character set or collation set global character_set_server=latin2; diff --git a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result index 6b36b3bb2bf..46fe452d9e4 100644 --- a/mysql-test/r/sp-error.result +++ b/mysql-test/r/sp-error.result @@ -44,7 +44,7 @@ call foo()| ERROR 42000: PROCEDURE test.foo does not exist drop procedure if exists foo| Warnings: -Warning 1304 PROCEDURE foo does not exist +Note 1305 PROCEDURE foo does not exist show create procedure foo| ERROR 42000: PROCEDURE foo does not exist create procedure foo() @@ -121,7 +121,7 @@ declare y int; set x = y; end| Warnings: -Warning 1310 Referring to uninitialized variable y +Warning 1311 Referring to uninitialized variable y drop procedure foo| create procedure foo() return 42| diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index afd8c3430b3..7ea95a067c4 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -1,6 +1,28 @@ use test; -drop table if exists t1; -drop table if exists t2; +drop table if exists t1,t2,t3,t4; +drop procedure if exists goto1; +drop procedure if exists goto2; +drop procedure if exists goto3; +drop procedure if exists goto4; +drop procedure if exists goto5; +drop procedure if exists goto6; +drop procedure if exists into_outfile; +drop procedure if exists into_dumpfile; +drop procedure if exists create_select; +drop procedure if exists bar; +drop procedure if exists hndlr1; +drop procedure if exists hndlr2; +drop procedure if exists hndlr3; +drop procedure if exists hndlr4; +drop procedure if exists cur1; +drop procedure if exists cur2; +drop procedure if exists chistics; +drop procedure if exists chistics2; +drop procedure if exists modes; +drop procedure if exists dummy; +drop procedure if exists bug; +drop procedure if exists bug2; +drop function if exists fac; create table t1 ( id char(16) not null, data int not null @@ -78,7 +100,6 @@ foo 1 kaka 3 delete from t1| drop procedure setcontext| -drop table if exists t3| create table t3 ( d date, i int, f double, s varchar(32) )| create procedure nullset() begin @@ -962,7 +983,7 @@ select row_count()| row_count() -1 drop procedure rc| -create procedure bug822(a_id char(16), a_data int) +create procedure bug(a_id char(16), a_data int) begin declare n int; select count(*) into n from t1 where id = a_id and data = a_data; @@ -970,16 +991,16 @@ if n = 0 then insert into t1 (id, data) values (a_id, a_data); end if; end| -call bug822('foo', 42)| -call bug822('foo', 42)| -call bug822('bar', 666)| +call bug('foo', 42)| +call bug('foo', 42)| +call bug('bar', 666)| select * from t1| id data foo 42 bar 666 delete from t1| -drop procedure bug822| -create procedure bug1495() +drop procedure bug| +create procedure bug() begin declare x int; select data into x from t1 order by id limit 1; @@ -990,18 +1011,18 @@ insert into t1 values ("more", x+10); end if; end| insert into t1 values ('foo', 12)| -call bug1495()| +call bug()| delete from t1 where id='foo'| insert into t1 values ('bar', 7)| -call bug1495()| +call bug()| delete from t1 where id='bar'| select * from t1| id data less 2 more 17 delete from t1| -drop procedure bug1495| -create procedure bug1547(s char(16)) +drop procedure bug| +create procedure bug(s char(16)) begin declare x int; select data into x from t1 where s = id limit 1; @@ -1012,8 +1033,8 @@ insert into t1 values ("more", x+10); end if; end| insert into t1 values ("foo", 12), ("bar", 7)| -call bug1547("foo")| -call bug1547("bar")| +call bug("foo")| +call bug("bar")| select * from t1| id data foo 12 @@ -1021,34 +1042,34 @@ bar 7 less 2 more 17 delete from t1| -drop procedure bug1547| +drop procedure bug| drop table if exists t70| create table t70 (s1 int,s2 int)| insert into t70 values (1,2)| -create procedure bug1656(out p1 int, out p2 int) +create procedure bug(out p1 int, out p2 int) select * into p1, p1 from t70| -call bug1656(@1, @2)| +call bug(@1, @2)| select @1, @2| @1 @2 2 NULL drop table t70| -drop procedure bug1656| +drop procedure bug| drop table if exists t3| create table t3(a int)| -create procedure bug1862() +create procedure bug() begin insert into t3 values(2); flush tables; end| -call bug1862()| -call bug1862()| +call bug()| +call bug()| select * from t3| a 2 2 drop table t3| -drop procedure bug1862| -create procedure bug1874() +drop procedure bug| +create procedure bug() begin declare x int; declare y double; @@ -1062,7 +1083,7 @@ select avg(data) into y from t1; insert into t2 values ("avg", 0, y); end| insert into t1 (data) values (3), (1), (5), (9), (4)| -call bug1874()| +call bug()| select * from t2| s i d max 9 0 @@ -1071,8 +1092,8 @@ sum 22 0 avg 0 4.4 delete from t1| delete from t2| -drop procedure bug1874| -create procedure bug2260() +drop procedure bug| +create procedure bug() begin declare v1 int; declare c1 cursor for select data from t1; @@ -1082,11 +1103,11 @@ fetch c1 into v1; set @x2 = 2; close c1; end| -call bug2260()| +call bug()| select @x2| @x2 2 -drop procedure bug2260| +drop procedure bug| create procedure bug2267_1() begin show procedure status; @@ -1133,35 +1154,35 @@ drop procedure bug2267_1| drop procedure bug2267_2| drop procedure bug2267_3| drop procedure bug2267_4| -create procedure bug2227(x int) +create procedure bug(x int) begin declare y float default 2.6; declare z char(16) default "zzz"; select 1.3, x, y, 42, z; end| -call bug2227(9)| +call bug(9)| 1.3 x y 42 z 1.3 9 2.6 42 zzz -drop procedure bug2227| -create procedure bug2614() +drop procedure bug| +create procedure bug() begin drop table if exists t3; create table t3 (id int default '0' not null); insert into t3 select 12; insert into t3 select * from t3; end| -call bug2614()| -call bug2614()| +call bug()| +call bug()| drop table t3| -drop procedure bug2614| -create function bug2674 () returns int +drop procedure bug| +create function bug () returns int return @@sort_buffer_size| set @osbs = @@sort_buffer_size| set @@sort_buffer_size = 262000| -select bug2674()| -bug2674() +select bug()| +bug() 262000 -drop function bug2674| +drop function bug| set @@sort_buffer_size = @osbs| create procedure bug3259_1 () begin end| create procedure BUG3259_2 () begin end| @@ -1175,48 +1196,48 @@ call bUG3259_3()| drop procedure bUg3259_1| drop procedure BuG3259_2| drop procedure BUG3259_3| -create function bug2772() returns char(10) character set latin2 +create function bug() returns char(10) character set latin2 return 'a'| -select bug2772()| -bug2772() +select bug()| +bug() a -drop function bug2772| -create procedure bug2776_1(out x int) +drop function bug| +create procedure bug(out x int) begin declare v int; set v = default; set x = v; end| -create procedure bug2776_2(out x int) +create procedure bug2(out x int) begin declare v int default 42; set v = default; set x = v; end| set @x = 1| -call bug2776_1(@x)| +call bug(@x)| select @x| @x NULL -call bug2776_2(@x)| +call bug2(@x)| select @x| @x 42 -drop procedure bug2776_1| -drop procedure bug2776_2| +drop procedure bug| +drop procedure bug2| drop table if exists t3| create table t3 (s1 smallint)| insert into t3 values (123456789012)| Warnings: Warning 1264 Data truncated; out of range for column 's1' at row 1 -create procedure bug2780() +create procedure bug() begin declare exit handler for sqlwarning set @x = 1; set @x = 0; insert into t3 values (123456789012); insert into t3 values (0); end| -call bug2780()| +call bug()| select @x| @x 1 @@ -1224,13 +1245,11 @@ select * from t3| s1 32767 32767 -drop procedure bug2780| +drop procedure bug| drop table t3| -drop table if exists t3| create table t3 (content varchar(10) )| insert into t3 values ("test1")| insert into t3 values ("test2")| -drop table if exists t4| create table t4 (f1 int, rc int, t3 int)| create procedure bug1863(in1 int) begin @@ -1406,7 +1425,6 @@ select @a| @a 5 drop procedure bug3863| -drop table if exists t3| create table t3 ( id int(10) unsigned not null default 0, rid int(10) unsigned not null default 0, diff --git a/mysql-test/r/type_blob.result b/mysql-test/r/type_blob.result index 95bba1d4ec7..2f564112e40 100644 --- a/mysql-test/r/type_blob.result +++ b/mysql-test/r/type_blob.result @@ -9,9 +9,9 @@ d mediumtext YES NULL e longtext YES NULL CREATE TABLE t2 (a char(257), b varbinary(70000), c varchar(70000000)); Warnings: -Warning 1246 Converting column 'a' from CHAR to TEXT -Warning 1246 Converting column 'b' from CHAR to BLOB -Warning 1246 Converting column 'c' from CHAR to TEXT +Note 1246 Converting column 'a' from CHAR to TEXT +Note 1246 Converting column 'b' from CHAR to BLOB +Note 1246 Converting column 'c' from CHAR to TEXT show columns from t2; Field Type Null Key Default Extra a text YES NULL diff --git a/mysql-test/r/user_var.result b/mysql-test/r/user_var.result index 9bbfefeb34a..80d8af095cb 100644 --- a/mysql-test/r/user_var.result +++ b/mysql-test/r/user_var.result @@ -23,7 +23,11 @@ i @vv1:=if(sv1.i,1,0) @vv2:=if(sv2.i,1,0) @vv3:=if(sv3.i,1,0) @vv1+@vv2+@vv3 2 1 0 0 1 explain select * from t1 where i=@vv1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref i i 4 const 1 Using where +1 SIMPLE t1 ref i i 4 const 1 +select @vv1,i,v from t1 where i=@vv1; +@vv1 i v +1 1 1 +1 1 3 explain select * from t1 where @vv1:=@vv1+1 and i=@vv1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where @@ -32,7 +36,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL i 4 NULL 3 Using where; Using index explain select * from t1 where i=@vv1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref i i 4 const 1 Using where +1 SIMPLE t1 ref i i 4 const 1 drop table t1,t2; set @a=0,@b=0; select @a:=10, @b:=1, @a > @b, @a < @b; diff --git a/mysql-test/r/variables.result b/mysql-test/r/variables.result index a84cf733f21..399e6af848c 100644 --- a/mysql-test/r/variables.result +++ b/mysql-test/r/variables.result @@ -364,7 +364,7 @@ set sql_log_bin=1; set sql_log_off=1; set sql_log_update=1; Warnings: -Note 1314 The update log is deprecated and replaced by the binary log; SET SQL_LOG_UPDATE has been ignored +Note 1315 The update log is deprecated and replaced by the binary log; SET SQL_LOG_UPDATE has been ignored set sql_low_priority_updates=1; set sql_max_join_size=200; select @@sql_max_join_size,@@max_join_size; diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 18da4882894..ef3743877c6 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -761,7 +761,7 @@ set sql_updatable_view_key=LIMIT1; update v1 set x=x+1; update v2 set x=x+1; Warnings: -Note 1354 View being updated does not have complete key of underlying table in it +Note 1355 View being updated does not have complete key of underlying table in it update v1 set x=x+1 limit 1; update v2 set x=x+1 limit 1; ERROR HY000: The target table v2 of the UPDATE is not updatable @@ -769,7 +769,7 @@ set sql_updatable_view_key=NO; update v1 set x=x+1 limit 1; update v2 set x=x+1 limit 1; Warnings: -Note 1354 View being updated does not have complete key of underlying table in it +Note 1355 View being updated does not have complete key of underlying table in it set sql_updatable_view_key=DEFAULT; select * from t1; a b c @@ -979,7 +979,7 @@ create view v1 (a,a) as select 'a','a'; ERROR 42S21: Duplicate column name 'a' create procedure p1 () begin declare v int; create view v1 as select v; end;// Warnings: -Warning 1310 Referring to uninitialized variable v +Warning 1311 Referring to uninitialized variable v call p1(); ERROR HY000: View's SELECT contains a variable or parameter drop procedure p1; diff --git a/mysql-test/t/mysqlbinlog2.test b/mysql-test/t/mysqlbinlog2.test index c6cff7558d4..6a4c99c746d 100644 --- a/mysql-test/t/mysqlbinlog2.test +++ b/mysql-test/t/mysqlbinlog2.test @@ -46,11 +46,11 @@ select "--- offset --" as ""; --disable_query_log select "--- start-position --" as ""; --enable_query_log ---exec $MYSQL_BINLOG --short-form --start-position=497 $MYSQL_TEST_DIR/var/log/master-bin.000001 +--exec $MYSQL_BINLOG --short-form --start-position=601 $MYSQL_TEST_DIR/var/log/master-bin.000001 --disable_query_log select "--- stop-position --" as ""; --enable_query_log ---exec $MYSQL_BINLOG --short-form --stop-position=497 $MYSQL_TEST_DIR/var/log/master-bin.000001 +--exec $MYSQL_BINLOG --short-form --stop-position=601 $MYSQL_TEST_DIR/var/log/master-bin.000001 --disable_query_log select "--- start-datetime --" as ""; --enable_query_log @@ -75,11 +75,11 @@ select "--- offset --" as ""; --disable_query_log select "--- start-position --" as ""; --enable_query_log ---exec $MYSQL_BINLOG --short-form --start-position=497 $MYSQL_TEST_DIR/var/log/master-bin.000001 $MYSQL_TEST_DIR/var/log/master-bin.000002 +--exec $MYSQL_BINLOG --short-form --start-position=601 $MYSQL_TEST_DIR/var/log/master-bin.000001 $MYSQL_TEST_DIR/var/log/master-bin.000002 --disable_query_log select "--- stop-position --" as ""; --enable_query_log ---exec $MYSQL_BINLOG --short-form --stop-position=32 $MYSQL_TEST_DIR/var/log/master-bin.000001 $MYSQL_TEST_DIR/var/log/master-bin.000002 +--exec $MYSQL_BINLOG --short-form --stop-position=123 $MYSQL_TEST_DIR/var/log/master-bin.000001 $MYSQL_TEST_DIR/var/log/master-bin.000002 --disable_query_log select "--- start-datetime --" as ""; --enable_query_log @@ -102,11 +102,11 @@ select "--- offset --" as ""; --disable_query_log select "--- start-position --" as ""; --enable_query_log ---exec $MYSQL_BINLOG --short-form --start-position=497 --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 +--exec $MYSQL_BINLOG --short-form --start-position=601 --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 --disable_query_log select "--- stop-position --" as ""; --enable_query_log ---exec $MYSQL_BINLOG --short-form --stop-position=497 --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 +--exec $MYSQL_BINLOG --short-form --stop-position=601 --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 --disable_query_log select "--- start-datetime --" as ""; --enable_query_log @@ -129,11 +129,11 @@ select "--- offset --" as ""; --disable_query_log select "--- start-position --" as ""; --enable_query_log ---exec $MYSQL_BINLOG --short-form --start-position=497 --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 master-bin.000002 +--exec $MYSQL_BINLOG --short-form --start-position=601 --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 master-bin.000002 --disable_query_log select "--- stop-position --" as ""; --enable_query_log ---exec $MYSQL_BINLOG --short-form --stop-position=32 --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 master-bin.000002 +--exec $MYSQL_BINLOG --short-form --stop-position=123 --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 master-bin.000002 --disable_query_log select "--- start-datetime --" as ""; --enable_query_log diff --git a/mysql-test/t/null.test b/mysql-test/t/null.test index 7d30fd06ba7..027443c485a 100644 --- a/mysql-test/t/null.test +++ b/mysql-test/t/null.test @@ -100,9 +100,9 @@ drop table t1; select cast(NULL as signed); # -# Test case for bug #4256 +# IS NULL is unable to use index in range if column is declared not null +# (Bug #4256) # - create table t1(i int, key(i)); insert into t1 values(1); insert into t1 select i*2 from t1; @@ -114,8 +114,11 @@ insert into t1 select i*2 from t1; insert into t1 select i*2 from t1; insert into t1 select i*2 from t1; insert into t1 select i*2 from t1; +insert into t1 values(null); explain select * from t1 where i=2 or i is null; +select count(*) from t1 where i=2 or i is null; alter table t1 change i i int not null; explain select * from t1 where i=2 or i is null; +select count(*) from t1 where i=2 or i is null; drop table t1; diff --git a/mysql-test/t/null_key.test b/mysql-test/t/null_key.test index 9b346a181bf..d7f6a634d1e 100644 --- a/mysql-test/t/null_key.test +++ b/mysql-test/t/null_key.test @@ -26,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; +select * from t1 where a > 1 and a < 3 limit 1; +select * from t1 where a > 8 and a < 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)); diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test index 61886221fcf..0059cd5bd37 100644 --- a/mysql-test/t/range.test +++ b/mysql-test/t/range.test @@ -195,13 +195,15 @@ explain select count(*) from t1 where x in (1,2); drop table t1; # -# bug #1172 +# bug #1172: "Force index" option caused server crash # CREATE TABLE t1 (key1 int(11) NOT NULL default '0', KEY i1 (key1)); INSERT INTO t1 VALUES (0),(0),(1),(1); CREATE TABLE t2 (keya int(11) NOT NULL default '0', KEY j1 (keya)); INSERT INTO t2 VALUES (0),(0),(1),(1),(2),(2); explain select * from t1, t2 where (t1.key1 <t2.keya + 1) and t2.keya=3; +explain select * from t1 force index(i1), t2 force index(j1) where + (t1.key1 <t2.keya + 1) and t2.keya=3; DROP TABLE t1,t2; # @@ -415,14 +417,17 @@ drop table t1; --disable_warnings create table t1 (x bigint unsigned not null primary key) engine=innodb; --enable_warnings -insert into t1(x) values (0xfffffffffffffff0); -insert into t1(x) values (0xfffffffffffffff1); +insert into t1(x) values (0xfffffffffffffff0),(0xfffffffffffffff1); select * from t1; select count(*) from t1 where x>0; select count(*) from t1 where x=0; select count(*) from t1 where x<0; select count(*) from t1 where x < -16; select count(*) from t1 where x = -16; +explain select count(*) from t1 where x > -16; + +# The following result should be (2). To be fixed when we add 'unsigned flag' to +# Field::store(longlong) select count(*) from t1 where x > -16; select count(*) from t1 where x = 18446744073709551601; diff --git a/mysql-test/t/rpl_charset.test b/mysql-test/t/rpl_charset.test index ba2ebe03a12..74112ac44fe 100644 --- a/mysql-test/t/rpl_charset.test +++ b/mysql-test/t/rpl_charset.test @@ -106,6 +106,7 @@ select * from mysqltest2.t1 order by a; connection master; drop database mysqltest2; drop database mysqltest3; +--replace_column 2 # 5 # show binlog events from 95; sync_slave_with_master; diff --git a/mysql-test/t/sp-error.test b/mysql-test/t/sp-error.test index f8abab0e7e3..d07c866ff79 100644 --- a/mysql-test/t/sp-error.test +++ b/mysql-test/t/sp-error.test @@ -49,18 +49,18 @@ create function func1() returns int return 42| # Can't create recursively ---error 1302 +--error 1303 create procedure foo() create procedure bar() set @x=3| ---error 1302 +--error 1303 create procedure foo() create function bar() returns double return 2.3| # Already exists ---error 1303 +--error 1304 create procedure proc1() set @x = 42| ---error 1303 +--error 1304 create function func1() returns int return 42| @@ -68,42 +68,42 @@ drop procedure proc1| drop function func1| # Does not exist ---error 1304 +--error 1305 alter procedure foo| ---error 1304 +--error 1305 alter function foo| ---error 1304 +--error 1305 drop procedure foo| ---error 1304 +--error 1305 drop function foo| ---error 1304 +--error 1305 call foo()| drop procedure if exists foo| ---error 1304 +--error 1305 show create procedure foo| # LEAVE/ITERATE/GOTO with no match ---error 1307 +--error 1308 create procedure foo() foo: loop leave bar; end loop| ---error 1307 +--error 1308 create procedure foo() foo: loop iterate bar; end loop| ---error 1307 +--error 1308 create procedure foo() foo: begin iterate foo; end| ---error 1307 +--error 1308 create procedure foo() begin goto foo; end| ---error 1307 +--error 1308 create procedure foo() begin begin @@ -111,7 +111,7 @@ begin end; goto foo; end| ---error 1307 +--error 1308 create procedure foo() begin goto foo; @@ -119,7 +119,7 @@ begin label foo; end; end| ---error 1307 +--error 1308 create procedure foo() begin begin @@ -129,7 +129,7 @@ begin label foo; end; end| ---error 1307 +--error 1308 create procedure foo() begin begin @@ -141,7 +141,7 @@ begin end| # Redefining label ---error 1308 +--error 1309 create procedure foo() foo: loop foo: loop @@ -150,7 +150,7 @@ foo: loop end loop foo| # End label mismatch ---error 1309 +--error 1310 create procedure foo() foo: loop set @x=2; @@ -165,12 +165,12 @@ end| drop procedure foo| # RETURN in FUNCTION only ---error 1312 +--error 1313 create procedure foo() return 42| # Doesn't allow queries in FUNCTIONs (for now :-( ) ---error 1313 +--error 1314 create function foo() returns int begin declare x int; @@ -184,19 +184,19 @@ create procedure p(x int) create function f(x int) returns int return x+42| ---error 1317 +--error 1318 call p()| ---error 1317 +--error 1318 call p(1, 2)| ---error 1317 +--error 1318 select f()| ---error 1317 +--error 1318 select f(1, 2)| drop procedure p| drop function f| ---error 1318 +--error 1319 create procedure p(val int, out res int) begin declare x int default 0; @@ -210,7 +210,7 @@ begin end if; end| ---error 1318 +--error 1319 create procedure p(val int, out res int) begin declare x int default 0; @@ -225,7 +225,7 @@ begin end if; end| ---error 1319 +--error 1320 create function f(val int) returns int begin declare x int; @@ -243,12 +243,12 @@ begin end if; end| ---error 1320 +--error 1321 select f(10)| drop function f| ---error 1321 +--error 1322 create procedure p() begin declare c cursor for insert into test.t1 values ("foo", 42); @@ -257,7 +257,7 @@ begin close c; end| ---error 1322 +--error 1323 create procedure p() begin declare x int; @@ -267,7 +267,7 @@ begin close c; end| ---error 1323 +--error 1324 create procedure p() begin declare c cursor for select * from test.t; @@ -289,7 +289,7 @@ begin open c; close c; end| ---error 1324 +--error 1325 call p()| drop procedure p| @@ -301,11 +301,11 @@ begin close c; close c; end| ---error 1325 +--error 1326 call p()| drop procedure p| ---error 1304 +--error 1305 alter procedure bar3 sql security invoker| --error 1059 alter procedure bar3 name @@ -319,7 +319,7 @@ drop table if exists t1| create table t1 (val int, x float)| insert into t1 values (42, 3.1), (19, 1.2)| ---error 1326 +--error 1327 create procedure p() begin declare x int; @@ -339,7 +339,7 @@ begin fetch c into x; close c; end| ---error 1327 +--error 1328 call p()| drop procedure p| @@ -354,34 +354,34 @@ begin fetch c into x, y, z; close c; end| ---error 1327 +--error 1328 call p()| drop procedure p| ---error 1329 +--error 1330 create procedure p(in x int, x char(10)) begin end| ---error 1329 +--error 1330 create function p(x int, x char(10)) begin end| ---error 1330 +--error 1331 create procedure p() begin declare x float; declare x int; end| ---error 1331 +--error 1332 create procedure p() begin declare c condition for 1064; declare c condition for 1065; end| ---error 1332 +--error 1333 create procedure p() begin declare c cursor for select * from t1; @@ -389,18 +389,18 @@ begin end| # USE is not allowed ---error 1335 +--error 1336 create procedure u() use sptmp| # Enforced standard order of declarations ---error 1336 +--error 1337 create procedure p() begin declare c cursor for select * from t1; declare x int; end| ---error 1336 +--error 1337 create procedure p() begin declare x int; @@ -408,7 +408,7 @@ begin declare foo condition for sqlstate '42S99'; end| ---error 1337 +--error 1338 create procedure p() begin declare x int; @@ -416,7 +416,7 @@ begin declare c cursor for select * from t1; end| ---error 1357 +--error 1358 create procedure p() begin declare continue handler for sqlexception @@ -445,13 +445,13 @@ drop procedure bug1965| # # BUG#1966 # ---error 1326 +--error 1327 select 1 into a| # # BUG#1654 # ---error 1313 +--error 1314 create function bug1654() returns int return (select sum(t.data) from test.t2 t)| @@ -489,7 +489,7 @@ begin fetch c1 into v1; end| ---error 1325 +--error 1326 call bug2259()| drop procedure bug2259| @@ -545,7 +545,7 @@ begin end case; return 2; end| ---error 1338 +--error 1339 select bug3287()| drop function bug3287| @@ -556,7 +556,7 @@ when 0 then when 1 then insert into test.t1 values (x, 1.1); end case| ---error 1338 +--error 1339 call bug3287(2)| drop procedure bug3287| @@ -622,9 +622,9 @@ drop procedure bug2653_2| # # BUG#4344 # ---error 1356 +--error 1357 create procedure bug4344() drop procedure bug4344| ---error 1356 +--error 1357 create procedure bug4344() drop function bug4344| diff --git a/mysql-test/t/sp-security.test b/mysql-test/t/sp-security.test index d1c9c6c639d..4804d44e63a 100644 --- a/mysql-test/t/sp-security.test +++ b/mysql-test/t/sp-security.test @@ -60,7 +60,7 @@ select * from db1_secret.t1; # ...and not this --error 1049 create procedure db1_secret.dummy() begin end; ---error 1304 +--error 1305 drop procedure db1_secret.dummy; @@ -80,7 +80,7 @@ select * from db1_secret.t1; # ...and not this --error 1049 create procedure db1_secret.dummy() begin end; ---error 1304 +--error 1305 drop procedure db1_secret.dummy; diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index 92d6110cf7a..bc811607ef1 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -8,8 +8,30 @@ use test; --disable_warnings -drop table if exists t1; -drop table if exists t2; +drop table if exists t1,t2,t3,t4; +drop procedure if exists goto1; +drop procedure if exists goto2; +drop procedure if exists goto3; +drop procedure if exists goto4; +drop procedure if exists goto5; +drop procedure if exists goto6; +drop procedure if exists into_outfile; +drop procedure if exists into_dumpfile; +drop procedure if exists create_select; +drop procedure if exists bar; +drop procedure if exists hndlr1; +drop procedure if exists hndlr2; +drop procedure if exists hndlr3; +drop procedure if exists hndlr4; +drop procedure if exists cur1; +drop procedure if exists cur2; +drop procedure if exists chistics; +drop procedure if exists chistics2; +drop procedure if exists modes; +drop procedure if exists dummy; +drop procedure if exists bug; +drop procedure if exists bug2; +drop function if exists fac; --enable_warnings create table t1 ( @@ -114,9 +136,6 @@ drop procedure setcontext| # Set things to null ---disable_warnings -drop table if exists t3| ---enable_warnings create table t3 ( d date, i int, f double, s varchar(32) )| create procedure nullset() @@ -1076,7 +1095,7 @@ drop procedure rc| # # BUG#822 # -create procedure bug822(a_id char(16), a_data int) +create procedure bug(a_id char(16), a_data int) begin declare n int; select count(*) into n from t1 where id = a_id and data = a_data; @@ -1085,17 +1104,17 @@ begin end if; end| -call bug822('foo', 42)| -call bug822('foo', 42)| -call bug822('bar', 666)| +call bug('foo', 42)| +call bug('foo', 42)| +call bug('bar', 666)| select * from t1| delete from t1| -drop procedure bug822| +drop procedure bug| # # BUG#1495 # -create procedure bug1495() +create procedure bug() begin declare x int; @@ -1108,19 +1127,19 @@ begin end| insert into t1 values ('foo', 12)| -call bug1495()| +call bug()| delete from t1 where id='foo'| insert into t1 values ('bar', 7)| -call bug1495()| +call bug()| delete from t1 where id='bar'| select * from t1| delete from t1| -drop procedure bug1495| +drop procedure bug| # # BUG#1547 # -create procedure bug1547(s char(16)) +create procedure bug(s char(16)) begin declare x int; @@ -1133,11 +1152,11 @@ begin end| insert into t1 values ("foo", 12), ("bar", 7)| -call bug1547("foo")| -call bug1547("bar")| +call bug("foo")| +call bug("bar")| select * from t1| delete from t1| -drop procedure bug1547| +drop procedure bug| # # BUG#1656 @@ -1148,13 +1167,13 @@ drop table if exists t70| create table t70 (s1 int,s2 int)| insert into t70 values (1,2)| -create procedure bug1656(out p1 int, out p2 int) +create procedure bug(out p1 int, out p2 int) select * into p1, p1 from t70| -call bug1656(@1, @2)| +call bug(@1, @2)| select @1, @2| drop table t70| -drop procedure bug1656| +drop procedure bug| # # BUG#1862 @@ -1164,23 +1183,23 @@ drop table if exists t3| --enable_warnings create table t3(a int)| -create procedure bug1862() +create procedure bug() begin insert into t3 values(2); flush tables; end| -call bug1862()| +call bug()| # the second call caused a segmentation -call bug1862()| +call bug()| select * from t3| drop table t3| -drop procedure bug1862| +drop procedure bug| # # BUG#1874 # -create procedure bug1874() +create procedure bug() begin declare x int; declare y double; @@ -1195,16 +1214,16 @@ begin end| insert into t1 (data) values (3), (1), (5), (9), (4)| -call bug1874()| +call bug()| select * from t2| delete from t1| delete from t2| -drop procedure bug1874| +drop procedure bug| # # BUG#2260 # -create procedure bug2260() +create procedure bug() begin declare v1 int; declare c1 cursor for select data from t1; @@ -1216,9 +1235,9 @@ begin close c1; end| -call bug2260()| +call bug()| select @x2| -drop procedure bug2260| +drop procedure bug| # # BUG#2267 @@ -1258,7 +1277,7 @@ drop procedure bug2267_4| # # BUG#2227 # -create procedure bug2227(x int) +create procedure bug(x int) begin declare y float default 2.6; declare z char(16) default "zzz"; @@ -1266,13 +1285,13 @@ begin select 1.3, x, y, 42, z; end| -call bug2227(9)| -drop procedure bug2227| +call bug(9)| +drop procedure bug| # # BUG#2614 # -create procedure bug2614() +create procedure bug() begin drop table if exists t3; create table t3 (id int default '0' not null); @@ -1281,22 +1300,22 @@ begin end| --disable_warnings -call bug2614()| +call bug()| --enable_warnings -call bug2614()| +call bug()| drop table t3| -drop procedure bug2614| +drop procedure bug| # # BUG#2674 # -create function bug2674 () returns int +create function bug () returns int return @@sort_buffer_size| set @osbs = @@sort_buffer_size| set @@sort_buffer_size = 262000| -select bug2674()| -drop function bug2674| +select bug()| +drop function bug| set @@sort_buffer_size = @osbs| # @@ -1320,16 +1339,16 @@ drop procedure BUG3259_3| # # BUG##2772 # -create function bug2772() returns char(10) character set latin2 +create function bug() returns char(10) character set latin2 return 'a'| -select bug2772()| -drop function bug2772| +select bug()| +drop function bug| # # BUG#2776 # -create procedure bug2776_1(out x int) +create procedure bug(out x int) begin declare v int; @@ -1337,7 +1356,7 @@ begin set x = v; end| -create procedure bug2776_2(out x int) +create procedure bug2(out x int) begin declare v int default 42; @@ -1346,12 +1365,12 @@ begin end| set @x = 1| -call bug2776_1(@x)| +call bug(@x)| select @x| -call bug2776_2(@x)| +call bug2(@x)| select @x| -drop procedure bug2776_1| -drop procedure bug2776_2| +drop procedure bug| +drop procedure bug2| # # BUG#2780 @@ -1363,7 +1382,7 @@ create table t3 (s1 smallint)| insert into t3 values (123456789012)| -create procedure bug2780() +create procedure bug() begin declare exit handler for sqlwarning set @x = 1; @@ -1372,26 +1391,19 @@ begin insert into t3 values (0); end| -call bug2780()| +call bug()| select @x| select * from t3| -drop procedure bug2780| +drop procedure bug| drop table t3| # # BUG#1863 # ---disable_warnings -drop table if exists t3| ---enable_warnings create table t3 (content varchar(10) )| insert into t3 values ("test1")| insert into t3 values ("test2")| - ---disable_warnings -drop table if exists t4| ---enable_warnings create table t4 (f1 int, rc int, t3 int)| create procedure bug1863(in1 int) @@ -1591,9 +1603,7 @@ drop procedure bug3863| # # BUG#2460 # ---disable_warnings -drop table if exists t3| ---enable_warnings + create table t3 ( id int(10) unsigned not null default 0, rid int(10) unsigned not null default 0, diff --git a/mysql-test/t/user_var.test b/mysql-test/t/user_var.test index 412003aa547..48ca9e0ba32 100644 --- a/mysql-test/t/user_var.test +++ b/mysql-test/t/user_var.test @@ -20,6 +20,7 @@ insert into t2 select distinct i from t1; select * from t2; select distinct t2.i,@vv1:=if(sv1.i,1,0),@vv2:=if(sv2.i,1,0),@vv3:=if(sv3.i,1,0), @vv1+@vv2+@vv3 from t2 left join t1 as sv1 on sv1.i=t2.i and sv1.v=1 left join t1 as sv2 on sv2.i=t2.i and sv2.v=2 left join t1 as sv3 on sv3.i=t2.i and sv3.v=3; explain select * from t1 where i=@vv1; +select @vv1,i,v from t1 where i=@vv1; explain select * from t1 where @vv1:=@vv1+1 and i=@vv1; explain select @vv1:=i from t1 where i=@vv1; explain select * from t1 where i=@vv1; diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index e73133afa67..51e81987cd9 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -15,7 +15,7 @@ create view v1 (c,d) as select a,b from t1; create temporary table t1 (a int, b int); #view on temporary table --- error 1351 +-- error 1352 create view v1 (c) as select b+1 from t1; drop table t1; @@ -23,7 +23,7 @@ create table t1 (a int, b int); insert into t1 values (1,2), (1,3), (2,4), (2,5), (3,10); #view with variable --- error 1350 +-- error 1351 create view v1 (c,d) as select a,b+@@global.max_user_connections from t1; # simple view @@ -38,7 +38,7 @@ select * from t1; select c from v1; show create table v1; show create view v1; --- error 1346 +-- error 1347 show create view t1; drop table t1; @@ -131,7 +131,7 @@ show grants for test@localhost; drop view v100; #try to drop table with DROP VIEW --- error 1346 +-- error 1347 drop view t1; #try to drop VIEW with DROP TABLE @@ -265,21 +265,21 @@ select c from mysqltest.v4; show columns from mysqltest.v1; show columns from mysqltest.v2; # but explain/show do not --- error 1344 +-- error 1345 explain select c from mysqltest.v1; --- error 1344 +-- error 1345 show create table mysqltest.v1; --- error 1344 +-- error 1345 explain select c from mysqltest.v2; --- error 1344 +-- error 1345 show create table mysqltest.v2; --- error 1344 +-- error 1345 explain select c from mysqltest.v3; --- error 1344 +-- error 1345 show create table mysqltest.v3; --- error 1344 +-- error 1345 explain select c from mysqltest.v4; --- error 1344 +-- error 1345 show create table mysqltest.v4; # allow to see one of underlaing table @@ -292,13 +292,13 @@ show create table mysqltest.v1; explain select c from mysqltest.v2; show create table mysqltest.v2; # but other EXPLAINs do not --- error 1344 +-- error 1345 explain select c from mysqltest.v3; --- error 1344 +-- error 1345 show create table mysqltest.v3; --- error 1344 +-- error 1345 explain select c from mysqltest.v4; --- error 1344 +-- error 1345 show create table mysqltest.v4; # allow to see any view in mysqltest database @@ -445,7 +445,7 @@ insert into t1 values (10,2), (20,3), (30,4), (40,5), (50,10); create view v1 (a,c) as select a, b+1 from t1; create algorithm=temptable view v2 (a,c) as select a, b+1 from t1; # try to update expression --- error 1347 +-- error 1348 update v1 set c=a+c; # try to update VIEW with forced TEMPORARY TABLE algorithm -- error 1288 @@ -467,7 +467,7 @@ insert into t2 values (10), (20); create view v1 (a,c) as select a, b+1 from t1; create algorithm=temptable view v2 (a,c) as select a, b+1 from t1; # try to update expression --- error 1347 +-- error 1348 update t2,v1 set v1.c=v1.a+v1.c where t2.x=v1.a; # try to update VIEW with forced TEMPORARY TABLE algorithm -- error 1288 @@ -798,7 +798,7 @@ connection user1; create view v1 as select * from mysqltest.t1; create view v2 as select b from mysqltest.t2; # There are not rights on mysqltest.v1 ---error 1142 +-- error 1142 create view mysqltest.v1 as select * from mysqltest.t1; # There are not any rights on mysqltest.t2.a -- error 1143 @@ -888,10 +888,10 @@ create table t1 (col1 char(5),col2 char(5)); create view v1 as select * from t1; drop table t1; create table t1 (col1 char(5),newcol2 char(5)); --- error 1355 +-- error 1356 insert into v1 values('a','aa'); drop table t1; --- error 1355 +-- error 1356 select * from v1; drop view v1; @@ -907,7 +907,7 @@ create view v1 (a,a) as select 'a','a'; delimiter //; create procedure p1 () begin declare v int; create view v1 as select v; end;// delimiter ;// --- error 1350 +-- error 1351 call p1(); drop procedure p1; @@ -993,7 +993,7 @@ drop view v1; # create table t1 (col1 int,col2 char(22)); create view v1 as select * from t1; --- error 1346 +-- error 1347 create index i1 on v1 (col1); drop view v1; drop table t1; diff --git a/mysys/my_alloc.c b/mysys/my_alloc.c index c9784ddc9a0..7b4287fc7a6 100644 --- a/mysys/my_alloc.c +++ b/mysys/my_alloc.c @@ -133,6 +133,7 @@ gptr alloc_root(MEM_ROOT *mem_root,unsigned int Size) next->next= mem_root->used; next->size= Size; mem_root->used= next; + DBUG_PRINT("exit",("ptr: 0x%lx", (((char*) next)+ALIGN_SIZE(sizeof(USED_MEM))))); DBUG_RETURN((gptr) (((char*) next)+ALIGN_SIZE(sizeof(USED_MEM)))); #else uint get_size, block_size; diff --git a/sql/ha_isam.cc b/sql/ha_isam.cc index 85ab25a31d9..2fd75462329 100644 --- a/sql/ha_isam.cc +++ b/sql/ha_isam.cc @@ -330,7 +330,7 @@ int ha_isam::create(const char *name, register TABLE *form, } } } - DBUG_PRINT("loop",("found: %lx recpos: %d minpos: %d length: %d", + DBUG_PRINT("loop",("found: 0x%lx recpos: %d minpos: %d length: %d", found,recpos,minpos,length)); if (recpos != minpos) { // Reserved space (Null bits?) diff --git a/sql/ha_myisam.cc b/sql/ha_myisam.cc index 89288d6059f..4a6f2c556bf 100644 --- a/sql/ha_myisam.cc +++ b/sql/ha_myisam.cc @@ -1435,7 +1435,7 @@ int ha_myisam::create(const char *name, register TABLE *table_arg, } } } - DBUG_PRINT("loop",("found: %lx recpos: %d minpos: %d length: %d", + DBUG_PRINT("loop",("found: 0x%lx recpos: %d minpos: %d length: %d", found,recpos,minpos,length)); if (recpos != minpos) { // Reserved space (Null bits?) diff --git a/sql/ha_ndbcluster.cc b/sql/ha_ndbcluster.cc index 815aed13ce3..09cb0c0f02d 100644 --- a/sql/ha_ndbcluster.cc +++ b/sql/ha_ndbcluster.cc @@ -2297,6 +2297,8 @@ int ha_ndbcluster::extra(enum ha_extra_function operation) break; case HA_EXTRA_CHANGE_KEY_TO_DUP: DBUG_PRINT("info", ("HA_EXTRA_CHANGE_KEY_TO_DUP")); + case HA_EXTRA_KEYREAD_PRESERVE_FIELDS: + DBUG_PRINT("info", ("HA_EXTRA_KEYREAD_PRESERVE_FIELDS")); break; } diff --git a/sql/handler.cc b/sql/handler.cc index 640c4f3710d..e7e1c807306 100644 --- a/sql/handler.cc +++ b/sql/handler.cc @@ -483,7 +483,7 @@ int ha_commit_trans(THD *thd, THD_TRANS* trans) if (trans == &thd->transaction.all && mysql_bin_log.is_open() && my_b_tell(&thd->transaction.trans_log)) { - if (error= wait_if_global_read_lock(thd, 0, 0)) + if ((error= wait_if_global_read_lock(thd, 0, 0))) { /* Note that ROLLBACK [TO SAVEPOINT] does not have this test; it's diff --git a/sql/item.cc b/sql/item.cc index ecbe2d22fa4..5fa6b2400df 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -105,6 +105,15 @@ void Item::print_item_w_name(String *str) } +void Item::cleanup() +{ + DBUG_ENTER("Item::cleanup"); + DBUG_PRINT("info", ("Item: 0x%lx", this)); + DBUG_PRINT("info", ("Type: %d", (int)type())); + fixed=0; + DBUG_VOID_RETURN; +} + Item_ident::Item_ident(const char *db_name_par,const char *table_name_par, const char *field_name_par) :orig_db_name(db_name_par), orig_table_name(table_name_par), @@ -1397,9 +1406,9 @@ bool Item_field::fix_fields(THD *thd, TABLE_LIST *tables, Item **ref) } if (!tmp) return -1; - else if (!refer) + if (!refer) return 1; - else if (tmp == not_found_field && refer == (Item **)not_found_item) + if (tmp == not_found_field && refer == (Item **)not_found_item) { if (upward_lookup) { @@ -1903,7 +1912,7 @@ bool Item::send(Protocol *protocol, String *buffer) { bool result; enum_field_types type; - LINT_INIT(result); + LINT_INIT(result); // Will be set if null_value == 0 switch ((type=field_type())) { default: diff --git a/sql/item.h b/sql/item.h index 4d3f1736b4e..a9e4831b40b 100644 --- a/sql/item.h +++ b/sql/item.h @@ -143,13 +143,7 @@ public: } /*lint -e1509 */ void set_name(const char *str,uint length, CHARSET_INFO *cs); void init_make_field(Send_field *tmp_field,enum enum_field_types type); - virtual void cleanup() - { - DBUG_ENTER("Item::cleanup"); - DBUG_PRINT("info", ("Type: %d", (int)type())); - fixed=0; - DBUG_VOID_RETURN; - } + virtual void cleanup(); virtual void make_field(Send_field *field); virtual bool fix_fields(THD *, struct st_table_list *, Item **); /* diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index f6daf0f5ed4..0356ea02ada 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -217,14 +217,10 @@ void Item_bool_func2::fix_length_and_dec() in its memory: it will be reused on each execute. */ Item_arena *arena= thd->current_arena, backup; - if (!arena->is_stmt_prepare()) - arena= 0; - else - thd->set_n_backup_item_arena(arena, &backup); + thd->set_n_backup_item_arena(arena, &backup); conv= new Item_func_conv_charset(args[weak], args[strong]->collation.collation); - if (arena) - thd->restore_backup_item_arena(arena, &backup); + thd->restore_backup_item_arena(arena, &backup); conv->collation.set(args[weak]->collation.derivation); conv->fix_fields(thd, 0, &conv); } @@ -1625,7 +1621,7 @@ cmp_item* cmp_item_row::make_same() cmp_item_row::~cmp_item_row() { DBUG_ENTER("~cmp_item_row"); - DBUG_PRINT("enter",("this: %lx", this)); + DBUG_PRINT("enter",("this: 0x%lx", this)); if (comparators) { for (uint i= 0; i < n; i++) diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 6d86d7d4b2d..23dbcf8af48 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -137,7 +137,6 @@ bool Item_subselect::fix_fields(THD *thd_param, TABLE_LIST *tables, Item **ref) DBUG_ASSERT(fixed == 0); engine->set_thd((thd= thd_param)); - arena= thd->current_arena; if (check_stack_overrun(thd, (gptr)&res)) return 1; @@ -872,7 +871,7 @@ Item_in_subselect::single_value_transformer(JOIN *join, ER_SELECT_REDUCED, warn_buff); } result= RES_REDUCE; - goto end; + goto err; } } } diff --git a/sql/item_subselect.h b/sql/item_subselect.h index e870feddedf..ed3dbfa9855 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -36,8 +36,6 @@ class Item_subselect :public Item_result_field protected: /* thread handler, will be assigned in fix_fields only */ THD *thd; - /* Item_arena used or 0 */ - Item_arena *arena; /* substitution instead of subselect in case of optimization */ Item *substitution; /* unit of subquery */ diff --git a/sql/item_sum.cc b/sql/item_sum.cc index 13b6329daae..bfa0f86c744 100644 --- a/sql/item_sum.cc +++ b/sql/item_sum.cc @@ -64,7 +64,7 @@ Item_sum::Item_sum(THD *thd, Item_sum *item): /* - Save copy of arguments if we prepare prepared statement + Save copy of arguments if we are preparing a prepared statement (arguments can be rewritten in get_tmp_table_item()) SYNOPSIS diff --git a/sql/log_event.cc b/sql/log_event.cc index 97e041774f8..0c3b36c37b5 100644 --- a/sql/log_event.cc +++ b/sql/log_event.cc @@ -1266,6 +1266,8 @@ void Query_log_event::print(FILE* file, bool short_form, { // TODO: print the catalog ?? char buff[40],*end; // Enough for SET TIMESTAMP + bool different_db= 1; + if (!short_form) { print_header(file); @@ -1273,9 +1275,7 @@ void Query_log_event::print(FILE* file, bool short_form, (ulong) thread_id, (ulong) exec_time, error_code); } - bool different_db= 1; - - if (db && last_event_info->db) + if (db) { if ((different_db = memcmp(last_event_info->db, db, db_len + 1))) memcpy(last_event_info->db, db, db_len + 1); @@ -2300,7 +2300,7 @@ void Load_log_event::print(FILE* file, bool short_form, LAST_EVENT_INFO* last_ev } bool different_db= 1; - if (db && last_event_info->db) + if (db) { /* If the database is different from the one of the previous statement, we @@ -2845,7 +2845,8 @@ int Intvar_log_event::write_data(IO_CACHE* file) */ #ifdef MYSQL_CLIENT -void Intvar_log_event::print(FILE* file, bool short_form, LAST_EVENT_INFO* last_event_info) +void Intvar_log_event::print(FILE* file, bool short_form, + LAST_EVENT_INFO* last_event_info) { char llbuff[22]; const char *msg; diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h index c668e152df5..73ac000d953 100644 --- a/sql/mysql_priv.h +++ b/sql/mysql_priv.h @@ -1038,7 +1038,8 @@ void mysql_lock_abort_for_thread(THD *thd, TABLE *table); MYSQL_LOCK *mysql_lock_merge(MYSQL_LOCK *a,MYSQL_LOCK *b); bool lock_global_read_lock(THD *thd); void unlock_global_read_lock(THD *thd); -bool wait_if_global_read_lock(THD *thd, bool abort_on_refresh, bool is_not_commit); +bool wait_if_global_read_lock(THD *thd, bool abort_on_refresh, + bool is_not_commit); void start_waiting_global_read_lock(THD *thd); void make_global_read_lock_block_commit(THD *thd); diff --git a/sql/mysqld.cc b/sql/mysqld.cc index 81651862255..24f6c4a3fa9 100644 --- a/sql/mysqld.cc +++ b/sql/mysqld.cc @@ -528,14 +528,14 @@ static void close_connections(void) (void) pthread_mutex_lock(&LOCK_manager); if (manager_thread_in_use) { - DBUG_PRINT("quit",("killing manager thread: %lx",manager_thread)); + DBUG_PRINT("quit",("killing manager thread: 0x%lx",manager_thread)); (void) pthread_cond_signal(&COND_manager); } (void) pthread_mutex_unlock(&LOCK_manager); /* kill connection thread */ #if !defined(__WIN__) && !defined(__EMX__) && !defined(OS2) && !defined(__NETWARE__) - DBUG_PRINT("quit",("waiting for select thread: %lx",select_thread)); + DBUG_PRINT("quit",("waiting for select thread: 0x%lx",select_thread)); (void) pthread_mutex_lock(&LOCK_thread_count); while (select_thread_in_use) @@ -2476,7 +2476,7 @@ static void init_ssl() ssl_acceptor_fd= new_VioSSLAcceptorFd(opt_ssl_key, opt_ssl_cert, opt_ssl_ca, opt_ssl_capath, opt_ssl_cipher); - DBUG_PRINT("info",("ssl_acceptor_fd: %lx", (long) ssl_acceptor_fd)); + DBUG_PRINT("info",("ssl_acceptor_fd: 0x%lx", (long) ssl_acceptor_fd)); if (!ssl_acceptor_fd) opt_use_ssl = 0; } @@ -4331,6 +4331,10 @@ Disable with --skip-isam.", log and this option justs turns on --log-bin instead.", (gptr*) &opt_update_logname, (gptr*) &opt_update_logname, 0, GET_STR, OPT_ARG, 0, 0, 0, 0, 0, 0}, + {"log-warnings", 'W', "Log some not critical warnings to the log file.", + (gptr*) &global_system_variables.log_warnings, + (gptr*) &max_system_variables.log_warnings, 0, GET_ULONG, OPT_ARG, 1, 0, 0, + 0, 0, 0}, {"low-priority-updates", OPT_LOW_PRIORITY_UPDATES, "INSERT/DELETE/UPDATE has lower priority than selects.", (gptr*) &global_system_variables.low_priority_updates, diff --git a/sql/opt_range.cc b/sql/opt_range.cc index e0e2b5c8045..e3a130f55f0 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -1633,8 +1633,9 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, if (!head->used_keys.is_clear_all()) { int key_for_use= find_shortest_key(head, &head->used_keys); - double key_read_time= get_index_only_read_time(¶m, records, - key_for_use); + double key_read_time= (get_index_only_read_time(¶m, records, + key_for_use) + + (double) records / TIME_FOR_COMPARE); DBUG_PRINT("info", ("'all'+'using index' scan will be using key %d, " "read time %g", key_for_use, key_read_time)); if (key_read_time < read_time) @@ -2111,6 +2112,12 @@ skip_to_ror_scan: NOTES It is assumed that we will read trough the whole key range and that all key blocks are half full (normally things are much better). + + TODO: + Move this to handler->read_time() by adding a flag 'index-only-read' to + this call. The reason for doing this is that the current function doesn't + handle the case when the row is stored in the b-tree (like in innodb + clustered index) */ inline double get_index_only_read_time(const PARAM* param, ha_rows records, @@ -2125,6 +2132,7 @@ inline double get_index_only_read_time(const PARAM* param, ha_rows records, return read_time; } + typedef struct st_ror_scan_info { uint idx; /* # of used key in param->keys */ @@ -2992,7 +3000,8 @@ static TRP_RANGE *get_key_scans_params(PARAM *param, SEL_TREE *tree, !(pk_is_clustered && keynr == param->table->primary_key)) { /* We can resolve this by only reading through this key. */ - found_read_time= get_index_only_read_time(param,found_records,keynr); + found_read_time= (get_index_only_read_time(param,found_records,keynr)+ + (double) found_records / TIME_FOR_COMPARE); } else { @@ -3673,7 +3682,8 @@ tree_and(PARAM *param,SEL_TREE *tree1,SEL_TREE *tree2) } result_keys.set_bit(key1 - tree1->keys); #ifdef EXTRA_DEBUG - (*key1)->test_use_count(*key1); + if (*key1) + (*key1)->test_use_count(*key1); #endif } } @@ -4654,7 +4664,7 @@ void SEL_ARG::test_use_count(SEL_ARG *root) ulong count=count_key_part_usage(root,pos->next_key_part); if (count > pos->next_key_part->use_count) { - sql_print_error("Note: Use_count: Wrong count for key at %lx, %lu should be %lu", + sql_print_error("Note: Use_count: Wrong count for key at 0x%lx, %lu should be %lu", pos,pos->next_key_part->use_count,count); return; } @@ -4662,7 +4672,7 @@ void SEL_ARG::test_use_count(SEL_ARG *root) } } if (e_count != elements) - sql_print_error("Warning: Wrong use count: %u (should be %u) for tree at %lx", + sql_print_error("Warning: Wrong use count: %u (should be %u) for tree at 0x%lx", e_count, elements, (gptr) this); } diff --git a/sql/set_var.cc b/sql/set_var.cc index d92d5eb42b2..4b347f91869 100644 --- a/sql/set_var.cc +++ b/sql/set_var.cc @@ -2404,7 +2404,7 @@ bool sys_var_thd_time_zone::check(THD *thd, set_var *var) #endif if (!(var->save_result.time_zone= - my_tz_find(res, thd->lex->time_zone_tables_used))) + my_tz_find(res, thd->lex->time_zone_tables_used))) { my_error(ER_UNKNOWN_TIME_ZONE, MYF(0), res ? res->c_ptr() : "NULL"); return 1; diff --git a/sql/sp_head.cc b/sql/sp_head.cc index 16d13154263..d0ffe3fa051 100644 --- a/sql/sp_head.cc +++ b/sql/sp_head.cc @@ -262,6 +262,7 @@ sp_head::sp_head() { DBUG_ENTER("sp_head::sp_head"); + state= INITIALIZED; m_backpatch.empty(); m_lex.empty(); DBUG_VOID_RETURN; @@ -498,8 +499,7 @@ sp_head::execute(THD *thd) } } while (ret == 0 && !thd->killed && !thd->query_error); - if (thd->current_arena) - cleanup_items(thd->current_arena->free_list); + cleanup_items(thd->current_arena->free_list); thd->current_arena= old_arena; done: @@ -945,7 +945,9 @@ sp_head::restore_thd_mem_root(THD *thd) { DBUG_ENTER("sp_head::restore_thd_mem_root"); Item *flist= free_list; // The old list - set_item_arena(thd); // Get new fre_list and mem_root + set_item_arena(thd); // Get new free_list and mem_root + state= INITIALIZED; + DBUG_PRINT("info", ("mem_root 0x%lx returned from thd mem root 0x%lx", (ulong) &mem_root, (ulong) &thd->mem_root)); thd->free_list= flist; // Restore the old one diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 0aee91af0da..f07b93ec9f0 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -1732,10 +1732,10 @@ int open_and_lock_tables(THD *thd, TABLE_LIST *tables) { for (TABLE_LIST *cursor= (TABLE_LIST *) sl->table_list.first; cursor; - cursor=cursor->next) + cursor=cursor->next_local) { - if (cursor->table_list) - cursor->table= cursor->table_list->table; + if (cursor->correspondent_table) + cursor->table= cursor->correspondent_table->table; } } } @@ -1948,15 +1948,8 @@ find_field_in_table(THD *thd, TABLE_LIST *table_list, *ref= trans[i]; else { - Item_arena *arena= thd->current_arena, backup; - if (!arena->is_stmt_prepare()) - arena= 0; - else - thd->set_n_backup_item_arena(arena, &backup); - *ref= new Item_ref(trans + i, 0, table_list->view_name.str, + *ref= new Item_ref(trans + i, ref, table_list->view_name.str, item_name); - if (arena) - thd->restore_backup_item_arena(arena, &backup); /* as far as Item_ref have defined refernce it do not need tables */ if (*ref) (*ref)->fix_fields(thd, 0, ref); @@ -2440,14 +2433,14 @@ int setup_wild(THD *thd, TABLE_LIST *tables, List<Item> &fields, if (!wild_num) return 0; Item_arena *arena= thd->current_arena, backup; - if (!arena->is_stmt_prepare()) - arena= 0; // For easier test /* - If we are in preparing prepared statement phase then we have change - temporary mem_root to statement mem root to save changes of SELECT list + Don't use arena if we are not in prepared statements or stored procedures + For PS/SP we have to use arena to remember the changes */ - if (arena) + if (arena->state == Item_arena::CONVENTIONAL_EXECUTION) + arena= 0; // For easier test later one + else thd->set_n_backup_item_arena(arena, &backup); List_iterator<Item> it(fields); @@ -3057,7 +3050,7 @@ int setup_conds(THD *thd,TABLE_LIST *tables,COND **conds) err: if (arena) - thd->restore_backup_item_arena(arena, &backup); + thd->restore_backup_item_arena(arena, &backup); DBUG_RETURN(1); } diff --git a/sql/sql_class.cc b/sql/sql_class.cc index 456b58ee95e..a807a4b75e5 100644 --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@ -1472,8 +1472,10 @@ void Statement::end_statement() void Item_arena::set_n_backup_item_arena(Item_arena *set, Item_arena *backup) { + DBUG_ENTER("Item_arena::set_n_backup_item_arena"); backup->set_item_arena(this); set_item_arena(set); + DBUG_VOID_RETURN; } @@ -1482,7 +1484,7 @@ void Item_arena::restore_backup_item_arena(Item_arena *set, Item_arena *backup) set->set_item_arena(this); set_item_arena(backup); // reset backup mem_root to avoid its freeing - init_alloc_root(&backup->mem_root, 0, 0); + clear_alloc_root(&backup->mem_root); } void Item_arena::set_item_arena(Item_arena *set) diff --git a/sql/sql_class.h b/sql/sql_class.h index 2b941b317f6..1612ab6fb17 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -1031,6 +1031,10 @@ public: return 0; #endif } + inline bool only_prepare() + { + return command == COM_PREPARE; + } inline gptr trans_alloc(unsigned int size) { return alloc_root(&transaction.mem_root,size); diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index b0707955522..1f0b63b5df3 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -1014,7 +1014,7 @@ void st_select_lex::init_query() subquery_in_having= explicit_limit= 0; first_execution= 1; first_cond_optimization= 1; - parsing_place= SELECT_LEX_NODE::NO_MATTER; + parsing_place= NO_MATTER; no_wrap_view_item= 0; } @@ -1659,11 +1659,6 @@ void st_select_lex_unit::set_limit(SELECT_LEX *values, } -st_lex::st_lex() - :result(0) -{} - - /* Unlink first table from global table list and first table from outer select list (lex->select_lex) @@ -1793,10 +1788,10 @@ void st_lex::link_first_table_back(TABLE_LIST *first, void st_select_lex::fix_prepare_information(THD *thd, Item **conds) { - if (thd->current_arena && first_execution) + if (thd->current_arena->is_stmt_prepare() && first_execution) { - prep_where= where; first_execution= 0; + prep_where= where; } } diff --git a/sql/sql_lex.h b/sql/sql_lex.h index de0d5d90f16..d9eff46ea48 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -713,7 +713,7 @@ typedef struct st_lex */ bool empty_field_list_on_rset; - st_lex() + st_lex() :result(0) { bzero((char *)&spfuns, sizeof(spfuns)); } diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 59a3e4b545b..a1180d29e99 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -1958,6 +1958,20 @@ mysql_execute_command(THD *thd) } #endif /* !HAVE_REPLICATION */ + if (lex->time_zone_tables_used) + { + TABLE_LIST *tmp; + if ((tmp= my_tz_get_table_list(thd, &lex->query_tables_last)) == + &fake_time_zone_tables_list) + { + send_error(thd, 0); + DBUG_RETURN(-1); + } + lex->time_zone_tables_used= tmp; + if (!all_tables) + all_tables= tmp; + } + /* When option readonly is set deny operations which change tables. Except for the replication thread and the 'super' users. @@ -2393,11 +2407,11 @@ mysql_execute_command(THD *thd) if (lex->create_info.used_fields & HA_CREATE_USED_UNION) { TABLE_LIST *tab; - for (tab= select_tables; tab; tab= tab->next) + for (tab= select_tables; tab; tab= tab->next_local) { - if (find_real_table_in_list((TABLE_LIST*) lex->create_info. - merge_list.first, - select_tables->db, tab->real_name)) + if (find_table_in_local_list((TABLE_LIST*) lex->create_info. + merge_list.first, + select_tables->db, tab->real_name)) { net_printf(thd, ER_UPDATE_TABLE_USED, tab->real_name); goto create_error; @@ -3767,7 +3781,7 @@ purposes internal to the MySQL server", MYF(0)); case SP_KEY_NOT_FOUND: if (lex->drop_if_exists) { - push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_WARN, + push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_NOTE, ER_SP_DOES_NOT_EXIST, ER(ER_SP_DOES_NOT_EXIST), SP_COM_STRING(lex), lex->spname->m_name.str); res= 0; @@ -4699,7 +4713,7 @@ bool add_field_to_list(THD *thd, char *field_name, enum_field_types type, new_field->sql_type= FIELD_TYPE_BLOB; sprintf(warn_buff, ER(ER_AUTO_CONVERT), field_name, "CHAR", (cs == &my_charset_bin) ? "BLOB" : "TEXT"); - push_warning(thd, MYSQL_ERROR::WARN_LEVEL_WARN, ER_AUTO_CONVERT, + push_warning(thd, MYSQL_ERROR::WARN_LEVEL_NOTE, ER_AUTO_CONVERT, warn_buff); /* fall through */ case FIELD_TYPE_BLOB: diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc index 5fccdd624de..c84d6457d4f 100644 --- a/sql/sql_prepare.cc +++ b/sql/sql_prepare.cc @@ -414,6 +414,7 @@ static void set_param_datetime(Item_param *param, uchar **pos, ulong len) *pos+= length; } + static void set_param_date(Item_param *param, uchar **pos, ulong len) { MYSQL_TIME tm; @@ -1864,7 +1865,7 @@ void mysql_stmt_execute(THD *thd, char *packet, uint packet_length) } thd->set_statement(&thd->stmt_backup); - thd->current_arena= 0; + thd->current_arena= thd; DBUG_VOID_RETURN; set_params_data_err: @@ -1891,6 +1892,8 @@ void mysql_sql_stmt_execute(THD *thd, LEX_STRING *stmt_name) String expanded_query; DBUG_ENTER("mysql_sql_stmt_execute"); + DBUG_ASSERT(thd->free_list == NULL); + if (!(stmt= (Prepared_statement*)thd->stmt_map.find_by_name(stmt_name))) { my_error(ER_UNKNOWN_STMT_HANDLER, MYF(0), stmt_name->length, @@ -1906,9 +1909,8 @@ void mysql_sql_stmt_execute(THD *thd, LEX_STRING *stmt_name) DBUG_VOID_RETURN; } - DBUG_ASSERT(thd->free_list == NULL); - thd->set_n_backup_statement(stmt, &thd->stmt_backup); + thd->set_statement(stmt); if (stmt->set_params_from_vars(stmt, thd->stmt_backup.lex->prepared_stmt_params, &expanded_query)) @@ -1916,9 +1918,7 @@ void mysql_sql_stmt_execute(THD *thd, LEX_STRING *stmt_name) my_error(ER_WRONG_ARGUMENTS, MYF(0), "EXECUTE"); send_error(thd); } - thd->current_arena= stmt; execute_stmt(thd, stmt, &expanded_query); - thd->current_arena= 0; DBUG_VOID_RETURN; } @@ -1941,7 +1941,6 @@ static void execute_stmt(THD *thd, Prepared_statement *stmt, { DBUG_ENTER("execute_stmt"); - thd->set_n_backup_statement(stmt, &thd->stmt_backup); reset_stmt_for_execute(thd, stmt->lex); if (expanded_query->length() && diff --git a/sql/sql_select.cc b/sql/sql_select.cc index db3a0c90141..2d8ea72ed51 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -4910,13 +4910,13 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) JOIN_TAB *tab=join->join_tab+i; JOIN_TAB *first_inner_tab= tab->first_inner; table_map current_map= tab->table->map; + bool use_quick_range=0; /* Following force including random expression in last table condition. It solve problem with select like SELECT * FROM t1 WHERE rand() > 0.5 */ if (i == join->tables-1) current_map|= OUTER_REF_TABLE_BIT | RAND_TABLE_BIT; - bool use_quick_range=0; used_tables|=current_map; if (tab->type == JT_REF && tab->quick && @@ -4935,11 +4935,26 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) COND *tmp=make_cond_for_table(cond,used_tables,current_map); if (!tmp && tab->quick) { // Outer join - /* - Hack to handle the case where we only refer to a table - in the ON part of an OUTER JOIN. - */ - tmp=new Item_int((longlong) 1,1); // Always true + if (tab->type != JT_ALL) + { + /* + Don't use the quick method + We come here in the case where we have 'key=constant' and + the test is removed by make_cond_for_table() + */ + delete tab->quick; + tab->quick= 0; + } + else + { + /* + Hack to handle the case where we only refer to a table + in the ON part of an OUTER JOIN. In this case we want the code + below to check if we should use 'quick' instead. + */ + tmp= new Item_int((longlong) 1,1); // Always true + } + } if (tmp) { @@ -5989,6 +6004,7 @@ simplify_joins(JOIN *join, List<TABLE_LIST> *join_list, COND *conds, bool top) NESTED_JOIN *nested_join; TABLE_LIST *prev_table= 0; List_iterator<TABLE_LIST> li(*join_list); + DBUG_ENTER("simplify_joins"); /* Try to simplify join operations from join_list. @@ -6122,36 +6138,34 @@ simplify_joins(JOIN *join, List<TABLE_LIST> *join_list, COND *conds, bool top) li.replace(nested_join->join_list); } } - return conds; + DBUG_RETURN(conds); } static COND * optimize_cond(JOIN *join, COND *conds, Item::cond_result *cond_value) { + THD *thd= join->thd; SELECT_LEX *select= thd->lex->current_select; DBUG_ENTER("optimize_cond"); - THD *thd= join->thd; - SELECT_LEX *select= thd->lex->current_select; if (select->first_cond_optimization) { - Item_arena *arena, backup; + /* + The following code will allocate the new items in a permanent + MEMROOT for prepared statements and stored procedures. + */ + + Item_arena *arena=thd->current_arena, backup; select->first_cond_optimization= 0; - arena= thd->current_arena; - if (!arena->is_stmt_prepare()) - arena= 0; - else - thd->set_n_backup_item_arena(arena, &backup); + thd->set_n_backup_item_arena(arena, &backup); /* Convert all outer joins to inner joins if possible */ conds= simplify_joins(join, join->join_list, conds, TRUE); select->prep_where= conds ? conds->copy_andor_structure(thd) : 0; - select->first_cond_optimization= 0; - if (arena) - thd->restore_backup_item_arena(arena, &backup); + thd->restore_backup_item_arena(arena, &backup); } if (!conds) @@ -6161,7 +6175,7 @@ optimize_cond(JOIN *join, COND *conds, Item::cond_result *cond_value) } else { - DBUG_EXECUTE("where", print_where(conds, "after negation elimination");); + DBUG_EXECUTE("where", print_where(conds, "original");); /* change field = field to field = const for each found field = const */ propagate_cond_constants((I_List<COND_CMP> *) 0,conds,conds); /* diff --git a/sql/sql_table.cc b/sql/sql_table.cc index 03c20be198e..1d4414426d0 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -2209,7 +2209,7 @@ table_exists: char warn_buff[MYSQL_ERRMSG_SIZE]; my_snprintf(warn_buff, sizeof(warn_buff), ER(ER_TABLE_EXISTS_ERROR), table_name); - push_warning(thd, MYSQL_ERROR::WARN_LEVEL_WARN, + push_warning(thd, MYSQL_ERROR::WARN_LEVEL_NOTE, ER_TABLE_EXISTS_ERROR,warn_buff); res= 0; } diff --git a/sql/sql_union.cc b/sql/sql_union.cc index 827d75a9848..d23e11d5443 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -290,13 +290,14 @@ int st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result, thd_arg->lex->current_select= lex_select_save; if (!item_list.elements) { + Field **field; Item_arena *arena= thd->current_arena; Item_arena backup; if (!arena->is_stmt_prepare()) - arena= 0 + arena= 0; else thd->set_n_backup_item_arena(arena, &backup); - Field **field; + for (field= table->field; *field; field++) { Item_field *item= new Item_field(*field); diff --git a/sql/sql_view.cc b/sql/sql_view.cc index 183b55ca12f..8cc342e99fe 100644 --- a/sql/sql_view.cc +++ b/sql/sql_view.cc @@ -271,9 +271,9 @@ int mysql_create_view(THD *thd, } #endif - if (wait_if_global_read_lock(thd, 0)) + if (wait_if_global_read_lock(thd, 0, 0)) { - VOID(pthread_mutex_unlock(&LOCK_open)); + res= -1; goto err; } VOID(pthread_mutex_lock(&LOCK_open)); @@ -513,7 +513,9 @@ mysql_make_view(File_parser *parser, TABLE_LIST *table) will be TRUE as far as we make new table cache). */ Item_arena *arena= thd->current_arena, backup; - if (arena) + if (!arena->is_stmt_prepare()) + arena= 0; + else thd->set_n_backup_item_arena(arena, &backup); /* init timestamp */ diff --git a/sql/table.cc b/sql/table.cc index 5d0c60718d3..d07d2ca085d 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -85,7 +85,7 @@ int openfrm(const char *name, const char *alias, uint db_stat, uint prgflag, SQL_CRYPT *crypted=0; MEM_ROOT *old_root; DBUG_ENTER("openfrm"); - DBUG_PRINT("enter",("name: '%s' form: %lx",name,outparam)); + DBUG_PRINT("enter",("name: '%s' form: 0x%lx",name,outparam)); error=1; disk_buff=NULL; @@ -1548,9 +1548,7 @@ bool st_table_list::setup_ancestor(THD *thd, Item **conds) /* view fields translation table */ if (!(transl= - (Item**)(thd->current_arena ? - thd->current_arena : - thd)->alloc(select->item_list.elements * sizeof(Item*)))) + (Item**)(thd->current_arena->alloc(select->item_list.elements * sizeof(Item*))))) { DBUG_RETURN(1); } @@ -1585,6 +1583,9 @@ bool st_table_list::setup_ancestor(THD *thd, Item **conds) if (where) { Item_arena *arena= thd->current_arena, backup; + if (!arena->is_stmt_prepare()) + arena= 0; // For easier test + if (!where->fixed && where->fix_fields(thd, ancestor, &where)) goto err; diff --git a/sql/tztime.cc b/sql/tztime.cc index af9af530fec..5b7162a6a0e 100644 --- a/sql/tztime.cc +++ b/sql/tztime.cc @@ -1406,20 +1406,30 @@ extern "C" byte* my_offset_tzs_get_key(Time_zone_offset *entry, uint *length, for opening of time zone tables from preallocated array. */ -void -tz_init_table_list(TABLE_LIST *tz_tabs) +static void +tz_init_table_list(TABLE_LIST *tz_tabs, TABLE_LIST ***global_next_ptr) { bzero(tz_tabs, sizeof(TABLE_LIST) * 4); tz_tabs[0].alias= tz_tabs[0].real_name= (char*)"time_zone_name"; tz_tabs[1].alias= tz_tabs[1].real_name= (char*)"time_zone"; tz_tabs[2].alias= tz_tabs[2].real_name= (char*)"time_zone_transition_type"; tz_tabs[3].alias= tz_tabs[3].real_name= (char*)"time_zone_transition"; - tz_tabs[0].next= tz_tabs+1; - tz_tabs[1].next= tz_tabs+2; - tz_tabs[2].next= tz_tabs+3; + tz_tabs[0].next_global= tz_tabs[0].next_local= tz_tabs+1; + tz_tabs[1].next_global= tz_tabs[1].next_local= tz_tabs+2; + tz_tabs[2].next_global= tz_tabs[2].next_local= tz_tabs+3; tz_tabs[0].lock_type= tz_tabs[1].lock_type= tz_tabs[2].lock_type= tz_tabs[3].lock_type= TL_READ; tz_tabs[0].db= tz_tabs[1].db= tz_tabs[2].db= tz_tabs[3].db= (char *)"mysql"; + + /* Link into global list */ + tz_tabs[0].prev_global= *global_next_ptr; + tz_tabs[1].prev_global= &tz_tabs[0].next_global; + tz_tabs[2].prev_global= &tz_tabs[1].next_global; + tz_tabs[3].prev_global= &tz_tabs[2].next_global; + + **global_next_ptr= tz_tabs; + /* Update last-global-pointer to point to pointer in last table */ + *global_next_ptr= &tz_tabs[3].next_global; } @@ -1440,19 +1450,20 @@ tz_init_table_list(TABLE_LIST *tz_tabs) */ TABLE_LIST * -my_tz_get_table_list(THD *thd) +my_tz_get_table_list(THD *thd, TABLE_LIST ***global_next_ptr) { TABLE_LIST *tz_tabs; + DBUG_ENTER("my_tz_get_table_list"); if (!time_zone_tables_exist) - return 0; + DBUG_RETURN(0); if (!(tz_tabs= (TABLE_LIST *)thd->alloc(sizeof(TABLE_LIST) * 4))) - return &fake_time_zone_tables_list; + DBUG_RETURN(&fake_time_zone_tables_list); - tz_init_table_list(tz_tabs); + tz_init_table_list(tz_tabs, global_next_ptr); - return tz_tabs; + DBUG_RETURN(tz_tabs); } @@ -1486,7 +1497,7 @@ my_tz_init(THD *org_thd, const char *default_tzname, my_bool bootstrap) { THD *thd; TABLE_LIST *tables= 0; - TABLE_LIST tables_buff[5]; + TABLE_LIST tables_buff[5], **tmp_link, *first_table; TABLE *table; TZ_NAMES_ENTRY *tmp_tzname; my_bool return_val= 1; @@ -1553,9 +1564,10 @@ my_tz_init(THD *org_thd, const char *default_tzname, my_bool bootstrap) (char*)"time_zone_leap_second"; tables_buff[0].lock_type= TL_READ; tables_buff[0].db= thd->db; - tables_buff[0].next= tables_buff + 1; + tables_buff[0].next_global= tables_buff[0].next_local= tables_buff + 1; /* Fill TABLE_LIST for rest of the time zone describing tables */ - tz_init_table_list(tables_buff + 1); + tmp_link= &first_table; + tz_init_table_list(tables_buff + 1, &tmp_link); if (open_tables(thd, tables_buff, &counter) || lock_tables(thd, tables_buff, counter)) @@ -1757,8 +1769,9 @@ tz_load_from_open_tables(const String *tz_name, TABLE_LIST *tz_tables) and it is specifically for this purpose). */ table= tz_tables->table; - tz_tables= tz_tables->next; - table->field[0]->store(tz_name->ptr(), tz_name->length(), &my_charset_latin1); + tz_tables= tz_tables->next_local; + table->field[0]->store(tz_name->ptr(), tz_name->length(), + &my_charset_latin1); /* It is OK to ignore ha_index_init()/ha_index_end() return values since mysql.time_zone* tables are MyISAM and these operations always succeed @@ -1769,7 +1782,9 @@ tz_load_from_open_tables(const String *tz_name, TABLE_LIST *tz_tables) if (table->file->index_read(table->record[0], (byte*)table->field[0]->ptr, 0, HA_READ_KEY_EXACT)) { +#ifdef EXTRA_DEBUG sql_print_error("Can't find description of time zone."); +#endif goto end; } @@ -1783,7 +1798,7 @@ tz_load_from_open_tables(const String *tz_name, TABLE_LIST *tz_tables) using the only index in this table). */ table= tz_tables->table; - tz_tables= tz_tables->next; + tz_tables= tz_tables->next_local; table->field[0]->store((longlong)tzid); (void)table->file->ha_index_init(0); @@ -1810,7 +1825,7 @@ tz_load_from_open_tables(const String *tz_name, TABLE_LIST *tz_tables) Right - using special index. */ table= tz_tables->table; - tz_tables= tz_tables->next; + tz_tables= tz_tables->next_local; table->field[0]->store((longlong)tzid); (void)table->file->ha_index_init(0); diff --git a/sql/tztime.h b/sql/tztime.h index aabec260ec7..1325e921c67 100644 --- a/sql/tztime.h +++ b/sql/tztime.h @@ -59,7 +59,7 @@ public: extern Time_zone * my_tz_UTC; extern Time_zone * my_tz_SYSTEM; -extern TABLE_LIST * my_tz_get_table_list(THD *thd); +extern TABLE_LIST * my_tz_get_table_list(THD *thd, TABLE_LIST ***global_next_ptr); extern Time_zone * my_tz_find(const String *name, TABLE_LIST *tz_tables); extern my_bool my_tz_init(THD *org_thd, const char *default_tzname, my_bool bootstrap); extern void my_tz_free(); diff --git a/sql/unireg.cc b/sql/unireg.cc index c82fcc4abef..2a6abdb1cfd 100644 --- a/sql/unireg.cc +++ b/sql/unireg.cc @@ -321,7 +321,7 @@ static uint pack_keys(uchar *keybuff,uint key_count,KEY *keyinfo) pos[6]=pos[7]=0; // For the future pos+=8; key_parts+=key->key_parts; - DBUG_PRINT("loop",("flags: %d key_parts: %d at %lx", + DBUG_PRINT("loop",("flags: %d key_parts: %d at 0x%lx", key->flags,key->key_parts, key->key_part)); for (key_part=key->key_part,key_part_end=key_part+key->key_parts ; |