February 2016 - developers - lists.mariadb.org

[Maria-developers] MDEV-7231 Field ROUTINE_DEFINITION in INFORMATION_SCHEMA.`ROUTINES` contains broken procedure body when used shielding quotes inside.
by Alexander Barkov 22 Feb '16

22 Feb '16

Hi Sergei, Please review a patch for MDEV-7231. Thanks.

2 4

[Maria-developers] Getting started
by Varun Gupta 19 Feb '16

19 Feb '16

Hey, I am Varun and this year I wish to take part in Google Summer of Code 2016 . I have been looking around the project ideas on the ideas page of MariaDB and am interested in the idea 'AGGREGATE STORE FUNCTIONS' . It would be helpful if someone could guide me where should i start looking to get more details related to this project idea. Regards

2 1

[Maria-developers] A proposal to deprecate syntax: SELECT a'test'
by Alexander Barkov 19 Feb '16

19 Feb '16

Hello Monty, all, We discussed this with Sergei and both think that it will be a good idea to deprecate this syntax: SELECT a'test'; where 'a' is an identifier and 'test' its alias. This is a non-standard way, and it conflicts with some other important standard SQL grammar. See below. Typified literals and a syntax conflict --------------------------------------- An identifier followed by a single-quoted text string is needed for typified literals: SELECT INET6'ffff::ffff'; We … [View More]

1 0

[Maria-developers] Google Summer of Code 2016
by Colin Charles 18 Feb '16

18 Feb '16

Hi! Its that time of year again and I believe that we plan to participate in Google Summer of Code 2016 again (we did so in 2013, 2014, and 2015). We are of course waiting to get accepted (we will know on Feb 29 2016) but that should not stop you, a *potential mentor* from adding your idea to the idea’s list page: https://mariadb.com/kb/en/mariadb/google-summer-of-code-2016/ Potential mentors should also see if there are Jira tasks they’d like completed: https://mariadb.atlassian.net/… [View More]

1 0

Re: [Maria-developers] [Commits] fd8e846: MDEV-9564 - added s390x to lib64 INSTALL_LIBDIR handling
by Sergei Golubchik 18 Feb '16

18 Feb '16

Hi, Sergey! Ok to push. I didn't like it at first, as x86_64 is amd64 on FreeBSD, x64 on Windows, and I don't know what else can there be. For example, FreeBSD doesn't use /usr/lib64. But your change is for RPM packages only - so it's fine. On Feb 18, Sergey Vojtovich wrote: > revision-id: fd8e846a3b049903706267d58e6d8e61eea97df8 (mariadb-10.1.11-17-gfd8e846) > parent(s): db5b51fb7e3360d4ebc303bd67363bc8324e86b6 > committer: Sergey Vojtovich > timestamp: 2016-02-18 11:01:22 +… [View More]

1 0

[Maria-developers] Window functions code, MDEV-9543 and MDEV-8646 are now merged
by Sergey Petrunia 15 Feb '16

15 Feb '16

Hello, Following the Thursday discussion, I've managed to re-base 10.2-window_simple branch onto the bb-10.2-mdev9543. The result is now pushed into bb-10.2-mdev9543. https://github.com/MariaDB/server/commits/bb-10.2-mdev9543 Since rebase happened, 10.2-window_simple tree should be abandoned. Let's try to avoid re-bases and "take everything and re-commit" steps from now on. BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] bb-10.2-mdev9543 is missing item_windowfunc.*
by Sergey Petrunia 13 Feb '16

13 Feb '16

Hello Igor, The tree at bb-10.2-mdev9543 is missing item_windowfunc.* files. Could you please add them there and push? BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] MDEV-9541 Selecting a time using a datetime fails
by Alexander Barkov 12 Feb '16

12 Feb '16

Hi Monty, Please have a look into the latest comments in MDEV-9541 on how PostgreSQL, SQL Server, Oracle 11i and MySQL-5.7 work when comparing temporal data types. There is no a general rule. Every vendor implements it in a different way. 1. PostgreSQL almost follows the standard, which disallows direct comparison of TIME and TIMESTAMP/DATETIME. The only exception, it allows to compare a TIME field to a string literal which looks like TIMESTAMP: SELECT * FROM t1 WHERE a='2001-01-01 10:20:… [View More]30'; a | b ----------+--------------------- 10:20:30 | 2001-01-01 10:20:30 But strangely not the other way around! Comparing of a TIMESTAMP field to a string which looks like TIME does not work. I did not find an explanation for this. 2. SQL Server - Comparison of TIME + VARCHAR is done as TIME - Comparison of DATETIME + VARCHAR is done as DATETIME - Comparison of TIME and DATETIME is not allowed - Comparison of TIME and INT is not allowed - Comparison of DATETIME and INT is allowed (not sure how exactly it works though) - Temporal literals {t'10:20:30'} have data type VARCHAR, this is just a syntactic variation of a string literal '10:20:30', with no any additional difference. 3. Oracle 11i Oracle does not have the TIME data type. So mixture of TIME and TIMESTAMP is not possible. But comparison of TIMESTAMP and VARCHAR is always done as TIMESTAMP, no matter what kind expressions are involved (fields, functions, literals). 4. MySQL-5.7 It has a special code: for a TIME field compared to a non-DATE constant, the constant argument is converted to TIME. But as soon as you wrap the field say into COALESCE, or put a non-constant instead of a constant, this works in a different way. So, comparing of a TIME field to a string literal that looks like a TIMESTAMP/DATETIME works similar in PostgreSQL, SQL Server, MySQL-5.7. However, this looks only as a coincidence for this particular corner case. Perhaps we should not fix that in 10.0. It seems that MariaDB since 5.3 handles comparison of TIME in a more consistent way (less exceptions). Btw, why not to add a warning every time when an implicit non-standard type conversion happens and suggest an explicit CAST? This will encourage users to avoid doubtful constructions. Also, I noticed that the SQL Server documentation says that comparison of different data types is a subject to change in the future versions, and using this is not recommended. Perhaps we should also say something like this. I propose instead of adding a quick fix for this exception, to introduce a conception of "type strength" or "type derivation" in 10.2. The idea is simple: 1. Comparison is done according to the type of the strongest side. 2. Strengths(derivations) are as follows: - A field or an SP variable has the strongest type derivation, say 4. - Functions have derivation between field and typified constants, say 3. - Some functions that return input arguments (e.g. COALESCE, LEFT) can repeat derivations of their arguments. - A typified constant like TIME'10:20:30' has derivation 2. - A typeless constant like '10:20:30' has a very weak derivation, say 1. - An explicit NULL has the weakest derivation, say 0. 3. Only in case of equal strengths the result depends on the data types. Perhaps data types can have some sort of "minor strength", for conflict resolution. Say DATETIME/TIMESTAMP can have more minor strength over TIME or DATE. So a DATETIME field is compared to a TIME field as DATETIME rather than TIME. This will give some advantages: - Universal comparison rules for all data types - This idea will simplify adding new data types a lot. They will just follow the universal rule. Otherwise, new data types (say INET6), we'll have to have different execution paths with some exceptions and exceptions from exceptions :) - The problem mentioned in MDEV-9541 will be gone: it will compare as TIME just because it's a field compared to a typeless constant. - We'll remove a special code covering comparison of YEAR and LONGLONG fields. They will just work as before, but now because a field always wins a constant, according to the universal rules. - We'll probably remove some examples of a special code covering explicit NULLs (i.e. Item::NULL_ITEM). - A field compared to a constant will always be able to use indexes. Less chances to put the server into a heavy load because of a non-smart query executed. - The same results for a field compared to some expression, and for the same field wrapped into a hybrid function like COALESCE. - Hybrid functions with field arguments will also be able to use indexes in some cases. - Equal field propagation will be possible in more cases. - It should be easier to add a new SQL mode for the strict SQL standard compliant comparison style. I think it's good to have this option. Greetings. [View Less]

1 0

[Maria-developers] MDEV-9371 select insert('a', 2, 1, 'b') doesn't return expected 'a'
by Alexander Barkov 10 Feb '16

10 Feb '16

Hello Sergei, Please review a fix for MDEV-9371. I'd suggest to push it to 10.0 instead of 5.5, as MySQL fixed this bug in 5.6 (not 5.5). Also, I think it's worthy to backport MDEV-9332 to 10.0 as well, because the patch for MDEV-9371 touches the test which we modified in MDEV-9332. Thanks!

2 2

Re: [Maria-developers] [Commits] 841c317: MDEV-7122: Assertion `0' failed in subselect_hash_sj_engine::init
by Sergey Petrunia 09 Feb '16

09 Feb '16

Hi Vicentiu, Please find some cosmetic feedback below. Ok to push after it is addressed. On Tue, Feb 09, 2016 at 04:02:58PM +0200, Vicentiu Ciorbaru wrote: > revision-id: 841c317d237453f3e6c253e2a884cad73622b59a (mariadb-5.5.47-33-g841c317) > parent(s): ad94790f468bacb2cd62d02d3a6e7f012ca72e65 > author: Vicențiu Ciorbaru > committer: Vicențiu Ciorbaru > timestamp: 2016-02-09 16:00:18 +0200 > message: > > MDEV-7122: Assertion `0' failed in subselect_hash_sj_engine::… [View More]init > > The select mentioned in the bug attempted to create a temporary table > using the maria storage engine. The table needs to have primary keys such that > duplicates can be removed. Unfortunately this use case has a longer > than allowed key and the tmp table got created without a temporary key. > We must not allow materialization for the subquery if the total key > length and key parts is greater than what the storage engine supports. > > --- > mysql-test/r/subselect.result | 12 ++++++++++++ > mysql-test/r/subselect_no_mat.result | 12 ++++++++++++ > mysql-test/r/subselect_no_opts.result | 12 ++++++++++++ > mysql-test/r/subselect_no_scache.result | 12 ++++++++++++ > mysql-test/r/subselect_no_semijoin.result | 12 ++++++++++++ > mysql-test/t/subselect.test | 9 +++++++++ > sql/opt_subselect.cc | 10 ++++++++++ > sql/sql_select.cc | 25 +++++++++++++++++++++++++ > 8 files changed, 104 insertions(+) > > diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result > index ba52fd9..27e8eaa 100644 > --- a/mysql-test/r/subselect.result > +++ b/mysql-test/r/subselect.result > @@ -7081,3 +7081,15 @@ sq > NULL > deallocate prepare stmt; > drop table t1,t2,t3,t4; > +# MDEV-7122 > +# Assertion `0' failed in subselect_hash_sj_engine::init > +# > +SET SESSION big_tables=1; Please unset the variable back at the end of this testcase. We don't want the rest of the test to run with non-standard settings. > +CREATE TABLE t1(a char(255) DEFAULT '', KEY(a(10))) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; > +INSERT INTO t1 VALUES(0),(0),(0); > +SELECT * FROM t1 WHERE a IN(SELECT MIN(a) FROM t1); > +a > +0 > +0 > +0 > +DROP TABLE t1; > diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result > index d8d5c4e..4727f9f 100644 > --- a/mysql-test/r/subselect_no_mat.result > +++ b/mysql-test/r/subselect_no_mat.result > @@ -7078,6 +7078,18 @@ sq > NULL > deallocate prepare stmt; > drop table t1,t2,t3,t4; > +# MDEV-7122 > +# Assertion `0' failed in subselect_hash_sj_engine::init > +# > +SET SESSION big_tables=1; > +CREATE TABLE t1(a char(255) DEFAULT '', KEY(a(10))) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; > +INSERT INTO t1 VALUES(0),(0),(0); > +SELECT * FROM t1 WHERE a IN(SELECT MIN(a) FROM t1); > +a > +0 > +0 > +0 > +DROP TABLE t1; > set optimizer_switch=default; > select @@optimizer_switch like '%materialization=on%'; > @@optimizer_switch like '%materialization=on%' > diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result > index 78908bc..7c542dd 100644 > --- a/mysql-test/r/subselect_no_opts.result > +++ b/mysql-test/r/subselect_no_opts.result > @@ -7076,4 +7076,16 @@ sq > NULL > deallocate prepare stmt; > drop table t1,t2,t3,t4; > +# MDEV-7122 > +# Assertion `0' failed in subselect_hash_sj_engine::init > +# > +SET SESSION big_tables=1; > +CREATE TABLE t1(a char(255) DEFAULT '', KEY(a(10))) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; > +INSERT INTO t1 VALUES(0),(0),(0); > +SELECT * FROM t1 WHERE a IN(SELECT MIN(a) FROM t1); > +a > +0 > +0 > +0 > +DROP TABLE t1; > set @optimizer_switch_for_subselect_test=null; > diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result > index d1de4b8..3b58b3c 100644 > --- a/mysql-test/r/subselect_no_scache.result > +++ b/mysql-test/r/subselect_no_scache.result > @@ -7087,6 +7087,18 @@ sq > NULL > deallocate prepare stmt; > drop table t1,t2,t3,t4; > +# MDEV-7122 > +# Assertion `0' failed in subselect_hash_sj_engine::init > +# > +SET SESSION big_tables=1; > +CREATE TABLE t1(a char(255) DEFAULT '', KEY(a(10))) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; > +INSERT INTO t1 VALUES(0),(0),(0); > +SELECT * FROM t1 WHERE a IN(SELECT MIN(a) FROM t1); > +a > +0 > +0 > +0 > +DROP TABLE t1; > set optimizer_switch=default; > select @@optimizer_switch like '%subquery_cache=on%'; > @@optimizer_switch like '%subquery_cache=on%' > diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result > index 524a5dd..9246ffb 100644 > --- a/mysql-test/r/subselect_no_semijoin.result > +++ b/mysql-test/r/subselect_no_semijoin.result > @@ -7076,5 +7076,17 @@ sq > NULL > deallocate prepare stmt; > drop table t1,t2,t3,t4; > +# MDEV-7122 > +# Assertion `0' failed in subselect_hash_sj_engine::init > +# > +SET SESSION big_tables=1; > +CREATE TABLE t1(a char(255) DEFAULT '', KEY(a(10))) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; > +INSERT INTO t1 VALUES(0),(0),(0); > +SELECT * FROM t1 WHERE a IN(SELECT MIN(a) FROM t1); > +a > +0 > +0 > +0 > +DROP TABLE t1; > set @optimizer_switch_for_subselect_test=null; > set @join_cache_level_for_subselect_test=NULL; > diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test > index 67eac0d..5ca21ea 100644 > --- a/mysql-test/t/subselect.test > +++ b/mysql-test/t/subselect.test > @@ -5964,3 +5964,12 @@ EXECUTE stmt; > > deallocate prepare stmt; > drop table t1,t2,t3,t4; > + > +--echo # MDEV-7122 > +--echo # Assertion `0' failed in subselect_hash_sj_engine::init > +--echo # > +SET SESSION big_tables=1; > +CREATE TABLE t1(a char(255) DEFAULT '', KEY(a(10))) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; > +INSERT INTO t1 VALUES(0),(0),(0); > +SELECT * FROM t1 WHERE a IN(SELECT MIN(a) FROM t1); > +DROP TABLE t1; > diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc > index 827290f..a39e04b 100644 > --- a/sql/opt_subselect.cc > +++ b/sql/opt_subselect.cc > @@ -830,12 +830,14 @@ bool subquery_types_allow_materialization(Item_in_subselect *in_subs) > in_subs->sjm_scan_allowed= FALSE; > > bool all_are_fields= TRUE; > + uint32 total_key_length = 0; > for (uint i= 0; i < elements; i++) > { > Item *outer= in_subs->left_expr->element_index(i); > Item *inner= it++; > all_are_fields &= (outer->real_item()->type() == Item::FIELD_ITEM && > inner->real_item()->type() == Item::FIELD_ITEM); > + total_key_length += inner->max_length; > if (outer->cmp_type() != inner->cmp_type()) > DBUG_RETURN(FALSE); > switch (outer->cmp_type()) { > @@ -866,6 +868,14 @@ bool subquery_types_allow_materialization(Item_in_subselect *in_subs) > } > } > > + /* > + Make sure that create_tmp_table will not fail due to too long keys. > + See MDEV-7122. This check is performed inside create_tmp_table also and > + we must do it so that we know the table has keys created. > + */ > + if (total_key_length > HA_MAX_KEY_LENGTH || elements > HA_MAX_KEY_SEG) > + DBUG_RETURN(FALSE); > + > in_subs->types_allow_materialization= TRUE; > in_subs->sjm_scan_allowed= all_are_fields; > DBUG_PRINT("info",("subquery_types_allow_materialization: ok, allowed")); > diff --git a/sql/sql_select.cc b/sql/sql_select.cc > index f43f506..218a4ce 100644 > --- a/sql/sql_select.cc > +++ b/sql/sql_select.cc > @@ -15307,6 +15307,13 @@ create_tmp_table(THD *thd, TMP_TABLE_PARAM *param, List<Item> &fields, > if (!table->file) > goto err; > > + /* > + Temporary table storage engines must allow keys of at least > + HA_MAX_KEY_LENGT and at least HA_MAX_KEY_SEG key parts. psergey: typo. ^^^^^^^^^^^ > + */ > + DBUG_ASSERT(table->file->max_key_length() >= HA_MAX_KEY_LENGTH && > + table->file->max_key_parts() >= HA_MAX_KEY_SEG); > + > if (!using_unique_constraint) > reclength+= group_null_items; // null flag is stored separately > > @@ -15940,6 +15947,12 @@ bool create_internal_tmp_table(TABLE *table, KEY *keyinfo, > goto err; > > bzero(seg, sizeof(*seg) * keyinfo->key_parts); > + /* > + Note that a simillar check is performed during typo ^^^^^^^^^ > + subquery_types_allow_materialization. See MDEV-7122 for more details as > + to why. Whenever this changes, it must be updated there as well, for > + all tmp_table engines. > + */ > if (keyinfo->key_length > table->file->max_key_length() || > keyinfo->key_parts > table->file->max_key_parts() || > share->uniques) > @@ -16126,6 +16139,12 @@ bool create_internal_tmp_table(TABLE *table, KEY *keyinfo, > goto err; > > bzero(seg, sizeof(*seg) * keyinfo->key_parts); > + /* > + Note that a simillar check is performed during typo ^^^^^^^^^ > + subquery_types_allow_materialization. See MDEV-7122 for more details as > + to why. Whenever this changes, it must be updated there as well, for > + all tmp_table engines. > + */ > if (keyinfo->key_length > table->file->max_key_length() || > keyinfo->key_parts > table->file->max_key_parts() || > share->uniques) > @@ -16286,6 +16305,12 @@ create_internal_tmp_table_from_heap2(THD *thd, TABLE *table, > if (!(new_table.file= get_new_handler(&share, &new_table.mem_root, > new_table.s->db_type()))) > DBUG_RETURN(1); // End of memory > + /* > + Temporary table storage engines must allow keys of at least > + HA_MAX_KEY_LENGTH and at least HA_MAX_KEY_SEG key parts. > + */ > + DBUG_ASSERT(new_table.file->max_key_length() >= HA_MAX_KEY_LENGTH && > + new_table.file->max_key_parts() >= HA_MAX_KEY_SEG); > > save_proc_info=thd->proc_info; > thd_proc_info(thd, proc_info); > _______________________________________________ > commits mailing list > commits(a)mariadb.org > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits -- BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog [View Less]

1 0