- developers - lists.mariadb.org

Re: [Maria-developers] [Commits] Rev 2835: added a sphinxse test suite in http://bazaar.launchpad.net/~maria-captains/maria/5.2/
by Kristian Nielsen 06 Aug '10

06 Aug '10

<serg(a)askmonty.org> writes: > === added file 'mysql-test/suite/sphinx/sphinx.test' > --- a/mysql-test/suite/sphinx/sphinx.test 1970-01-01 00:00:00 +0000 > +++ b/mysql-test/suite/sphinx/sphinx.test 2010-08-06 13:11:37 +0000 > @@ -0,0 +1,30 @@ > +if (`SELECT "$HAVE_SPHINX" < "0000.0009.0009"`) { > + skip Need Sphinx version 0.9.9 or later; > +} > +if (`SELECT "$HA_SPHINX_SO" = ""`) { > + skip No SphinxSE plugin; > +} > + > +eval INSTALL PLUGIN sphinx SONAME "$HA_SPHINX_SO"; Should there be an UNINSTALL PLUGIN at the end of the test case? Otherwise I suppose following test cases could end up running with the plugin loaded (or not, at random), potentially causing .result differences. - Kristian.

2 1

[Maria-developers] wl#127 - changes to mtr for sphinx
by Sergei Golubchik 05 Aug '10

05 Aug '10

Hi, Kristian, I've done the changes - would you mind to review the patch ? Somehow bzr didn't send the email automatically - could not connect to the server, so I'll attach the output of bzr bundle to this email. If you dont have time to review it (or don't want to :), please remove yourself from a "reviewer" field in WL#127. Thanks! Regards, Sergei P.S. there are three patches attached - three bundles. the first one fixes ndb. as ndb is hacked in mtr and is all over the place, I could not change mtr without touching ndb related pieces, and I need to make sure that I didn't break them. I found out that ndb doesn't even build - without my changes, so I fixed that. I think we need to build ndb at on some buildbot slaves, just to make sure we don't break it. Or remove it completely - as it's clearly not used, and not expected to be used, ndb users should use telco trees. the second contain mtr changes. the third adds sphinx suite.

3 5

[Maria-developers] Rev 2809: The test files renamed to have uniform name. in file:///home/bell/maria/bzr/work-maria-5.3-rename/
by sanja＠askmonty.org 05 Aug '10

05 Aug '10

At file:///home/bell/maria/bzr/work-maria-5.3-rename/ ------------------------------------------------------------ revno: 2809 revision-id: sanja(a)askmonty.org-20100805142348-wdtwgvj5tm2si5gt parent: sanja(a)askmonty.org-20100730041658-2naumadh26t93e3g committer: sanja(a)askmonty.org branch nick: work-maria-5.3-rename timestamp: Thu 2010-08-05 17:23:48 +0300 message: The test files renamed to have uniform name. === renamed file 'mysql-test/r/subquery_cache.result' => 'mysql-test/r/subselect_cache.result' === renamed file 'mysql-test/t/subquery_cache.test' => 'mysql-test/t/subselect_cache.test'

1 0

[Maria-developers] WL#129 New (by Sergei): Rename " Maria" to " Aria"
by worklog-noreply＠askmonty.org 05 Aug '10

05 Aug '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Rename "Maria" to "Aria" CREATION DATE..: Thu, 05 Aug 2010, 14:02 SUPERVISOR.....: Sergei IMPLEMENTOR....: Sergei COPIES TO......: CATEGORY.......: Server-Sprint TASK ID........: 129 (http://askmonty.org/worklog/?tid=129) VERSION........: Server-5.2 STATUS.........: Assigned PRIORITY.......: 90 WORKED HOURS...: 0 ESTIMATE.......: 16 (hours remain) ORIG. ESTIMATE.: 16 PROGRESS NOTES: DESCRIPTION: The engine Maria was renamed to Aria. The code needs to be changed to reflect that ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

[Maria-developers] WL#128 New (by Igor): Implement Block Nested Loop Hash Join
by worklog-noreply＠askmonty.org 04 Aug '10

04 Aug '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Implement Block Nested Loop Hash Join CREATION DATE..: Wed, 04 Aug 2010, 18:56 SUPERVISOR.....: Monty IMPLEMENTOR....: Igor COPIES TO......: Igor Mneptok Monty Psergey Sanja Sergei Timour CATEGORY.......: Server-BackLog TASK ID........: 128 (http://askmonty.org/worklog/?tid=128) VERSION........: Server-5.3 STATUS.........: Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: Block Nested Loop Hash (BNLH) Join Algorithm (also known as Classic Hash Join Algorithm) can be applied when the join condition contains conjunctive equality predicates over some attributes of the joined tables. The algorithm employs the same logical schema as Block Nested Loop Join Algorithm. A portions of rows of the left join operand fit into a preallocated join buffer are put into the buffer. A hash table over the equi-join attributes for the rows in the buffer is built. Then the table of the second operand is scanned and for each row from this table the matching rows in the join buffer are found using the built hash table. These actions are repeated until there is no more rows can be supplied by the first operand. The algorithm requires as many scans of the second table as many times the join buffer is refilled. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

[Maria-developers] Rev 2836: Check of maria engine presence added. in file:///home/bell/maria/bzr/work-maria-5.2-lb607147/
by sanja＠askmonty.org 04 Aug '10

04 Aug '10

At file:///home/bell/maria/bzr/work-maria-5.2-lb607147/ ------------------------------------------------------------ revno: 2836 revision-id: sanja(a)askmonty.org-20100804094351-8yyx0m06vi4pr9fj parent: sanja(a)askmonty.org-20100803094925-fpuj52qvdkkw5994 committer: sanja(a)askmonty.org branch nick: work-maria-5.2-lb607147 timestamp: Wed 2010-08-04 12:43:51 +0300 message: Check of maria engine presence added. Comment fixed. === modified file 'mysql-test/suite/vcol/t/vcol_handler_maria.test' --- a/mysql-test/suite/vcol/t/vcol_handler_maria.test 2010-08-03 09:49:25 +0000 +++ b/mysql-test/suite/vcol/t/vcol_handler_maria.test 2010-08-04 09:43:51 +0000 @@ -14,8 +14,10 @@ # Change: # ################################################################################ +--source include/have_maria.inc + # -# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# NOTE: PLEASE DO NOT ADD NOT MARIA SPECIFIC TESTCASES HERE ! # TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN # THE SOURCED FILES ONLY. #

1 0

[Maria-developers] Rev 2835: Fix for launchpad bug #612894 in file:///home/bell/maria/bzr/work-maria-5.2-lb607147/
by sanja＠askmonty.org 03 Aug '10

03 Aug '10

At file:///home/bell/maria/bzr/work-maria-5.2-lb607147/ ------------------------------------------------------------ revno: 2835 revision-id: sanja(a)askmonty.org-20100803094925-fpuj52qvdkkw5994 parent: igor(a)askmonty.org-20100728190938-esx94q58hw3v5jue committer: sanja(a)askmonty.org branch nick: work-maria-5.2-lb607147 timestamp: Tue 2010-08-03 12:49:25 +0300 message: Fix for launchpad bug #612894 Support of virtual columns added to maria engine. === added file 'mysql-test/suite/vcol/r/vcol_handler_maria.result' --- a/mysql-test/suite/vcol/r/vcol_handler_maria.result 1970-01-01 00:00:00 +0000 +++ b/mysql-test/suite/vcol/r/vcol_handler_maria.result 2010-08-03 09:49:25 +0000 @@ -0,0 +1,76 @@ +SET @@session.storage_engine = 'maria'; +create table t1 (a int, +b int as (-a), +c int as (-a) persistent, +d char(1), +index (a), +index (c)); +insert into t1 (a,d) values (4,'a'), (2,'b'), (1,'c'), (3,'d'); +select * from t1; +a b c d +4 -4 -4 a +2 -2 -2 b +1 -1 -1 c +3 -3 -3 d +# HANDLER tbl_name OPEN +handler t1 open; +# HANDLER tbl_name READ non-vcol_index_name > (value1,value2,...) +handler t1 read a > (2); +a b c d +3 -3 -3 d +# HANDLER tbl_name READ non-vcol_index_name > (value1,value2,...) WHERE non-vcol_field=expr +handler t1 read a > (2) where d='c'; +a b c d +# HANDLER tbl_name READ vcol_index_name = (value1,value2,...) +handler t1 read c = (-2); +a b c d +2 -2 -2 b +# HANDLER tbl_name READ vcol_index_name = (value1,value2,...) WHERE non-vcol_field=expr +handler t1 read c = (-2) where d='c'; +a b c d +# HANDLER tbl_name READ non-vcol_index_name > (value1,value2,...) WHERE vcol_field=expr +handler t1 read a > (2) where b=-3 && c=-3; +a b c d +3 -3 -3 d +# HANDLER tbl_name READ vcol_index_name <= (value1,value2,...) +handler t1 read c <= (-2); +a b c d +2 -2 -2 b +# HANDLER tbl_name READ vcol_index_name > (value1,value2,...) WHERE vcol_field=expr +handler t1 read c <= (-2) where b=-3; +a b c d +3 -3 -3 d +# HANDLER tbl_name READ vcol_index_name FIRST +handler t1 read c first; +a b c d +4 -4 -4 a +# HANDLER tbl_name READ vcol_index_name NEXT +handler t1 read c next; +a b c d +3 -3 -3 d +# HANDLER tbl_name READ vcol_index_name PREV +handler t1 read c prev; +a b c d +4 -4 -4 a +# HANDLER tbl_name READ vcol_index_name LAST +handler t1 read c last; +a b c d +1 -1 -1 c +# HANDLER tbl_name READ FIRST where non-vcol=expr +handler t1 read FIRST where a >= 2; +a b c d +4 -4 -4 a +# HANDLER tbl_name READ FIRST where vcol=expr +handler t1 read FIRST where b >= -2; +a b c d +2 -2 -2 b +# HANDLER tbl_name READ NEXT where non-vcol=expr +handler t1 read NEXT where d='c'; +a b c d +1 -1 -1 c +# HANDLER tbl_name READ NEXT where vcol=expr +handler t1 read NEXT where b<=-4; +a b c d +# HANDLER tbl_name CLOSE +handler t1 close; +drop table t1; === added file 'mysql-test/suite/vcol/t/vcol_handler_maria.test' --- a/mysql-test/suite/vcol/t/vcol_handler_maria.test 1970-01-01 00:00:00 +0000 +++ b/mysql-test/suite/vcol/t/vcol_handler_maria.test 2010-08-03 09:49:25 +0000 @@ -0,0 +1,50 @@ +################################################################################ +# t/vcol_handler_maria.test # +# # +# Purpose: # +# Testing HANDLER. +# # +# Maria branch # +# # +#------------------------------------------------------------------------------# +# Original Author: Andrey Zhakov # +# Original Date: 2008-09-04 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements +--source suite/vcol/inc/vcol_init_vars.pre + +#------------------------------------------------------------------------------# +# Cleanup +--source suite/vcol/inc/vcol_cleanup.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +# Set the session storage engine +eval SET @@session.storage_engine = 'maria'; + +##### Workarounds for known open engine specific bugs +# none + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/vcol/inc/vcol_handler.inc + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests + +#------------------------------------------------------------------------------# +# Cleanup +--source suite/vcol/inc/vcol_cleanup.inc === modified file 'storage/maria/ha_maria.cc' --- a/storage/maria/ha_maria.cc 2010-07-25 15:09:21 +0000 +++ b/storage/maria/ha_maria.cc 2010-08-03 09:49:25 +0000 @@ -468,7 +468,7 @@ recinfo_pos= recinfo; create_info->null_bytes= table_arg->s->null_bytes; - while (recpos < (uint) share->reclength) + while (recpos < (uint) share->stored_rec_length) { Field **field, *found= 0; minpos= share->reclength; === modified file 'storage/maria/ha_maria.h' --- a/storage/maria/ha_maria.h 2010-07-23 20:37:21 +0000 +++ b/storage/maria/ha_maria.h 2010-08-03 09:49:25 +0000 @@ -148,6 +148,7 @@ int assign_to_keycache(THD * thd, HA_CHECK_OPT * check_opt); int preload_keys(THD * thd, HA_CHECK_OPT * check_opt); bool check_if_incompatible_data(HA_CREATE_INFO * info, uint table_changes); + bool check_if_supported_virtual_columns(void) { return TRUE;} #ifdef HAVE_REPLICATION int dump(THD * thd, int fd); int net_read_dump(NET * net);

1 0

[Maria-developers] fixed for 571200 pushed
by Patrick Galbraith 02 Aug '10

02 Aug '10

Hi all, I hand-added Antony's fix for 571200 that I had started to https://code.launchpad.net/~capttofu/maria/bug_571200/+merge/31606 for review. I'll be adding his other fixes for each bug as well. Thanks Antony! Patrick

1 0

[Maria-developers] Rev 2809: Fix for luanchpad bug#611625: Removing NULL references from subquery parameter list added. in file:///home/bell/maria/bzr/work-maria-5.3-lb611625/
by sanja＠askmonty.org 01 Aug '10

01 Aug '10

At file:///home/bell/maria/bzr/work-maria-5.3-lb611625/ ------------------------------------------------------------ revno: 2809 revision-id: sanja(a)askmonty.org-20100802055612-se9olthiaazi5xju parent: sanja(a)askmonty.org-20100730041658-2naumadh26t93e3g committer: sanja(a)askmonty.org branch nick: work-maria-5.3-lb611625 timestamp: Mon 2010-08-02 08:56:12 +0300 message: Fix for luanchpad bug#611625: Removing NULL references from subquery parameter list added. Incorrect limitation on number of parameters removed. === modified file 'mysql-test/r/subquery_cache.result' --- a/mysql-test/r/subquery_cache.result 2010-07-30 04:16:58 +0000 +++ b/mysql-test/r/subquery_cache.result 2010-08-02 05:56:12 +0000 @@ -2985,3 +2985,201 @@ 1 NULL f drop table t1,t2,t3,t4; set @@optimizer_switch= default; +#launchpad BUG#611625 +CREATE TABLE `t1` ( +`pk` int(11) NOT NULL AUTO_INCREMENT, +`col_int_nokey` int(11) DEFAULT NULL, +`col_varchar_nokey` varchar(1) DEFAULT NULL, +PRIMARY KEY (`pk`) +) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1; +INSERT INTO `t1` VALUES (1,NULL,'w'); +INSERT INTO `t1` VALUES (2,7,'m'); +INSERT INTO `t1` VALUES (3,9,'m'); +INSERT INTO `t1` VALUES (4,7,'k'); +INSERT INTO `t1` VALUES (5,4,'r'); +INSERT INTO `t1` VALUES (6,2,'t'); +INSERT INTO `t1` VALUES (7,6,'j'); +INSERT INTO `t1` VALUES (8,8,'u'); +INSERT INTO `t1` VALUES (9,NULL,'h'); +INSERT INTO `t1` VALUES (10,5,'o'); +INSERT INTO `t1` VALUES (11,NULL,NULL); +INSERT INTO `t1` VALUES (12,6,'k'); +INSERT INTO `t1` VALUES (13,188,'e'); +INSERT INTO `t1` VALUES (14,2,'n'); +INSERT INTO `t1` VALUES (15,1,'t'); +INSERT INTO `t1` VALUES (16,1,'c'); +INSERT INTO `t1` VALUES (17,0,'m'); +INSERT INTO `t1` VALUES (18,9,'y'); +INSERT INTO `t1` VALUES (19,NULL,'f'); +INSERT INTO `t1` VALUES (20,4,'d'); +CREATE TABLE `t3` ( +`pk` int(11) NOT NULL AUTO_INCREMENT, +`col_int_nokey` int(11) DEFAULT NULL, +`col_varchar_nokey` varchar(1) DEFAULT NULL, +PRIMARY KEY (`pk`) +) ENGINE=MyISAM AUTO_INCREMENT=101 DEFAULT CHARSET=latin1; +INSERT INTO `t3` VALUES (1,6,'r'); +INSERT INTO `t3` VALUES (2,8,'c'); +INSERT INTO `t3` VALUES (3,6,'o'); +INSERT INTO `t3` VALUES (4,6,'c'); +INSERT INTO `t3` VALUES (5,3,'d'); +INSERT INTO `t3` VALUES (6,9,'v'); +INSERT INTO `t3` VALUES (7,2,'m'); +INSERT INTO `t3` VALUES (8,1,'j'); +INSERT INTO `t3` VALUES (9,8,'f'); +INSERT INTO `t3` VALUES (10,0,'n'); +INSERT INTO `t3` VALUES (11,9,'z'); +INSERT INTO `t3` VALUES (12,8,'h'); +INSERT INTO `t3` VALUES (13,NULL,'q'); +INSERT INTO `t3` VALUES (14,0,'w'); +INSERT INTO `t3` VALUES (15,5,'z'); +INSERT INTO `t3` VALUES (16,1,'j'); +INSERT INTO `t3` VALUES (17,1,'a'); +INSERT INTO `t3` VALUES (18,6,'m'); +INSERT INTO `t3` VALUES (19,6,'n'); +INSERT INTO `t3` VALUES (20,1,'e'); +INSERT INTO `t3` VALUES (21,8,'u'); +INSERT INTO `t3` VALUES (22,1,'s'); +INSERT INTO `t3` VALUES (23,0,'u'); +INSERT INTO `t3` VALUES (24,4,'r'); +INSERT INTO `t3` VALUES (25,9,'g'); +INSERT INTO `t3` VALUES (26,8,'o'); +INSERT INTO `t3` VALUES (27,5,'w'); +INSERT INTO `t3` VALUES (28,9,'b'); +INSERT INTO `t3` VALUES (29,5,NULL); +INSERT INTO `t3` VALUES (30,NULL,'y'); +INSERT INTO `t3` VALUES (31,NULL,'y'); +INSERT INTO `t3` VALUES (32,105,'u'); +INSERT INTO `t3` VALUES (33,0,'p'); +INSERT INTO `t3` VALUES (34,3,'s'); +INSERT INTO `t3` VALUES (35,1,'e'); +INSERT INTO `t3` VALUES (36,75,'d'); +INSERT INTO `t3` VALUES (37,9,'d'); +INSERT INTO `t3` VALUES (38,7,'c'); +INSERT INTO `t3` VALUES (39,NULL,'b'); +INSERT INTO `t3` VALUES (40,NULL,'t'); +INSERT INTO `t3` VALUES (41,4,NULL); +INSERT INTO `t3` VALUES (42,0,'y'); +INSERT INTO `t3` VALUES (43,204,'c'); +INSERT INTO `t3` VALUES (44,0,'d'); +INSERT INTO `t3` VALUES (45,9,'x'); +INSERT INTO `t3` VALUES (46,8,'p'); +INSERT INTO `t3` VALUES (47,7,'e'); +INSERT INTO `t3` VALUES (48,8,'g'); +INSERT INTO `t3` VALUES (49,NULL,'x'); +INSERT INTO `t3` VALUES (50,6,'s'); +INSERT INTO `t3` VALUES (51,5,'e'); +INSERT INTO `t3` VALUES (52,2,'l'); +INSERT INTO `t3` VALUES (53,3,'p'); +INSERT INTO `t3` VALUES (54,7,'h'); +INSERT INTO `t3` VALUES (55,NULL,'m'); +INSERT INTO `t3` VALUES (56,145,'n'); +INSERT INTO `t3` VALUES (57,0,'v'); +INSERT INTO `t3` VALUES (58,1,'b'); +INSERT INTO `t3` VALUES (59,7,'x'); +INSERT INTO `t3` VALUES (60,3,'r'); +INSERT INTO `t3` VALUES (61,NULL,'t'); +INSERT INTO `t3` VALUES (62,2,'w'); +INSERT INTO `t3` VALUES (63,2,'w'); +INSERT INTO `t3` VALUES (64,2,'k'); +INSERT INTO `t3` VALUES (65,8,'a'); +INSERT INTO `t3` VALUES (66,6,'t'); +INSERT INTO `t3` VALUES (67,1,'z'); +INSERT INTO `t3` VALUES (68,NULL,'e'); +INSERT INTO `t3` VALUES (69,1,'q'); +INSERT INTO `t3` VALUES (70,0,'e'); +INSERT INTO `t3` VALUES (71,4,'v'); +INSERT INTO `t3` VALUES (72,1,'d'); +INSERT INTO `t3` VALUES (73,1,'u'); +INSERT INTO `t3` VALUES (74,27,'o'); +INSERT INTO `t3` VALUES (75,4,'b'); +INSERT INTO `t3` VALUES (76,6,'c'); +INSERT INTO `t3` VALUES (77,2,'q'); +INSERT INTO `t3` VALUES (78,248,NULL); +INSERT INTO `t3` VALUES (79,NULL,'h'); +INSERT INTO `t3` VALUES (80,9,'d'); +INSERT INTO `t3` VALUES (81,75,'w'); +INSERT INTO `t3` VALUES (82,2,'m'); +INSERT INTO `t3` VALUES (83,9,'i'); +INSERT INTO `t3` VALUES (84,4,'w'); +INSERT INTO `t3` VALUES (85,0,'f'); +INSERT INTO `t3` VALUES (86,0,'k'); +INSERT INTO `t3` VALUES (87,1,'v'); +INSERT INTO `t3` VALUES (88,119,'c'); +INSERT INTO `t3` VALUES (89,1,'y'); +INSERT INTO `t3` VALUES (90,7,'h'); +INSERT INTO `t3` VALUES (91,2,NULL); +INSERT INTO `t3` VALUES (92,7,'t'); +INSERT INTO `t3` VALUES (93,2,'l'); +INSERT INTO `t3` VALUES (94,6,'a'); +INSERT INTO `t3` VALUES (95,4,'r'); +INSERT INTO `t3` VALUES (96,5,'s'); +INSERT INTO `t3` VALUES (97,7,'z'); +INSERT INTO `t3` VALUES (98,1,'j'); +INSERT INTO `t3` VALUES (99,7,'c'); +INSERT INTO `t3` VALUES (100,2,'f'); +CREATE TABLE `t2` ( +`pk` int(11) NOT NULL AUTO_INCREMENT, +`col_int_nokey` int(11) DEFAULT NULL, +`col_varchar_nokey` varchar(1) DEFAULT NULL, +PRIMARY KEY (`pk`) +) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1; +INSERT INTO `t2` VALUES (10,8,NULL); +set optimizer_switch='subquery_cache=off'; +SELECT ( +SELECT `col_int_nokey` +FROM t3 +WHERE table1 .`col_varchar_nokey` ) field13 +FROM t2 table1 JOIN t1 table2 ON table2 .`pk` +ORDER BY field13; +field13 +NULL +NULL +NULL +NULL +NULL +NULL +NULL +NULL +NULL +NULL +NULL +NULL +NULL +NULL +NULL +NULL +NULL +NULL +NULL +NULL +set optimizer_switch='subquery_cache=on'; +SELECT +(SELECT `col_int_nokey` + FROM t3 +WHERE table1 .`col_varchar_nokey` ) field13 +FROM t2 table1 JOIN t1 table2 ON table2 .`pk` +ORDER BY field13; +field13 +NULL +NULL +NULL +NULL +NULL +NULL +NULL +NULL +NULL +NULL +NULL +NULL +NULL +NULL +NULL +NULL +NULL +NULL +NULL +NULL +drop table t1,t2,t3; +set @@optimizer_switch= default; === modified file 'mysql-test/t/subquery_cache.test' --- a/mysql-test/t/subquery_cache.test 2010-07-30 04:16:58 +0000 +++ b/mysql-test/t/subquery_cache.test 2010-08-02 05:56:12 +0000 @@ -1306,3 +1306,167 @@ drop table t1,t2,t3,t4; set @@optimizer_switch= default; + +# +--echo #launchpad BUG#611625 +# +CREATE TABLE `t1` ( + `pk` int(11) NOT NULL AUTO_INCREMENT, + `col_int_nokey` int(11) DEFAULT NULL, + `col_varchar_nokey` varchar(1) DEFAULT NULL, + PRIMARY KEY (`pk`) +) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1; +INSERT INTO `t1` VALUES (1,NULL,'w'); +INSERT INTO `t1` VALUES (2,7,'m'); +INSERT INTO `t1` VALUES (3,9,'m'); +INSERT INTO `t1` VALUES (4,7,'k'); +INSERT INTO `t1` VALUES (5,4,'r'); +INSERT INTO `t1` VALUES (6,2,'t'); +INSERT INTO `t1` VALUES (7,6,'j'); +INSERT INTO `t1` VALUES (8,8,'u'); +INSERT INTO `t1` VALUES (9,NULL,'h'); +INSERT INTO `t1` VALUES (10,5,'o'); +INSERT INTO `t1` VALUES (11,NULL,NULL); +INSERT INTO `t1` VALUES (12,6,'k'); +INSERT INTO `t1` VALUES (13,188,'e'); +INSERT INTO `t1` VALUES (14,2,'n'); +INSERT INTO `t1` VALUES (15,1,'t'); +INSERT INTO `t1` VALUES (16,1,'c'); +INSERT INTO `t1` VALUES (17,0,'m'); +INSERT INTO `t1` VALUES (18,9,'y'); +INSERT INTO `t1` VALUES (19,NULL,'f'); +INSERT INTO `t1` VALUES (20,4,'d'); +CREATE TABLE `t3` ( + `pk` int(11) NOT NULL AUTO_INCREMENT, + `col_int_nokey` int(11) DEFAULT NULL, + `col_varchar_nokey` varchar(1) DEFAULT NULL, + PRIMARY KEY (`pk`) +) ENGINE=MyISAM AUTO_INCREMENT=101 DEFAULT CHARSET=latin1; +INSERT INTO `t3` VALUES (1,6,'r'); +INSERT INTO `t3` VALUES (2,8,'c'); +INSERT INTO `t3` VALUES (3,6,'o'); +INSERT INTO `t3` VALUES (4,6,'c'); +INSERT INTO `t3` VALUES (5,3,'d'); +INSERT INTO `t3` VALUES (6,9,'v'); +INSERT INTO `t3` VALUES (7,2,'m'); +INSERT INTO `t3` VALUES (8,1,'j'); +INSERT INTO `t3` VALUES (9,8,'f'); +INSERT INTO `t3` VALUES (10,0,'n'); +INSERT INTO `t3` VALUES (11,9,'z'); +INSERT INTO `t3` VALUES (12,8,'h'); +INSERT INTO `t3` VALUES (13,NULL,'q'); +INSERT INTO `t3` VALUES (14,0,'w'); +INSERT INTO `t3` VALUES (15,5,'z'); +INSERT INTO `t3` VALUES (16,1,'j'); +INSERT INTO `t3` VALUES (17,1,'a'); +INSERT INTO `t3` VALUES (18,6,'m'); +INSERT INTO `t3` VALUES (19,6,'n'); +INSERT INTO `t3` VALUES (20,1,'e'); +INSERT INTO `t3` VALUES (21,8,'u'); +INSERT INTO `t3` VALUES (22,1,'s'); +INSERT INTO `t3` VALUES (23,0,'u'); +INSERT INTO `t3` VALUES (24,4,'r'); +INSERT INTO `t3` VALUES (25,9,'g'); +INSERT INTO `t3` VALUES (26,8,'o'); +INSERT INTO `t3` VALUES (27,5,'w'); +INSERT INTO `t3` VALUES (28,9,'b'); +INSERT INTO `t3` VALUES (29,5,NULL); +INSERT INTO `t3` VALUES (30,NULL,'y'); +INSERT INTO `t3` VALUES (31,NULL,'y'); +INSERT INTO `t3` VALUES (32,105,'u'); +INSERT INTO `t3` VALUES (33,0,'p'); +INSERT INTO `t3` VALUES (34,3,'s'); +INSERT INTO `t3` VALUES (35,1,'e'); +INSERT INTO `t3` VALUES (36,75,'d'); +INSERT INTO `t3` VALUES (37,9,'d'); +INSERT INTO `t3` VALUES (38,7,'c'); +INSERT INTO `t3` VALUES (39,NULL,'b'); +INSERT INTO `t3` VALUES (40,NULL,'t'); +INSERT INTO `t3` VALUES (41,4,NULL); +INSERT INTO `t3` VALUES (42,0,'y'); +INSERT INTO `t3` VALUES (43,204,'c'); +INSERT INTO `t3` VALUES (44,0,'d'); +INSERT INTO `t3` VALUES (45,9,'x'); +INSERT INTO `t3` VALUES (46,8,'p'); +INSERT INTO `t3` VALUES (47,7,'e'); +INSERT INTO `t3` VALUES (48,8,'g'); +INSERT INTO `t3` VALUES (49,NULL,'x'); +INSERT INTO `t3` VALUES (50,6,'s'); +INSERT INTO `t3` VALUES (51,5,'e'); +INSERT INTO `t3` VALUES (52,2,'l'); +INSERT INTO `t3` VALUES (53,3,'p'); +INSERT INTO `t3` VALUES (54,7,'h'); +INSERT INTO `t3` VALUES (55,NULL,'m'); +INSERT INTO `t3` VALUES (56,145,'n'); +INSERT INTO `t3` VALUES (57,0,'v'); +INSERT INTO `t3` VALUES (58,1,'b'); +INSERT INTO `t3` VALUES (59,7,'x'); +INSERT INTO `t3` VALUES (60,3,'r'); +INSERT INTO `t3` VALUES (61,NULL,'t'); +INSERT INTO `t3` VALUES (62,2,'w'); +INSERT INTO `t3` VALUES (63,2,'w'); +INSERT INTO `t3` VALUES (64,2,'k'); +INSERT INTO `t3` VALUES (65,8,'a'); +INSERT INTO `t3` VALUES (66,6,'t'); +INSERT INTO `t3` VALUES (67,1,'z'); +INSERT INTO `t3` VALUES (68,NULL,'e'); +INSERT INTO `t3` VALUES (69,1,'q'); +INSERT INTO `t3` VALUES (70,0,'e'); +INSERT INTO `t3` VALUES (71,4,'v'); +INSERT INTO `t3` VALUES (72,1,'d'); +INSERT INTO `t3` VALUES (73,1,'u'); +INSERT INTO `t3` VALUES (74,27,'o'); +INSERT INTO `t3` VALUES (75,4,'b'); +INSERT INTO `t3` VALUES (76,6,'c'); +INSERT INTO `t3` VALUES (77,2,'q'); +INSERT INTO `t3` VALUES (78,248,NULL); +INSERT INTO `t3` VALUES (79,NULL,'h'); +INSERT INTO `t3` VALUES (80,9,'d'); +INSERT INTO `t3` VALUES (81,75,'w'); +INSERT INTO `t3` VALUES (82,2,'m'); +INSERT INTO `t3` VALUES (83,9,'i'); +INSERT INTO `t3` VALUES (84,4,'w'); +INSERT INTO `t3` VALUES (85,0,'f'); +INSERT INTO `t3` VALUES (86,0,'k'); +INSERT INTO `t3` VALUES (87,1,'v'); +INSERT INTO `t3` VALUES (88,119,'c'); +INSERT INTO `t3` VALUES (89,1,'y'); +INSERT INTO `t3` VALUES (90,7,'h'); +INSERT INTO `t3` VALUES (91,2,NULL); +INSERT INTO `t3` VALUES (92,7,'t'); +INSERT INTO `t3` VALUES (93,2,'l'); +INSERT INTO `t3` VALUES (94,6,'a'); +INSERT INTO `t3` VALUES (95,4,'r'); +INSERT INTO `t3` VALUES (96,5,'s'); +INSERT INTO `t3` VALUES (97,7,'z'); +INSERT INTO `t3` VALUES (98,1,'j'); +INSERT INTO `t3` VALUES (99,7,'c'); +INSERT INTO `t3` VALUES (100,2,'f'); +CREATE TABLE `t2` ( + `pk` int(11) NOT NULL AUTO_INCREMENT, + `col_int_nokey` int(11) DEFAULT NULL, + `col_varchar_nokey` varchar(1) DEFAULT NULL, + PRIMARY KEY (`pk`) +) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1; +INSERT INTO `t2` VALUES (10,8,NULL); + +set optimizer_switch='subquery_cache=off'; + +SELECT ( +SELECT `col_int_nokey` +FROM t3 +WHERE table1 .`col_varchar_nokey` ) field13 +FROM t2 table1 JOIN t1 table2 ON table2 .`pk` +ORDER BY field13; + +set optimizer_switch='subquery_cache=on'; + +SELECT + (SELECT `col_int_nokey` + FROM t3 + WHERE table1 .`col_varchar_nokey` ) field13 +FROM t2 table1 JOIN t1 table2 ON table2 .`pk` +ORDER BY field13; + +drop table t1,t2,t3; +set @@optimizer_switch= default; === modified file 'sql/sql_class.h' --- a/sql/sql_class.h 2010-07-16 11:02:15 +0000 +++ b/sql/sql_class.h 2010-08-02 05:56:12 +0000 @@ -62,9 +62,9 @@ class Item_iterator_ref_list: public Item_iterator { - List_iterator_fast<Item*> list; + List_iterator<Item*> list; public: - Item_iterator_ref_list(List_iterator_fast<Item*> &arg_list): + Item_iterator_ref_list(List_iterator<Item*> &arg_list): list(arg_list) {} void open() { list.rewind(); } Item *next() { return *(list++); } === modified file 'sql/sql_expression_cache.cc' --- a/sql/sql_expression_cache.cc 2010-07-30 04:16:58 +0000 +++ b/sql/sql_expression_cache.cc 2010-08-02 05:56:12 +0000 @@ -96,22 +96,39 @@ void Expression_cache_tmptable::init() { - List_iterator_fast<Item*> li(*list); + List_iterator<Item*> li(*list); Item_iterator_ref_list it(li); Item **item; uint field_counter; DBUG_ENTER("Expression_cache_tmptable::init"); DBUG_ASSERT(!inited); inited= TRUE; - - if (!(ULONGLONG_MAX >> (list->elements + 1))) - { - DBUG_PRINT("info", ("Too many dependencies")); + cache_table= NULL; + + while ((item= li++)) + { + DBUG_ASSERT(item); + if (*item) + { + DBUG_ASSERT((*item)->fixed); + items.push_back((*item)); + } + else + { + /* + This is possible when optimizer already executed this subquery and + optimized out a condition predicate. See launchpad bug#611625 + */ + li.remove(); + } + } + + if (list->elements == 0) + { + DBUG_PRINT("info", ("All parameters was removed by optimizer.")); DBUG_VOID_RETURN; } - cache_table= NULL; - cache_table_param.init(); /* dependent items and result */ cache_table_param.field_count= list->elements + 1; @@ -119,13 +136,6 @@ cache_table_param.skip_create_table= 1; cache_table= NULL; - while ((item= li++)) - { - DBUG_ASSERT(item); - DBUG_ASSERT(*item); - DBUG_ASSERT((*item)->fixed); - items.push_back((*item)); - } items.push_front(val); if (!(cache_table= create_tmp_table(table_thd, &cache_table_param,

1 0

[Maria-developers] Rev 2808: Fix for luanchpad bug#609043 in file:///home/bell/maria/bzr/work-maria-5.3-lb609043/
by sanja＠askmonty.org 29 Jul '10

29 Jul '10

At file:///home/bell/maria/bzr/work-maria-5.3-lb609043/ ------------------------------------------------------------ revno: 2808 revision-id: sanja(a)askmonty.org-20100730041658-2naumadh26t93e3g parent: sanja(a)askmonty.org-20100729111348-jjp89wlvs3kg0fqq committer: sanja(a)askmonty.org branch nick: work-maria-5.3-lb609043 timestamp: Fri 2010-07-30 07:16:58 +0300 message: Fix for luanchpad bug#609043 Removed indirect reference in equalities for cache index lookup. We should use a direct reference because some optimization of the query may optimize out a condition predicate and if the outer reference is the only element of the condition predicate the indirect reference becomes NULL. We can resolve correctly the indirect reference in Expression_cache_tmptable::make_equalities because it is called before optimization of the cached subquery. === modified file 'mysql-test/r/subquery_cache.result' --- a/mysql-test/r/subquery_cache.result 2010-07-29 11:13:48 +0000 +++ b/mysql-test/r/subquery_cache.result 2010-07-30 04:16:58 +0000 @@ -2881,3 +2881,107 @@ field1 field2 field3 field4 field5 field6 field7 field8 field9 field10 drop table t1,t2,t3,t4,t5; set @@optimizer_switch= default; +#launchpad BUG#609043 +CREATE TABLE `t1` ( +`pk` int(11) NOT NULL AUTO_INCREMENT, +`col_int_nokey` int(11) DEFAULT NULL, +`col_int_key` int(11) DEFAULT NULL, +`col_date_key` date DEFAULT NULL, +`col_date_nokey` date DEFAULT NULL, +`col_time_key` time DEFAULT NULL, +`col_time_nokey` time DEFAULT NULL, +`col_datetime_key` datetime DEFAULT NULL, +`col_datetime_nokey` datetime DEFAULT NULL, +`col_varchar_key` varchar(1) DEFAULT NULL, +`col_varchar_nokey` varchar(1) DEFAULT NULL, +PRIMARY KEY (`pk`), +KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`) +) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1; +INSERT INTO `t1` VALUES (19,NULL,6,'2004-08-20','2004-08-20','05:03:03','05:03:03','2007-04-19 00:19:53','2007-04-19 00:19:53','f','f'); +INSERT INTO `t1` VALUES (20,4,2,'1900-01-01','1900-01-01','18:38:59','18:38:59','1900-01-01 00:00:00','1900-01-01 00:00:00','d','d'); +CREATE TABLE `t2` ( +`pk` int(11) NOT NULL AUTO_INCREMENT, +`col_int_nokey` int(11) DEFAULT NULL, +`col_int_key` int(11) DEFAULT NULL, +`col_date_key` date DEFAULT NULL, +`col_date_nokey` date DEFAULT NULL, +`col_time_key` time DEFAULT NULL, +`col_time_nokey` time DEFAULT NULL, +`col_datetime_key` datetime DEFAULT NULL, +`col_datetime_nokey` datetime DEFAULT NULL, +`col_varchar_key` varchar(1) DEFAULT NULL, +`col_varchar_nokey` varchar(1) DEFAULT NULL, +PRIMARY KEY (`pk`), +KEY `col_int_key` (`col_int_key`), +KEY `col_date_key` (`col_date_key`), +KEY `col_time_key` (`col_time_key`), +KEY `col_datetime_key` (`col_datetime_key`), +KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`) +) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1; +CREATE TABLE `t3` ( +`pk` int(11) NOT NULL AUTO_INCREMENT, +`col_int_nokey` int(11) DEFAULT NULL, +`col_int_key` int(11) DEFAULT NULL, +`col_date_key` date DEFAULT NULL, +`col_date_nokey` date DEFAULT NULL, +`col_time_key` time DEFAULT NULL, +`col_time_nokey` time DEFAULT NULL, +`col_datetime_key` datetime DEFAULT NULL, +`col_datetime_nokey` datetime DEFAULT NULL, +`col_varchar_key` varchar(1) DEFAULT NULL, +`col_varchar_nokey` varchar(1) DEFAULT NULL, +PRIMARY KEY (`pk`), +KEY `col_int_key` (`col_int_key`), +KEY `col_date_key` (`col_date_key`), +KEY `col_time_key` (`col_time_key`), +KEY `col_datetime_key` (`col_datetime_key`), +KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`) +) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1; +CREATE TABLE `t4` ( +`pk` int(11) NOT NULL AUTO_INCREMENT, +`col_int_nokey` int(11) DEFAULT NULL, +`col_int_key` int(11) DEFAULT NULL, +`col_date_key` date DEFAULT NULL, +`col_date_nokey` date DEFAULT NULL, +`col_time_key` time DEFAULT NULL, +`col_time_nokey` time DEFAULT NULL, +`col_datetime_key` datetime DEFAULT NULL, +`col_datetime_nokey` datetime DEFAULT NULL, +`col_varchar_key` varchar(1) DEFAULT NULL, +`col_varchar_nokey` varchar(1) DEFAULT NULL, +PRIMARY KEY (`pk`), +KEY `col_int_key` (`col_int_key`), +KEY `col_date_key` (`col_date_key`), +KEY `col_time_key` (`col_time_key`), +KEY `col_datetime_key` (`col_datetime_key`), +KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`) +) ENGINE=MyISAM AUTO_INCREMENT=101 DEFAULT CHARSET=latin1; +INSERT INTO `t4` VALUES (100,2,5,'2001-07-26','2001-07-26','11:49:25','11:49:25','2007-04-25 05:08:49','2007-04-25 05:08:49','f','f'); +SET @@optimizer_switch = 'subquery_cache=off'; +/* cache is off */ SELECT COUNT( DISTINCT table2 .`col_int_key` ) , ( +SELECT SUBQUERY2_t1 .`col_int_key` +FROM t3 SUBQUERY2_t1 JOIN t2 ON SUBQUERY2_t1 .`col_int_key` +WHERE table1 .`col_varchar_key` ) , table2 .`col_varchar_nokey` field10 +FROM t4 table1 JOIN ( t1 table2 STRAIGHT_JOIN t1 table3 ON table2 .`pk` ) ON table3 .`col_varchar_key` = table2 .`col_varchar_key` +GROUP BY field10 ; +COUNT( DISTINCT table2 .`col_int_key` ) ( +SELECT SUBQUERY2_t1 .`col_int_key` +FROM t3 SUBQUERY2_t1 JOIN t2 ON SUBQUERY2_t1 .`col_int_key` +WHERE table1 .`col_varchar_key` ) field10 +1 NULL d +1 NULL f +SET @@optimizer_switch = 'subquery_cache=on'; +/* cache is on */ SELECT COUNT( DISTINCT table2 .`col_int_key` ) , ( +SELECT SUBQUERY2_t1 .`col_int_key` +FROM t3 SUBQUERY2_t1 JOIN t2 ON SUBQUERY2_t1 .`col_int_key` +WHERE table1 .`col_varchar_key` ) , table2 .`col_varchar_nokey` field10 +FROM t4 table1 JOIN ( t1 table2 STRAIGHT_JOIN t1 table3 ON table2 .`pk` ) ON table3 .`col_varchar_key` = table2 .`col_varchar_key` +GROUP BY field10 ; +COUNT( DISTINCT table2 .`col_int_key` ) ( +SELECT SUBQUERY2_t1 .`col_int_key` +FROM t3 SUBQUERY2_t1 JOIN t2 ON SUBQUERY2_t1 .`col_int_key` +WHERE table1 .`col_varchar_key` ) field10 +1 NULL d +1 NULL f +drop table t1,t2,t3,t4; +set @@optimizer_switch= default; === modified file 'mysql-test/t/subquery_cache.test' --- a/mysql-test/t/subquery_cache.test 2010-07-29 11:13:48 +0000 +++ b/mysql-test/t/subquery_cache.test 2010-07-30 04:16:58 +0000 @@ -1202,3 +1202,107 @@ drop table t1,t2,t3,t4,t5; set @@optimizer_switch= default; + + +# +--echo #launchpad BUG#609043 +# +CREATE TABLE `t1` ( + `pk` int(11) NOT NULL AUTO_INCREMENT, + `col_int_nokey` int(11) DEFAULT NULL, + `col_int_key` int(11) DEFAULT NULL, + `col_date_key` date DEFAULT NULL, + `col_date_nokey` date DEFAULT NULL, + `col_time_key` time DEFAULT NULL, + `col_time_nokey` time DEFAULT NULL, + `col_datetime_key` datetime DEFAULT NULL, + `col_datetime_nokey` datetime DEFAULT NULL, + `col_varchar_key` varchar(1) DEFAULT NULL, + `col_varchar_nokey` varchar(1) DEFAULT NULL, + PRIMARY KEY (`pk`), + KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`) +) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1; +INSERT INTO `t1` VALUES (19,NULL,6,'2004-08-20','2004-08-20','05:03:03','05:03:03','2007-04-19 00:19:53','2007-04-19 00:19:53','f','f'); +INSERT INTO `t1` VALUES (20,4,2,'1900-01-01','1900-01-01','18:38:59','18:38:59','1900-01-01 00:00:00','1900-01-01 00:00:00','d','d'); + +CREATE TABLE `t2` ( + `pk` int(11) NOT NULL AUTO_INCREMENT, + `col_int_nokey` int(11) DEFAULT NULL, + `col_int_key` int(11) DEFAULT NULL, + `col_date_key` date DEFAULT NULL, + `col_date_nokey` date DEFAULT NULL, + `col_time_key` time DEFAULT NULL, + `col_time_nokey` time DEFAULT NULL, + `col_datetime_key` datetime DEFAULT NULL, + `col_datetime_nokey` datetime DEFAULT NULL, + `col_varchar_key` varchar(1) DEFAULT NULL, + `col_varchar_nokey` varchar(1) DEFAULT NULL, + PRIMARY KEY (`pk`), + KEY `col_int_key` (`col_int_key`), + KEY `col_date_key` (`col_date_key`), + KEY `col_time_key` (`col_time_key`), + KEY `col_datetime_key` (`col_datetime_key`), + KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`) +) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1; + +CREATE TABLE `t3` ( + `pk` int(11) NOT NULL AUTO_INCREMENT, + `col_int_nokey` int(11) DEFAULT NULL, + `col_int_key` int(11) DEFAULT NULL, + `col_date_key` date DEFAULT NULL, + `col_date_nokey` date DEFAULT NULL, + `col_time_key` time DEFAULT NULL, + `col_time_nokey` time DEFAULT NULL, + `col_datetime_key` datetime DEFAULT NULL, + `col_datetime_nokey` datetime DEFAULT NULL, + `col_varchar_key` varchar(1) DEFAULT NULL, + `col_varchar_nokey` varchar(1) DEFAULT NULL, + PRIMARY KEY (`pk`), + KEY `col_int_key` (`col_int_key`), + KEY `col_date_key` (`col_date_key`), + KEY `col_time_key` (`col_time_key`), + KEY `col_datetime_key` (`col_datetime_key`), + KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`) +) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1; + +CREATE TABLE `t4` ( + `pk` int(11) NOT NULL AUTO_INCREMENT, + `col_int_nokey` int(11) DEFAULT NULL, + `col_int_key` int(11) DEFAULT NULL, + `col_date_key` date DEFAULT NULL, + `col_date_nokey` date DEFAULT NULL, + `col_time_key` time DEFAULT NULL, + `col_time_nokey` time DEFAULT NULL, + `col_datetime_key` datetime DEFAULT NULL, + `col_datetime_nokey` datetime DEFAULT NULL, + `col_varchar_key` varchar(1) DEFAULT NULL, + `col_varchar_nokey` varchar(1) DEFAULT NULL, + PRIMARY KEY (`pk`), + KEY `col_int_key` (`col_int_key`), + KEY `col_date_key` (`col_date_key`), + KEY `col_time_key` (`col_time_key`), + KEY `col_datetime_key` (`col_datetime_key`), + KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`) +) ENGINE=MyISAM AUTO_INCREMENT=101 DEFAULT CHARSET=latin1; +INSERT INTO `t4` VALUES (100,2,5,'2001-07-26','2001-07-26','11:49:25','11:49:25','2007-04-25 05:08:49','2007-04-25 05:08:49','f','f'); + +SET @@optimizer_switch = 'subquery_cache=off'; + +/* cache is off */ SELECT COUNT( DISTINCT table2 .`col_int_key` ) , ( +SELECT SUBQUERY2_t1 .`col_int_key` +FROM t3 SUBQUERY2_t1 JOIN t2 ON SUBQUERY2_t1 .`col_int_key` +WHERE table1 .`col_varchar_key` ) , table2 .`col_varchar_nokey` field10 +FROM t4 table1 JOIN ( t1 table2 STRAIGHT_JOIN t1 table3 ON table2 .`pk` ) ON table3 .`col_varchar_key` = table2 .`col_varchar_key` +GROUP BY field10 ; + +SET @@optimizer_switch = 'subquery_cache=on'; + +/* cache is on */ SELECT COUNT( DISTINCT table2 .`col_int_key` ) , ( +SELECT SUBQUERY2_t1 .`col_int_key` +FROM t3 SUBQUERY2_t1 JOIN t2 ON SUBQUERY2_t1 .`col_int_key` +WHERE table1 .`col_varchar_key` ) , table2 .`col_varchar_nokey` field10 +FROM t4 table1 JOIN ( t1 table2 STRAIGHT_JOIN t1 table3 ON table2 .`pk` ) ON table3 .`col_varchar_key` = table2 .`col_varchar_key` +GROUP BY field10 ; + +drop table t1,t2,t3,t4; +set @@optimizer_switch= default; === modified file 'sql/sql_expression_cache.cc' --- a/sql/sql_expression_cache.cc 2010-07-10 10:37:30 +0000 +++ b/sql/sql_expression_cache.cc 2010-07-30 04:16:58 +0000 @@ -41,7 +41,6 @@ List<Item> args; List_iterator_fast<Item*> li(*list); Item **ref; - Name_resolution_context *cn= NULL; DBUG_ENTER("Expression_cache_tmptable::make_equalities"); for (uint i= 1 /* skip result filed */; (ref= li++); i++) @@ -58,14 +57,7 @@ fld->type() == MYSQL_TYPE_NEWDECIMAL || fld->type() == MYSQL_TYPE_DECIMAL) { - if (!cn) - { - // dummy resolution context - cn= new Name_resolution_context(); - cn->init(); - } - args.push_front(new Item_func_eq(new Item_ref(cn, ref, "", "", FALSE), - new Item_field(fld))); + args.push_front(new Item_func_eq(*ref, new Item_field(fld))); } } if (args.elements == 1)

1 0