- developers - lists.mariadb.org

[Maria-developers] Rev 2897: Fix for LP bug#611379. in file:///home/bell/maria/bzr/work-maria-5.1-lb611379/
by sanja＠askmonty.org 09 Aug '10

09 Aug '10

At file:///home/bell/maria/bzr/work-maria-5.1-lb611379/ ------------------------------------------------------------ revno: 2897 revision-id: sanja(a)askmonty.org-20100809193502-gdriohzan0t6s727 parent: monty(a)mysql.com-20100809170542-ewa2awm6pcoi1ipy committer: sanja(a)askmonty.org branch nick: work-maria-5.1-lb611379 timestamp: Mon 2010-08-09 22:35:02 +0300 message: Fix for LP bug#611379. maybe_null/null_value flag set to TRUE for Item_sum_distinct. === modified file 'mysql-test/r/func_group.result' --- a/mysql-test/r/func_group.result 2009-11-24 15:26:13 +0000 +++ b/mysql-test/r/func_group.result 2010-08-09 19:35:02 +0000 @@ -1713,4 +1713,60 @@ NULL NULL NULL NULL NULL drop table t1; # +#test for LP Bug#611379 +# +CREATE TABLE `t1` ( +`pk` int(11) NOT NULL AUTO_INCREMENT, +`col_int_key` int(11) DEFAULT NULL, +`col_varchar_key` varchar(1) DEFAULT NULL, +PRIMARY KEY (`pk`), +KEY `col_int_key` (`col_int_key`), +KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`) +) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1; +INSERT INTO `t1` VALUES (10,8,'v'); +INSERT INTO `t1` VALUES (11,9,'r'); +INSERT INTO `t1` VALUES (12,9,'a'); +INSERT INTO `t1` VALUES (13,186,'m'); +INSERT INTO `t1` VALUES (14,NULL,'y'); +INSERT INTO `t1` VALUES (15,2,'j'); +INSERT INTO `t1` VALUES (16,3,'d'); +INSERT INTO `t1` VALUES (17,0,'z'); +INSERT INTO `t1` VALUES (18,133,'e'); +INSERT INTO `t1` VALUES (19,1,'h'); +INSERT INTO `t1` VALUES (20,8,'b'); +INSERT INTO `t1` VALUES (21,5,'s'); +INSERT INTO `t1` VALUES (22,5,'e'); +INSERT INTO `t1` VALUES (23,8,'j'); +INSERT INTO `t1` VALUES (24,6,'e'); +INSERT INTO `t1` VALUES (25,51,'f'); +INSERT INTO `t1` VALUES (26,4,'v'); +INSERT INTO `t1` VALUES (27,7,'x'); +INSERT INTO `t1` VALUES (28,6,'m'); +INSERT INTO `t1` VALUES (29,4,'c'); +CREATE TABLE `BB` ( +`pk` int(11) NOT NULL AUTO_INCREMENT, +`col_int_key` int(11) DEFAULT NULL, +`col_varchar_key` varchar(1) DEFAULT NULL, +PRIMARY KEY (`pk`), +KEY `col_int_key` (`col_int_key`), +KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`) +) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1; +INSERT INTO `BB` VALUES (10,8,NULL); +CREATE TABLE `B` ( +`pk` int(11) NOT NULL AUTO_INCREMENT, +`col_int_key` int(11) DEFAULT NULL, +`col_varchar_key` varchar(1) DEFAULT NULL, +PRIMARY KEY (`pk`), +KEY `col_int_key` (`col_int_key`), +KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`) +) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1; +INSERT INTO `B` VALUES (1,7,'f'); +SELECT SUM( DISTINCT table1 .`pk` ) FROM B table1 STRAIGHT_JOIN ( BB table2 JOIN t1 ON table2 .`col_varchar_key` ) ON table2 .`pk` ; +SUM( DISTINCT table1 .`pk` ) +NULL +SELECT * FROM ( SELECT SUM( DISTINCT table1 .`pk` ) FROM B table1 STRAIGHT_JOIN ( BB table2 JOIN t1 ON table2 .`col_varchar_key` ) ON table2 .`pk` ) AS t1; +SUM( DISTINCT table1 .`pk` ) +NULL +drop table t1; +# End of 5.1 tests === modified file 'mysql-test/t/func_group.test' --- a/mysql-test/t/func_group.test 2009-11-24 15:26:13 +0000 +++ b/mysql-test/t/func_group.test 2010-08-09 19:35:02 +0000 @@ -1082,6 +1082,61 @@ from t1 a, t1 b; select *, f1 = f2 from t1; drop table t1; + +--echo # +--echo #test for LP Bug#611379 +--echo # +CREATE TABLE `t1` ( + `pk` int(11) NOT NULL AUTO_INCREMENT, + `col_int_key` int(11) DEFAULT NULL, + `col_varchar_key` varchar(1) DEFAULT NULL, + PRIMARY KEY (`pk`), + KEY `col_int_key` (`col_int_key`), + KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`) +) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1; +INSERT INTO `t1` VALUES (10,8,'v'); +INSERT INTO `t1` VALUES (11,9,'r'); +INSERT INTO `t1` VALUES (12,9,'a'); +INSERT INTO `t1` VALUES (13,186,'m'); +INSERT INTO `t1` VALUES (14,NULL,'y'); +INSERT INTO `t1` VALUES (15,2,'j'); +INSERT INTO `t1` VALUES (16,3,'d'); +INSERT INTO `t1` VALUES (17,0,'z'); +INSERT INTO `t1` VALUES (18,133,'e'); +INSERT INTO `t1` VALUES (19,1,'h'); +INSERT INTO `t1` VALUES (20,8,'b'); +INSERT INTO `t1` VALUES (21,5,'s'); +INSERT INTO `t1` VALUES (22,5,'e'); +INSERT INTO `t1` VALUES (23,8,'j'); +INSERT INTO `t1` VALUES (24,6,'e'); +INSERT INTO `t1` VALUES (25,51,'f'); +INSERT INTO `t1` VALUES (26,4,'v'); +INSERT INTO `t1` VALUES (27,7,'x'); +INSERT INTO `t1` VALUES (28,6,'m'); +INSERT INTO `t1` VALUES (29,4,'c'); +CREATE TABLE `BB` ( + `pk` int(11) NOT NULL AUTO_INCREMENT, + `col_int_key` int(11) DEFAULT NULL, + `col_varchar_key` varchar(1) DEFAULT NULL, + PRIMARY KEY (`pk`), + KEY `col_int_key` (`col_int_key`), + KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`) +) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1; +INSERT INTO `BB` VALUES (10,8,NULL); +CREATE TABLE `B` ( + `pk` int(11) NOT NULL AUTO_INCREMENT, + `col_int_key` int(11) DEFAULT NULL, + `col_varchar_key` varchar(1) DEFAULT NULL, + PRIMARY KEY (`pk`), + KEY `col_int_key` (`col_int_key`), + KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`) +) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1; +INSERT INTO `B` VALUES (1,7,'f'); + +SELECT SUM( DISTINCT table1 .`pk` ) FROM B table1 STRAIGHT_JOIN ( BB table2 JOIN t1 ON table2 .`col_varchar_key` ) ON table2 .`pk` ; +SELECT * FROM ( SELECT SUM( DISTINCT table1 .`pk` ) FROM B table1 STRAIGHT_JOIN ( BB table2 JOIN t1 ON table2 .`col_varchar_key` ) ON table2 .`pk` ) AS t1; +drop table t1; + --echo # --echo End of 5.1 tests === modified file 'sql/item_sum.cc' --- a/sql/item_sum.cc 2010-08-02 09:01:24 +0000 +++ b/sql/item_sum.cc 2010-08-09 19:35:02 +0000 @@ -949,6 +949,7 @@ { DBUG_ASSERT(args[0]->fixed); + null_value= maybe_null= TRUE; table_field_type= args[0]->field_type(); /* Adjust tmp table type according to the chosen aggregation type */

1 0

[Maria-developers] Rev 2843: Fix of soft group commit (assigned LSN instead of file number). Found by Monty. in file:///home/bell/maria/bzr/work-maria-5.2-soft_sync/
by sanja＠askmonty.org 09 Aug '10

09 Aug '10

At file:///home/bell/maria/bzr/work-maria-5.2-soft_sync/ ------------------------------------------------------------ revno: 2843 revision-id: sanja(a)askmonty.org-20100809124203-7lz5zukr5q7wtdyz parent: monty(a)askmonty.org-20100807150304-1qb1s7hvv0fh9wlq committer: sanja(a)askmonty.org branch nick: work-maria-5.2-soft_sync timestamp: Mon 2010-08-09 15:42:03 +0300 message: Fix of soft group commit (assigned LSN instead of file number). Found by Monty. === modified file 'storage/maria/ma_loghandler.c' --- a/storage/maria/ma_loghandler.c 2010-05-06 09:59:10 +0000 +++ b/storage/maria/ma_loghandler.c 2010-08-09 12:42:03 +0000 @@ -8073,7 +8073,7 @@ } else { - soft_sync_max= lsn; + soft_sync_max= LSN_FILE_NO(lsn); soft_need_sync= 1; }

1 0

[Maria-developers] Rev 2810: Fix for LP 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 09 Aug '10

09 Aug '10

At file:///home/bell/maria/bzr/work-maria-5.3-lb611625/ ------------------------------------------------------------ revno: 2810 revision-id: sanja(a)askmonty.org-20100809100058-qqmlj17yajig4gwj parent: sanja(a)askmonty.org-20100805142348-wdtwgvj5tm2si5gt committer: sanja(a)askmonty.org branch nick: work-maria-5.3-lb611625 timestamp: Mon 2010-08-09 13:00:58 +0300 message: Fix for LP bug#611625: Removing NULL references from subquery parameter list added. Incorrect limitation on number of parameters removed. === modified file 'mysql-test/r/subselect_cache.result' --- a/mysql-test/r/subselect_cache.result 2010-08-05 14:23:48 +0000 +++ b/mysql-test/r/subselect_cache.result 2010-08-09 10:00:58 +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/subselect_cache.test' --- a/mysql-test/t/subselect_cache.test 2010-08-05 14:23:48 +0000 +++ b/mysql-test/t/subselect_cache.test 2010-08-09 10:00:58 +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-09 10:00:58 +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-09 10:00:58 +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 the condition predicate. + */ + li.remove(); + } + } + + if (list->elements == 0) + { + DBUG_PRINT("info", ("All parameters were 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

Re: [Maria-developers] 64 table join limit
by dan_meany 07 Aug '10

07 Aug '10

Yes, flexible indexing for the dynamic columns will be key for performance, for example, for date or numeric range scans, and perhaps multipart keys. The single-table self-join method I'm currently using is indexed both id/fieldname/value and value/fieldname/id to help make searching and logical joining betwen dynamic tables perform, with additional indexes using the numeric and date equivalents for the string values where applicable. I agree there are optimal use cases for both schemaless and schemafull, and I use a hybrid model with both. On Aug 7, 2010, at 8:27 AM, Michael Widenius <monty(a)askmonty.org> wrote: Hi! "Arjen" == Arjen Lentz <arjen(a)openquery.com> writes: Arjen> Hi Dan Arjen> On 05/08/2010, at 12:28 AM, Dan Meany wrote: Hi, I was wondering if anyone here had any opinions about this proposed fix for the 64 table join limit (originally by Sergei) http://lists.mysql.com/internals/38013 With the increased use of schemaless and hybrid, partly schemaless application designs, this would be a big enhancement. In my own application, being able to make minor, typed, virtual column additions for different SAAS clients in virtual tables at runtime without changing the physical schema or regenerating application binding layers is pretty priceless. But currently we are limited to 64 virtual columns by this mySQL join limit. <cut> Arjen> - if you really want to be schemaless, don't use an RDBMS. RDBMS are Arjen> structured, for a reason. It helps in a multitude of ways. Sometimes Arjen> for whatever reason you can decide that structure is not what you Arjen> need, but then using an RDBMS (apart from the familiar convenient Arjen> interface) makes no sense. We have plans to add dynamic columns into MariaDB soon. This will probably happen in 5.3, assuming there is some interest in this feature. With dynamic columns you can store 'any' number of columns in a blob. In effect, each row in the database may have it's own set of columns. One will be able to trivially access and update data in the dynamic columns and also add/drop columns inside the blob. Example usage: SELECT column_get(blob, 1, varchar(100)) from table_with_dynamic; UPDATE table_with_dynamic SET blob=column_add(blob, 2, "hello") where id=1; UPDATE table_with_dynamic SET blob=column_del(blob,4) where id=5; Note that 'column_add()' will replace any old value with the given column_id. Future ideas: - Allow indexing with name instead of numbers. When this is done we can drop the type as part of column_get() - Allow indexing on dynamic fields. You can find a full specification of this feature here: http://askmonty.org/worklog/Server-BackLog/?tid=34 Regards, Monty _______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers(a)lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp

1 0

Re: [Maria-developers] [Commits] bzr commit into MariaDB 5.1, with Maria 1.5:maria branch (monty:2877)
by Kristian Nielsen 07 Aug '10

07 Aug '10

Michael Widenius <monty(a)askmonty.org> writes: > + &find_innodb_plugin; > + if (&find_innodb_plugin) Why use such wierd syntax for calling a function? Why not the normal find_innodb_plugin() ? - Kristian.

2 1

[Maria-developers] rename maria to aria in 5.2
by Sergei Golubchik 07 Aug '10

07 Aug '10

Hi, I'v renamed everything user-visible (error messages, variables, command-line utilites, etc). Do you want me to rename just *everything* ? file names, function names, etc ? I mean, all internal stuff. It wouldn't add much value, and would complicate the merges. On the other hand, it'd be conststent. Regards, Sergei

1 0

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