- developers - lists.mariadb.org

[Maria-developers] Patch for MDEV-12874 - UPDATE statements with the same source and target
by jerome brauge 29 Aug '17

29 Aug '17

Hello, Here is a patch for MDEV-12874 (on branch 10.2-ext). Can anyone review it ? Alexander, during my work on this MDEV, I've found a new issue with update statement and Oracle compatibility. create table t1 (c1 integer, c2 integer, c3 integer); insert into t1(c1,c2,c3) values (1,1,1); update t1 set c1 = c1+1, c2 = c1+1, c3 = c2+1; select * from t1; Mariadb : +------+------+------+ | c1 | c2 | c3 | +------+------+------+ | 2 | 3 | 4 | +------+------+------+ 1 row in set (0.00 sec) Oracle : DVTORA> C1 C2 C3 ---------- ---------- ---------- 2 2 2 Can you open a new MDEV for this ? Best regards, Jérôme.

3 8

[Maria-developers] Oracle strings functions compatibility : substr
by jerome brauge 17 Aug '17

17 Aug '17

Hello Alexander, Here is a patch for function SUBSTR in sql_mode=oracle (If position is 0, then it is treated as 1). Regards, Jérôme.

1 0

[Maria-developers] sys_vars.sysvars_server_embedded test failure
by Alexander Barkov 15 Aug '17

15 Aug '17

Hello Igor, sys_vars.sysvars_server_embedded is failing. It looks like it's related to your recent optimizer change. -SESSION_VALUE index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on -GLOBAL_VALUE index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on +SESSION_VALUE index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_grouping_derived=on +GLOBAL_VALUE index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown= Can you please fix this? Thanks!

1 0

[Maria-developers] Valgrind reports uninitialized value on "if (!with_two_phase_optimization..."
by Alexander Barkov 15 Aug '17

15 Aug '17

Hello Igor, Please check the output of "mtr --valgrind main.sp" I'm getting these warnings: line ==6490== Thread 6: ==6490== Conditional jump or move depends on uninitialised value(s) ==6490== at 0x7762A1: JOIN::optimize() (sql_select.cc:1123) ==6490== by 0x77F2A7: mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) (sql_select.cc:3721) ==6490== by 0x773994: handle_select(THD*, LEX*, select_result*, unsigned long) (sql_select.cc:376) ==6490== by 0x73F055: execute_sqlcom_select(THD*, TABLE_LIST*) (sql_parse.cc:6431) ==6490== by 0x7354C9: mysql_execute_command(THD*) (sql_parse.cc:3631) ==6490== by 0x742A48: mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool) (sql_parse.cc:7886) ==6490== by 0x730684: dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool) (sql_parse.cc:1814) ==6490== by 0x72EFF7: do_command(THD*) (sql_parse.cc:1372) ==6490== by 0x883380: do_handle_one_connection(CONNECT*) (sql_connect.cc:1354) ==6490== by 0x883100: handle_one_connection (sql_connect.cc:1260) ==6490== by 0x10D6329: pfs_spawn_thread (pfs.cc:1862) ==6490== by 0x4E3C619: start_thread (in /usr/lib64/libpthread-2.22.so) ==6490== by 0x6DB75FC: clone (in /usr/lib64/libc-2.22.so) ^ Found warnings in /home/bar/maria-git/server.10.2-ext/mysql-test/var/6/log/mysqld.1.err Thanks!

1 0

[Maria-developers] history of lf_hash
by MARK CALLAGHAN 14 Aug '17

14 Aug '17

Comments for lf_hash are sparse. Does it implement a well-known algorithm? https://github.com/mysql/mysql-server/blob/8.0/mysys/lf_hash.cc https://github.com/mysql/mysql-server/blob/8.0/include/lf.h -- Mark Callaghan mdcallag(a)gmail.com

2 1

[Maria-developers] On Recursive CTE and CONNECT BY
by Sergey Petrunia 12 Aug '17

12 Aug '17

Hi Vicentiu, This is a followup to the Wednesday discussion. Background: see attached file for two queries. One uses a Recursive-CTE, another uses CONNECT-BY syntax. They produce different results for some reason. Initial guess about the cause was depth-first vs breadth-first traversal. Short answer: No it's not. The reason is that Recursive-CTE query is not equivalent to the CONNECT-BY query. Results from the recursive-CTE query: ID N P CYCLE PATH 1 A 0 /1A 2 B A 0 /1A/2B 3 C A 0 /1A/3C 4 D B 0 /1A/2B/4D 5 D C 0 /1A/3C/5D 6 E D 0 /1A/3C/5D/6E 6 E D 0 /1A/2B/4D/6E 7 F E 0 /1A/2B/4D/6E/7F 7 F E 0 /1A/3C/5D/6E/7F 8 C F 0 /1A/3C/5D/6E/7F/8C 8 C F 0 /1A/2B/4D/6E/7F/8C 5 D C 0 /1A/2B/4D/6E/7F/8C/5D 12 rows selected. Results from CONNECT-BY query: ID N P CYCLE Path 1 A 0 /1A 2 B A 0 /1A/2B 4 D B 0 /1A/2B/4D 6 E D 0 /1A/2B/4D/6E 7 F E 0 /1A/2B/4D/6E/7F 8 C F 1 /1A/2B/4D/6E/7F/8C 3 C A 0 /1A/3C 5 D C 0 /1A/3C/5D 6 E D 0 /1A/3C/5D/6E 7 F E 1 /1A/3C/5D/6E/7F 10 rows selected. The rows that are missing are: ID N P CYCLE PATH 8 C F 0 /1A/3C/5D/6E/7F/8C 5 D C 0 /1A/2B/4D/6E/7F/8C/5D (denote this as MISSING-ROWS) Another thing to note is that the CTE query has returned CYCLE=0 for all rows, while the CONNECT BY one has returned two rows with CYCLE=1: 8 C F 1 /1A/2B/4D/6E/7F/8C 7 F E 1 /1A/3C/5D/6E/7F Note that these rows are parents of rows in MISSING-ROWS. I don't think the issue is caused by breadth-first vs depth-first traversal differences. The issue is caused by us not understanding how Oracle's NOCYCLE clause works. Take a look at the original-dataset.jpg, and PATH columns of MISSING-ROWS We see that the path forms the entire loop (or "cycle"). Oracle's NOCYCLE seems to be about *not* constructing the entire loop. To rule out breadth-first vs depth-first differences, let's make an example where there's just one loop: create table g3(a int, b int); insert into g3 values (1, 2); insert into g3 values (2, 3); insert into g3 values (3, 4); insert into g3 values (4, 5); insert into g3 values (5, 6); insert into g3 values (6, 7); insert into g3 values (7, 4); select a, CONNECT_BY_ISCYCLE as cycle, SYS_CONNECT_BY_PATH(a, '/') "Path" from g3 start with a=1 connect by NOCYCLE prior b= a; A CYCLE Path 1 0 /1 2 0 /1/2 3 0 /1/2/3 4 0 /1/2/3/4 5 0 /1/2/3/4/5 6 1 /1/2/3/4/5/6 The row with a=7 is not listed. If it did, the path would have had a complete cycle. It is a bit counter intuitive that /1/2/3/4/5/6 has CYCLE=1, while actually it does not form a cycle. It's one step short of doing so. The documentation is vague about this, but here's somebody thinking the same on the internet: http://www.sql.ru/forum/255418/zadachka-anagrammy-na-sql?hl=%e0%ed%e0%e3%f0… : While posting messages on MetaLink, I think I finally got the logic of NOCYCLE. Clue was right there in the name itself. NOCYCLE means do not cycle. So if you have a hierarchical query without NOCYCLE and a cycle is detected Oracle does not know how to handle it and throws ORA-01436. If NOCYCLE is used Oracle simply cuts of part of a branch right at loop point without raising an error (another word it simply does not follow the loop). And that explains why CONNECT_BY_ISCYCLE is set to 1 for the last row in first iteration of the loop. Otherwise, we would not get 1 at all. Now next logical step would be to implement CYCLE. BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] Win32 - Sysvar 'optimizer_switch' failed 'def_val < my_set_bits(typelib.count)'
by jerome brauge 11 Aug '17

11 Aug '17

Hi All, Build of last 10.2-ext fail on Win32 with message : Sysvar 'optimizer_switch' failed 'def_val < my_set_bits(typelib.count)' The problem seems to be in sys_vars.ic (Sys_var_set) : > > { > option.var_type|= GET_SET; > global_var(ulonglong)= def_val; > SYSVAR_ASSERT(typelib.count > 0); > SYSVAR_ASSERT(typelib.count <= 64); > SYSVAR_ASSERT(def_val <= my_set_bits(typelib.count)); Patch b14e2b04 introduce a new optimizer_switch and now 'optimizer_switch' has 32 values. my_set_bits works with unit32. A new function like this static inline uint64 my_set_bits64(int n) { return (((1ULL << (n - 1)) - 1) << 1) | 1; } might solve this issue. Regards.

1 0

[Maria-developers] Virtual columns in sql_mode=ORACLE
by jerome brauge 11 Aug '17

11 Aug '17

Hello Alexander, Something seems to be wrong in sql_yacc_ora.yy. (10.2-ext branch) We can't create a table with a virtual column because the grammar try to reduce the rule sp_cursor_name_and_offset. CREATE TABLE t1 (c1 INTEGER, c2 INTEGER AS (c1 MOD 10) VIRTUAL) ENGINE=InnoDb; ERROR 1324 (42000): Undefined CURSOR: c1 Regards, Jérôme.

2 1

[Maria-developers] Default value on column in sql_mode=ORACLE
by jerome brauge 11 Aug '17

11 Aug '17

Hello Alexander, Do you know why STRICT_TRANS_TABLES is not set in sql_mode=ORACLE ? The following script should not work: set sql_mode=oracle; create table t1 (c1 integer not null,c2 integer not null); insert into t1 (c1) values(1); Query OK, 1 row affected, 1 warning Regards, Jérôme.

2 1

Re: [Maria-developers] MDEV-13439: Database permissions are not enough to run a subquery with GROUP BY within a view
by Sergei Golubchik 09 Aug '17

09 Aug '17

Hi, Oleksandr! On Aug 07, Oleksandr Byelkin wrote: > revision-id: cf66c30ce592ad88d447568272bdced2046f2906 (mariadb-10.2.7-20-gcf66c30ce59) > parent(s): bcc10a5a447805ce64aa13ee6a037c1618219616 > committer: Oleksandr Byelkin > timestamp: 2017-08-07 13:42:35 +0200 > message: > > MDEV-13439: Database permissions are not enough to run a subquery with GROUP BY within a view > > The bug is result adding ability to have derived tables inside views. > Fixed checks should be a switch between view/derived or select derived and information schema. > > diff --git a/sql/sql_acl.cc b/sql/sql_acl.cc > index 77f502d3a7b..9bffcbe6a9e 100644 > --- a/sql/sql_acl.cc > +++ b/sql/sql_acl.cc > @@ -7594,8 +7594,11 @@ bool check_grant(THD *thd, ulong want_access, TABLE_LIST *tables, > /* > It is subquery in the FROM clause. VIEW set t_ref->derived after > table opening, but this function always called before table opening. > + > + NOTE: is_derived() can't be used here bacause subquery in this case typo "bacause" > + the FROM clase (derived tables) can be not be marked yet. > */ > - if (!t_ref->referencing_view) > + if (t_ref->is_anonymous_derived_table() || t_ref->schema_table) > { > /* > If it's a temporary table created for a subquery in the FROM > diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc > index 649f745fdc4..8f8d051a976 100644 > --- a/sql/sql_derived.cc > +++ b/sql/sql_derived.cc > @@ -822,10 +822,12 @@ bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived) > table->derived_select_number= first_select->select_number; > table->s->tmp_table= INTERNAL_TMP_TABLE; > #ifndef NO_EMBEDDED_ACCESS_CHECKS > - if (derived->referencing_view) > + if (derived->is_view()) > table->grant= derived->grant; > else > { > + DBUG_ASSERT(derived->is_derived()); > + DBUG_ASSERT(derived->is_anonymous_derived_table()); Hmm, if derived->is_derived() is always true here, why there's an if() below? If it's redundant - please, remove it. > table->grant.privilege= SELECT_ACL; > if (derived->is_derived()) > derived->grant.privilege= SELECT_ACL; Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

1 0