- developers - lists.mariadb.org

[Maria-developers] Please test: Rev 3458: MDEV-376: Wrong result (missing rows) with index_merge+index_merge_intersection, join
by Sergei Petrunia 04 Jul '12

04 Jul '12

Hi Elena, Could you please re-run the tests with this fix: ----- Forwarded message from Sergey Petrunya <psergey(a)askmonty.org> ----- From: Sergey Petrunya <psergey(a)askmonty.org> To: commits(a)mariadb.org X-Mailer: mail (GNU Mailutils 1.2) Date: Wed, 4 Jul 2012 13:34:48 +0300 (EEST) Subject: [Commits] Rev 3458: MDEV-376: Wrong result (missing rows) with index_merge+index_merge_intersection, join in file:///home/psergey/dev2/5.5-look6/ At file:///home/psergey/dev2/5.5-look6/ ------------------------------------------------------------ revno: 3458 revision-id: psergey(a)askmonty.org-20120704103445-ro4sk3cwbbzn3t4p parent: psergey(a)askmonty.org-20120623161254-ido3pf16l31psyyv committer: Sergey Petrunya <psergey(a)askmonty.org> branch nick: 5.5-look6 timestamp: Wed 2012-07-04 14:34:45 +0400 message: MDEV-376: Wrong result (missing rows) with index_merge+index_merge_intersection, join - Let QUICK_RANGE_SELECT::init_ror_merged_scan() call quick->reset() only after we've set the column read bitmaps. === modified file 'mysql-test/r/index_merge_innodb.result' --- a/mysql-test/r/index_merge_innodb.result 2012-06-06 19:26:40 +0000 +++ b/mysql-test/r/index_merge_innodb.result 2012-07-04 10:34:45 +0000 @@ -777,4 +777,19 @@ commit; select * from t1 where t1.zone_id=830 AND modified=9; pk zone_id modified drop table t0, t1; +# +# MDEV-376: Wrong result (missing rows) with index_merge+index_merge_intersection, join +# +CREATE TABLE t1 ( +a INT, b CHAR(1), c CHAR(1), KEY(a), KEY(b) +) ENGINE=InnoDB; +INSERT INTO t1 VALUES (8,'v','v'),(8,'m','m'),(9,'d','d'); +SELECT ta.* FROM t1 AS ta, t1 AS tb +WHERE ( tb.b != ta.b OR tb.a = ta.a ) +AND ( tb.b = ta.c OR tb.b = ta.b ); +a b c +8 v v +8 m m +9 d d +DROP TABLE t1; set optimizer_switch= @optimizer_switch_save; === modified file 'mysql-test/t/index_merge_innodb.test' --- a/mysql-test/t/index_merge_innodb.test 2012-06-04 15:26:11 +0000 +++ b/mysql-test/t/index_merge_innodb.test 2012-07-04 10:34:45 +0000 @@ -156,6 +156,21 @@ select * from t1 where t1.zone_id=830 AN drop table t0, t1; +--echo # +--echo # MDEV-376: Wrong result (missing rows) with index_merge+index_merge_intersection, join +--echo # +CREATE TABLE t1 ( + a INT, b CHAR(1), c CHAR(1), KEY(a), KEY(b) +) ENGINE=InnoDB; + +INSERT INTO t1 VALUES (8,'v','v'),(8,'m','m'),(9,'d','d'); + + +SELECT ta.* FROM t1 AS ta, t1 AS tb +WHERE ( tb.b != ta.b OR tb.a = ta.a ) + AND ( tb.b = ta.c OR tb.b = ta.b ); + +DROP TABLE t1; set optimizer_switch= @optimizer_switch_save; === modified file 'sql/opt_range.cc' --- a/sql/opt_range.cc 2012-06-04 15:26:11 +0000 +++ b/sql/opt_range.cc 2012-07-04 10:34:45 +0000 @@ -2008,7 +2008,7 @@ int QUICK_RANGE_SELECT::init_ror_merged_ if (reuse_handler) { DBUG_PRINT("info", ("Reusing handler 0x%lx", (long) file)); - if (init() || reset()) + if (init()) { DBUG_RETURN(1); } @@ -2043,7 +2043,7 @@ int QUICK_RANGE_SELECT::init_ror_merged_ if (file->ha_external_lock(thd, F_RDLCK)) goto failure; - if (init() || reset()) + if (init()) { file->ha_external_lock(thd, F_UNLCK); file->ha_close(); @@ -2090,7 +2090,18 @@ int QUICK_RANGE_SELECT::init_ror_merged_ head->key_read= org_key_read; bitmap_copy(&column_bitmap, head->read_set); head->column_bitmaps_set(&column_bitmap, &column_bitmap); - + + if (reset()) + { + if (!reuse_handler) + { + file->ha_external_lock(thd, F_UNLCK); + file->ha_close(); + goto failure; + } + else + DBUG_RETURN(1); + } DBUG_RETURN(0); failure: _______________________________________________ commits mailing list commits(a)mariadb.org https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits ----- End forwarded message ----- -- BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

2 1

[Maria-developers] Fixing 10.0 buildbot: OS-dependent error messages
by Sergei Petrunia 04 Jul '12

04 Jul '12

Hi! As some have noticed, 10.0 tree is red for all non-Linux builds. The errors look like this (on Windows) http://buildbot.askmonty.org/buildbot/builders/win64-release2/builds/1558/s… -ERROR HY000: Got error 1478 "Unknown error 1478" from storage engine +ERROR HY000: Got error 1478 "Unknown error" from storage engine ... -Error 1005 Can't create table 'test.t1' (errno: 1478 "Unknown error 1478") +Error 1005 Can't create table 'test.t1' (errno: 1478 "Unknown error") or like this (on Mac) http://buildbot.askmonty.org/buildbot/builders/labrador/builds/95/steps/tes… -ERROR HY000: Got error 1478 "Unknown error 1478" from storage engine +ERROR HY000: Got error 1478 "Unknown error: 1478" from storage engine ... -ERROR HY000: Out of resources when opening file '<partition file>' (Errcode: 24 - Too many open files) +ERROR HY000: Out of resources when opening file '<partition file>' (Errcode: 24 "Too many open files") I've narrowed it down. It is caused by revision 3414 monty(a)askmonty.org-20120529213755-876ptdhhaj0t7l8r. The revision adds %M modifier to error error messages, which prints "error number - system-error-text" into the error message (see strings/my_vsnprintf.c, my_strerror()). The problem is that system-error-text depends on the OS one is running. grep '%M' sql/share/errmsg-utf8.txt | grep 'eng ' | wc -l 27 There are currently 27 errors that use %M modifier in the error message The testsuite's .result files have only 5 of the errors, but there are lots of occurrences. Buildbot can get at least 3 variants of error message (Windows, Mac, Linux). I suppose, --replace_result will be too complex and fragile. We need to solve it somewhere else. My first idea was to: make mysql-test-run recognize error messages that have the %M pattern and remove the custom text, leaving the error code. To keep things simple/loosely coupled, I'd base recognition of error messages on list of patterns. I'm not sure where to keep the patterns and where to do the matching: should it be done inside mysql-test-run, or inside mysqltest.cc, or I should introduce some commands into mysqltest.cc that will make it apply rewrite rules to all error messages. Any ideas? -- BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

2 1

[Maria-developers] replication tests crash mysqld in mariadb 5.5.25
by Rich Prohaska 28 Jun '12

28 Jun '12

with mariadb-5.5.25 on a centos 5.8 machine, when i run this: ./mysql-test-run.pl --suite=rpl --force --max-test-fail=0 --nowarnings --parallel=4 mysqld crashes in the xtradb code trying to deref a NULL pointer. core files are available. Core was generated by `/home/prohaska/distros/mariadb-5.5.25-linux-x86_64/bin/mysqld --defaults-group-'. Program terminated with signal 11, Segmentation fault. #0 0x000000000095aa15 in os_aio_get_array_and_local_segment (global_seg=4, message1=0x43b910d8, message2=0x43b910d0, type=0x43b910c8, space_id=0x43b910c0) at /home/buildbot/buildbot/build/storage/xtradb/os/os0file.c:3787 3787 /home/buildbot/buildbot/build/storage/xtradb/os/os0file.c: No such file or directory. in /home/buildbot/buildbot/build/storage/xtradb/os/os0file.c (gdb) bt #0 0x000000000095aa15 in os_aio_get_array_and_local_segment (global_seg=4, message1=0x43b910d8, message2=0x43b910d0, type=0x43b910c8, space_id=0x43b910c0) at /home/buildbot/buildbot/build/storage/xtradb/os/os0file.c:3787 #1 os_aio_linux_handle (global_seg=4, message1=0x43b910d8, message2=0x43b910d0, type=0x43b910c8, space_id=0x43b910c0) at /home/buildbot/buildbot/build/storage/xtradb/os/os0file.c:4671 #2 0x00000000009114d7 in fil_aio_wait (segment=4) at /home/buildbot/buildbot/build/storage/xtradb/fil/fil0fil.c:5530 #3 0x000000000088d1ee in io_handler_thread (arg=<value optimized out>) at /home/buildbot/buildbot/build/storage/xtradb/srv/srv0start.c:483 #4 0x000000310e60677d in start_thread () from /lib64/libpthread.so.0 #5 0x000000310dad325d in clone () from /lib64/libc.so.6 (gdb) p os_aio_read_array $1 = (os_aio_array_t *) 0x0

2 1

[Maria-developers] mysql engines tests fail on mariadb 5.5.25
by Rich Prohaska 26 Jun '12

26 Jun '12

hello, when i run "./mysql-test-run.pl --suite=engines/funcs,engines/iuds --force --mysqld=--partition --nowarnings --max-test-fail=0", i see lots of test failures on mariadb-5.5.25 (and other distributions). either (1) i am not running the tests correctly, or (2) the test and/or test result files are broken, or (3) there are real problems being detected. i suspect most of the failures are caused by (2) above. anyway, here are the failures: Completed: Failed 37/323 tests, 88.54% were successful. Failing test(s): engines/funcs.an_calendar engines/funcs.datetime_function engines/funcs.db_alter_collate_ascii engines/funcs.db_alter_collate_utf8 engines/funcs.db_use_error engines/funcs.de_calendar_range engines/funcs.in_calendar_2_unique_constraints_duplicate_update engines/funcs.in_calendar_pk_constraint_duplicate_update engines/funcs.in_calendar_pk_constraint_error engines/funcs.in_calendar_pk_constraint_ignore engines/funcs.in_calendar_unique_constraint_duplicate_update engines/funcs.in_calendar_unique_constraint_error engines/funcs.in_calendar_unique_constraint_ignore engines/funcs.in_multicolumn_calendar_pk_constraint_duplicate_update engines/funcs.in_multicolumn_calendar_pk_constraint_error engines/funcs.in_multicolumn_calendar_pk_constraint_ignore engines/funcs.in_multicolumn_calendar_unique_constraint_duplicate_update engines/funcs.in_multicolumn_calendar_unique_constraint_error engines/funcs.in_multicolumn_calendar_unique_constraint_ignore engines/funcs.in_multicolumn_number_pk_constraint_duplicate_update engines/funcs.in_number_boundary_error engines/funcs.in_number_decimal_boundary_error engines/funcs.ld_all_number_string_calendar_types engines/funcs.se_join_left engines/funcs.se_join_left_outer engines/funcs.se_join_natural_left engines/funcs.se_join_natural_left_outer engines/funcs.sq_all engines/funcs.sq_error engines/funcs.up_calendar_range engines/iuds.delete_time engines/iuds.insert_decimal engines/iuds.insert_number engines/iuds.insert_time engines/iuds.type_bit_iuds engines/iuds.update_delete_number engines/iuds.update_time thanks rich prohaska

3 2

Re: [Maria-developers] Add back testcase for lp:817966 (was lost in the merge)
by Sergei Golubchik 23 Jun '12

23 Jun '12

Hi, Sergey! On Jun 23, Sergey Petrunya wrote: > At file:///home/psergey/dev2/5.5-look6/ > > ------------------------------------------------------------ > revno: 3457 > revision-id: psergey(a)askmonty.org-20120623161254-ido3pf16l31psyyv > parent: knielsen(a)knielsen-hq.org-20120622084255-u53u21xv3w7sqxms > committer: Sergey Petrunya <psergey(a)askmonty.org> > branch nick: 5.5-look6 > timestamp: Sat 2012-06-23 20:12:54 +0400 > message: > Add back testcase for lp:817966 (was lost in the merge) > > === modified file 'mysql-test/t/func_in.test' > --- a/mysql-test/t/func_in.test 2012-06-20 11:01:28 +0000 > +++ b/mysql-test/t/func_in.test 2012-06-23 16:12:54 +0000 > @@ -577,6 +577,24 @@ > --echo # > --echo End of 5.1 tests > > +# > +# lp:817966 int_column IN (string_constant) > +# > +# rather illogically, when BIGINT field is compared to a string, > +# the string is converted to an integer, not to a double. > +# When some other integer field (not BIGINT) is compared to a string, > +# or when the BIGINT is not a field, but an expression, both > +# operands are compared as doubles. The latter behavior is correct, > +# according to the manual. I don't think this comment holds anymore, perhaps it'd better to remove it. > +# > +create table t1 (a bigint, b int); > +insert t1 values (1,1),(2,2),(3,3); > +select * from t1 where a in ('2.1'); > +select * from t1 where b in ('2.1'); > +select * from t1 where a='2.1'; > +select * from t1 where b='2.1'; > +select * from t1 where IF(1,a,a)='2.1'; > +drop table t1; > --echo # > --echo # LP bug#992380 Crash when creating PS for a query with > --echo # subquery in WHERE (see also mysql bug#13012483) > Regards, Sergei

1 0

[Maria-developers] lp:817966 causes wrong results when added into 5.5
by Sergei Petrunia 22 Jun '12

22 Jun '12

Hi Sergei, Remember I've pointed out that the testcase for lp:817966 is present in 5.3 but is missing in 5.5, and we figured it was lost in the merge and decided to add it? Well, if I add the testcase into 5.5 it fails like this: main.func_in [ fail ] Test ended at 2012-06-20 15:11:27 CURRENT_TEST: main.func_in --- /home/psergey/dev2/5.5-look6/mysql-test/r/func_in.result 2012-06-20 15:11:12.000000000 +0400 +++ /home/psergey/dev2/5.5-look6/mysql-test/r/func_in.reject 2012-06-20 15:11:27.000000000 +0400 @@ -793,12 +793,10 @@ insert t1 values (1,1),(2,2),(3,3); select * from t1 where a in ('2.1'); a b -2 2 select * from t1 where b in ('2.1'); a b select * from t1 where a='2.1'; a b -2 2 select * from t1 where b='2.1'; a b select * from t1 where IF(1,a,a)='2.1'; I cannot readily tell whether this is a bug or not (could be, the typecasting rules are changed in 5.5?) Do you think this should be filed as a jira issue? BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

2 1

[Maria-developers] MariaDB 5.5.25 release prep
by Daniel Bartholomew 21 Jun '12

21 Jun '12

All, I'm working on getting the bits and pieces ready for this release. Preliminary versions of the Release Notes and Changelog are available here: http://kb.askmonty.org/en/mariadb-5525-release-notes/ http://kb.askmonty.org/en/mariadb-5525-changelog/ As always, if there is anything which should be mentioned in the Release Notes, please let me know ASAP or jump in and edit the page directly. Thanks! -- Daniel Bartholomew MariaDB - http://mariadb.org Monty Program - http://montyprogram.com AskMonty Knowledgebase - http://kb.askmonty.org

1 0

Re: [Maria-developers] MDEV-342: Do not mark old binlog file as cleanly closed during rotate until
by Sergei Golubchik 21 Jun '12

21 Jun '12

Hi, Kristian! Look good. Just one comment, see below, and ok to push On Jun 20, Kristian Nielsen wrote: > > ------------------------------------------------------------ > revno: 3449 > revision-id: knielsen(a)knielsen-hq.org-20120619130327-7vvemizq0akoysvn > parent: knielsen(a)knielsen-hq.org-20120620122323-bimn76haijiwpo7u > committer: knielsen(a)knielsen-hq.org > branch nick: mariadb-5.5 > timestamp: Tue 2012-06-19 15:03:27 +0200 > message: > MDEV-342: Do not mark old binlog file as cleanly closed during rotate until > the new file is fully synced to disk and binlog index. This fixes a window > where a crash would leave next server restart unable to detect that a crash > occured, causing recovery to fail. > === modified file 'sql/log.cc' > --- a/sql/log.cc 2012-05-21 18:54:41 +0000 > +++ b/sql/log.cc 2012-06-19 13:03:27 +0000 > @@ -6344,6 +6373,21 @@ void MYSQL_BIN_LOG::close(uint exiting) > } > > > +/* > + For binlog rotation, this function is used to ensure that the old binlog > + file is not marked as cleanly closed until the new binlog file is synced to > + disk and index. This is needed to ensure that we will always detect a prior > + crash at next server restart. > +*/ > +void MYSQL_BIN_LOG::delayed_close(File file) > +{ > + my_off_t offset= BIN_LOG_HEADER_SIZE + FLAGS_OFFSET; > + uchar flags= 0; // clearing LOG_EVENT_BINLOG_IN_USE_F > + mysql_file_pwrite(file, &flags, 1, offset, MYF(0)); I'd rather reuse this piece of code that overwrites the flag. If you move mysql_file_close() to the caller, you can call this function set_binlog_flag_when_closing() and use it both for normal and delayed close. > + mysql_file_close(file, MYF(MY_WME)); > +} > + Regards, Sergei

1 0

[Maria-developers] Some input re MDEV-83
by Sergei Petrunia 21 Jun '12

21 Jun '12

Hi Timour, Please find below some input re MDEV-83: * I expect that MDEV-83 will have an @@optimizer_switch flag. Is that so? * MDEV-83 will change optimizer behavior, however the change will not be visible in EXPLAIN. We have a history of such attempts and we've always ended up having to fix the EXPLAIN so that the change is visible. Do you have any ideas how to do it? My proposal is to show "subquery#n" in the Extra column of the table to which the subquery predicate is attached. * You need to take join buffering into account. For example, for this query: explain select * from ten A, ten B where cond(B.col) and cond(A.col, B.col); +--+-----------+-----+----+-------------+----+-------+----+----+------------------------------------------------+ |id|select_type|table|type|possible_keys|key |key_len|ref |rows|Extra | +--+-----------+-----+----+-------------+----+-------+----+----+------------------------------------------------+ | 1|SIMPLE |A |ALL |NULL |NULL|NULL |NULL| 10| | | 1|SIMPLE |B |ALL |NULL |NULL|NULL |NULL| 10|Using where; Using join buffer (flat, BNL join) | +--+-----------+-----+----+-------------+----+-------+----+----+------------------------------------------------+ both cond(B.col) and cond(A.col, B.col) will be attached to table B, however they will have different cardinalities. * The same goes for semi-join strategies. It is possible to calculate output cardinality for any condition, however it is much less obvious. * Suppose you have a query: select * from ot1, ot2 where expr in (select it1.col1 from it1, it2 where subquery_cond(it2.col2)) suppose the join order is: SJ-Materialize-scan(it1, it2), ot1, ot2 Here, subquery_cond may only be attached inside the SJ-Materialization nest. This is because subquery cond relies on the value of it2.col2, which is not available outside the SJ-Materialization nest. * LEFT JOINs: when the query has outer joins, predicates that are attached to tables that are inner w.r.t some LEFT JOIN, are wrapped in Item_func_trig_cond. I suppose you'll reuse the wrapping code, so that repositioned conditions are wrapped into appropriate Item_func_trig_conds. I'm not sure whether we should support predicate repositioning for parts of ON expressions. I suppose not implementing this allows to deliver sooner. If this is the case, we should not support repositioning inside ON expressions in the first milestone. * Section 1.2 says: " An estimate is made of the partial result cardinality, such that it takes into account actual pushed predicate selectivity" As far as I understand, you only have ability to estimate selectivity of predicates in form "tbl1.col CMP const" For such conditions, it is known in advance which table they will be attached to. There is no need to traverse the "cheap" pushed-down conditions. Then, what exactly do you mean by "take into account actual pushed predicate selectivity"? BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

Re: [Maria-developers] Please review: [Commits] Rev 3538: Fix bug lp:1008773 in file:///home/tsk/mprog/src/5.3-lpb1008773/
by Sergei Petrunia 18 Jun '12

18 Jun '12

Hi Timour, Ok to push. On Tue, Jun 05, 2012 at 04:20:35PM +0300, Timour Katchaounov wrote: > Sergey, > > Could you please review yet another rather trivial fix. > The only thing here is whether the semantics is correct. > As the bug report says, all MariaDB/MySQL versions (except > mariadb 5.3/5.5), and Postgresql return the value of the > constant as part of the empty row produced when we have > implicit grouping. I didn't verify this in the SQL standard, > because AFAIR this is non-standard. > > Timour > > ------------------------------------------------------------ > revno: 3538 > revision-id: timour(a)askmonty.org-20120605124124-akdmgdmr01h10s2w > parent: psergey(a)askmonty.org-20120602121305-mhw0u2zppjk0erhk > fixes bug(s): https://launchpad.net/bugs/1008773 > committer: timour(a)askmonty.org > branch nick: 5.3-lpb1008773 > timestamp: Tue 2012-06-05 15:41:24 +0300 > message: > Fix bug lp:1008773 > > Analysis: > Queries with implicit grouping (there is aggregate, but no group by) > follow some non-obvious semantics in the case of empty result set. > Aggregate functions produce some special "natural" value depending on > the function. For instance MIN/MAX return NULL, COUNT returns 0. > > The complexity comes from non-aggregate expressions in the select list. > If the non-aggregate expression is a constant, it can be computed, so > we should return its value, however if the expression is non-constant, > and depends on columns from the empty result set, then the only meaningful > value is NULL. > > The cause of the wrong result was that for subqueries the optimizer didn't > make a difference between constant and non-constant ones in the case of > empty result for implicit grouping. > > Solution: > In all implementations of Item_subselect::no_rows_in_result() check if the > subquery predicate is constant. If it is constant, do not set it to the > default value for implicit grouping, instead let it be evaluated. > === modified file 'mysql-test/r/subselect4.result' > --- a/mysql-test/r/subselect4.result 2012-02-28 14:41:55 +0000 > +++ b/mysql-test/r/subselect4.result 2012-06-05 12:41:24 +0000 > @@ -2124,6 +2124,61 @@ ERROR 21000: Subquery returns more than > DROP TABLE t2; > ERROR 42S02: Unknown table 't2' > DROP TABLE t1; > +# > +# LP BUG#1008773 Wrong result (NULL instead of a value) with no matching rows, subquery in FROM and HAVING > +# > +CREATE TABLE t1 (a INT) ENGINE=MyISAM; > +CREATE TABLE t2 (b INT) ENGINE=MyISAM; > +INSERT INTO t2 VALUES (1); > +EXPLAIN > +SELECT MAX(a), ( SELECT 1 FROM t2 ) AS bb FROM t1; > +id select_type table type possible_keys key key_len ref rows Extra > +1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found > +2 SUBQUERY t2 system NULL NULL NULL NULL 1 > +SELECT MAX(a), ( SELECT 1 FROM t2 ) AS bb FROM t1; > +MAX(a) bb > +NULL 1 > +EXPLAIN > +SELECT MAX(a), 1 in ( SELECT b FROM t2 ) AS bb FROM t1; > +id select_type table type possible_keys key key_len ref rows Extra > +1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found > +2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 1 > +SELECT MAX(a), 1 in ( SELECT b FROM t2 ) AS bb FROM t1; > +MAX(a) bb > +NULL 1 > +EXPLAIN > +SELECT MAX(a), 1 >= ALL ( SELECT b FROM t2 ) AS bb FROM t1; > +id select_type table type possible_keys key key_len ref rows Extra > +1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found > +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables > +SELECT MAX(a), 1 >= ALL ( SELECT b FROM t2 ) AS bb FROM t1; > +MAX(a) bb > +NULL 1 > +EXPLAIN > +SELECT MAX(a), ( SELECT 1 FROM t2 where b = a) AS bb FROM t1; > +id select_type table type possible_keys key key_len ref rows Extra > +1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found > +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables > +SELECT MAX(a), ( SELECT 1 FROM t2 where b = a) AS bb FROM t1; > +MAX(a) bb > +NULL NULL > +EXPLAIN > +SELECT MAX(a), a in ( SELECT b FROM t2 ) AS bb FROM t1; > +id select_type table type possible_keys key key_len ref rows Extra > +1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found > +2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 1 > +SELECT MAX(a), a in ( SELECT b FROM t2 ) AS bb FROM t1; > +MAX(a) bb > +NULL NULL > +EXPLAIN > +SELECT MAX(a), a >= ALL ( SELECT b FROM t2 ) AS bb FROM t1; > +id select_type table type possible_keys key key_len ref rows Extra > +1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found > +2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 1 > +SELECT MAX(a), a >= ALL ( SELECT b FROM t2 ) AS bb FROM t1; > +MAX(a) bb > +NULL NULL > +drop table t1, t2; > set optimizer_switch=@subselect4_tmp; > SET optimizer_switch= @@global.optimizer_switch; > set @@tmp_table_size= @@global.tmp_table_size; > > === modified file 'mysql-test/t/subselect4.test' > --- a/mysql-test/t/subselect4.test 2012-02-28 14:41:55 +0000 > +++ b/mysql-test/t/subselect4.test 2012-06-05 12:41:24 +0000 > @@ -1761,6 +1761,40 @@ CREATE TABLE t2 AS > DROP TABLE t2; > DROP TABLE t1; > > +--echo # > +--echo # LP BUG#1008773 Wrong result (NULL instead of a value) with no matching rows, subquery in FROM and HAVING > +--echo # > + > +CREATE TABLE t1 (a INT) ENGINE=MyISAM; > +CREATE TABLE t2 (b INT) ENGINE=MyISAM; > +INSERT INTO t2 VALUES (1); > + > +EXPLAIN > +SELECT MAX(a), ( SELECT 1 FROM t2 ) AS bb FROM t1; > +SELECT MAX(a), ( SELECT 1 FROM t2 ) AS bb FROM t1; > + > +EXPLAIN > +SELECT MAX(a), 1 in ( SELECT b FROM t2 ) AS bb FROM t1; > +SELECT MAX(a), 1 in ( SELECT b FROM t2 ) AS bb FROM t1; > + > +EXPLAIN > +SELECT MAX(a), 1 >= ALL ( SELECT b FROM t2 ) AS bb FROM t1; > +SELECT MAX(a), 1 >= ALL ( SELECT b FROM t2 ) AS bb FROM t1; > + > + > +EXPLAIN > +SELECT MAX(a), ( SELECT 1 FROM t2 where b = a) AS bb FROM t1; > +SELECT MAX(a), ( SELECT 1 FROM t2 where b = a) AS bb FROM t1; > + > +EXPLAIN > +SELECT MAX(a), a in ( SELECT b FROM t2 ) AS bb FROM t1; > +SELECT MAX(a), a in ( SELECT b FROM t2 ) AS bb FROM t1; > + > +EXPLAIN > +SELECT MAX(a), a >= ALL ( SELECT b FROM t2 ) AS bb FROM t1; > +SELECT MAX(a), a >= ALL ( SELECT b FROM t2 ) AS bb FROM t1; > + > +drop table t1, t2; > > set optimizer_switch=@subselect4_tmp; > > > === modified file 'sql/item_subselect.cc' > --- a/sql/item_subselect.cc 2012-05-22 12:22:55 +0000 > +++ b/sql/item_subselect.cc 2012-06-05 12:41:24 +0000 > @@ -892,6 +892,8 @@ void Item_maxmin_subselect::print(String > > void Item_maxmin_subselect::no_rows_in_result() > { > + if (const_item()) > + return; > value= Item_cache::get_cache(new Item_null()); > null_value= 0; > was_values= 0; > @@ -901,6 +903,8 @@ void Item_maxmin_subselect::no_rows_in_r > > void Item_singlerow_subselect::no_rows_in_result() > { > + if (const_item()) > + return; > value= Item_cache::get_cache(new Item_null()); > reset(); > make_const(); > @@ -1363,6 +1367,8 @@ Item* Item_exists_subselect::expr_cache_ > > void Item_exists_subselect::no_rows_in_result() > { > + if (const_item()) > + return; > value= 0; > null_value= 0; > make_const(); > @@ -2707,6 +2713,8 @@ void Item_allany_subselect::print(String > > void Item_allany_subselect::no_rows_in_result() > { > + if (const_item()) > + return; > value= 0; > null_value= 0; > was_null= 0; > > _______________________________________________ > commits mailing list > commits(a)mariadb.org > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits -- BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

3 2