March 2011 - developers - lists.mariadb.org

[Maria-developers] WL#185 New (by Knielsen): Group commits on slave to reduce fsync() load
by worklog-noreply＠askmonty.org 18 Mar '11

18 Mar '11

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Group commits on slave to reduce fsync() load CREATION DATE..: Fri, 18 Mar 2011, 14:25 SUPERVISOR.....: IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 185 (http://askmonty.org/worklog/?tid=185) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: MWL#116 introduces group commit for the binary log; this can reduce the number of fsync() calls needed when running with sync_binlog=1. But this does not currently help on the slave. The reason is that the slave SQL thread is single-threaded, so there will never be any opportunity for group commit. However, an alternative way to reduce fsync() on the slave when sync_binlog=1 is to combine multiple transactions from the binlog into one transaction on the slave. If we have N transactions T_1, ..., T_N in the binlog, we can combine them simply into one transaction on the slave like this: - Change COMMIT in T_1, ..., T_(N-1) into SAVEPOINT T_i - Change any ROLLBACK into ROLLBACK to T_i This will reduce the number of fsync() calls, since it results in fewer COMMITs. The downside is that the latency between a transaction being visible on the master and on the slave will increase. There should probably be some option to limit how much transactions may be delayed on the slave. It would also be useful to have some kind of timestamps in the binlog to be able to estimate the duration of a transaction on the slave before starting to apply it; this would allow to avoid combining a later long transaction with a previous short transaction, which could cause undue delay for the short transaction to become visible on the slave. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

Re: [Maria-developers] mysql_upgrade issue for libmysqld and amarok
by Kristian Nielsen 18 Mar '11

18 Mar '11

Ian Monroe <ian(a)monroe.nu> writes: > On Fri, Mar 18, 2011 at 05:44, Kristian Nielsen > <knielsen(a)knielsen-hq.org> wrote: >> The issue is how to upgrade libmysqd from MySQL version 5.1 to version 5.5 (or >> more generally any major version upgrade), in particular as relates to >> Amarok's use of libmysqld. >> One idea is to also build mysql_upgrade_embedded; this would be a variant of >> mysql_upgrade linked with libmysqld, so it does not depend on the mysqld >> server. Similarly, we would need mysqlcheck_embedded (mysqlcheck is called >> from mysql_upgrade). These two binaries could be included in the libmysqld >> package (they are small). > > wouldn't they be huge? libmysqld is a static library, so anything > linking to them is going to be at least 50mb. In MariaDB, we have both static and dynamic libmysqld. So we should link with the dynamic libmysqld.so, and binaries should be small. >> The upgrade procedure for amarok would then be to stop amarok linked with 5.1, >> then run mysql_upgrade_embedded (from 5.5) against the amarok libmysqld data >> directory, then start amarok linked with 5.5. > > There can't be any procedure that the user has to do. Agree. > But if there is some way for us to determine if mysql_upgrade_embedded > needs to be run, I don't see why this would a problem. Amarok could > run it before initializing the database. I would suggest to write a small file in the user's data directory with the version last upgraded to. If this is < 5.5 at startup (eg. "5.1"), then Amarok can run the upgrade and afterwards update the file to say "5.5". This will also allow to detect and warn about downgrading 5.5->5.1. Debian does something similar for the main MySQL server packages. >> The other idea that came up was to add this as extra calls in the library. So >> that the amarok application could just call some mysql_upgrade() function >> instead of having to run an external upgrade binary. Would this be more useful >> for amarok? > > This sounds much more straightforward. Ok, I will see if we can do this. Probably we could do both (then mysql_upgrade_embedded will just be a stub calling into mysql_upgrade() in the library, might be useful in other applications). >> We could offer to implement this (either solution I think) in MariaDB (a >> version of MariaDB that includes MySQL 5.5 is in the works). This would allow >> to use the above to upgrade Amarok to using MariaDB 5.5. Would this work? > > Sounds good. > We really could care less about maria vs. mysql, we just want > something that works. Ok, great :-) > Since I talked to you last time, I've had reports of people going from > mysql 5.1 to 5.5 without needing mysql_upgrade. mysql_upgrade seemed > to fix things for me, but maybe it was just a coincidence. Yes. The main issue I can think of now is that when character sets/collations change, the indexes of affected tables may need to be rebuilt. Otherwise they may contain incorrect ordering or indexing of affected characters. Since such changes are rare, and tables with affected characters probably even rarer, a missing upgrade is likely to go ok or at least unnoticed for some time. > Either way though, relying on newer versions of mysql simply not > needing to do any upgrade procedures seems like poor planning. Agree, even if it works for 5.5 it will be sure to break sooner or later. Monty said he would look into implementing this. He is quite busy, so it may be a few weeks, but I will try to follow up with him to see if we can move forward with it reasonably quickly. - Kristian.

1 0

[Maria-developers] WL#184 New (by Knielsen): Parallel replication of group-committed transactions
by worklog-noreply＠askmonty.org 18 Mar '11

18 Mar '11

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Parallel replication of group-committed transactions CREATION DATE..: Fri, 18 Mar 2011, 14:06 SUPERVISOR.....: Sergei IMPLEMENTOR....: Knielsen COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 184 (http://askmonty.org/worklog/?tid=184) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: Overview -------- Parallel replication is the idea of executing multiple transactions (or more precisely event groups from the binlog) in parallel in multiple threads on the slave, in order to make the slave applier more scalable to multiple CPU cores and disk spindles. The main problem with parallel replication is identifying transactions that are *independent*, meaning that they can be safely executed in parallel on the slave without dead-locking each other or producing different results than on the master. This worklog describes one way of identifying such independence. MWL#169 describes another. Apart from discovering independent transactions, it is also necessary to implement a mechanism for actually distributing events to different execution threads, coordinating commit order, handling errors and crash recovery, etc. It is expected that different ways of determining independent transactions, such as this worklog and MWL#169, will share the same mechanism for actual parallel execution. It should even be possible to combine different ways to discover independence at the same time. Idea ---- MWL#116 introduces group commit, where multiple transactions are committed together and also written to the binlog together. This works by collecting parallel transactions that are waiting inside COMMIT (or autocommit) for access to the binary log. The idea in this worklog is that all transactions participating in a single group commit must necessarily be independent, and can be safely applied in parallel on a slave. Suppose we are about to write N transactions T_1, ..., T_N into the binlog in group commit. Then all statements for each transaction have completed, but none of them are committed yet. This means that (1) there can be no conflicting row locks between any two T_i and T_j, and (2) no transaction T_i was able to see any changes from T_j (for i<>j and assuming isolation level greater than read uncommitted). Another way to see this is to note that on the master, the order of T_1, ..., T_N is essentially random and depends only on how the kernel decided to schedule the threads during the COMMIT. Any ordering would have been possible just by having different thread scheduling delays when the transactions are put in the ordered list used for group commit; there is no inter-thread dependencies at that point in the code. So any order of the transactions in the binlog would have been possible, and thus any order of slave execution. Therefore, as long as COMMIT cannot change what modifications the transaction already did, any execution order of the transactions on the slave is permissible. Implementation -------------- I suggest that when this feature is enabled, we introduce two new binlog events, group_commit_begin and group_commit_end. We write a group_commit_begin event just before all the events for the transactions in a group commit, and a group_commit_end event just after. We can use the "ignorable events" from MySQL 5.5 for these events; this will allow a slave that does not implement these events to safely ignore them. If needed, we can further increase compatibility by - Having a server option to enable/disable the writing of these two events. - Add a flag that the slave sets to request the sending of these events, and only send them if requested (similar to MWL#47). It still needs to be decided how much of this to do. The slave can then collect all the event groups between the group_commit_begin and group_commit_end pair, and schedule them in parallel. Limitations ----------- The main limitation of this approach is that on the slave, it can only run transactions in parallel if they happened to commit at roughly the same time on the master. This means that there will be little opportunity for parallelism on the slave if 1. The master has low write load 2. The master is mainly running few but long transactions 3. The master has fast fsync() performance for the commit In the case (1), there is in any case less need for scaling the event execution. In the case (2), another method (for example MWL#169) will be needed to achieve efficient parallel replication. For (3), we can improve parallelism by deliberately delaying commits on the master. By inserting a short sleep just before group commit, we allow more independent transactions time to participate, giving more opportunity for parallelism on the slave. At the same time, we reduce fsync() load on the master, potentially freeing more I/O capacity for other work. For example, we might add an option to rate-limit COMMIT fsync() to 25 per seconds or whatever. Care will be needed when using this option, as if the master load has insufficient parallelism, such rate-limitation will reduce throughput on the master. The MWL#163 innodb_release_locks_early option causes transactions to effectively be committed early in COMMIT during the prepare phase. This allows another transaction to see and/or modify rows also modified by the first transaction, and still participate in the same group commit. This means that when innodb_release_locks_early is set, the transactions cannot be safely used for parallel replication (eg. in this case we must disable the writing of group_commit_begin / group_commit_end). Events that modify non-transactional tables or do DDL causes more complications, as they are written directly to the binlog and make changes visible immediately to other transactions (basically they are not transactional). To simplify things, we will not declare any such events independent with respect to any other transactions according to this worklog. There are many useful applications of parallel replications where such events are rare. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

[Maria-developers] mysql_upgrade issue for libmysqld and amarok
by Kristian Nielsen 18 Mar '11

18 Mar '11

This is a follow up on a discussion I had with eean on Freenode IRC #maria. The issue is how to upgrade libmysqd from MySQL version 5.1 to version 5.5 (or more generally any major version upgrade), in particular as relates to Amarok's use of libmysqld. The procedure for upgrading a normal MySQL server is to run the mysql_upgrade command. However, this requires a running mysqld server, so it is not appropriate for libmysqld, or at least very inconvenient. We discussed this briefly at our MariaDB meeting in Lisbon, and here is what we came up with: One idea is to also build mysql_upgrade_embedded; this would be a variant of mysql_upgrade linked with libmysqld, so it does not depend on the mysqld server. Similarly, we would need mysqlcheck_embedded (mysqlcheck is called from mysql_upgrade). These two binaries could be included in the libmysqld package (they are small). The upgrade procedure for amarok would then be to stop amarok linked with 5.1, then run mysql_upgrade_embedded (from 5.5) against the amarok libmysqld data directory, then start amarok linked with 5.5. Does that sound useful? The other idea that came up was to add this as extra calls in the library. So that the amarok application could just call some mysql_upgrade() function instead of having to run an external upgrade binary. Would this be more useful for amarok? Either way, we then need to think on how to implement this change. We could offer to implement this (either solution I think) in MariaDB (a version of MariaDB that includes MySQL 5.5 is in the works). This would allow to use the above to upgrade Amarok to using MariaDB 5.5. Would this work? (I think it would even work to keep Amarok using MySQL 5.5 libmysqld, but using MariaDB 5.5 mysql_upgrade_embedded, though that is kind of a strange solution. Or we could provide the patch for MariaDB and you could patch libmysqld similarly to provide the same functionality in relevant distros.) Does this sound like a feasible way forward? One question about how Amarok uses libmysqld, which will help understand what is needed to solve the upgrade issue: Does Amarok use libmysqld built with the --with-embedded-privilege-control option, or use stored procedures, or other things that require the `mysql' database schema to be available in MySQL? If this does not make sense, the answer is probably "no" :-) The background: When a normal standalone mysql database server is installed, it is necessary to create the `mysql' database schema using mysql_create_db. This database schema contains such things as user accounts and passwords, stored procedure code, etc. Normally, libmysqld does not do any access control on accounts, and it is possible to use it without running mysql_install_db. In fact it's a bit hard to run mysql_install_db, as it has the same issue as mysql_upgrade. So I assume that Amarok does not do this and just does not use account privileges or stored procedures/functions, but just wanted to check? This affects how much mysql_upgrade_embedded has to do, as part of the upgrade is to add any missing columns in tables in the `mysql' database schema. - Kristian.

1 0

Re: [Maria-developers] [All] How are we going to handle incompatible changes in MariaDB?
by Kristian Nielsen 18 Mar '11

18 Mar '11

"Philip Stoev" <pstoev(a)askmonty.org> writes: > We should discuss how do we handle new developments in MariaDB that cause > our behavior to diverge from the one in MySQL. Since we claim to be a Yes, agree. Thank you for bringing it up! Note that this is a general MariaDB discussion, not a Monty Program specific one. So I move the thread to maria-developers@. So far, we have generally been ok at handing incompatible changes. The basic approach is to shape new features to be backwards compatible as much as possible, and to add any extra needed code to handle interoperability. For example, if an option is changed due to a feature, we should keep the old option around and emulate the bahaviour, eventually deprecating it in a future version. Another example, with replication, we check the version of the master or slave at the other end, and take care to only send what the other end understands, and likewise interpret what it sends correctly based on its version. However, this has been mostly up to the individual developer (or often reviewer), and if we can get some more structure/process to this to avoid it being forgotten, then that would be very good. > The particular situation we are facing now is the microsecond precision > patch from Serg. As part of introducing precision date and time datatypes, > he made some refactoring around the Item_* tree. > > As a result, various corner cases, e.g. invalid dates, partially valid > dates, invalid conversions between date and time, etc. are now handled > differently, with either different warnings being printed (or no warnings at > all), or with the result of the expression being different, or with a NULL > or 0000-00-00 returned. > > For example, mysql reports that DATE ( SEC_TO_TIME( 8 ) ) is equal to > "2000-00-08" whereas serg's patch returns "0000-00-00" . In other words, > MySQL chose to place the "8" literal into the days portion of the date > value, whereas Serg's patch filtered the literal somewhere along the way. > There are numerous other such examples. > > While in most cases Serg's new behavior is more intuitive and a program that > is written to conform to postgresql-level type safety will not be affected > either way, it entirely possible that a program written under mysql's > relaxed type safety rules, and with MySQL's behavior in mind, would start > behaving unexpectedly. It sounds like in this case, the changes in behaviour could be considered improvements, or even bug fixes, when seen in isolation. And the behaviour they change is not something that an application would deliberately be written to rely on. But since it is a change in behaviour, it has the potential to break applications that by accident happen to depend on such behaviour. My opinion on this is that we should do the change in a major version (eg. 5.2->5.3), but not in a minor version (eg. 5.2.5->5.2.6). And document it in release notes as an incompatible change. Basically the same way we did in MySQL (and still do, I suppose). It is ok to change behaviour in a major version, if it is necessary, and documented. I believe users anticipate such changes, and consequently are more careful about going eg. 5.1->5.5 than 5.1.x->5.1.y. We should not needlessly change things. Every behaviour change should be carefully considered. If it has the potential to seriously break major applications, we should not change it (or use deprecation, compatibility mode, etc). If the change is not necessary or useful, it should probably be left. But if the change makes sense and the new behaviour is much more reasonable, then we should be ready to make the change. We cannot afford to be stuck forever on old poor decisions. Another example is optimiser changes. In my opinion, we cannot totally avoid that improvements to the optimiser will make some corner case queries slower in some cases. The optimiser has only limited knowledge of the data and application. If, based on this knowledge, execution plan A is better than execution plan B, then we should be prepared to change (in a major version) the code to select A over B. This will invaribly cause slowdown in some cases, where the knowledge is incomplete. But it is important to be able to make improvements. But again, we need to consider each case carefully. We should not do things that are obviously stupid (eg. do not select table scan over range scan unless _really_ sure it will be better), and we should avoid breaking major applications or use patterns, even if it theoretically makes sense. So summary of my opinion: We should be very careful about incompatible changes. We should generally avoid them unless there is a very good reason for them. We should create backward compatibility where possible and where it makes sense. And if we do change behaviour, we should only do it in a major version and document it clearly in an "incompatible changes" list. And being able to continually improve the server long-term is important. > So, how we should handle this particular case, and more importantly, how do > we handle future cases like that in a stable and predictable manner? I > understand that in the early days of MySQL it would have been a no-brainer > to change everything as needed, we are by now dealing with a huge installed > base and powerful competitors, so we can not simply apply the happy hacking > approach that was used in the 1990s. Agree. No "happy hacking" approach to incompatible changes! - Kristian.

1 0

[Maria-developers] request for feedback on live schema changes patch
by Zardosht Kasheff 18 Mar '11

18 Mar '11

Hello all, In another thread, there was a discussion about what it would take to allow the storage engine to support live schema changes in mysql and mariadb. I said that I was working porting this existing feature from MySQL Cluster to MySQL 5.1.46. I would like to show the work I have done so far and solicit feedback. Attached is the diff based off of 5.1.46. Before looking at the patch, please be aware of the following things: - I am not an expert in this code. As a result, I had to use an approach of that minimizes the risk of introducing regressions. \ - My main interest is to get something safe that works and can be integrated in 5.1. As a result, kept the current mysql_alter_table functionality in place (as old_mysql_alter_table), and added MySQL Cluster's as new_mysql_alter_table. For an engine to get new_mysql_alter_table to be used, the engine must expose an alter table flag HA_GENERAL_ONLINE. - Another reason why I kept old_mysql_alter_table was that I did not want to deal with engines that implement handler::add_index and make sure that they work properly under the new API. Also, I did not want to port alter table changes related to partitioned tables. - I have run the current 5,1 mysql test suite (that is in the mysql-test directory of source tarballs), and verified that using old_mysql_alter_table and new_mysql_alter_table works fine with them. That being said, I would love to hear feedback on this patch, and would also like to understand what (if anything) can be done to get this into MariaDB 5.1. I realize that may not be possible, but if it is, I would like to try. Thanks -Zardosht

2 5

[Maria-developers] WL#182 New (by Mdcallag): Explain running statements
by worklog-noreply＠askmonty.org 13 Mar '11

13 Mar '11

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Explain running statements CREATION DATE..: Sun, 13 Mar 2011, 09:44 SUPERVISOR.....: IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Client-BackLog TASK ID........: 182 (http://askmonty.org/worklog/?tid=182) VERSION........: Benchmarks-3.0 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: I want to know the query plan for running statements -- something like "SHOW PLAN FOR <connection_id>". This requires running statements to generate a string that describes their plan in case another thread needs to examine it. For me it would be good enough to know the join order and indexes. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

[Maria-developers] URLs for worklog tasks include bin name
by MARK CALLAGHAN 13 Mar '11

13 Mar '11

When I click on http://askmonty.org/worklog/?tid=180 I end up at http://askmonty.org/worklog/WorkLog-RawIdeaBin/?tid=180. Is that last URL only valid as long as tid=180 remains in the RawIdea bin? If so, then when I share it in email or include that URL elsewhere how are people to use it when tid=180 is moved from the RawIdeaBin to another bin? -- Mark Callaghan mdcallag(a)gmail.com

1 0

[Maria-developers] User Comment -> Add Comment is bugging in MariaDB worklogs
by MARK CALLAGHAN 13 Mar '11

13 Mar '11

Go to a worklog, Scroll down, Click on "Add a new comment" This takes me back to the top of the page. This is confusing and now I must scroll down again to reach the comment text box. Add text, click on OK This takes me to another page that has a text box. The text I previously entered is lost. This is a bug. If I enter text on this page and click OK it is not lost. -- Mark Callaghan mdcallag(a)gmail.com

1 1

Re: [Maria-developers] [Commits] Rev 2935: Fixed LP bugs BUG#729067/730466. in file:///home/igor/maria/maria-5.3-bug729067/
by Oleksandr Byelkin 12 Mar '11

12 Mar '11

Hi! Ok to push. 12 марта 2011, в 10:49, Igor Babaev написал(а): > At file:///home/igor/maria/maria-5.3-bug729067/ > > ------------------------------------------------------------ > revno: 2935 > revision-id: igor(a)askmonty.org-20110312084903-b9mmi2wdgbdar3tc > parent: timour(a)askmonty.org-20110308212344-7r4ue17b3rxschlj > committer: Igor Babaev <igor(a)askmonty.org> > branch nick: maria-5.3-bug729067 > timestamp: Sat 2011-03-12 00:49:03 -0800 > message: > Fixed LP bugs BUG#729067/730466. > Do not reset the value of the item_equal field in the Item_field object > once it has been set. > === modified file 'mysql-test/r/join.result' > --- a/mysql-test/r/join.result 2011-02-28 21:29:59 +0000 > +++ b/mysql-test/r/join.result 2011-03-12 08:49:03 +0000 > @@ -1285,3 +1285,38 @@ > 1 1 1 1 1 > 1 1 1 1 1 > drop table t1,t2,t3; > +# > +# BUG#729067/730466: unexpected 'Range checked for each record' > +# for queries with OR in WHERE clause > +# > +CREATE TABLE t1 (f1 int, f2 int) ; > +INSERT INTO t1 VALUES (4,0),(5,1); > +CREATE TABLE t2 (f1 int, f2 int, KEY (f2)) ; > +INSERT INTO t2 VALUES (5,7), (8,9); > +EXPLAIN > +SELECT * FROM t1 STRAIGHT_JOIN t2 ON t2.f1 = t1.f1 > +WHERE t1.f1<>0 OR t1.f2<>0 AND t1.f1 = t2.f2; > +id select_type table type possible_keys key key_len ref rows Extra > +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where > +1 SIMPLE t2 ALL f2 NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) > +SELECT * FROM t1 STRAIGHT_JOIN t2 ON t2.f1 = t1.f1 > +WHERE t1.f1<>0 OR t1.f2<>0 AND t1.f1 = t2.f2; > +f1 f2 f1 f2 > +5 1 5 7 > +DROP TABLE t1,t2; > +CREATE TABLE t1(f1 int PRIMARY KEY, f2 int) ; > +INSERT INTO t1 VALUES (9,4), (10,9); > +CREATE TABLE t2(f1 int PRIMARY KEY, f2 int) ; > +INSERT INTO t2 VALUES (9,4), (10,9); > +EXPLAIN > +SELECT STRAIGHT_JOIN * FROM t1 JOIN t2 ON t2.f2 = t1.f1 > +WHERE t1.f1 IN (SELECT f1 FROM t1) AND t1.f1 = t2.f1 OR t1.f1 = 9; > +id select_type table type possible_keys key key_len ref rows Extra > +1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 2 Using where > +1 PRIMARY t2 ALL PRIMARY NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) > +2 SUBQUERY t1 index NULL PRIMARY 4 NULL 2 Using index > +SELECT STRAIGHT_JOIN * FROM t1 JOIN t2 ON t2.f2 = t1.f1 > +WHERE t1.f1 IN (SELECT f1 FROM t1) AND t1.f1 = t2.f1 OR t1.f1 = 9; > +f1 f2 f1 f2 > +9 4 10 9 > +DROP TABLE t1,t2; > > === modified file 'mysql-test/t/join.test' > --- a/mysql-test/t/join.test 2011-02-28 21:29:59 +0000 > +++ b/mysql-test/t/join.test 2011-03-12 08:49:03 +0000 > @@ -964,3 +964,36 @@ > > drop table t1,t2,t3; > > +--echo # > +--echo # BUG#729067/730466: unexpected 'Range checked for each record' > +--echo # for queries with OR in WHERE clause > +--echo # > + > +CREATE TABLE t1 (f1 int, f2 int) ; > +INSERT INTO t1 VALUES (4,0),(5,1); > + > +CREATE TABLE t2 (f1 int, f2 int, KEY (f2)) ; > +INSERT INTO t2 VALUES (5,7), (8,9); > + > +EXPLAIN > +SELECT * FROM t1 STRAIGHT_JOIN t2 ON t2.f1 = t1.f1 > + WHERE t1.f1<>0 OR t1.f2<>0 AND t1.f1 = t2.f2; > +SELECT * FROM t1 STRAIGHT_JOIN t2 ON t2.f1 = t1.f1 > + WHERE t1.f1<>0 OR t1.f2<>0 AND t1.f1 = t2.f2; > + > +DROP TABLE t1,t2; > + > +CREATE TABLE t1(f1 int PRIMARY KEY, f2 int) ; > +INSERT INTO t1 VALUES (9,4), (10,9); > + > +CREATE TABLE t2(f1 int PRIMARY KEY, f2 int) ; > +INSERT INTO t2 VALUES (9,4), (10,9); > + > +EXPLAIN > +SELECT STRAIGHT_JOIN * FROM t1 JOIN t2 ON t2.f2 = t1.f1 > + WHERE t1.f1 IN (SELECT f1 FROM t1) AND t1.f1 = t2.f1 OR t1.f1 = 9; > +SELECT STRAIGHT_JOIN * FROM t1 JOIN t2 ON t2.f2 = t1.f1 > + WHERE t1.f1 IN (SELECT f1 FROM t1) AND t1.f1 = t2.f1 OR t1.f1 = 9; > + > +DROP TABLE t1,t2; > + > > === modified file 'sql/item_cmpfunc.cc' > --- a/sql/item_cmpfunc.cc 2011-02-06 04:57:03 +0000 > +++ b/sql/item_cmpfunc.cc 2011-03-12 08:49:03 +0000 > @@ -5723,7 +5723,8 @@ > not_null_tables_cache|= tmp_table_map; > if (item->maybe_null) > maybe_null=1; > - item->item_equal= this; > + if (!item->item_equal) > + item->item_equal= this; > } > fix_length_and_dec(); > fixed= 1; > > _______________________________________________ > commits mailing list > commits(a)mariadb.org > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits

1 0