August 2010 - developers - lists.mariadb.org

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

10 Aug '10

Michael Widenius <michael.widenius(a)gmail.com> writes: > #At lp:maria based on revid:monty@mysql.com-20100809170542-ewa2awm6pcoi1ipy > > 2897 Michael Widenius 2010-08-09 > Ignore ENOLCK errno from FreeBSD (known problem in old FreeBSD releases) > modified: > mysys/my_sync.c > > === modified file 'mysys/my_sync.c' > --- a/mysys/my_sync.c 2010-08-09 17:05:42 +0000 > +++ b/mysys/my_sync.c 2010-08-09 17:54:58 +0000 > @@ -68,6 +68,8 @@ … [View More]

1 0

[Maria-developers] FYI - Buildbots
by Adam M. Dutko 10 Aug '10

10 Aug '10

My ISP went down last night around midnight. I'm not sure if it affected some of the builds. Sorry if it did. Please let me know if you need to me to do anything with: adutko-centos5-amd64 adutko-ultrasparc3 mariadb-brs I just checked each of them and the buildbot status page and they all seem to be working normally. -Adam

1 0

[Maria-developers] Strange code in 5.2 & Assigned bugs
by Michael Widenius 10 Aug '10

10 Aug '10

Hi! Sanja, in Aria engine in 5.2 we have the code: else { soft_sync_max= lsn; soft_need_sync= 1; } This is likely wrong as soft_sync_max is supposed to be a file number and lsn is too big to fix into soft_sync_max (which is only a uint32). Could you please take a look at this. Note that I also assigned the following bug in 5.2 for you to take a look at: #611379 Equivalent queries with Impossible where return different results Please also try to get this one fixed (as it's … [View More]

2 1

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

10 Aug '10

At file:///home/bell/maria/bzr/work-maria-5.1-lb611379/ ------------------------------------------------------------ revno: 2897 revision-id: sanja(a)askmonty.org-20100810030810-q290n2tcc9uc4xg3 parent: monty(a)mysql.com-20100809170542-ewa2awm6pcoi1ipy committer: sanja(a)askmonty.org branch nick: work-maria-5.1-lb611379 timestamp: Tue 2010-08-10 06:08:10 +0300 message: Fix for LP bug#611379. maybe_null/null_value flag set to TRUE for Item_sum_distinct. === modified file 'mysql-test/r/… [View More]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-10 03:08:10 +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 `t2` ( +`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 `t2` VALUES (10,8,NULL); +CREATE TABLE `t3` ( +`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 `t3` VALUES (1,7,'f'); +SELECT SUM( DISTINCT table1 .`pk` ) FROM t3 table1 STRAIGHT_JOIN ( t2 table2 JOIN t1 ON table2 .`col_varchar_key` ) ON table2 .`pk` ; +SUM( DISTINCT table1 .`pk` ) +NULL +SELECT * FROM ( SELECT SUM( DISTINCT table1 .`pk` ) FROM t3 table1 STRAIGHT_JOIN ( t2 table2 JOIN t1 ON table2 .`col_varchar_key` ) ON table2 .`pk` ) AS t1; +SUM( DISTINCT table1 .`pk` ) +NULL +drop table t1,t2,t3; +# 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-10 03:08:10 +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 `t2` ( + `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 `t2` VALUES (10,8,NULL); +CREATE TABLE `t3` ( + `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 `t3` VALUES (1,7,'f'); + +SELECT SUM( DISTINCT table1 .`pk` ) FROM t3 table1 STRAIGHT_JOIN ( t2 table2 JOIN t1 ON table2 .`col_varchar_key` ) ON table2 .`pk` ; +SELECT * FROM ( SELECT SUM( DISTINCT table1 .`pk` ) FROM t3 table1 STRAIGHT_JOIN ( t2 table2 JOIN t1 ON table2 .`col_varchar_key` ) ON table2 .`pk` ) AS t1; +drop table t1,t2,t3; + --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-10 03:08:10 +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 */ [View Less]

1 0

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

10 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/… [View More]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 */ [View Less]

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/… [View More]

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 … [View More]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, [View Less]

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 … [View More]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 [View Less]

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