- developers - lists.mariadb.org

[Maria-developers] Rev 2755: Subquery optimizations: backport: enable disabled subquery code in BKA in file:///home/psergey/dev/maria-5.3-subqueries-r6/
by Sergey Petrunya 11 Feb '10

11 Feb '10

At file:///home/psergey/dev/maria-5.3-subqueries-r6/ ------------------------------------------------------------ revno: 2755 revision-id: psergey(a)askmonty.org-20100211223118-5fzuidow1pkubpzl parent: psergey(a)askmonty.org-20100211220036-qh3iw743tbgwpzax committer: Sergey Petrunya <psergey(a)askmonty.org> branch nick: maria-5.3-subqueries-r6 timestamp: Fri 2010-02-12 01:31:18 +0300 message: Subquery optimizations: backport: enable disabled subquery code in BKA === modified file 'sql/sql_select.h' --- a/sql/sql_select.h 2010-01-28 13:48:33 +0000 +++ b/sql/sql_select.h 2010-02-11 22:31:18 +0000 @@ -282,13 +282,11 @@ } bool check_rowid_field() { -/* !!!NB igor: enable the code in this comment after backporting the SJ code if (keep_current_rowid && !used_rowid_fields) { used_rowid_fields= 1; used_fieldlength+= table->file->ref_length; } -*/ return test(used_rowid_fields); } bool is_inner_table_of_semi_join_with_first_match()

1 0

[Maria-developers] Rev 2754: Subquery optimizations: backport in file:///home/psergey/dev/maria-5.3-subqueries-r6/
by Sergey Petrunya 11 Feb '10

11 Feb '10

At file:///home/psergey/dev/maria-5.3-subqueries-r6/ ------------------------------------------------------------ revno: 2754 revision-id: psergey(a)askmonty.org-20100211220036-qh3iw743tbgwpzax parent: psergey(a)askmonty.org-20100211215932-qi36vl0i3zkl86bv committer: Sergey Petrunya <psergey(a)askmonty.org> branch nick: maria-5.3-subqueries-r6 timestamp: Fri 2010-02-12 01:00:36 +0300 message: Subquery optimizations: backport - Fix valgrind failure: do initialize Item::is_expensive_cache. === modified file 'sql/item.cc' --- a/sql/item.cc 2010-01-17 14:55:08 +0000 +++ b/sql/item.cc 2010-02-11 22:00:36 +0000 @@ -373,8 +373,8 @@ Item::Item(): - rsize(0), name(0), orig_name(0), name_length(0), fixed(0), - is_autogenerated_name(TRUE), + is_expensive_cache(-1), rsize(0), name(0), orig_name(0), name_length(0), + fixed(0), is_autogenerated_name(TRUE), collation(&my_charset_bin, DERIVATION_COERCIBLE) { marker= 0; @@ -410,6 +410,7 @@ tables. */ Item::Item(THD *thd, Item *item): + is_expensive_cache(-1), rsize(0), str_value(item->str_value), name(item->name), === modified file 'sql/item.h' --- a/sql/item.h 2010-01-28 13:48:33 +0000 +++ b/sql/item.h 2010-02-11 22:00:36 +0000 @@ -513,6 +513,9 @@ enum traverse_order { POSTFIX, PREFIX }; + /* Cache of the result of is_expensive(). */ + int8 is_expensive_cache; + /* Reuse size, only used by SP local variable assignment, otherwize 0 */ uint rsize; @@ -878,9 +881,6 @@ static CHARSET_INFO *default_charset(); virtual CHARSET_INFO *compare_collation() { return NULL; } - /* Cache of the result of is_expensive(). */ - int8 is_expensive_cache; - virtual bool walk(Item_processor processor, bool walk_subquery, uchar *arg) { return (this->*processor)(arg);

1 0

[Maria-developers] Rev 2753: Subquery optimizations backport: Update test results (checked) in file:///home/psergey/dev/maria-5.3-subqueries-r6/
by Sergey Petrunya 11 Feb '10

11 Feb '10

At file:///home/psergey/dev/maria-5.3-subqueries-r6/ ------------------------------------------------------------ revno: 2753 revision-id: psergey(a)askmonty.org-20100211215932-qi36vl0i3zkl86bv parent: psergey(a)askmonty.org-20100211215823-63ikirl70ztmlk05 committer: Sergey Petrunya <psergey(a)askmonty.org> branch nick: maria-5.3-subqueries-r6 timestamp: Fri 2010-02-12 00:59:32 +0300 message: Subquery optimizations backport: Update test results (checked) === modified file 'mysql-test/r/join_cache.result' --- a/mysql-test/r/join_cache.result 2009-12-21 02:26:15 +0000 +++ b/mysql-test/r/join_cache.result 2010-02-11 21:59:32 +0000 @@ -1028,8 +1028,8 @@ WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY City ALL Population NULL NULL NULL 4079 Using where -2 DEPENDENT SUBQUERY Country unique_subquery PRIMARY,Name PRIMARY 3 func 1 Using where +1 PRIMARY Country range PRIMARY,Name Name 52 NULL 10 Using index condition; Using MRR +1 PRIMARY City ref Population,Country Country 3 world.Country.Code 18 Using where; Using join buffer SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; @@ -1343,8 +1343,8 @@ WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY City ALL Population NULL NULL NULL 4079 Using where -2 DEPENDENT SUBQUERY Country unique_subquery PRIMARY,Name PRIMARY 3 func 1 Using where +1 PRIMARY Country range PRIMARY,Name Name 52 NULL 10 Using index condition; Using MRR +1 PRIMARY City ref Population,Country Country 3 world.Country.Code 18 Using where; Using join buffer SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; @@ -1658,8 +1658,8 @@ WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY City ALL Population NULL NULL NULL 4079 Using where -2 DEPENDENT SUBQUERY Country unique_subquery PRIMARY,Name PRIMARY 3 func 1 Using where +1 PRIMARY Country range PRIMARY,Name Name 52 NULL 10 Using index condition; Using MRR +1 PRIMARY City ref Population,Country Country 3 world.Country.Code 18 Using where; Using join buffer SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; @@ -1973,8 +1973,8 @@ WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY City ALL Population NULL NULL NULL 4079 Using where -2 DEPENDENT SUBQUERY Country unique_subquery PRIMARY,Name PRIMARY 3 func 1 Using where +1 PRIMARY Country range PRIMARY,Name Name 52 NULL 10 Using index condition; Using MRR +1 PRIMARY City ref Population,Country Country 3 world.Country.Code 18 Using where; Using join buffer SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; @@ -2292,8 +2292,8 @@ WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY City ALL Population NULL NULL NULL 4079 Using where -2 DEPENDENT SUBQUERY Country unique_subquery PRIMARY,Name PRIMARY 3 func 1 Using where +1 PRIMARY Country range PRIMARY,Name Name 52 NULL 10 Using index condition; Using MRR +1 PRIMARY City ref Population,Country Country 3 world.Country.Code 18 Using where; Using join buffer SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; @@ -2514,8 +2514,8 @@ WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY City ALL Population NULL NULL NULL 4079 Using where -2 DEPENDENT SUBQUERY Country unique_subquery PRIMARY,Name PRIMARY 3 func 1 Using where +1 PRIMARY Country range PRIMARY,Name Name 52 NULL 10 Using index condition; Using MRR +1 PRIMARY City ref Population,Country Country 3 world.Country.Code 18 Using where; Using join buffer SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; @@ -2736,8 +2736,8 @@ WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY City ALL Population NULL NULL NULL 4079 Using where -2 DEPENDENT SUBQUERY Country unique_subquery PRIMARY,Name PRIMARY 3 func 1 Using where +1 PRIMARY Country range PRIMARY,Name Name 52 NULL 10 Using index condition; Using MRR +1 PRIMARY City ref Population,Country Country 3 world.Country.Code 18 Using where; Using join buffer SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; @@ -2958,8 +2958,8 @@ WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY City ALL Population NULL NULL NULL 4079 Using where -2 DEPENDENT SUBQUERY Country unique_subquery PRIMARY,Name PRIMARY 3 func 1 Using where +1 PRIMARY Country range PRIMARY,Name Name 52 NULL 10 Using index condition; Using MRR +1 PRIMARY City ref Population,Country Country 3 world.Country.Code 18 Using where; Using join buffer SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; === modified file 'mysql-test/r/type_datetime.result' --- a/mysql-test/r/type_datetime.result 2009-02-13 18:07:03 +0000 +++ b/mysql-test/r/type_datetime.result 2010-02-11 21:59:32 +0000 @@ -514,10 +514,9 @@ where id in (select id from t1 as x1 where (t1.cur_date is null)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE Warnings: Note 1276 Field or reference 'test.t1.cur_date' of SELECT #2 was resolved in SELECT #1 -Note 1003 select '1' AS `id`,'2007-04-25 18:30:22' AS `cur_date` from `test`.`t1` where <in_optimizer>('1',<exists>(select 1 AS `Not_used` from `test`.`t1` `x1` where 0)) +Note 1003 select '1' AS `id`,'2007-04-25 18:30:22' AS `cur_date` from `test`.`t1` `x1` join `test`.`t1` where (('2007-04-25 18:30:22' = 0)) select * from t1 where id in (select id from t1 as x1 where (t1.cur_date is null)); id cur_date @@ -526,10 +525,9 @@ where id in (select id from t2 as x1 where (t2.cur_date is null)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE Warnings: Note 1276 Field or reference 'test.t2.cur_date' of SELECT #2 was resolved in SELECT #1 -Note 1003 select '1' AS `id`,'2007-04-25' AS `cur_date` from `test`.`t2` where <in_optimizer>('1',<exists>(select 1 AS `Not_used` from `test`.`t2` `x1` where 0)) +Note 1003 select '1' AS `id`,'2007-04-25' AS `cur_date` from `test`.`t2` `x1` join `test`.`t2` where (('2007-04-25' = 0)) select * from t2 where id in (select id from t2 as x1 where (t2.cur_date is null)); id cur_date @@ -540,10 +538,10 @@ where id in (select id from t1 as x1 where (t1.cur_date is null)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where -2 DEPENDENT SUBQUERY x1 ALL NULL NULL NULL NULL 2 100.00 Using where +1 PRIMARY x1 ALL NULL NULL NULL NULL 2 100.00 Using where; FirstMatch(t1) Warnings: Note 1276 Field or reference 'test.t1.cur_date' of SELECT #2 was resolved in SELECT #1 -Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`cur_date` AS `cur_date` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`id`,<exists>(select 1 AS `Not_used` from `test`.`t1` `x1` where ((`test`.`t1`.`cur_date` = 0) and (<cache>(`test`.`t1`.`id`) = `test`.`x1`.`id`)))) +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`cur_date` AS `cur_date` from `test`.`t1` semi join (`test`.`t1` `x1`) where ((`test`.`x1`.`id` = `test`.`t1`.`id`) and (`test`.`t1`.`cur_date` = 0)) select * from t1 where id in (select id from t1 as x1 where (t1.cur_date is null)); id cur_date @@ -552,10 +550,10 @@ where id in (select id from t2 as x1 where (t2.cur_date is null)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where -2 DEPENDENT SUBQUERY x1 ALL NULL NULL NULL NULL 2 100.00 Using where +1 PRIMARY x1 ALL NULL NULL NULL NULL 2 100.00 Using where; FirstMatch(t2) Warnings: Note 1276 Field or reference 'test.t2.cur_date' of SELECT #2 was resolved in SELECT #1 -Note 1003 select `test`.`t2`.`id` AS `id`,`test`.`t2`.`cur_date` AS `cur_date` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`id`,<exists>(select 1 AS `Not_used` from `test`.`t2` `x1` where ((`test`.`t2`.`cur_date` = 0) and (<cache>(`test`.`t2`.`id`) = `test`.`x1`.`id`)))) +Note 1003 select `test`.`t2`.`id` AS `id`,`test`.`t2`.`cur_date` AS `cur_date` from `test`.`t2` semi join (`test`.`t2` `x1`) where ((`test`.`x1`.`id` = `test`.`t2`.`id`) and (`test`.`t2`.`cur_date` = 0)) select * from t2 where id in (select id from t2 as x1 where (t2.cur_date is null)); id cur_date

1 0

[Maria-developers] Rev 2752: Apply Jorgen Loland's fix: Bug#45221: Query "SELECT pk FROM C WHERE pk IN (SELECT int_key)" failing in file:///home/psergey/dev/maria-5.3-subqueries-r6/
by Sergey Petrunya 11 Feb '10

11 Feb '10

At file:///home/psergey/dev/maria-5.3-subqueries-r6/ ------------------------------------------------------------ revno: 2752 revision-id: psergey(a)askmonty.org-20100211215823-63ikirl70ztmlk05 parent: psergey(a)askmonty.org-20100211215602-irdyu314ddwew1xd committer: Sergey Petrunya <psergey(a)askmonty.org> branch nick: maria-5.3-subqueries-r6 timestamp: Fri 2010-02-12 00:58:23 +0300 message: Apply Jorgen Loland's fix: Bug#45221: Query "SELECT pk FROM C WHERE pk IN (SELECT int_key)" failing XOR conditions are not optimized, and Item_cond_xor therefore acts like type Func_item even though it inherits from Item_cond. A subtle difference between Item_func and Item_cond is that you can get the children Items from the former by calling arguments(), and from the latter by calling argument_list(). However, since Item_cond_xor inherits from Item_cond, arguments() did not return any Items. The fact that Item_cond_xor::arguments() did not return it's children items lead to a problem for make_cond_for_index(); the method accepted that XOR items on unindexed columns were pushed using ICP. ICP evaluation of non-indexed columns does not (and should not) work. The fix for this bug is to make Item_cond_xor return it's children items when the arguments() method is used. This makes Item_cond_xor behave more like Item_func and in turn allows make_cond_for_index() to discover any conflicting children Items. This is a temporary fix and should be removed when Item_cond_xor is optimized. === modified file 'mysql-test/r/group_by.result' --- a/mysql-test/r/group_by.result 2009-02-26 17:17:06 +0000 +++ b/mysql-test/r/group_by.result 2010-02-11 21:58:23 +0000 @@ -1542,8 +1542,8 @@ EXPLAIN SELECT 1 FROM t1 WHERE a IN (SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2)); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index NULL PRIMARY 4 NULL 144 Using where; Using index -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 144 Using where +1 PRIMARY t1 index PRIMARY,i2 PRIMARY 4 NULL 144 Using index +1 PRIMARY t1 ALL NULL NULL NULL NULL 144 Using where; FirstMatch(t1) CREATE TABLE t2 (a INT, b INT, KEY(a)); INSERT INTO t2 VALUES (1, 1), (2, 2), (3,3), (4,4); EXPLAIN SELECT a, SUM(b) FROM t2 GROUP BY a LIMIT 2; @@ -1555,8 +1555,8 @@ EXPLAIN SELECT 1 FROM t2 WHERE a IN (SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2)); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 index NULL a 5 NULL 4 Using where; Using index -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 144 Using where +1 PRIMARY t2 index a a 5 NULL 4 Using index +1 PRIMARY t1 ALL NULL NULL NULL NULL 144 Using where; FirstMatch(t2) SHOW VARIABLES LIKE 'old'; Variable_name Value old OFF === modified file 'sql/item_cmpfunc.h' --- a/sql/item_cmpfunc.h 2010-01-17 14:55:08 +0000 +++ b/sql/item_cmpfunc.h 2010-02-11 21:58:23 +0000 @@ -1715,14 +1715,34 @@ class Item_cond_xor :public Item_cond { public: - Item_cond_xor() :Item_cond() {} - Item_cond_xor(Item *i1,Item *i2) :Item_cond(i1,i2) {} + Item_cond_xor(Item *i1,Item *i2) :Item_cond(i1,i2) + { + /* + Items must be stored in args[] as well because this Item_cond is + treated as a FUNC_ITEM (see type()). I.e., users of it will get + it's children by calling arguments(), not argument_list(). This + is a temporary solution until XOR is optimized and treated like + a full Item_cond citizen. + */ + arg_count= 2; + args= tmp_arg; + args[0]= i1; + args[1]= i2; + } enum Functype functype() const { return COND_XOR_FUNC; } /* TODO: remove the next line when implementing XOR optimization */ enum Type type() const { return FUNC_ITEM; } longlong val_int(); const char *func_name() const { return "xor"; } void top_level_item() {} + /* Since child Items are stored in args[], Items cannot be added. + However, since Item_cond_xor is treated as a FUNC_ITEM (see + type()), the methods below should never be called. + */ + bool add(Item *item) { DBUG_ASSERT(FALSE); return FALSE; } + bool add_at_head(Item *item) { DBUG_ASSERT(FALSE); return FALSE; } + bool add_at_head(List<Item> *nlist) { DBUG_ASSERT(FALSE); return FALSE; } + void copy_andor_arguments(THD *thd, Item_cond *item) { DBUG_ASSERT(FALSE); } };

1 0

[Maria-developers] Rev 2751: Subquery backport: Update test results (checked) in file:///home/psergey/dev/maria-5.3-subqueries-r6/
by Sergey Petrunya 11 Feb '10

11 Feb '10

At file:///home/psergey/dev/maria-5.3-subqueries-r6/ ------------------------------------------------------------ revno: 2751 revision-id: psergey(a)askmonty.org-20100211215602-irdyu314ddwew1xd parent: psergey(a)askmonty.org-20100211215456-u85owf67gwqkkss5 committer: Sergey Petrunya <psergey(a)askmonty.org> branch nick: maria-5.3-subqueries-r6 timestamp: Fri 2010-02-12 00:56:02 +0300 message: Subquery backport: Update test results (checked) === modified file 'mysql-test/r/explain.result' --- a/mysql-test/r/explain.result 2009-12-15 07:16:46 +0000 +++ b/mysql-test/r/explain.result 2010-02-11 21:56:02 +0000 @@ -171,7 +171,7 @@ EXPLAIN SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN (SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.dt IS NULL ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY OUTR ALL NULL NULL NULL NULL 2 Using where -2 DEPENDENT SUBQUERY INNR ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY INNR ALL NULL NULL NULL NULL 2 Using where; FirstMatch(OUTR) flush tables; SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN (SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.dt IS NULL ); dt @@ -179,7 +179,7 @@ EXPLAIN SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN ( SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.t < '2005-11-13 7:41:31' ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY OUTR ALL NULL NULL NULL NULL 2 Using where -2 DEPENDENT SUBQUERY INNR ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY INNR ALL NULL NULL NULL NULL 2 Using where; FirstMatch(OUTR) flush tables; SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN ( SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.t < '2005-11-13 7:41:31' ); dt === modified file 'mysql-test/r/group_min_max.result' --- a/mysql-test/r/group_min_max.result 2009-08-30 07:03:37 +0000 +++ b/mysql-test/r/group_min_max.result 2010-02-11 21:56:02 +0000 @@ -2256,7 +2256,7 @@ a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1_outer index NULL a 10 NULL 15 Using where; Using index -2 DEPENDENT SUBQUERY t1 index NULL a 10 NULL 1 Using index +2 SUBQUERY t1 range NULL a 5 NULL 8 Using index for group-by EXPLAIN SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING a > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); id select_type table type possible_keys key key_len ref rows Extra === modified file 'mysql-test/r/subselect3_jcl6.result' --- a/mysql-test/r/subselect3_jcl6.result 2010-01-17 14:51:10 +0000 +++ b/mysql-test/r/subselect3_jcl6.result 2010-02-11 21:56:02 +0000 @@ -1140,7 +1140,7 @@ flush status; select count(*) from t0 A, t0 B, t0 C, t0 D where D.a in (select a from t1 E); count(*) -4999 +5000 show status like 'Created_tmp_disk_tables'; Variable_name Value Created_tmp_disk_tables 1

1 0

[Maria-developers] Rev 2750: Subquery optimization backport: Duplicate Elimination: in file:///home/psergey/dev/maria-5.3-subqueries-r6/
by Sergey Petrunya 11 Feb '10

11 Feb '10

At file:///home/psergey/dev/maria-5.3-subqueries-r6/ ------------------------------------------------------------ revno: 2750 revision-id: psergey(a)askmonty.org-20100211215456-u85owf67gwqkkss5 parent: psergey(a)askmonty.org-20100128134833-9000udjp5wa3tsff committer: Sergey Petrunya <psergey(a)askmonty.org> branch nick: maria-5.3-subqueries-r6 timestamp: Fri 2010-02-12 00:54:56 +0300 message: Subquery optimization backport: Duplicate Elimination: process temporary table overflow correctly. === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2010-01-28 13:48:33 +0000 +++ b/sql/sql_select.cc 2010-02-11 21:54:56 +0000 @@ -16250,12 +16250,12 @@ if (error) { /* create_internal_tmp_table_from_heap will generate error if needed */ - if (sjtbl->tmp_table->file->is_fatal_error(error, HA_CHECK_DUP) && - create_internal_tmp_table_from_heap(thd, sjtbl->tmp_table, + if (!sjtbl->tmp_table->file->is_fatal_error(error, HA_CHECK_DUP)) + DBUG_RETURN(1); /* Duplicate */ + if (create_internal_tmp_table_from_heap(thd, sjtbl->tmp_table, sjtbl->start_recinfo, &sjtbl->recinfo, error, 1)) DBUG_RETURN(-1); - DBUG_RETURN(1); } DBUG_RETURN(0); }

1 0

[Maria-developers] [Branch ~maria-captains/maria/5.1] Rev 2816: Fix for LPBug#520243: useability bug of thread pool configuration
by noreply＠launchpad.net 11 Feb '10

11 Feb '10

------------------------------------------------------------ revno: 2816 committer: Michael Widenius <monty(a)askmonty.org> branch nick: maria-5.1 timestamp: Thu 2010-02-11 21:15:24 +0200 message: Fix for LPBug#520243: useability bug of thread pool configuration Now mysqld --help --verbose shows the value for thread-handling Fixed also that mysqld --one-thread works as expected. modified: sql/mysqld.cc -- lp:maria https://code.launchpad.net/~maria-captains/maria/5.1 Your team Maria developers is subscribed to branch lp:maria. To unsubscribe from this branch go to https://code.launchpad.net/~maria-captains/maria/5.1/+edit-subscription.

1 0

[Maria-developers] bzr commit into MariaDB 5.1, with Maria 1.5:maria branch (monty:2816)
by Michael Widenius 11 Feb '10

11 Feb '10

#At lp:maria based on revid:monty@askmonty.org-20100210212606-xj84sp2fhbrf5epc 2816 Michael Widenius 2010-02-11 Fix for LPBug#520243: useability bug of thread pool configuration Now mysqld --help --verbose shows the value for thread-handling Fixed also that mysqld --one-thread works as expected. modified: sql/mysqld.cc === modified file 'sql/mysqld.cc' --- a/sql/mysqld.cc 2010-02-10 19:06:24 +0000 +++ b/sql/mysqld.cc 2010-02-11 19:15:24 +0000 @@ -598,6 +598,7 @@ char *mysqld_unix_port, *opt_mysql_tmpdi const char **errmesg; /**< Error messages */ const char *myisam_recover_options_str="OFF"; const char *myisam_stats_method_str="nulls_unequal"; +const char *opt_thread_handling= thread_handling_typelib.type_names[0]; /** name of reference on left espression in rewritten IN subquery */ const char *in_left_expr_name= "<left expr>"; @@ -7290,7 +7291,8 @@ The minimum value for this variable is 4 1024, 0}, {"thread_handling", OPT_THREAD_HANDLING, "Define threads usage for handling queries: " - "one-thread-per-connection or no-threads", 0, 0, + "one-thread-per-connection or no-threads", + (uchar**) &opt_thread_handling, (uchar**) &opt_thread_handling, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0}, {"updatable_views_with_limit", OPT_UPDATABLE_VIEWS_WITH_LIMIT, "1 = YES = Don't issue an error message (warning only) if a VIEW without presence of a key of the underlying table is used in queries with a LIMIT clause for updating. 0 = NO = Prohibit update of a VIEW, which does not contain a key of the underlying table and the query uses a LIMIT clause (usually get from GUI tools).", @@ -8721,14 +8723,15 @@ mysqld_get_one_option(int optid, break; } case OPT_ONE_THREAD: - global_system_variables.thread_handling= - SCHEDULER_ONE_THREAD_PER_CONNECTION; + global_system_variables.thread_handling= SCHEDULER_NO_THREADS; + opt_thread_handling= thread_handling_typelib.type_names[global_system_variables.thread_handling]; break; case OPT_THREAD_HANDLING: { int id; if (!find_opt_type(argument, &thread_handling_typelib, opt->name, &id)) global_system_variables.thread_handling= id - 1; + opt_thread_handling= thread_handling_typelib.type_names[global_system_variables.thread_handling]; break; } case OPT_FT_BOOLEAN_SYNTAX:

1 0

[Maria-developers] Updated (by Monty): Add support for dynamic columns (via google protocol buffers) (34)
by worklog-noreply＠askmonty.org 11 Feb '10

11 Feb '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Add support for dynamic columns (via google protocol buffers) CREATION DATE..: Tue, 21 Jul 2009, 21:11 SUPERVISOR.....: Monty IMPLEMENTOR....: Knielsen COPIES TO......: CATEGORY.......: Server-Sprint TASK ID........: 34 (http://askmonty.org/worklog/?tid=34) VERSION........: Server-5.3 STATUS.........: Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Monty - Thu, 11 Feb 2010, 20:04)=-=- Version updated. --- /tmp/wklog.34.old.18409 2010-02-11 18:04:35.000000000 +0000 +++ /tmp/wklog.34.new.18409 2010-02-11 18:04:35.000000000 +0000 @@ -1 +1 @@ -WorkLog-3.4 +Server-5.3 -=-=(Monty - Thu, 11 Feb 2010, 20:04)=-=- Status updated. --- /tmp/wklog.34.old.18409 2010-02-11 18:04:35.000000000 +0000 +++ /tmp/wklog.34.new.18409 2010-02-11 18:04:35.000000000 +0000 @@ -1 +1 @@ -Un-Assigned +Assigned -=-=(Monty - Thu, 11 Feb 2010, 20:04)=-=- Title modified. --- /tmp/wklog.34.old.18409 2010-02-11 18:04:35.000000000 +0000 +++ /tmp/wklog.34.new.18409 2010-02-11 18:04:35.000000000 +0000 @@ -1 +1 @@ -Add support for google protocol buffers +Add support for dynamic columns (via google protocol buffers) -=-=(Monty - Thu, 11 Feb 2010, 20:03)=-=- High Level Description modified. --- /tmp/wklog.34.old.18329 2010-02-11 18:03:42.000000000 +0000 +++ /tmp/wklog.34.new.18329 2010-02-11 18:03:42.000000000 +0000 @@ -19,3 +19,14 @@ Any support for indexing GPB data is outside of scope of this WL entry. +Example usage: + +SELECT proto_get(blob, 1, varchar) from table_with_proto; + +UPDATE table_with_proto SET blob=proto_add(blob, 2, "hello") where id=1; + +UPDATE table_with_proto SET blob=proto_del(blob,4) where id=5; + +Note that 'proto_add()' will replace any old value with the given proto_id. + + -=-=(Monty - Thu, 11 Feb 2010, 19:59)=-=- High-Level Specification modified. --- /tmp/wklog.34.old.17962 2010-02-11 19:59:45.000000000 +0200 +++ /tmp/wklog.34.new.17962 2010-02-11 19:59:45.000000000 +0200 @@ -1,13 +1,8 @@ - -<contents> 1. GPB Encoding overview 2. GPB in an SQL database -2.1 Informing server about GPB field names and types -2.2 Addressing GPB fields -2.2.1 Option1: SQL Function -2.2.2 Option2: SQL columns -</contents> - +3. Encoding to use for dynamic columns +4. How to store and access data in a protocol buffer from SQL +5. Extensions for the future 1. GPB Encoding overview ======================== @@ -37,42 +32,50 @@ traffic right away, and will open path to getting the best possible performance. -2.1 Informing server about GPB field names and types ----------------------------------------------------- -User-friendly/meaningful access to GPB fields requires knowledge of GPB field -names and types, which are not available from GPB message itself (see "GPB -encoding overview" section). - -So the first issue to be addressed is to get the server to know the definition -of stored messages. We intend to assume that all records have GPB messages -that conform to a certain single definition, which gives one definition per -GPB field. +3. Encoding to use for dynamic columns +====================================== -DecisionToMake: How to pass the server the GPB definition? -First idea: add a CREATE TABLE parameter which will specify either the -definition itself or path to .proto file with the definition. +The data should be coded into the proto buffer in the following format: + +<field_number><value_type><value>[<field_number><value_type><value>...] + +Where field_number is a number between 0-65536 that identifes the field +<value_type> is a enum of type 'Item_result' +<value> is the value coded in proto format. + +In other words, we should have no nested or complex structure. + +4. How to store and access data in a protocol buffer from SQL +============================================================ + +User-friendly/meaningful access to GPB fields requires knowledge of +GPB field names and types, which are not available from GPB message +itself (see "GPB encoding overview" section). + +To make things easy for the user, we will at first stage provide SQL +functions to manipulate a string that is actually in proto format. -2.2 Addressing GPB fields -------------------------- -We'll need to provide a way to access GPB fields. This can be complicated as -structures that are encoded in GPB message can be nested and recursive. - -2.2.1 Option1: SQL Function -~~~~~~~~~~~~~~~~~~~~~~~~~~~ -Introduce an SQL function GPB_FIELD(path) which will return contents of the -field. -- Return type of the function will be determined from GPB message definition. -- For path, we can use XPath selector (a subset of XPath) syntax. - -(TODO ^ the above needs to be specified in more detail. is the selector as -simple as filesystem path or we allow quantifiers (with predicates?)?) - -2.2.2 Option2: SQL columns -~~~~~~~~~~~~~~~~~~~~~~~~~~ -Make GPB columns to be accessible as SQL columns. -This approach has problems: -- It might be hard to implement code-wise - - (TODO will Virtual columns patch help??) -- It is not clear how to access fields from nested structures. Should we allow - quoted names like `foo/bar[2]/baz' ? +The functions we should provde are: +proto_get(gpb, field_number, type) + +This return the field tagged with 'field_number' from the 'gpb' buffer. + +Example: proto_get(blob, 1, varchar) -> Returns field number 1 as varchar + +proto_put(gpb, field_number, value) + +This returns a new gbp buffer with the new value appended. + +Example: proto_put(proto_put(blob, 1, 1), 2, "hello") + +5. Extension for future +======================= + +In the future we may want to access data based on name and get MariaDB to +automaticly know the correct type. To do this we need to be able to +store a definition for the content of the proto buffer somewhere. + +DecisionToMake: How to pass the server the GPB definition? +First idea: add a CREATE TABLE parameter which will specify the +definition itself. -=-=(Monty - Thu, 11 Feb 2010, 19:59)=-=- High Level Description modified. --- /tmp/wklog.34.old.17915 2010-02-11 17:59:17.000000000 +0000 +++ /tmp/wklog.34.new.17915 2010-02-11 17:59:17.000000000 +0000 @@ -1,5 +1,21 @@ -Add support for Google Protocol Buffers (further GPB). It should be possible -to have columns that store GPB-encoded data, as well as use SQL constructs to +Add support for dynamic columns: + +- A column that can hold information from many columns +- One can instantly add or remove column data + +This is a useful feature for any store type of application, where you want to +store different type of information for different kind of items. + +For example, for shoes you want to store: material, size, colour, maker +For a computer you want to store ram, hard disk size etc... + +In a normal 'relational' system you would need to a table for each type. +With dynamic columns you have all common items as fixed fields (like +product_code, manufacturer, price) and the rest stored in a dynamic column. + +The proposed idea is to store the dynamic information in a blob in +Google Protocol Buffers (further GPB) format and use SQL constructs to extract parts of GPB data for use in select list, for filtering, and so forth. + Any support for indexing GPB data is outside of scope of this WL entry. -=-=(Knielsen - Fri, 22 Jan 2010, 11:38)=-=- Low Level Design modified. --- /tmp/wklog.34.old.29965 2010-01-22 11:38:57.000000000 +0200 +++ /tmp/wklog.34.new.29965 2010-01-22 11:38:57.000000000 +0200 @@ -2,3 +2,12 @@ and a parser for text form of .proto file which then exposes the parsed file via standard GPB message navigation API. +* We should have both server-side support and client-side support (client side + means functions in libmysqlclient so that user can select the full BLOB and + extract fields in the application). + +* Add some kind of header to the GPB blob to support versioning and future + extensibility. + +* Add complete syntax description (update, add, drop, exists, ...). + -=-=(Psergey - Tue, 21 Jul 2009, 21:13)=-=- Low Level Design modified. --- /tmp/wklog.34.old.6462 2009-07-21 21:13:13.000000000 +0300 +++ /tmp/wklog.34.new.6462 2009-07-21 21:13:13.000000000 +0300 @@ -1 +1,4 @@ +* GPB tarball contains a protocol definition for .proto file structure itself + and a parser for text form of .proto file which then exposes the parsed + file via standard GPB message navigation API. -=-=(Psergey - Tue, 21 Jul 2009, 21:12)=-=- High-Level Specification modified. --- /tmp/wklog.34.old.6399 2009-07-21 21:12:23.000000000 +0300 +++ /tmp/wklog.34.new.6399 2009-07-21 21:12:23.000000000 +0300 @@ -1 +1,78 @@ +<contents> +1. GPB Encoding overview +2. GPB in an SQL database +2.1 Informing server about GPB field names and types +2.2 Addressing GPB fields +2.2.1 Option1: SQL Function +2.2.2 Option2: SQL columns +</contents> + + +1. GPB Encoding overview +======================== + +GBB is a compact encoding for structured and typed data. A unit of GPB data +(it is called message) is only partially self-describing: it's possible to +iterate over its parts, but, quoting the spec + +http://code.google.com/apis/protocolbuffers/docs/encoding.html: + " the name and declared type for each field can only be determined on the + decoding end by referencing the message type's definition (i.e. the .proto + file). " + +2. GPB in an SQL database +========================= + +It is possible to store GPB data in MariaDB today - one can declare a binary +blob column and use it to store GPB messages. Storing and retrieving entire +messages will be the only available operations, though, as the server has no +idea about the GPB format. +It is apparent that ability to peek inside GPB data from SQL layer would be of +great advantage: one would be able to +- select only certain fields or parts of GPB messages +- filter records based on the values of GPB fields +- etc +performing such operations at SQL layer will allow to reduce client<->server +traffic right away, and will open path to getting the best possible +performance. + +2.1 Informing server about GPB field names and types +---------------------------------------------------- +User-friendly/meaningful access to GPB fields requires knowledge of GPB field +names and types, which are not available from GPB message itself (see "GPB +encoding overview" section). + +So the first issue to be addressed is to get the server to know the definition +of stored messages. We intend to assume that all records have GPB messages +that conform to a certain single definition, which gives one definition per +GPB field. + +DecisionToMake: How to pass the server the GPB definition? +First idea: add a CREATE TABLE parameter which will specify either the +definition itself or path to .proto file with the definition. + +2.2 Addressing GPB fields +------------------------- +We'll need to provide a way to access GPB fields. This can be complicated as +structures that are encoded in GPB message can be nested and recursive. + +2.2.1 Option1: SQL Function +~~~~~~~~~~~~~~~~~~~~~~~~~~~ +Introduce an SQL function GPB_FIELD(path) which will return contents of the +field. +- Return type of the function will be determined from GPB message definition. +- For path, we can use XPath selector (a subset of XPath) syntax. + +(TODO ^ the above needs to be specified in more detail. is the selector as +simple as filesystem path or we allow quantifiers (with predicates?)?) + +2.2.2 Option2: SQL columns +~~~~~~~~~~~~~~~~~~~~~~~~~~ +Make GPB columns to be accessible as SQL columns. +This approach has problems: +- It might be hard to implement code-wise + - (TODO will Virtual columns patch help??) +- It is not clear how to access fields from nested structures. Should we allow + quoted names like `foo/bar[2]/baz' ? + DESCRIPTION: Add support for dynamic columns: - A column that can hold information from many columns - One can instantly add or remove column data This is a useful feature for any store type of application, where you want to store different type of information for different kind of items. For example, for shoes you want to store: material, size, colour, maker For a computer you want to store ram, hard disk size etc... In a normal 'relational' system you would need to a table for each type. With dynamic columns you have all common items as fixed fields (like product_code, manufacturer, price) and the rest stored in a dynamic column. The proposed idea is to store the dynamic information in a blob in Google Protocol Buffers (further GPB) format and use SQL constructs to extract parts of GPB data for use in select list, for filtering, and so forth. Any support for indexing GPB data is outside of scope of this WL entry. Example usage: SELECT proto_get(blob, 1, varchar) from table_with_proto; UPDATE table_with_proto SET blob=proto_add(blob, 2, "hello") where id=1; UPDATE table_with_proto SET blob=proto_del(blob,4) where id=5; Note that 'proto_add()' will replace any old value with the given proto_id. HIGH-LEVEL SPECIFICATION: 1. GPB Encoding overview 2. GPB in an SQL database 3. Encoding to use for dynamic columns 4. How to store and access data in a protocol buffer from SQL 5. Extensions for the future 1. GPB Encoding overview ======================== GBB is a compact encoding for structured and typed data. A unit of GPB data (it is called message) is only partially self-describing: it's possible to iterate over its parts, but, quoting the spec http://code.google.com/apis/protocolbuffers/docs/encoding.html: " the name and declared type for each field can only be determined on the decoding end by referencing the message type's definition (i.e. the .proto file). " 2. GPB in an SQL database ========================= It is possible to store GPB data in MariaDB today - one can declare a binary blob column and use it to store GPB messages. Storing and retrieving entire messages will be the only available operations, though, as the server has no idea about the GPB format. It is apparent that ability to peek inside GPB data from SQL layer would be of great advantage: one would be able to - select only certain fields or parts of GPB messages - filter records based on the values of GPB fields - etc performing such operations at SQL layer will allow to reduce client<->server traffic right away, and will open path to getting the best possible performance. 3. Encoding to use for dynamic columns ====================================== The data should be coded into the proto buffer in the following format: <field_number><value_type><value>[<field_number><value_type><value>...] Where field_number is a number between 0-65536 that identifes the field <value_type> is a enum of type 'Item_result' <value> is the value coded in proto format. In other words, we should have no nested or complex structure. 4. How to store and access data in a protocol buffer from SQL ============================================================ User-friendly/meaningful access to GPB fields requires knowledge of GPB field names and types, which are not available from GPB message itself (see "GPB encoding overview" section). To make things easy for the user, we will at first stage provide SQL functions to manipulate a string that is actually in proto format. The functions we should provde are: proto_get(gpb, field_number, type) This return the field tagged with 'field_number' from the 'gpb' buffer. Example: proto_get(blob, 1, varchar) -> Returns field number 1 as varchar proto_put(gpb, field_number, value) This returns a new gbp buffer with the new value appended. Example: proto_put(proto_put(blob, 1, 1), 2, "hello") 5. Extension for future ======================= In the future we may want to access data based on name and get MariaDB to automaticly know the correct type. To do this we need to be able to store a definition for the content of the proto buffer somewhere. DecisionToMake: How to pass the server the GPB definition? First idea: add a CREATE TABLE parameter which will specify the definition itself. LOW-LEVEL DESIGN: * GPB tarball contains a protocol definition for .proto file structure itself and a parser for text form of .proto file which then exposes the parsed file via standard GPB message navigation API. * We should have both server-side support and client-side support (client side means functions in libmysqlclient so that user can select the full BLOB and extract fields in the application). * Add some kind of header to the GPB blob to support versioning and future extensibility. * Add complete syntax description (update, add, drop, exists, ...). ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Monty): Add support for dynamic columns (via google protocol buffers) (34)
by worklog-noreply＠askmonty.org 11 Feb '10

11 Feb '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Add support for dynamic columns (via google protocol buffers) CREATION DATE..: Tue, 21 Jul 2009, 21:11 SUPERVISOR.....: Monty IMPLEMENTOR....: Knielsen COPIES TO......: CATEGORY.......: Server-Sprint TASK ID........: 34 (http://askmonty.org/worklog/?tid=34) VERSION........: Server-5.3 STATUS.........: Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Monty - Thu, 11 Feb 2010, 20:04)=-=- Version updated. --- /tmp/wklog.34.old.18409 2010-02-11 18:04:35.000000000 +0000 +++ /tmp/wklog.34.new.18409 2010-02-11 18:04:35.000000000 +0000 @@ -1 +1 @@ -WorkLog-3.4 +Server-5.3 -=-=(Monty - Thu, 11 Feb 2010, 20:04)=-=- Status updated. --- /tmp/wklog.34.old.18409 2010-02-11 18:04:35.000000000 +0000 +++ /tmp/wklog.34.new.18409 2010-02-11 18:04:35.000000000 +0000 @@ -1 +1 @@ -Un-Assigned +Assigned -=-=(Monty - Thu, 11 Feb 2010, 20:04)=-=- Title modified. --- /tmp/wklog.34.old.18409 2010-02-11 18:04:35.000000000 +0000 +++ /tmp/wklog.34.new.18409 2010-02-11 18:04:35.000000000 +0000 @@ -1 +1 @@ -Add support for google protocol buffers +Add support for dynamic columns (via google protocol buffers) -=-=(Monty - Thu, 11 Feb 2010, 20:03)=-=- High Level Description modified. --- /tmp/wklog.34.old.18329 2010-02-11 18:03:42.000000000 +0000 +++ /tmp/wklog.34.new.18329 2010-02-11 18:03:42.000000000 +0000 @@ -19,3 +19,14 @@ Any support for indexing GPB data is outside of scope of this WL entry. +Example usage: + +SELECT proto_get(blob, 1, varchar) from table_with_proto; + +UPDATE table_with_proto SET blob=proto_add(blob, 2, "hello") where id=1; + +UPDATE table_with_proto SET blob=proto_del(blob,4) where id=5; + +Note that 'proto_add()' will replace any old value with the given proto_id. + + -=-=(Monty - Thu, 11 Feb 2010, 19:59)=-=- High-Level Specification modified. --- /tmp/wklog.34.old.17962 2010-02-11 19:59:45.000000000 +0200 +++ /tmp/wklog.34.new.17962 2010-02-11 19:59:45.000000000 +0200 @@ -1,13 +1,8 @@ - -<contents> 1. GPB Encoding overview 2. GPB in an SQL database -2.1 Informing server about GPB field names and types -2.2 Addressing GPB fields -2.2.1 Option1: SQL Function -2.2.2 Option2: SQL columns -</contents> - +3. Encoding to use for dynamic columns +4. How to store and access data in a protocol buffer from SQL +5. Extensions for the future 1. GPB Encoding overview ======================== @@ -37,42 +32,50 @@ traffic right away, and will open path to getting the best possible performance. -2.1 Informing server about GPB field names and types ----------------------------------------------------- -User-friendly/meaningful access to GPB fields requires knowledge of GPB field -names and types, which are not available from GPB message itself (see "GPB -encoding overview" section). - -So the first issue to be addressed is to get the server to know the definition -of stored messages. We intend to assume that all records have GPB messages -that conform to a certain single definition, which gives one definition per -GPB field. +3. Encoding to use for dynamic columns +====================================== -DecisionToMake: How to pass the server the GPB definition? -First idea: add a CREATE TABLE parameter which will specify either the -definition itself or path to .proto file with the definition. +The data should be coded into the proto buffer in the following format: + +<field_number><value_type><value>[<field_number><value_type><value>...] + +Where field_number is a number between 0-65536 that identifes the field +<value_type> is a enum of type 'Item_result' +<value> is the value coded in proto format. + +In other words, we should have no nested or complex structure. + +4. How to store and access data in a protocol buffer from SQL +============================================================ + +User-friendly/meaningful access to GPB fields requires knowledge of +GPB field names and types, which are not available from GPB message +itself (see "GPB encoding overview" section). + +To make things easy for the user, we will at first stage provide SQL +functions to manipulate a string that is actually in proto format. -2.2 Addressing GPB fields -------------------------- -We'll need to provide a way to access GPB fields. This can be complicated as -structures that are encoded in GPB message can be nested and recursive. - -2.2.1 Option1: SQL Function -~~~~~~~~~~~~~~~~~~~~~~~~~~~ -Introduce an SQL function GPB_FIELD(path) which will return contents of the -field. -- Return type of the function will be determined from GPB message definition. -- For path, we can use XPath selector (a subset of XPath) syntax. - -(TODO ^ the above needs to be specified in more detail. is the selector as -simple as filesystem path or we allow quantifiers (with predicates?)?) - -2.2.2 Option2: SQL columns -~~~~~~~~~~~~~~~~~~~~~~~~~~ -Make GPB columns to be accessible as SQL columns. -This approach has problems: -- It might be hard to implement code-wise - - (TODO will Virtual columns patch help??) -- It is not clear how to access fields from nested structures. Should we allow - quoted names like `foo/bar[2]/baz' ? +The functions we should provde are: +proto_get(gpb, field_number, type) + +This return the field tagged with 'field_number' from the 'gpb' buffer. + +Example: proto_get(blob, 1, varchar) -> Returns field number 1 as varchar + +proto_put(gpb, field_number, value) + +This returns a new gbp buffer with the new value appended. + +Example: proto_put(proto_put(blob, 1, 1), 2, "hello") + +5. Extension for future +======================= + +In the future we may want to access data based on name and get MariaDB to +automaticly know the correct type. To do this we need to be able to +store a definition for the content of the proto buffer somewhere. + +DecisionToMake: How to pass the server the GPB definition? +First idea: add a CREATE TABLE parameter which will specify the +definition itself. -=-=(Monty - Thu, 11 Feb 2010, 19:59)=-=- High Level Description modified. --- /tmp/wklog.34.old.17915 2010-02-11 17:59:17.000000000 +0000 +++ /tmp/wklog.34.new.17915 2010-02-11 17:59:17.000000000 +0000 @@ -1,5 +1,21 @@ -Add support for Google Protocol Buffers (further GPB). It should be possible -to have columns that store GPB-encoded data, as well as use SQL constructs to +Add support for dynamic columns: + +- A column that can hold information from many columns +- One can instantly add or remove column data + +This is a useful feature for any store type of application, where you want to +store different type of information for different kind of items. + +For example, for shoes you want to store: material, size, colour, maker +For a computer you want to store ram, hard disk size etc... + +In a normal 'relational' system you would need to a table for each type. +With dynamic columns you have all common items as fixed fields (like +product_code, manufacturer, price) and the rest stored in a dynamic column. + +The proposed idea is to store the dynamic information in a blob in +Google Protocol Buffers (further GPB) format and use SQL constructs to extract parts of GPB data for use in select list, for filtering, and so forth. + Any support for indexing GPB data is outside of scope of this WL entry. -=-=(Knielsen - Fri, 22 Jan 2010, 11:38)=-=- Low Level Design modified. --- /tmp/wklog.34.old.29965 2010-01-22 11:38:57.000000000 +0200 +++ /tmp/wklog.34.new.29965 2010-01-22 11:38:57.000000000 +0200 @@ -2,3 +2,12 @@ and a parser for text form of .proto file which then exposes the parsed file via standard GPB message navigation API. +* We should have both server-side support and client-side support (client side + means functions in libmysqlclient so that user can select the full BLOB and + extract fields in the application). + +* Add some kind of header to the GPB blob to support versioning and future + extensibility. + +* Add complete syntax description (update, add, drop, exists, ...). + -=-=(Psergey - Tue, 21 Jul 2009, 21:13)=-=- Low Level Design modified. --- /tmp/wklog.34.old.6462 2009-07-21 21:13:13.000000000 +0300 +++ /tmp/wklog.34.new.6462 2009-07-21 21:13:13.000000000 +0300 @@ -1 +1,4 @@ +* GPB tarball contains a protocol definition for .proto file structure itself + and a parser for text form of .proto file which then exposes the parsed + file via standard GPB message navigation API. -=-=(Psergey - Tue, 21 Jul 2009, 21:12)=-=- High-Level Specification modified. --- /tmp/wklog.34.old.6399 2009-07-21 21:12:23.000000000 +0300 +++ /tmp/wklog.34.new.6399 2009-07-21 21:12:23.000000000 +0300 @@ -1 +1,78 @@ +<contents> +1. GPB Encoding overview +2. GPB in an SQL database +2.1 Informing server about GPB field names and types +2.2 Addressing GPB fields +2.2.1 Option1: SQL Function +2.2.2 Option2: SQL columns +</contents> + + +1. GPB Encoding overview +======================== + +GBB is a compact encoding for structured and typed data. A unit of GPB data +(it is called message) is only partially self-describing: it's possible to +iterate over its parts, but, quoting the spec + +http://code.google.com/apis/protocolbuffers/docs/encoding.html: + " the name and declared type for each field can only be determined on the + decoding end by referencing the message type's definition (i.e. the .proto + file). " + +2. GPB in an SQL database +========================= + +It is possible to store GPB data in MariaDB today - one can declare a binary +blob column and use it to store GPB messages. Storing and retrieving entire +messages will be the only available operations, though, as the server has no +idea about the GPB format. +It is apparent that ability to peek inside GPB data from SQL layer would be of +great advantage: one would be able to +- select only certain fields or parts of GPB messages +- filter records based on the values of GPB fields +- etc +performing such operations at SQL layer will allow to reduce client<->server +traffic right away, and will open path to getting the best possible +performance. + +2.1 Informing server about GPB field names and types +---------------------------------------------------- +User-friendly/meaningful access to GPB fields requires knowledge of GPB field +names and types, which are not available from GPB message itself (see "GPB +encoding overview" section). + +So the first issue to be addressed is to get the server to know the definition +of stored messages. We intend to assume that all records have GPB messages +that conform to a certain single definition, which gives one definition per +GPB field. + +DecisionToMake: How to pass the server the GPB definition? +First idea: add a CREATE TABLE parameter which will specify either the +definition itself or path to .proto file with the definition. + +2.2 Addressing GPB fields +------------------------- +We'll need to provide a way to access GPB fields. This can be complicated as +structures that are encoded in GPB message can be nested and recursive. + +2.2.1 Option1: SQL Function +~~~~~~~~~~~~~~~~~~~~~~~~~~~ +Introduce an SQL function GPB_FIELD(path) which will return contents of the +field. +- Return type of the function will be determined from GPB message definition. +- For path, we can use XPath selector (a subset of XPath) syntax. + +(TODO ^ the above needs to be specified in more detail. is the selector as +simple as filesystem path or we allow quantifiers (with predicates?)?) + +2.2.2 Option2: SQL columns +~~~~~~~~~~~~~~~~~~~~~~~~~~ +Make GPB columns to be accessible as SQL columns. +This approach has problems: +- It might be hard to implement code-wise + - (TODO will Virtual columns patch help??) +- It is not clear how to access fields from nested structures. Should we allow + quoted names like `foo/bar[2]/baz' ? + DESCRIPTION: Add support for dynamic columns: - A column that can hold information from many columns - One can instantly add or remove column data This is a useful feature for any store type of application, where you want to store different type of information for different kind of items. For example, for shoes you want to store: material, size, colour, maker For a computer you want to store ram, hard disk size etc... In a normal 'relational' system you would need to a table for each type. With dynamic columns you have all common items as fixed fields (like product_code, manufacturer, price) and the rest stored in a dynamic column. The proposed idea is to store the dynamic information in a blob in Google Protocol Buffers (further GPB) format and use SQL constructs to extract parts of GPB data for use in select list, for filtering, and so forth. Any support for indexing GPB data is outside of scope of this WL entry. Example usage: SELECT proto_get(blob, 1, varchar) from table_with_proto; UPDATE table_with_proto SET blob=proto_add(blob, 2, "hello") where id=1; UPDATE table_with_proto SET blob=proto_del(blob,4) where id=5; Note that 'proto_add()' will replace any old value with the given proto_id. HIGH-LEVEL SPECIFICATION: 1. GPB Encoding overview 2. GPB in an SQL database 3. Encoding to use for dynamic columns 4. How to store and access data in a protocol buffer from SQL 5. Extensions for the future 1. GPB Encoding overview ======================== GBB is a compact encoding for structured and typed data. A unit of GPB data (it is called message) is only partially self-describing: it's possible to iterate over its parts, but, quoting the spec http://code.google.com/apis/protocolbuffers/docs/encoding.html: " the name and declared type for each field can only be determined on the decoding end by referencing the message type's definition (i.e. the .proto file). " 2. GPB in an SQL database ========================= It is possible to store GPB data in MariaDB today - one can declare a binary blob column and use it to store GPB messages. Storing and retrieving entire messages will be the only available operations, though, as the server has no idea about the GPB format. It is apparent that ability to peek inside GPB data from SQL layer would be of great advantage: one would be able to - select only certain fields or parts of GPB messages - filter records based on the values of GPB fields - etc performing such operations at SQL layer will allow to reduce client<->server traffic right away, and will open path to getting the best possible performance. 3. Encoding to use for dynamic columns ====================================== The data should be coded into the proto buffer in the following format: <field_number><value_type><value>[<field_number><value_type><value>...] Where field_number is a number between 0-65536 that identifes the field <value_type> is a enum of type 'Item_result' <value> is the value coded in proto format. In other words, we should have no nested or complex structure. 4. How to store and access data in a protocol buffer from SQL ============================================================ User-friendly/meaningful access to GPB fields requires knowledge of GPB field names and types, which are not available from GPB message itself (see "GPB encoding overview" section). To make things easy for the user, we will at first stage provide SQL functions to manipulate a string that is actually in proto format. The functions we should provde are: proto_get(gpb, field_number, type) This return the field tagged with 'field_number' from the 'gpb' buffer. Example: proto_get(blob, 1, varchar) -> Returns field number 1 as varchar proto_put(gpb, field_number, value) This returns a new gbp buffer with the new value appended. Example: proto_put(proto_put(blob, 1, 1), 2, "hello") 5. Extension for future ======================= In the future we may want to access data based on name and get MariaDB to automaticly know the correct type. To do this we need to be able to store a definition for the content of the proto buffer somewhere. DecisionToMake: How to pass the server the GPB definition? First idea: add a CREATE TABLE parameter which will specify the definition itself. LOW-LEVEL DESIGN: * GPB tarball contains a protocol definition for .proto file structure itself and a parser for text form of .proto file which then exposes the parsed file via standard GPB message navigation API. * We should have both server-side support and client-side support (client side means functions in libmysqlclient so that user can select the full BLOB and extract fields in the application). * Add some kind of header to the GPB blob to support versioning and future extensibility. * Add complete syntax description (update, add, drop, exists, ...). ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0