- developers - lists.mariadb.org

[Maria-developers] Bug in ENUM options handling?
by Steve Ellcey 04 Nov '11

04 Nov '11

While working on MariaDB on HP-UX we noticed an inconsistency involving the type of enum arguments. In init_variables and init_one_value (mysys/my_getopt.c), ENUM options are treated as unsigned longs but the enum arguments are actually declared as uint instead of ulong. While this doesn't matter in 32 bit mode when int's and longs's are the same size, it can cause problems in 64 bit mode when long's are 64 bits and int's 32 bits. We found 4 variables that we think should be ulong instead of uint: plugin_maturity plugin_maturity_map thread_handling delay_key_write_options We also changed the value field in the sys_var_enum class (sql/set_var.h) from a uint pointer to a ulong pointer and changed the sys_var_enum and sys_var_enum_const constructors to use a ulong pointer argument for value_arg to make everything consistent. I suppose init_variables and init_one_value could be changed to treat ENUM's as uint instead of ulong but the use of ulong seemed to be intentional so we didn't try making that change as an alternative fix. Is this a change that should be made in the official MariaDB sources? Steve Ellcey sje(a)cup.hp.com

5 8

[Maria-developers] review: [Commits] Rev 3045: Fix gcc 4.6 warning after merge with 5.1 in file:///home/bell/maria/bzr/work-maria-5.2-merge/
by Michael Widenius 04 Nov '11

04 Nov '11

Hi! Review follows: > Fix gcc 4.6 warning after merge with 5.1 > === modified file 'sql/table.cc' > --- a/sql/table.cc 2011-09-01 18:18:29 +0000 > +++ b/sql/table.cc 2011-10-27 16:18:25 +0000 > @@ -5484,7 +5484,7 @@ int update_virtual_fields(THD *thd, TABL > { > DBUG_ENTER("update_virtual_fields"); > Field **vfield_ptr, *vfield; > - int error= 0; > + int error __attribute__ ((unused))= 0; > if (!table || !table->vfield) > DBUG_RETURN(0); The above is wrong. The bug is that we ignore the value of error later down: error= vfield->vcol_info->expr_item->save_in_field(vfield, 0); We should change the above code to be: if (!ignore_errors && error < 0) { my_printf_error(ER_UNKNOWN_ERROR, "Got error when computing value for virtual column %s", vfield->field_name, MYF(0)); DBUG_RETURN(1); } We also need to add 'ignore_errors' as a parameter and add the new parameter to the calls. This can for now be '0' for all cases except for the cases where we check the value for the function, like in fill_record where we already handle the ignore_errors variable. Regards, Monty

1 0

[Maria-developers] Ubuntu 11.10 "Oneiric" packages
by Kristian Nielsen 02 Nov '11

02 Nov '11

Hi Daniel, I believe I've fixed the issues for our .debs on Ubuntu 11.10. I made buildbot create packages for MariaDB 5.2.9. They pass the tests. Buildnumber: 1325 Location in archive: /archive/pack/5.2/build-1325/kvm-deb-oneiric-* Maybe you can make them available alongside the existing 5.2.9 packages? It is the exact code version as the other platforms, but the packaging scripts are modified of course. There is no apparmor profile by default (this kept causing new problems, and future packages on Ubuntu will have it disabled by default). Maybe you can help test them as an extra check on top of the automatic ones in buildbot? I also tried getting 5.3 packages built, but Buildbot kept building the latest revision rather than the 5.3.2 one. I can probably solve that somehow, but unless it is urgent I would like to just wait for the next 5.3 release, which will get Oneiric packages automatically. Note that for 5.1, we currently have a problem, as Oneiric has MySQL 5.1.58 while the MariaDB version is 5.1.55. But for the next 5.1 release we will presumably merge MySQL 5.1.58 anyway, so this will get solved and most people will want to use 5.2 anyway I think. I think that's it, let me know if you have questions or other suggestions. - Kristian.

2 4

Re: [Maria-developers] How you build the MariaDB packages for Ubuntu
by Kristian Nielsen 02 Nov '11

02 Nov '11

Marian Marinov <mm(a)yuhu.biz> writes: > I'm trying to build MariaDB packages for CentOS. > But the problem I have is that the Policy of the distro does not allow > packages that are drop-in replacements of core packages. > Meaning that I can not simply build MariaDB which will provide libmysqlclient > and mysql binaries. > > Can you share with me what is the situation over in the Debian/Ubuntu camp ? In Debian/Ubuntu, there are no "Official" MariaDB packages yet. The ones we provide do replace the standard mysql packages, which is possible since there are no policies enforced for 3rd-party repositories. It will need to be fixed eventually, of course. The normal way I think in Debian is to have MariaDB and MySQL as alternatives, just like Some versions of Ubuntu/Debian have mysql-5.0 and mysql-5.1 as alternatives (can install one or the other, but not both). It is not 100% clear to me what your situation is (I'm not much familiar with CentOS or RPM). Are you saying that the MariaDB packages you create *must* be possible to install alongside the Official CentOS packages? In that case I suppose you have little option but to run mariadb on a different port, different data directory, different socket, different binary names. It seems to be sub-optimal for users, as they would need to reconfigure anything they want to use with MariaDB away from the standard port/names in the MySQL world. Still, this is perfectly possible to do. Configure a non-standard port in your my.cnf, build and install mariadb with a different --prefix, /opt/mariadb or whatever. - Kristian.

3 2

Re: [Maria-developers] [Commits] Rev 3262: Fix bug lp:833702 in file:///home/tsk/mprog/src/5.3/
by Oleksandr Byelkin 01 Nov '11

01 Nov '11

Hi! 01.11.2011 18:19, timour(a)askmonty.org пишет: > At file:///home/tsk/mprog/src/5.3/ > > ------------------------------------------------------------ > revno: 3262 > revision-id: timour(a)askmonty.org-20111101161919-hukh3tipjms3lfzf > parent: sanja(a)askmonty.org-20111101112209-n4ghsy9hpliiassm > fixes bug(s): https://launchpad.net/bugs/833702 > committer: timour(a)askmonty.org > branch nick: 5.3 > timestamp: Tue 2011-11-01 18:19:19 +0200 > message: > Fix bug lp:833702 > > Analysis: > Equality propagation propagated the constant '7' into > args[0] of the Item_in_optimizer that stands for the > "< ANY" predicate. At the same the min/max subquery > rewrite swapped the order of the left and right operands > of the "<" predicate, but used Item_in_subselect::left_expr. > > As a result, when the<ANY predicate is executed early in the > execution phase as a contant condition, instead of a constant > right (swapped) argument of the< predicate, there was a field > (t3.a). This field had no data, since the whole predicate is > considered constant, and it is evaluated before any tables are > read. Having junk in the field row buffer produced wrong result > > Solution: > Fix create_swap to pick the correct Item_in_optimizer left > argument. OK to push.

1 0

[Maria-developers] MariaDB test question (main.cast)
by sje＠cup.hp.com 28 Oct '11

28 Oct '11

I have downloaded, built and tested MariaDB 5.3.2 Beta on HP-UX 11.31 and I get two test failures, I have a question about one of tests that I was hoping someone could help me with. The main.cast test fails and the problem is with this part of the test: select cast(cast(20010203101112.1 as double) as datetime(1)); It is converting the string "20010203101112.1" to a double value and then converting the double value to a date. The test expects "2001-02-03 10:11:12.1" to be output but I am getting "2001-02-03 10:11:12.0". I think the problem is with the conversion into floating point. When I use gdb to break sql at double_to_datetime, on HP-UX the value I have is 20010203101112.098, on Linux I see 20010203101112.102. Neither value is completely correct because 20010203101112.1 cannot be represented exactly as an IEEE floating point value. So I have two questions. Where did the string get converted into a floating point value? I couldn't find where this is happening, I assume it is using strtod or something but breaking on that function didn't do anything. Secondly, given that 20010203101112.1 cannot be represented exactly as a double precision floating point value, is this a valid test? FYI: The other failure I get is main.cast, this test is trying to check for a stack overflow before it happens but IA64 HP-UX has two stacks, a register stack and a user data stack. In this test case the recursion of p_ere and p_ere_exp wind up using only the register stack because all the local variables and arguments are stored in registers. The stack check in check_stack_overrun is checking the user stack which doesn't overflow (or even change) and so we eventually die with a register stack overflow. There is a way to check the register stack on IA64 HP-UX, but it would require using IA64 assembly language so it may not be worth doing. Steve Ellcey sje(a)cup.hp.com

3 4

Re: [Maria-developers] [Commits] Rev 3100: Fix gcc 4.6 warnings about assigned but not used variables. in file:///home/bell/maria/bzr/work-maria-5.1-applebuild/
by Michael Widenius 27 Oct '11

27 Oct '11

Hi! >>>>> "sanja" == sanja <sanja(a)askmonty.org> writes: sanja> At file:///home/bell/maria/bzr/work-maria-5.1-applebuild/ sanja> ------------------------------------------------------------ sanja> revno: 3100 sanja> revision-id: sanja(a)askmonty.org-20111025142424-dntpnipgi5qxg2io sanja> parent: wlad(a)montyprogram.com-20111019185101-fw3oxkpr2o060qk4 sanja> committer: sanja(a)askmonty.org sanja> branch nick: work-maria-5.1-applebuild sanja> timestamp: Tue 2011-10-25 17:24:24 +0300 sanja> message: sanja> Fix gcc 4.6 warnings about assigned but not used variables. sanja> Fixed my_gethwaddr.c to allow compilation on Mac OS X. <cut> === modified file 'sql/sql_show.cc' --- a/sql/sql_show.cc 2011-05-12 11:56:08 +0000 +++ b/sql/sql_show.cc 2011-10-25 14:24:24 +0000 @@ -1215,7 +1215,7 @@ int store_create_info(THD *thd, TABLE_LI handler *file= table->file; TABLE_SHARE *share= table->s; HA_CREATE_INFO create_info; - bool show_table_options= FALSE; + bool show_table_options __attribute__ ((unused))= FALSE; bool foreign_db_mode= (thd->variables.sql_mode & (MODE_POSTGRESQL | MODE_ORACLE | MODE_MSSQL | Add instead: #ifdef WITH_PARTITION_STORAGE_ENGINE around the this the definition of the variable. @@ -1432,7 +1432,9 @@ int store_create_info(THD *thd, TABLE_LI packet->append(STRING_WITH_LEN("\n)")); if (!(thd->variables.sql_mode & MODE_NO_TABLE_OPTIONS) && !foreign_db_mode) { +#ifdef WITH_PARTITION_STORAGE_ENGINE show_table_options= TRUE; +#endif The above is correct; Better to do it this way. === modified file 'storage/maria/ma_blockrec.c' --- a/storage/maria/ma_blockrec.c 2011-07-24 08:25:28 +0000 +++ b/storage/maria/ma_blockrec.c 2011-10-25 14:24:24 +0000 @@ -4997,7 +4997,7 @@ static my_bool read_row_extent_info(MARI MARIA_EXTENT_CURSOR extent; MARIA_RECORD_POS *tail_pos; uchar *data, *end_of_data; - uint flag, row_extents, row_extents_size, field_lengths; + uint flag, row_extents, row_extents_size; uchar *extents, *end; DBUG_ENTER("read_row_extent_info"); @@ -5032,9 +5032,6 @@ static my_bool read_row_extent_info(MARI } info->cur_row.extents_count= row_extents; - if (share->base.max_field_lengths) - get_key_length(field_lengths, data); - The above is wrong. get_key_length will increment data, which is required as data it's used later. Better to leave field_lengths there for the future. (Please fix this in 5.3 too !) You can avoid the warning by doing: get_key_length= get_key_length; === modified file 'storage/maria/ma_dynrec.c' --- a/storage/maria/ma_dynrec.c 2011-02-25 12:55:40 +0000 +++ b/storage/maria/ma_dynrec.c 2011-10-25 14:24:24 +0000 @@ -1741,7 +1741,8 @@ int _ma_read_rnd_dynamic_record(MARIA_HA MARIA_RECORD_POS filepos, my_bool skip_deleted_blocks) { - int block_of_record, info_read; + int block_of_record; + int info_read; Why the above? === modified file 'storage/maria/ma_ft_update.c' --- a/storage/maria/ma_ft_update.c 2009-01-30 21:55:42 +0000 +++ b/storage/maria/ma_ft_update.c 2011-10-25 14:24:24 +0000 @@ -319,7 +319,6 @@ my_bool _ma_ft_convert_to_ft2(MARIA_HA * uchar *key_ptr= (uchar*) dynamic_array_ptr(da, 0), *end; uint length, key_length; MARIA_PINNED_PAGE tmp_page_link, *page_link= &tmp_page_link; - MARIA_KEY tmp_key; MARIA_PAGE page; DBUG_ENTER("_ma_ft_convert_to_ft2"); @@ -357,13 +356,8 @@ my_bool _ma_ft_convert_to_ft2(MARIA_HA * /* inserting the rest of key values */ end= (uchar*) dynamic_array_ptr(da, da->elements); - tmp_key.keyinfo= keyinfo; - tmp_key.data_length= keyinfo->keylength; - tmp_key.ref_length= 0; - tmp_key.flag= 0; for (key_ptr+=length; key_ptr < end; key_ptr+=keyinfo->keylength) { - tmp_key.data= key_ptr; if (_ma_ck_real_write_btree(info, key, &root, SEARCH_SAME)) DBUG_RETURN(1); } Wrong. The correct fix is: if (_ma_ck_real_write_btree(info, key, &root, SEARCH_SAME)) DBUG_RETURN(1); -> if (_ma_ck_real_write_btree(info, &tmp_key, &root, SEARCH_SAME)) DBUG_RETURN(1); Apparently free textsearch with Aria is not tested properly :( <cut> === modified file 'storage/maria/ma_open.c' --- a/storage/maria/ma_open.c 2011-07-24 08:25:28 +0000 +++ b/storage/maria/ma_open.c 2011-10-25 14:24:24 +0000 @@ -1802,7 +1802,6 @@ void _ma_set_index_pagecache_callbacks(P int _ma_open_datafile(MARIA_HA *info, MARIA_SHARE *share, const char *org_name, File file_to_dup __attribute__((unused))) { - char *data_name= share->data_file_name.str; char real_data_name[FN_REFLEN]; if (org_name) @@ -1816,7 +1815,6 @@ int _ma_open_datafile(MARIA_HA *info, MA my_errno= HA_WRONG_CREATE_OPTION; return 1; } - data_name= real_data_name; } } Wrong: The correct fix is: info->dfile.file= share->bitmap.file.file= my_open(share->data_file_name.str, share->mode | O_SHARE, MYF(MY_WME)); -> info->dfile.file= share->bitmap.file.file= my_open(data_name, share->mode | O_SHARE, MYF(MY_WME)); The above means that symlinking didn't work on windows with Aria files. === modified file 'storage/maria/ma_rt_split.c' --- a/storage/maria/ma_rt_split.c 2010-09-05 23:25:44 +0000 +++ b/storage/maria/ma_rt_split.c 2011-10-25 14:24:24 +0000 @@ -380,7 +380,6 @@ int maria_rtree_split_page(const MARIA_K SplitStruct *stop; double *coord_buf; double *next_coord; - double *old_coord; int n_dim; uchar *source_cur, *cur1, *cur2; uchar *new_page_buff, *log_internal_copy, *log_internal_copy_ptr, @@ -426,7 +425,6 @@ int maria_rtree_split_page(const MARIA_K maria_rtree_d_mbr(keyinfo->seg, key->data, key_data_length, cur->coords); cur->key= key->data; - old_coord= next_coord; myisam/rt_split.c has the exact same code. You should do the same change there. === modified file 'storage/myisam/mi_check.c' --- a/storage/myisam/mi_check.c 2011-05-02 17:58:45 +0000 +++ b/storage/myisam/mi_check.c 2011-10-25 14:24:24 +0000 @@ -946,7 +946,6 @@ int chk_data_link(HA_CHECK *param, MI_IN char llbuff[22],llbuff2[22],llbuff3[22]; ha_checksum intern_record_checksum; ha_checksum key_checksum[HA_MAX_POSSIBLE_KEY]; - my_bool static_row_size; MI_KEYDEF *keyinfo; MI_BLOCK_INFO block_info; DBUG_ENTER("chk_data_link"); @@ -971,7 +970,6 @@ int chk_data_link(HA_CHECK *param, MI_IN empty=info->s->pack.header_length; /* Check how to calculate checksum of rows */ - static_row_size=1; if (info->s->data_file_type == COMPRESSED_RECORD) { for (field=0 ; field < info->s->base.fields ; field++) @@ -979,7 +977,6 @@ int chk_data_link(HA_CHECK *param, MI_IN if (info->s->rec[field].base_type == FIELD_BLOB || info->s->rec[field].base_type == FIELD_VARCHAR) { - static_row_size=0; break; } } Please remove also the if and the loop around the above code. === modified file 'storage/pbxt/src/ha_pbxt.cc' --- a/storage/pbxt/src/ha_pbxt.cc 2010-11-26 22:37:34 +0000 +++ b/storage/pbxt/src/ha_pbxt.cc 2011-10-25 14:24:24 +0000 @@ -1559,7 +1559,7 @@ static int pbxt_rollback(handlerton *hto if (!all) self->st_stat_trans = FALSE; } - return 0; + return err; } #ifdef DRIZZLED @@ -2863,7 +2863,7 @@ int ha_pbxt::update_row(const byte * old * insert into t1 (val) values (1); */ if (table->found_next_number_field && new_data == table->record[0]) { - MX_LONGLONG_T nr; + MX_LONGLONG_T nr __attribute__ ((unused)); my_bitmap_map *old_map; old_map = mx_tmp_use_all_columns(table, table->read_set); Better to remove the nr value totally (and the call that sets it). This is safe as in the code: nr = table->found_next_number_field->val_int(); ha_set_auto_increment(pb_open_tab, table->found_next_number_field); The first thing ha_set_auto_increment() is doing is calling val_int() on the second argument. <cut> === modified file 'storage/pbxt/src/thread_xt.cc' --- a/storage/pbxt/src/thread_xt.cc 2010-09-28 13:05:45 +0000 +++ b/storage/pbxt/src/thread_xt.cc 2011-10-25 14:24:24 +0000 @@ -485,7 +485,8 @@ static void thr_free_resources(XTThreadP xtPublic void xt_bug(XTThreadPtr XT_UNUSED(self)) { - static int *bug_ptr = NULL; + static int *bug_ptr __attribute__ ((unused)); + bug_ptr= NULL; bug_ptr = NULL; } No reason to set this to null twice. Better to remove the variable altogether. === modified file 'storage/pbxt/src/xactlog_xt.cc' --- a/storage/pbxt/src/xactlog_xt.cc 2010-05-05 10:59:57 +0000 +++ b/storage/pbxt/src/xactlog_xt.cc 2011-10-25 14:24:24 +0000 @@ -2117,7 +2117,7 @@ xtBool XTDatabaseLog::xlog_seq_next(XTXa size_t rec_offset; size_t max_rec_len; size_t size; - u_int check_size = 1; + u_int check_size __attribute__ ((unused))= 1; Don't understand the above change. We both set check_size and test it in the above function. Are you sure that you get a warning about the above? /* Go to the next record (xseq_record_len must be initialized * to 0 for this to work. @@ -2629,7 +2629,7 @@ static void *xlog_wr_run_thread(XTThread { XTDatabaseHPtr db = (XTDatabaseHPtr) self->t_data; int count; - void *mysql_thread; + void *mysql_thread __attribute__ ((unused)); mysql_thread = myxt_create_thread(); === modified file 'storage/xtradb/btr/btr0cur.c' --- a/storage/xtradb/btr/btr0cur.c 2011-04-29 14:16:42 +0000 +++ b/storage/xtradb/btr/btr0cur.c 2011-10-25 14:24:24 +0000 @@ -3301,7 +3301,7 @@ static void btr_record_not_null_field_in_rec( /*=============================*/ - rec_t* rec, /*!< in: physical record */ + rec_t* rec __attribute__ ((unused)),/*!< in: physical record */ ulint n_unique, /*!< in: dict_index_get_n_unique(index), number of columns uniquely determine an index entry */ @@ -3321,9 +3321,8 @@ btr_record_not_null_field_in_rec( for (i = 0; i < n_unique; i++) { ulint rec_len; - byte* field; - field = rec_get_nth_field(rec, offsets, i, &rec_len); + rec_get_nth_field_offs(offsets, i, &rec_len); if (rec_len != UNIV_SQL_NULL) { n_not_null[i]++; === modified file 'storage/xtradb/buf/buf0buf.c' --- a/storage/xtradb/buf/buf0buf.c 2011-04-29 14:16:42 +0000 +++ b/storage/xtradb/buf/buf0buf.c 2011-10-25 14:24:24 +0000 @@ -3895,7 +3895,7 @@ buf_page_io_complete( enum buf_io_fix io_type; const ibool uncompressed = (buf_page_get_state(bpage) == BUF_BLOCK_FILE_PAGE); - enum buf_flush flush_type; + //enum buf_flush flush_type; mutex_t* block_mutex; ut_a(buf_page_in_file(bpage)); @@ -4051,7 +4051,7 @@ corrupt: //buf_pool_mutex_enter(); if (io_type == BUF_IO_WRITE) { - flush_type = buf_page_get_flush_type(bpage); + //flush_type = buf_page_get_flush_type(bpage); /* to keep consistency at buf_LRU_insert_zip_clean() */ //if (flush_type == BUF_FLUSH_LRU) { /* optimistic! */ mutex_enter(&LRU_list_mutex); Remove the variable from the start of the function and enum buf_flush flush_type; to the if. That way, all the comments are in one place. Ok to push after the above changes. Regards, Monty

3 5

Re: [Maria-developers] FYI optimizer problems fixed in maria-5.5
by Sergei Petrunia 27 Oct '11

27 Oct '11

Hello! JFYI I've just pushed the fix for table_elim.test into 5.5-main. With this, I consider my involvement with 5.5 merge to be done, i.e. I don't have anything on my todo that's related to 5.5. BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

2 1

Re: [Maria-developers] [Commits] Rev 3249: Fix of LP BUG#872775. in file:///home/bell/maria/bzr/work-maria-5.3-lpb872775/
by Sergei Petrunia 26 Oct '11

26 Oct '11

Hi Sanja, Please add a comment around the defitions of nest_level / nest_level_base that nest_levels are local to the query or VIEW, and that view merge procedure does not re-calculate them. Ok to push after the above is addressed. On Tue, Oct 25, 2011 at 11:59:37AM +0300, sanja(a)askmonty.org wrote: > At file:///home/bell/maria/bzr/work-maria-5.3-lpb872775/ > > ------------------------------------------------------------ > revno: 3249 > revision-id: sanja(a)askmonty.org-20111025085936-h0jyyxtbv3qhp4f4 > parent: igor(a)askmonty.org-20111023124603-sjv665ajk25b4dgi > committer: sanja(a)askmonty.org > branch nick: work-maria-5.3-lpb872775 > timestamp: Tue 2011-10-25 11:59:36 +0300 > message: > Fix of LP BUG#872775. > > The problem was that merged views has its own nest_level numbering => > when we compare nest levels we should take into considiration basis (i.e. 0 level), > if it is different then nest levels are not comparable. > === modified file 'mysql-test/r/subselect_cache.result' > --- a/mysql-test/r/subselect_cache.result 2011-07-19 20:19:10 +0000 > +++ b/mysql-test/r/subselect_cache.result 2011-10-25 08:59:36 +0000 > @@ -1,3 +1,5 @@ > +drop table if exists t1,t2,t3,t4,t5; > +drop view if exists v1; > set optimizer_switch='subquery_cache=on'; > create table t1 (a int, b int); > insert into t1 values (1,2),(3,4),(1,2),(3,4),(3,4),(4,5),(4,5),(5,6),(5,6),(4,5); > @@ -3354,5 +3356,31 @@ f1 f2 f3 f3 > 7 0 0 0 > 7 0 0 0 > drop tables t1, t2, t3; > +# > +# Test of LP BUG#872775 view with "outer references" bug > +# > +set @@optimizer_switch= default; > +set optimizer_switch='subquery_cache=on'; > +CREATE TABLE t1 (a int) ; > +CREATE TABLE t2 (b int, c varchar(1) NOT NULL ) ; > +INSERT INTO t2 VALUES (1,'x'),(2,'y'); > +CREATE TABLE t3 (a int) ; > +CREATE TABLE t4 ( pk int(11) NOT NULL , b int(11) NOT NULL ) ; > +INSERT INTO t4 VALUES (26,9),(27,5),(28,0),(29,3); > +CREATE OR REPLACE VIEW v1 AS > +SELECT t2.b > +FROM t1 > +JOIN t2 > +WHERE t2 .c > ( > +SELECT t2.c FROM t3 > +); > +SELECT * FROM t4 WHERE b NOT IN ( SELECT * FROM v1 ); > +pk b > +26 9 > +27 5 > +28 0 > +29 3 > +drop view v1; > +drop table t1,t2,t3,t4; > # restore default > set @@optimizer_switch= default; > > === modified file 'mysql-test/t/subselect_cache.test' > --- a/mysql-test/t/subselect_cache.test 2011-07-19 20:19:10 +0000 > +++ b/mysql-test/t/subselect_cache.test 2011-10-25 08:59:36 +0000 > @@ -1,4 +1,10 @@ > > +--disable_warnings > +drop table if exists t1,t2,t3,t4,t5; > +drop view if exists v1; > +--enable_warnings > + > + > set optimizer_switch='subquery_cache=on'; > > create table t1 (a int, b int); > @@ -1664,5 +1670,33 @@ FROM t1 > WHERE t2.f1 OR t2.f3 ); > drop tables t1, t2, t3; > > +--echo # > +--echo # Test of LP BUG#872775 view with "outer references" bug > +--echo # > +set @@optimizer_switch= default; > +set optimizer_switch='subquery_cache=on'; > +CREATE TABLE t1 (a int) ; > + > +CREATE TABLE t2 (b int, c varchar(1) NOT NULL ) ; > +INSERT INTO t2 VALUES (1,'x'),(2,'y'); > + > +CREATE TABLE t3 (a int) ; > + > +CREATE TABLE t4 ( pk int(11) NOT NULL , b int(11) NOT NULL ) ; > +INSERT INTO t4 VALUES (26,9),(27,5),(28,0),(29,3); > + > +CREATE OR REPLACE VIEW v1 AS > +SELECT t2.b > +FROM t1 > +JOIN t2 > +WHERE t2 .c > ( > + SELECT t2.c FROM t3 > + ); > + > +SELECT * FROM t4 WHERE b NOT IN ( SELECT * FROM v1 ); > + > +drop view v1; > +drop table t1,t2,t3,t4; > + > --echo # restore default > set @@optimizer_switch= default; > > === modified file 'sql/item.cc' > --- a/sql/item.cc 2011-10-22 07:14:27 +0000 > +++ b/sql/item.cc 2011-10-25 08:59:36 +0000 > @@ -755,7 +755,9 @@ bool Item_ident::remove_dependence_proce > bool Item_ident::collect_outer_ref_processor(uchar *param) > { > Collect_deps_prm *prm= (Collect_deps_prm *)param; > - if (depended_from && depended_from->nest_level < prm->nest_level) > + if (depended_from && > + depended_from->nest_level_base == prm->nest_level_base && > + depended_from->nest_level < prm->nest_level) > prm->parameters->add_unique(this, &cmp_items); > return FALSE; > } > > === modified file 'sql/item.h' > --- a/sql/item.h 2011-10-22 07:14:27 +0000 > +++ b/sql/item.h 2011-10-25 08:59:36 +0000 > @@ -505,6 +505,7 @@ typedef void (*Cond_traverser) (const It > class Item_equal; > class COND_EQUAL; > > +class st_select_lex_unit; > > class Item { > Item(const Item &); /* Prevent use of these */ > @@ -1159,8 +1160,10 @@ public: > } > struct Collect_deps_prm > { > - int nest_level; > List<Item> *parameters; > + /* unit from which we count nest_level */ > + st_select_lex_unit *nest_level_base; > + int nest_level; > }; > /** > Collect outer references > > === modified file 'sql/item_subselect.cc' > --- a/sql/item_subselect.cc 2011-10-22 07:14:27 +0000 > +++ b/sql/item_subselect.cc 2011-10-25 08:59:36 +0000 > @@ -599,7 +599,9 @@ bool Item_subselect::exec() > > void Item_subselect::get_cache_parameters(List<Item> &parameters) > { > - Collect_deps_prm prm= { unit->first_select()->nest_level, &parameters }; > + Collect_deps_prm prm= {&parameters, > + unit->first_select()->nest_level_base, > + unit->first_select()->nest_level}; > walk(&Item::collect_outer_ref_processor, TRUE, (uchar*)&prm); > } > > > === modified file 'sql/item_sum.cc' > --- a/sql/item_sum.cc 2011-07-19 20:19:10 +0000 > +++ b/sql/item_sum.cc 2011-10-25 08:59:36 +0000 > @@ -359,7 +359,9 @@ bool Item_sum::collect_outer_ref_process > { > Collect_deps_prm *prm= (Collect_deps_prm *)param; > SELECT_LEX *ds; > - if ((ds= depended_from()) && ds->nest_level < prm->nest_level) > + if ((ds= depended_from()) && > + ds->nest_level_base == prm->nest_level_base && > + ds->nest_level < prm->nest_level) > prm->parameters->add_unique(this, &cmp_items); > return FALSE; > } > > === modified file 'sql/sql_lex.cc' > --- a/sql/sql_lex.cc 2011-10-22 07:14:27 +0000 > +++ b/sql/sql_lex.cc 2011-10-25 08:59:36 +0000 > @@ -339,6 +339,7 @@ void lex_start(THD *thd) > lex->event_parse_data= NULL; > lex->profile_options= PROFILE_NONE; > lex->nest_level=0 ; > + lex->select_lex.nest_level_base= &lex->unit; > lex->allow_sum_func= 0; > lex->in_sum_func= NULL; > lex->protect_against_global_read_lock= FALSE; > > === modified file 'sql/sql_lex.h' > --- a/sql/sql_lex.h 2011-09-22 22:13:38 +0000 > +++ b/sql/sql_lex.h 2011-10-25 08:59:36 +0000 > @@ -684,6 +684,7 @@ public: > ulong table_join_options; > uint in_sum_expr; > uint select_number; /* number of select (used for EXPLAIN) */ > + SELECT_LEX_UNIT *nest_level_base; /* unit from which we count nest_level */ > int nest_level; /* nesting level of select */ > Item_sum *inner_sum_func_list; /* list of sum func in nested selects */ > uint with_wild; /* item list contain '*' */ > > === modified file 'sql/sql_parse.cc' > --- a/sql/sql_parse.cc 2011-10-11 10:55:42 +0000 > +++ b/sql/sql_parse.cc 2011-10-25 08:59:36 +0000 > @@ -5917,6 +5917,7 @@ mysql_new_select(LEX *lex, bool move_dow > DBUG_RETURN(1); > } > select_lex->nest_level= lex->nest_level; > + select_lex->nest_level_base= &thd->lex->unit; > if (move_down) > { > SELECT_LEX_UNIT *unit; > > _______________________________________________ > commits mailing list > commits(a)mariadb.org > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits -- BR Sergey -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

Re: [Maria-developers] [Commits] Rev 3249: Fix of LP BUG#872775. in file:///home/bell/maria/bzr/work-maria-5.3-lpb872775/
by Sergei Petrunia 25 Oct '11

25 Oct '11

On Tue, Oct 25, 2011 at 11:59:37AM +0300, sanja(a)askmonty.org wrote: > At file:///home/bell/maria/bzr/work-maria-5.3-lpb872775/ > > ------------------------------------------------------------ > revno: 3249 > revision-id: sanja(a)askmonty.org-20111025085936-h0jyyxtbv3qhp4f4 > parent: igor(a)askmonty.org-20111023124603-sjv665ajk25b4dgi > committer: sanja(a)askmonty.org > branch nick: work-maria-5.3-lpb872775 > timestamp: Tue 2011-10-25 11:59:36 +0300 > message: > Fix of LP BUG#872775. > > The problem was that merged views has its own nest_level numbering => > when we compare nest levels we should take into considiration basis (i.e. 0 level), > if it is different then nest levels are not comparable. :grep nest_level_base *.h *.c* item.h|1165| st_select_lex_unit *nest_level_base; sql_lex.h|687| SELECT_LEX_UNIT *nest_level_base; /* unit from which we count nest_level */ item.cc|759| depended_from->nest_level_base == prm->nest_level_base && item_subselect.cc|603| unit->first_select()->nest_level_base, item_sum.cc|363| ds->nest_level_base == prm->nest_level_base && sql_lex.cc|342| lex->select_lex.nest_level_base= &lex->unit; sql_parse.cc|5920| select_lex->nest_level_base= &thd->lex->unit; The only places where nest_level_base is assigned are: sql_lex.cc|342| lex->select_lex.nest_level_base= &lex->unit; sql_parse.cc|5920| select_lex->nest_level_base= &thd->lex->unit; both of these initialize it to the top-level unit. So what is the point of having nest_level_base in this case? Please clarify :) > === modified file 'mysql-test/r/subselect_cache.result' > --- a/mysql-test/r/subselect_cache.result 2011-07-19 20:19:10 +0000 > +++ b/mysql-test/r/subselect_cache.result 2011-10-25 08:59:36 +0000 > @@ -1,3 +1,5 @@ > +drop table if exists t1,t2,t3,t4,t5; > +drop view if exists v1; > set optimizer_switch='subquery_cache=on'; > create table t1 (a int, b int); > insert into t1 values (1,2),(3,4),(1,2),(3,4),(3,4),(4,5),(4,5),(5,6),(5,6),(4,5); > @@ -3354,5 +3356,31 @@ f1 f2 f3 f3 > 7 0 0 0 > 7 0 0 0 > drop tables t1, t2, t3; > +# > +# Test of LP BUG#872775 view with "outer references" bug > +# > +set @@optimizer_switch= default; > +set optimizer_switch='subquery_cache=on'; > +CREATE TABLE t1 (a int) ; > +CREATE TABLE t2 (b int, c varchar(1) NOT NULL ) ; > +INSERT INTO t2 VALUES (1,'x'),(2,'y'); > +CREATE TABLE t3 (a int) ; > +CREATE TABLE t4 ( pk int(11) NOT NULL , b int(11) NOT NULL ) ; > +INSERT INTO t4 VALUES (26,9),(27,5),(28,0),(29,3); > +CREATE OR REPLACE VIEW v1 AS > +SELECT t2.b > +FROM t1 > +JOIN t2 > +WHERE t2 .c > ( > +SELECT t2.c FROM t3 > +); > +SELECT * FROM t4 WHERE b NOT IN ( SELECT * FROM v1 ); > +pk b > +26 9 > +27 5 > +28 0 > +29 3 > +drop view v1; > +drop table t1,t2,t3,t4; > # restore default > set @@optimizer_switch= default; > > === modified file 'mysql-test/t/subselect_cache.test' > --- a/mysql-test/t/subselect_cache.test 2011-07-19 20:19:10 +0000 > +++ b/mysql-test/t/subselect_cache.test 2011-10-25 08:59:36 +0000 > @@ -1,4 +1,10 @@ > > +--disable_warnings > +drop table if exists t1,t2,t3,t4,t5; > +drop view if exists v1; > +--enable_warnings > + > + > set optimizer_switch='subquery_cache=on'; > > create table t1 (a int, b int); > @@ -1664,5 +1670,33 @@ FROM t1 > WHERE t2.f1 OR t2.f3 ); > drop tables t1, t2, t3; > > +--echo # > +--echo # Test of LP BUG#872775 view with "outer references" bug > +--echo # > +set @@optimizer_switch= default; > +set optimizer_switch='subquery_cache=on'; > +CREATE TABLE t1 (a int) ; > + > +CREATE TABLE t2 (b int, c varchar(1) NOT NULL ) ; > +INSERT INTO t2 VALUES (1,'x'),(2,'y'); > + > +CREATE TABLE t3 (a int) ; > + > +CREATE TABLE t4 ( pk int(11) NOT NULL , b int(11) NOT NULL ) ; > +INSERT INTO t4 VALUES (26,9),(27,5),(28,0),(29,3); > + > +CREATE OR REPLACE VIEW v1 AS > +SELECT t2.b > +FROM t1 > +JOIN t2 > +WHERE t2 .c > ( > + SELECT t2.c FROM t3 > + ); > + > +SELECT * FROM t4 WHERE b NOT IN ( SELECT * FROM v1 ); > + > +drop view v1; > +drop table t1,t2,t3,t4; > + > --echo # restore default > set @@optimizer_switch= default; > > === modified file 'sql/item.cc' > --- a/sql/item.cc 2011-10-22 07:14:27 +0000 > +++ b/sql/item.cc 2011-10-25 08:59:36 +0000 > @@ -755,7 +755,9 @@ bool Item_ident::remove_dependence_proce > bool Item_ident::collect_outer_ref_processor(uchar *param) > { > Collect_deps_prm *prm= (Collect_deps_prm *)param; > - if (depended_from && depended_from->nest_level < prm->nest_level) > + if (depended_from && > + depended_from->nest_level_base == prm->nest_level_base && > + depended_from->nest_level < prm->nest_level) > prm->parameters->add_unique(this, &cmp_items); > return FALSE; > } > > === modified file 'sql/item.h' > --- a/sql/item.h 2011-10-22 07:14:27 +0000 > +++ b/sql/item.h 2011-10-25 08:59:36 +0000 > @@ -505,6 +505,7 @@ typedef void (*Cond_traverser) (const It > class Item_equal; > class COND_EQUAL; > > +class st_select_lex_unit; > > class Item { > Item(const Item &); /* Prevent use of these */ > @@ -1159,8 +1160,10 @@ public: > } > struct Collect_deps_prm > { > - int nest_level; > List<Item> *parameters; > + /* unit from which we count nest_level */ > + st_select_lex_unit *nest_level_base; > + int nest_level; > }; > /** > Collect outer references > > === modified file 'sql/item_subselect.cc' > --- a/sql/item_subselect.cc 2011-10-22 07:14:27 +0000 > +++ b/sql/item_subselect.cc 2011-10-25 08:59:36 +0000 > @@ -599,7 +599,9 @@ bool Item_subselect::exec() > > void Item_subselect::get_cache_parameters(List<Item> &parameters) > { > - Collect_deps_prm prm= { unit->first_select()->nest_level, &parameters }; > + Collect_deps_prm prm= {&parameters, > + unit->first_select()->nest_level_base, > + unit->first_select()->nest_level}; > walk(&Item::collect_outer_ref_processor, TRUE, (uchar*)&prm); > } > > > === modified file 'sql/item_sum.cc' > --- a/sql/item_sum.cc 2011-07-19 20:19:10 +0000 > +++ b/sql/item_sum.cc 2011-10-25 08:59:36 +0000 > @@ -359,7 +359,9 @@ bool Item_sum::collect_outer_ref_process > { > Collect_deps_prm *prm= (Collect_deps_prm *)param; > SELECT_LEX *ds; > - if ((ds= depended_from()) && ds->nest_level < prm->nest_level) > + if ((ds= depended_from()) && > + ds->nest_level_base == prm->nest_level_base && > + ds->nest_level < prm->nest_level) > prm->parameters->add_unique(this, &cmp_items); > return FALSE; > } > > === modified file 'sql/sql_lex.cc' > --- a/sql/sql_lex.cc 2011-10-22 07:14:27 +0000 > +++ b/sql/sql_lex.cc 2011-10-25 08:59:36 +0000 > @@ -339,6 +339,7 @@ void lex_start(THD *thd) > lex->event_parse_data= NULL; > lex->profile_options= PROFILE_NONE; > lex->nest_level=0 ; > + lex->select_lex.nest_level_base= &lex->unit; > lex->allow_sum_func= 0; > lex->in_sum_func= NULL; > lex->protect_against_global_read_lock= FALSE; > > === modified file 'sql/sql_lex.h' > --- a/sql/sql_lex.h 2011-09-22 22:13:38 +0000 > +++ b/sql/sql_lex.h 2011-10-25 08:59:36 +0000 > @@ -684,6 +684,7 @@ public: > ulong table_join_options; > uint in_sum_expr; > uint select_number; /* number of select (used for EXPLAIN) */ > + SELECT_LEX_UNIT *nest_level_base; /* unit from which we count nest_level */ > int nest_level; /* nesting level of select */ > Item_sum *inner_sum_func_list; /* list of sum func in nested selects */ > uint with_wild; /* item list contain '*' */ > > === modified file 'sql/sql_parse.cc' > --- a/sql/sql_parse.cc 2011-10-11 10:55:42 +0000 > +++ b/sql/sql_parse.cc 2011-10-25 08:59:36 +0000 > @@ -5917,6 +5917,7 @@ mysql_new_select(LEX *lex, bool move_dow > DBUG_RETURN(1); > } > select_lex->nest_level= lex->nest_level; > + select_lex->nest_level_base= &thd->lex->unit; > if (move_down) > { > SELECT_LEX_UNIT *unit; > BR Sergey -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

2 1