[Maria-developers] GSoC Student Introduction for MDEV-24676
Hello, my name is Vladislav Kakurin, and this year I'm a GSOC 2021 participant for MariaDB. I will work on concurrent ring-buffer for improvement of IO_CACHE (https://jira.mariadb.org/browse/MDEV-24676) I hope I will succeed in this matter, as well as become a part of the MariaDB community ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
Re: [Maria-developers] 38a888da0f1: MDEV-17554 Auto-create new partition for system versioned tables with history partitioned by INTERVAL/LIMIT
Hi, Aleksey!
Like last time, it's a review of the diff bc04ded2353 22158b3c05a,
so not only commit 38a888da0f1.
The main thing below - I didn't understand a couple of changes and asked
for clarifications.
On May 30, Aleksey Midenkov wrote:
> revision-id: 38a888da0f1 (mariadb-10.5.2-653-g38a888da0f1)
> parent(s): bc04ded2353
> author: Aleksey Midenkov
> committer: Aleksey Midenkov
> timestamp: 2021-04-22 23:35:06 +0300
> message:
>
> MDEV-17554 Auto-create new partition for system versioned tables with history
> partitioned by INTERVAL/LIMIT
> diff --git a/mysql-test/suite/versioning/r/delete_history.result
> b/mysql-test/suite/versioning/r/delete_history.result
> --- a/mysql-test/suite/versioning/r/delete_history.result
> +++ b/mysql-test/suite/versioning/r/delete_history.result
> @@ -154,3 +152,21 @@ select * from t1;
> a
> 1
> drop table t1;
> +#
> +# MDEV-17554 Auto-create new partition for system versioned tables with
> history partitioned by INTERVAL/LIMIT
> +#
> +# Don't auto-create new partition on DELETE HISTORY:
> +set timestamp= unix_timestamp('2000-01-01 00:00:00');
> +create or replace table t (a int) with system versioning
> +partition by system_time interval 1 hour auto;
> +set timestamp= unix_timestamp('2000-01-01 10:00:00');
perhaps, make it only 02:00:00 to avoid any chance that
a partition won't be created because of max-auto-create limit.
it's possible we put the limit at 10, but 2 is very unlikely.
or does max-auto-create limit always result in a warning?
> +delete history from t;
> +set timestamp= default;
> +show create table t;
> +TableCreate Table
> +tCREATE TABLE `t` (
> + `a` int(11) DEFAULT NULL
> +) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
> + PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01
> 00:00:00' AUTO
> +PARTITIONS 2
> +drop table t;
> diff --git a/mysql-test/suite/versioning/r/partition.result
> b/mysql-test/suite/versioning/r/partition.result
> --- a/mysql-test/suite/versioning/r/partition.result
> +++ b/mysql-test/suite/versioning/r/partition.result
> @@ -1236,27 +1270,826 @@ t1 CREATE TABLE `t1` (
> PARTITION `p5` HISTORY ENGINE = DEFAULT_ENGINE,
> PARTITION `pn` CURRENT ENGINE = DEFAULT_ENGINE)
> alter table t1 add partition partitions 8;
> +drop table t1;
> +#
> +# End of 10.5 tests
> +#
> +#
> +# MDEV-17554 Auto-create new partition for system versioned tables with
> history partitioned by INTERVAL/LIMIT
> +#
> +create or replace table t1 (x int) with system versioning
> +partition by system_time limit 1 auto;
> show create table t1;
> TableCreate Table
> t1 CREATE TABLE `t1` (
>`x` int(11) DEFAULT NULL
> ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
> - PARTITION BY SYSTEM_TIME LIMIT 1
> -(PARTITION `p2` HISTORY ENGINE = DEFAULT_ENGINE,
> - PARTITION `p8` HISTORY ENGINE = DEFAULT_ENGINE,
> + PARTITION BY SYSTEM_TIME LIMIT 1 AUTO
> +PARTITIONS 2
> +insert into t1 values (1);
> +create or replace table t2 (y int);
> +insert into t2 values (2);
> +insert into t1 select * from t2;
> +insert into t2 select * from t1;
> +show create table t1;
> +TableCreate Table
> +t1 CREATE TABLE `t1` (
> + `x` int(11) DEFAULT NULL
> +) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
> + PARTITION BY SYSTEM_TIME LIMIT 1 AUTO
> +PARTITIONS 2
> +# Too many partitions error
> +set timestamp= unix_timestamp('2000-01-01 00:00:00');
> +create or replace table t1 (x int) with system versioning
> +partition by system_time interval 1 hour auto;
> +set timestamp= unix_timestamp('2001-01-01 00:01:00');
> +update t1 set x= x + 1;
> +ERROR HY000: Too many partitions (including subpartitions) were defined
> +# Partition overflow error and manual fix
seems to be a wrong comment? no manual fix here.
> +set timestamp= unix_timestamp('2000-01-01 00:00:00');
> +create or replace table t1 (x int) with system versioning
> +partition by system_time interval 1 hour;
> +insert into t1 values (333);
> +set timestamp= unix_timestamp('2000-01-01 01:00:00');
> +update t1 set x= x + 1;
> +Warnings:
> +Warning 4114Versioned table `test`.`t1`: last HISTORY partition
> (`p0`) is out of INTERVAL, need more HISTORY partitions
> +drop table t1;
> +# Partition overflow error and manual fix
> +set timestamp= unix_timestamp('2000-01-01 00:00:00');
> +create or replace table t1 (x int) with system versioning
> +partition by system_time interval 1 hour;
> +insert into t1 values (440);
> +set timestamp= unix_timestamp('2000-01-01 00:10:00');
> +update t1 set x= x + 1;
> +# Check how pruning boundaries work
> +explain partitions select * from t1 for system_time as of '2000-01-01
> 00:59:58';
> +id select_type table partitions typepossible_keys key
> key_len ref rowsExtra
> +1SIMPLE t1 p0,pn # NULLNULLNULLNULL#
> #
> +explain partitions select * from t1 for system_time as of
Re: [Maria-developers] JSON_TABLE: on default values
Hi Alexey,
Ok I've filed https://jira.mariadb.org/browse/MDEV-25822 for this. Let me
review the patch.
On Sun, May 30, 2021 at 06:44:19AM +0400, Alexey Botchkov wrote:
> Hi, Sergey!
>
> I meditated about it for some time. I remember i was thinking on that part
> before and
> did that so for some reason. Though either i was wrong or didn't finish
> what i planned.
> This time i'd say we should allow numeric constants there too.
> Here's the patch i'd push to fix this:
> https://github.com/MariaDB/server/commit/9c518e4cc9b0569cae2daa5a4024e209293eca45
>
> Best regards.
> HF
>
>
> On Wed, May 26, 2021 at 8:01 PM Sergey Petrunia wrote:
>
> > Hi Alexey,
> >
> > At the moment MariaDB requires that the values in DEFAULT clauses are
> > quoted.
> > Example:
> >
> > select *
> > from
> > json_table(
> > '{"intval": 1000}',
> > '$' columns(
> > col1 int path '$.intval_'
> > default '100' on empty
> >)
> >) as T;
> >
> > here, "100" must be quoted, otherwise one gets a parse error. However, the
> > quoted value is interpreted as an SQL literal. This looks puzzling.
> >
> > MySQL-8 also requires that the default value is quoted, but they have a
> > (very
> > odd) reason for it: they interpret the default value as JSON:
> >
> > https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/json-table-functions.html
> > says:
> >
> > DEFAULT json_string ON EMPTY: the provided json_string is parsed as
> > JSON, as
> > long as it is valid, and stored instead of the missing value. Column
> > type
> > rules also apply to the default value.
> >
> > I am not sure why MySQL chose to do this. Looking into the SQL Standard,
> > one can
> > see:
> >
> > ::=
> >
> > [ PATH ]
> > [ ON EMPTY ]
> > [ ON ERROR ]
> >
> > ::=
> > ERROR
> > | NULL
> > | DEFAULT
> >
> > ...
> > This doesn't say whether the should be interepreted as
> > JSON
> > or just as a value. But one can find this passage:
> >
> >
> > Without Feature T826, “General value expression in ON ERROR or ON EMPTY
> > clauses”, the > expression> contained in or > column error behavior>
> > contained in a JTRCD shall be a
> > that can be cast to the
> > data type specified by the contained in JTRCD without raising
> > an exception condition
> > according to the General Rules of Subclause 6.13, “”.
> >
> >
> > The important part is:
> >
> > ... shall be a that can be cast to the data type specified ...
> >
> > which means it is not JSON. It is just a literal, and literal can be a
> > string
> > literal (in quotes, 'string') or an integer literal (without quotes) or
> > other
> > kind of literal.
> >
> > Btw, Oracle Database allows non-string literals in the default clause:
> >
> > https://dbfiddle.uk/?rdbms=oracle_18=9af7e43ede77ee285e1a65f1f419d3bd
> >
> > What are your thoughts on this?
> > Is MariaDB's behavior intentional? Should we follow the standard and allow
> > all
> > kinds of literals? What was the reason for the limitation that default
> > values
> > are quoted?
> >
> > BR
> > Sergei
> > --
> > Sergei Petrunia, Software Developer
> > MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net
> >
> >
--
BR
Sergei
--
Sergei Petrunia, Software Developer
MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help : https://help.launchpad.net/ListHelp
