- developers - lists.mariadb.org

[Maria-developers] Check constraints
by Martijn Tonies (Upscene Productions) 10 Jan '18

10 Jan '18

Hi, Where can I find meta information for check constraints on tables? With regards, Martijn Tonies Upscene Productions http://www.upscene.com Database Workbench - developer tool for Oracle, MS SQL Server, PostgreSQL, SQL Anywhere, MySQL, InterBase, NexusDB and Firebird.

3 4

[Maria-developers] subscribe
by Martijn Tonies (Upscene Productions) 09 Jan '18

09 Jan '18

subscribe With regards, Martijn Tonies Upscene Productions http://www.upscene.com Database Workbench - developer tool for Oracle, MS SQL Server, PostgreSQL, SQL Anywhere, MySQL, InterBase, NexusDB and Firebird.

1 0

Re: [Maria-developers] [Commits] fbe0fc2: Fixed mdev-14879 Lost rows for query using recursive CTE
by Oleksandr Byelkin 09 Jan '18

09 Jan '18

Hi, Igor! Am 06.01.2018 um 18:32 schrieb IgorBabaev: > revision-id: fbe0fc2d87cb1bdb9f7040d44ad3c58a94857e20 (mariadb-10.2.12-14-gfbe0fc2) > parent(s): 15b1840f43be8c660382458826e9d5a47d148a67 > author: Igor Babaev > committer: Igor Babaev > timestamp: 2018-01-06 09:32:47 -0800 > message: > > Fixed mdev-14879 Lost rows for query using recursive CTE > with recursive reference in subquery > > If a recursive CTE uses a subquery with recursive reference then > the virtual function reset() must be called after each iteration > performed at the execution of the CTE. OK to push. [skip]

1 0

Re: [Maria-developers] [Commits] 81965f0: Fixed mdev-14852 Fails to reopen temp table within standard CTE
by Oleksandr Byelkin 05 Jan '18

05 Jan '18

Am 05.01.2018 um 08:40 schrieb IgorBabaev: > revision-id: 81965f03065b84322f2d03d784893179e8b79fd9 (mariadb-10.2.12-6-g81965f0) > parent(s): 0f253d3e644d3027face26371eb1bb59e8280728 > author: Igor Babaev > committer: Igor Babaev > timestamp: 2018-01-04 23:40:37 -0800 > message: > > Fixed mdev-14852 Fails to reopen temp table within standard CTE > > If the specification of a CTE contains a reference to a temporary table > then THD::open_temporary_table() must be called for this reference for > any occurrence of the CTE in the query. By mistake this was done only > for the first occurrences of CTEs. > The patch fixes this problem in With_element::clone_parsed_spec(). > It also moves there the call of check_dependencies_in_with_clauses() > to its proper place before the call of check_table_access(). > Additionally the patch optimizes the number of calls of the > function check_dependencies_in_with_clauses(). OK to push! > > --- > mysql-test/r/cte_nonrecursive.result | 30 ++++++++++++++++++++++++++++++ > mysql-test/t/cte_nonrecursive.test | 29 +++++++++++++++++++++++++++++ > sql/sql_cte.cc | 7 +++++++ > sql/sql_parse.cc | 22 +++++----------------- > sql/sql_prepare.cc | 11 +++-------- > 5 files changed, 74 insertions(+), 25 deletions(-) > > diff --git a/mysql-test/r/cte_nonrecursive.result b/mysql-test/r/cte_nonrecursive.result > index b1b41b1..c1f4c9f 100644 > --- a/mysql-test/r/cte_nonrecursive.result > +++ b/mysql-test/r/cte_nonrecursive.result > @@ -1265,3 +1265,33 @@ a > 4 > deallocate prepare stmt; > drop table t1; > +# > +# MDEV-14852: CTE using temporary table in query > +# with two references to the CTE > +# > +create temporary table t1 (i int); > +insert into t1 values (5),(4),(1),(2),(3); > +with > +c1 as (select i from t1), > +c2 as (select i from c1 where c1.i=2) > +select i from c1 where i > 3 union select i from c2; > +i > +5 > +4 > +2 > +drop table t1; > +create table t1 (term char(10)); > +create temporary table t2 (term char(10)); > +insert into t1 values ('TERM01'),('TERM02'),('TERM03'); > +insert into t2 values ('TERM02'),('TERM03'),('TERM04'); > +with c1 as (select * from t1), c2 as (select * from t2) > +(select * from c1 left outer join c2 on c1.term = c2.term) > +union all > +(select * from c1 right outer join c2 on c1.term = c2.term > +where c1.term is null); > +term term > +TERM02 TERM02 > +TERM03 TERM03 > +TERM01 NULL > +NULL TERM04 > +drop table t1,t2; > diff --git a/mysql-test/t/cte_nonrecursive.test b/mysql-test/t/cte_nonrecursive.test > index 1f21dbc..9436665 100644 > --- a/mysql-test/t/cte_nonrecursive.test > +++ b/mysql-test/t/cte_nonrecursive.test > @@ -853,3 +853,32 @@ execute stmt; > deallocate prepare stmt; > > drop table t1; > + > +--echo # > +--echo # MDEV-14852: CTE using temporary table in query > +--echo # with two references to the CTE > +--echo # > + > +create temporary table t1 (i int); > +insert into t1 values (5),(4),(1),(2),(3); > + > +with > +c1 as (select i from t1), > +c2 as (select i from c1 where c1.i=2) > +select i from c1 where i > 3 union select i from c2; > + > +drop table t1; > + > +create table t1 (term char(10)); > +create temporary table t2 (term char(10)); > + > +insert into t1 values ('TERM01'),('TERM02'),('TERM03'); > +insert into t2 values ('TERM02'),('TERM03'),('TERM04'); > + > +with c1 as (select * from t1), c2 as (select * from t2) > +(select * from c1 left outer join c2 on c1.term = c2.term) > +union all > +(select * from c1 right outer join c2 on c1.term = c2.term > + where c1.term is null); > + > +drop table t1,t2; > diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc > index 601c192..d12948f 100644 > --- a/sql/sql_cte.cc > +++ b/sql/sql_cte.cc > @@ -817,12 +817,19 @@ st_select_lex_unit *With_element::clone_parsed_spec(THD *thd, > parse_status= parse_sql(thd, &parser_state, 0); > if (parse_status) > goto err; > + > + if (check_dependencies_in_with_clauses(lex->with_clauses_list)) > + goto err; > + > spec_tables= lex->query_tables; > spec_tables_tail= 0; > for (TABLE_LIST *tbl= spec_tables; > tbl; > tbl= tbl->next_global) > { > + if (!tbl->derived && !tbl->schema_table && > + thd->open_temporary_table(tbl)) > + goto err; > spec_tables_tail= tbl; > } > if (check_table_access(thd, SELECT_ACL, spec_tables, FALSE, UINT_MAX, FALSE)) > diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc > index f00c74b..6d068eb 100644 > --- a/sql/sql_parse.cc > +++ b/sql/sql_parse.cc > @@ -2990,6 +2990,9 @@ mysql_execute_command(THD *thd) > thd->get_stmt_da()->opt_clear_warning_info(thd->query_id); > } > > + if (check_dependencies_in_with_clauses(thd->lex->with_clauses_list)) > + DBUG_RETURN(1); > + > #ifdef HAVE_REPLICATION > if (unlikely(thd->slave_thread)) > { > @@ -3446,14 +3449,6 @@ mysql_execute_command(THD *thd) > ulong privileges_requested= lex->exchange ? SELECT_ACL | FILE_ACL : > SELECT_ACL; > > - /* > - The same function must be called for DML commands > - when CTEs are supported in DML statements > - */ > - res= check_dependencies_in_with_clauses(thd->lex->with_clauses_list); > - if (res) > - break; > - > if (all_tables) > res= check_table_access(thd, > privileges_requested, > @@ -3879,8 +3874,7 @@ mysql_execute_command(THD *thd) > /* Copy temporarily the statement flags to thd for lock_table_names() */ > uint save_thd_create_info_options= thd->lex->create_info.options; > thd->lex->create_info.options|= create_info.options; > - if (!(res= check_dependencies_in_with_clauses(lex->with_clauses_list))) > - res= open_and_lock_tables(thd, create_info, lex->query_tables, TRUE, 0); > + res= open_and_lock_tables(thd, create_info, lex->query_tables, TRUE, 0); > thd->lex->create_info.options= save_thd_create_info_options; > if (res) > { > @@ -4493,8 +4487,7 @@ mysql_execute_command(THD *thd) > > unit->set_limit(select_lex); > > - if (!(res= check_dependencies_in_with_clauses(lex->with_clauses_list)) && > - !(res=open_and_lock_tables(thd, all_tables, TRUE, 0))) > + if (!(res=open_and_lock_tables(thd, all_tables, TRUE, 0))) > { > MYSQL_INSERT_SELECT_START(thd->query()); > /* > @@ -4821,9 +4814,6 @@ mysql_execute_command(THD *thd) > { > List<set_var_base> *lex_var_list= &lex->var_list; > > - if (check_dependencies_in_with_clauses(thd->lex->with_clauses_list)) > - goto error; > - > if ((check_table_access(thd, SELECT_ACL, all_tables, FALSE, UINT_MAX, FALSE) > || open_and_lock_tables(thd, all_tables, TRUE, 0))) > goto error; > @@ -6376,8 +6366,6 @@ static bool execute_sqlcom_select(THD *thd, TABLE_LIST *all_tables) > new (thd->mem_root) Item_int(thd, > (ulonglong) thd->variables.select_limit); > } > - if (check_dependencies_in_with_clauses(lex->with_clauses_list)) > - return 1; > > if (!(res= open_and_lock_tables(thd, all_tables, TRUE, 0))) > { > diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc > index be78a76..390b708 100644 > --- a/sql/sql_prepare.cc > +++ b/sql/sql_prepare.cc > @@ -1573,8 +1573,6 @@ static int mysql_test_select(Prepared_statement *stmt, > lex->select_lex.context.resolve_in_select_list= TRUE; > > ulong privilege= lex->exchange ? SELECT_ACL | FILE_ACL : SELECT_ACL; > - if (check_dependencies_in_with_clauses(lex->with_clauses_list)) > - goto error; > if (tables) > { > if (check_table_access(thd, privilege, tables, FALSE, UINT_MAX, FALSE)) > @@ -1841,9 +1839,6 @@ static bool mysql_test_create_table(Prepared_statement *stmt) > if (create_table_precheck(thd, tables, create_table)) > DBUG_RETURN(TRUE); > > - if (check_dependencies_in_with_clauses(lex->with_clauses_list)) > - DBUG_RETURN(TRUE); > - > if (select_lex->item_list.elements) > { > /* Base table and temporary table are not in the same name space. */ > @@ -2234,9 +2229,6 @@ static bool mysql_test_insert_select(Prepared_statement *stmt, > if (insert_precheck(stmt->thd, tables)) > return 1; > > - if (check_dependencies_in_with_clauses(lex->with_clauses_list)) > - return 1; > - > /* store it, because mysql_insert_select_prepare_tester change it */ > first_local_table= lex->select_lex.table_list.first; > DBUG_ASSERT(first_local_table != 0); > @@ -2339,6 +2331,9 @@ static bool check_prepared_statement(Prepared_statement *stmt) > if (tables) > thd->get_stmt_da()->opt_clear_warning_info(thd->query_id); > > + if (check_dependencies_in_with_clauses(thd->lex->with_clauses_list)) > + goto error; > + > if (sql_command_flags[sql_command] & CF_HA_CLOSE) > mysql_ha_rm_tables(thd, tables); > > _______________________________________________ > commits mailing list > commits(a)mariadb.org > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits

1 0

Re: [Maria-developers] [Commits] de21526: This is a full cost-based implementation of the optimization that employs
by Oleksandr Byelkin 29 Dec '17

29 Dec '17

Hi, Igor! Am 29.12.2017 um 01:47 schrieb IgorBabaev: > revision-id: de215264682a9f3291ca028fb3ae052ecb191ca3 (mariadb-10.3.3-4-gde21526) > parent(s): 7a66e0ab8f52f3bd32850463daa05f9a2401e6b1 > author: Igor Babaev > committer: Igor Babaev > timestamp: 2017-12-28 16:47:25 -0800 > message: > > This is a full cost-based implementation of the optimization that employs > splitting technique for equi-joins of materialized derived tables/views/CTEs. > (see mdev-13369 and mdev-13389). I have to make special mention of really good description. As we agreed OK to push after adding test of double execution. [skip]

1 0

Re: [Maria-developers] a01f9ff: MDEV-11071 - Assertion `thd->transaction.stmt.is_empty()' failed in
by Sergei Golubchik 27 Dec '17

27 Dec '17

Hi, Sergey! On Dec 26, Sergey Vojtovich wrote: > revision-id: a01f9ff80b892bf32dd2754da17438e8884c003a (mariadb-10.2.11-51-ga01f9ff) > parent(s): c3bd0b0301936d0dcd0918bb8c966c272610d61b > committer: Sergey Vojtovich > timestamp: 2017-12-26 14:38:45 +0400 > message: > > MDEV-11071 - Assertion `thd->transaction.stmt.is_empty()' failed in > Locked_tables_list::unlock_locked_tables > > Similarly to regular DROP TABLE, don't leave locked tables mode if CREATE OR > REPLACE dropped temporary table but failed to cerate new one. > > The problem is that there's no track of which temporary table was "locked" by > LOCK TABLES. Looks reasonable. Could you also add a test where t2 is locked and t2 is dropped? Just to show that pos_in_locked_tables works for temporary tables. Ok to push after that. Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

1 0

[Maria-developers] Please document HOUR() vs EXTRACT(HOUR)
by Alexander Barkov 23 Dec '17

23 Dec '17

Hello Ian, Can you please document the difference between functions HOUR() and EXTRACT(HOUR)? Please see details here: https://jira.mariadb.org/browse/MDEV-14731 Thanks!

1 0

[Maria-developers] mysql and mariadb got different result
by 落寞回头不如华丽转身 21 Dec '17

21 Dec '17

mysql but mariadb

2 1

[Maria-developers] Fwd: Need Help
by Deepak Gupta 20 Dec '17

20 Dec '17

Hi There, I am working on a project where I have some wired requirement for my project. Issue : I am looking for the generic script that I can run with the help of Admin rights on the data base and I want the return output in such a way that it will give me the complete data base structure along with the table relation ship. For an example , there is one HR schema , with in that schema there might be 100 tables example , Employee / Salary / Address / Department etc and every table has primary and foreign key concept. So I need the script should be such a way that it will fetch all these information and as soon as I run the same script in my local system it will replicate the same data base structure for me in my local system. Kindly suggest how can I achieve this requirement ? Any help or suggestions are more than welcome. Looking foreword to here you soon. Thanks D

2 1

Re: [Maria-developers] [Commits] 203a658: MDEV-11084 Select statement with partition selection against MyISAM table opens all partitions.
by Sergei Golubchik 19 Dec '17

19 Dec '17

Hi, Alexey! On Dec 18, Alexey Botchkov wrote: > revision-id: 203a6585d3deedb36e30d5ebf388de44b6810403 (mariadb-10.1.23-51-g203a658) > parent(s): e4d10e09cf318aad237143254c45458d16009f70 > committer: Alexey Botchkov > timestamp: 2017-12-18 19:31:26 +0400 > message: > > MDEV-11084 Select statement with partition selection against MyISAM table opens all partitions. > > Now SELECT FROM t PARTITION(x) only opens the 'x' file. > The table->partition_names parameter is sent to ha_open > so it only opens the required partitions. > If the table is reopened, the > change_partition_to_open(partition_names) is called that > closes and opens partitions specified for the query. Looks ok to me, thanks! Please add some tests, can status variables show the difference in the number of opened partitions? And make sure that the whole tree compiles without partitioning. Then ok to push into 10.3 Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

1 0