Re: [Maria-developers] [Commits] 787610a: MDEV-10766: Queries which start with WITH clause do not get inserted into query cache

[Varun Gupta]

On Sat, Feb 4, 2017 at 4:21 AM, Sergey Petrunia  wrote:

> Hi Varun,
>
> On Fri, Feb 03, 2017 at 08:20:51PM +0530, Varun wrote:
> > revision-id: 787610a1f5cec04311fd16f6c1de87171e5cfdd4
> (mariadb-10.2.3-159-g787610a)
> > parent(s): bc12d993d7bc94a9533028a258afc7e4ceb21e92
> > author: Varun Gupta
> > committer: Varun Gupta
> > timestamp: 2017-02-03 20:17:16 +0530
> > message:
> >
> > MDEV-10766: Queries which start with WITH clause do not get inserted
> into query cache
> >
> > Added conditions so that the WITH queries are also added to the query
> cache
> >
> > ---
> >  mysql-test/r/query_cache.result | 30 ++
> >  mysql-test/t/query_cache.test   | 14 ++
> >  sql/sql_cache.cc|  5 -
> >  3 files changed, 48 insertions(+), 1 deletion(-)
> >
> > diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.
> result
> > index d34c96c..fa169d4 100644
> > --- a/mysql-test/r/query_cache.result
> > +++ b/mysql-test/r/query_cache.result
> > @@ -2136,6 +2136,36 @@ Qcache_hits1
> >  use test;
> >  drop database `foo.bar`;
> >  End of 10.0 tests
> > +FLUSH USER_STATISTICS;
> > +FLUSH CLIENT_STATISTICS;
>
> I'm wondering what is the reason for the above? Qcache_XXX status variables
> are flushed with FLUSH STATUS.
>

Updated

>
> > +flush status;
> > +show status like "Qcache_inserts";
> > +Variable_nameValue
> > +Qcache_inserts   0
> > +create table t1 (i int);
> > +with cte as (select * from t1) select * from cte;
> > +i
> > +show status like "Qcache_queries_in_cache";
> > +Variable_nameValue
> > +Qcache_queries_in_cache  1
> > +show status like "Qcache_inserts";
> > +Variable_nameValue
> > +Qcache_inserts   1
> > +show status like "Qcache_hits";
> > +Variable_nameValue
> > +Qcache_hits  0
> > +with cte as (select * from t1) select * from cte;
> > +i
> > +show status like "Qcache_queries_in_cache";
> > +Variable_nameValue
> > +Qcache_queries_in_cache  1
> > +show status like "Qcache_inserts";
> > +Variable_nameValue
> > +Qcache_inserts   1
> > +show status like "Qcache_hits";
> > +Variable_nameValue
> > +Qcache_hits  1
> > +drop table t1;
> >  restore defaults
> >  SET GLOBAL query_cache_type= default;
> >  SET GLOBAL query_cache_size= default;
>
> BR
>  Sergei
> --
> Sergei Petrunia, Software Developer
> MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog
>
>
>
___
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

[Maria-developers] Archive Storage Engine

[Varun Gupta]

Hi,

Here is the issue https://jira.mariadb.org/browse/MDEV-11645 on which I
need suggestions

The observations made are:

- in 10.1, varchar endspace was bzero-ed
archive depended on it to not pack garbage

- in 10.2, varchar endspace is not bzero-ed
 which breaks the archive SE.

Was this change intended?

If yes, then we should update ha_archive to fill the garbage parts of the
buffer with zeroes that will cause it to be (slightly?) slower  but there's
no other way.


BR,
Varun
___
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] Archive Storage Engine

[Varun Gupta]

Hi,

The commit is:

commit c697ddc315429c56dec567b2fe85cfe5c03834ea
Author: Sergei Golubchik 
Date:   Mon Dec 12 15:47:51 2016 +0100

cleanup: remove unused handler table flag

diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc

index 901fd48..9c21cb7 100644
--- a/sql/sql_insert.cc
+++ b/sql/sql_insert.cc

@@ -955,12 +955,7 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list,
 be overwritten by fill_record() anyway (and fill_record() does
not
 use default values in this case).
   */
-#ifdef HAVE_valgrind
-  if (table->file->ha_table_flags() &&
HA_RECORD_MUST_BE_CLEAN_ON_WRITE)
-restore_record(table,s->default_values);   // Get empty record
-  else
-#endif
-table->record[0][0]= share->default_values[0];
+  table->record[0][0]= share->default_values[0];





On Sun, Feb 5, 2017 at 8:10 PM, Sergei Golubchik  wrote:

> Hi, Varun!
>
> On Feb 05, Varun Gupta wrote:
> > Hi,
> >
> > Here is the issue https://jira.mariadb.org/browse/MDEV-11645 on which I
> > need suggestions
> >
> > The observations made are:
> >
> > - in 10.1, varchar endspace was bzero-ed
> > archive depended on it to not pack garbage
> >
> > - in 10.2, varchar endspace is not bzero-ed
> >  which breaks the archive SE.
> >
> > Was this change intended?
> >
> > If yes, then we should update ha_archive to fill the garbage parts of the
> > buffer with zeroes that will cause it to be (slightly?) slower  but
> there's
> > no other way.
>
> Could you find what commit caused it? git bisect is quite useful for
> that.
>
> Regards,
> Sergei
> Chief Architect MariaDB
> and secur...@mariadb.org
>
___
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] [Commits] 5ac7ee3: MDEV-11196: Error:Run-Time Check Failure #2 - Stack around the variable 'key_buff'

[Varun Gupta]

On Wed, May 24, 2017 at 9:45 PM, Sergey Petrunia  wrote:

> Hi Varun,
>
> Good to see that the feedback from the last time was addressed.
>
> There is another issue, though.
>
> Consider a case where some (but not necessarily all) PK columns are
> present in
> the secondary key:
>
> create table t1 (
>   pk1 VARCHAR(1000) not null,
>   pk2 INT  not null,
>   col2 INT not null,
>
>   KEY key1(pk1, col2),
>   PRIMARY KEY(pk1, pk2)
> ) CHARSET utf8 engine=innodb;
> insert into t1 select a,a,a from ten;
>
> In this case, key1 still has a suffix, but the suffix only includes columns
> that are not already present in the key.
> That is, the "real" definition of key1 is
>
>   (pk1, col2, pk2)
>
> and not
>
>   (pk1, col2, pk1, pk2)
> .
>
> Let's try that.
>
> explain
> select * from t1 force index(key1) where pk1 < 'zz' and col2 <=2 and pk2
> <=4;
>
> Without the patch, I have all 3 key parts used:
>
> | {
>   "query_block": {
> "select_id": 1,
> "table": {
>   "table_name": "t1",
>   "access_type": "range",
>   "possible_keys": ["key1"],
>   "key": "key1",
>   "key_length": "3010",
>   "used_key_parts": ["pk1", "col2", "pk2"],
>   "rows": 1,
>   "filtered": 100,
>   "attached_condition": "t1.pk1 <= '0' and t1.col2 <= 2 and t1.pk2 <=
> 4",
>   "using_index": true
> }
>   }
> } |
>
> With the patch, I only see the explicitly defined key parts used:
>
> | {
>   "query_block": {
> "select_id": 1,
> "table": {
>   "table_name": "t1",
>   "access_type": "range",
>   "possible_keys": ["key1"],
>   "key": "key1",
>   "key_length": "3006",
>   "used_key_parts": ["pk1", "col2"],
>   "rows": 1,
>   "filtered": 100,
>   "attached_condition": "t1.pk1 <= '0' and t1.col2 <= 2 and t1.pk2 <=
> 4",
>   "using_index": true
> }
>   }
> } |
>
> Which I think is wrong, because the key length never exceeds MAX_KEY_LEN.
>
> This can be easily fixed by appyling this patch over your patch:
>
> --- table.cc.varun  2017-05-24 18:30:39.076341082 +0300
> +++ table.cc2017-05-24 19:04:25.637740027 +0300
> @@ -2164,10 +2164,13 @@
>for (i= 0; i < add_keyparts_for_this_key; i++)
>{
>  uint pk_part_length= key_first_info->key_part[i].
> store_length;
> -if (ext_key_length + pk_part_length > MAX_KEY_LENGTH)
> +if (keyinfo->ext_key_part_map & 1<  {
> -  add_keyparts_for_this_key= i;
> -  break;
> +  if (ext_key_length + pk_part_length > MAX_KEY_LENGTH)
> +  {
> +add_keyparts_for_this_key= i;
> +break;
> +  }
>  }
>  ext_key_length+= pk_part_length;
>}
>
> Do you agree with this change? Any amendments? If it's ok, please add this
> (together with the testcase) into your patch.


Yes I agree with the above change. I have added the testcase to the patch
and would be sending it in another commit.

>
>
> I've checked MySQL 5.7 also, and they do something weird:
> ...
> insert into t1 select A.a + 1000*B.a, A.a + 1000*B.a, A.a + 1000*B.a from
> one_k A, ten B;
> explain format=json
> select * from t1 force index(key1) where pk1 <= '0' and col2 <=2 and pk2
> <=4;
> EXPLAIN
> {
>   "query_block": {
> "select_id": 1,
> "cost_info": {
>   "query_cost": "1.41"
> },
> "table": {
>   "table_name": "t1",
>   "access_type": "range",
>   "possible_keys": [
> "key1"
>   ],
>   "key": "key1",
>   "used_key_parts": [
> "pk1"
>   ],
>   "key_length": "3010",
>   "rows_examined_per_scan": 1,
>   "rows_produced_per_join": 0,
>   "filtered": "11.11",
>   "using_index": true,
>   "cost_info": 

Re: [Maria-developers] eq_range_index_dives_limit

[Varun Gupta]

Hi Ian,
The task was mainly to set a value greater than 0. So on the optimizer call
it was decided that we can raise it to 200 too. I will do it in a new patch.

On Sat 2 Feb, 2019, 12:36 AM Ian Gilfillan  I see the eq_range_index_dive_limit default has been set to 10 in
> 10.4.3. I see some different EXPLAIN results in the commit, but I don't
> see any notes on why or how 10 was chosen over any other value. MySQL
> increased the default to 200 from MySQL 5.7.4, based it seems on some
> community feedback that 10 was too low, for example:
>
> https://www.facebook.com/notes/mysql-at-facebook/eq_range_index_dive_limit-system-variable-in-mysql-56/10151533648715933.
>
> What was the thinking behind choosing 10 as the new default?
>
> On 2018/10/12 12:58, Sergey Petrunia wrote:
> > Hello,
> >
> > So, MDEV-16934 introduced eq_range_index_dives_limit into 10.2.8 and
> 10.3.0.
> >
> > The default was set to 0 (which means no limit) in order to not introduce
> > optimizer behavior change into stable versions.
> >
> > The question is: should 10.4 also have 0 by default or we can set it to
> some
> > finite limit? MySQL's default value is 10.
> >
> > BR
> >   Sergei
>
>
> ___
> 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
>
___
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] Review for: b4696ee97e684 : MDEV-15777: Support Early NULLs filtering-like restrictions in the range optimizer

[varun gupta]

On Wed, May 22, 2019 at 9:44 PM Sergey Petrunia  wrote:

> Hi Varun,
>
> Please find review input below. Only cosmetic changes are requested.
>
> I also assume that after this is pushed
> - we add a optimizer trace printout into 10.4
>
has created MDEV-19567 for this

> - the feature is enabled by default in 10.5
>
let us decide the name of the optimizer flag and then I will create the
issue for this

> ?  (do we need separate MDEVs for this?)
>
> One final question is the choice of name for @@optimizer_switch flag. Let
> me get back to
> you on this.
>
> > commit b4696ee97e6846ba49ef203cfc7189f50c1e53a7
> > Author: Varun Gupta 
> > Date:   Mon May 20 15:14:30 2019 +0530
> >
> > MDEV-15777: Support Early NULLs filtering-like restrictions in the
> range optimizer
> >
> > For eqjoin conditions, we add a NOT NULL predicate so as to allow
> the range optimizer
> > to use the predicate and possibly create a range access on the given
> table.
> >
> > Example:
> >   select * from t1,t2 where t1.a=t2.a;  we have KEY(a) on t1
> >   we would inject a NOT NULL predicate t1.a IS NOT NULL for table t1
> >   this would allow the range optimizer to create ranges and we get a
> >   range access on table t1, then we will be able
> >   to do an early NULL filtering for ranges too.
> >
> > diff --git a/mysql-test/main/range.result b/mysql-test/main/range.result
> > index 32e0cf2868c..d75d6734277 100644
> > --- a/mysql-test/main/range.result
> > +++ b/mysql-test/main/range.result
> > @@ -3024,3 +3024,151 @@ drop table t1;
> >  #
> >  # End of 10.2 tests
> >  #
> > +#
> > +# MDEV-15777: Support Early NULLs filtering-like restrictions in the
> range optimizer
> > +#
> > +set @save_optimizer_switch= @@optimizer_switch;
> > +set @@optimizer_switch='null_rejecting_for_ranges=on';
> > +create table ten(a int);
> > +insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
> > +create table one_k(a int);
> > +insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B,
> ten C;
>
> Did we abandon the convention of naming the tables t1,t2,t3,... ?
> If not, please rename.
>
As decided,  I have kept this the same

>
> > +create table t1 (
> > +id int(10) unsigned NOT NULL,
> > +subset_id int(11) DEFAULT NULL,
> > +PRIMARY KEY (id),
> > +KEY t1_subset_id (subset_id));
> ...
>
> > diff --git a/sql/opt_range.cc b/sql/opt_range.cc
> > index ec7b3dbbd7a..cb7800d1ba6 100644
> > --- a/sql/opt_range.cc
> > +++ b/sql/opt_range.cc
> > @@ -2539,6 +2546,42 @@ int SQL_SELECT::test_quick_select(THD *thd,
> key_map keys_to_use,
> >  TRP_GROUP_MIN_MAX *group_trp;
> >  double best_read_time= read_time;
> >
> > +/*
> > +  For the range optimizer, null_rejecting_conds are just an extra
> condition
> > +  on which one can use index to create ranges.
> > +
> > +  Also here we try to allow consider range access for a column
> involed in a
> > +  null rejecting predicate only if it is the first keypart of an
> index.
> > +  Let try to make it clear with examples:
> > +
> > +  Query 1
> > +select * from t1,t2 where t1.a=2 and t1.b=t2.b;
> > +and we have a key(a,b)
> > +So lets consider range access for table t1:
> > +Null rejecting predicate : t1.b IS NOT NULL
> > +but the column t1.b is NOT the first keypart in the index,
> > +so we don't consider it while building ranges
> > +
> > +  Query 2
> > +select * from t1,t2 where t1.a=t2.a and t1.b=t2.b;
> > +and we have a key (a,b)
> > +
> > +So lets consider range access for table t1:
> > +Null rejecting predicate : t1.a IS NOT NULL AND t1.b IS NOT NULL
> > +so here t1.a is the first keypart in the index, so we consider
> > +the predicate for building the ranges.
> > +
> > +The first keypart prerequisite is made as we try to reuse range
> > +estimates (because they are more accurate) at different places
> > +and we needed to make sure that correct/accurate estimates are
> used there.
> > +*/
> > +
> > +if (null_rejecting_conds)
> > +  not_null_cond_tree= null_rejecting_conds->get_mm_tree(¶m,
> > +
> &null_rejecting_conds);
> Please fix indentation and fit within 80 chars. ^
> And also use curly braces as the code inside if doesn't fit on one line.
>
Done

>
> > +if (not_null_c

[Maria-developers] Regarding approach used for Early NULL filtering in the range optimizer(MDEV-15777)

[Varun Gupta]

Hi Igor,
After discussing with Sergey, we came up with these conclusions as to why
we used the approach of going through all the keyuses in the KEYUSE array

Cases to consider:
we have an index on column a

1) a OP const
where OP can be (not_null_tables() currently, but not not_null_columns()).

b) the conditions in form of arbitrary functions are not as frequently used
as
ref access conditions.

to sum up a) and b) - doable but will require a lot of effort.

Do you have any specific practically relevant example in mind that we should
handle?

Regards,
Varun and Sergey
___
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] [Commits] 3ead2cea95c: MDEV-13266: Race condition in ANALYZE TABLE / statistics collection

[Varun Gupta]

On Fri, May 1, 2020 at 2:15 AM Sergey Petrunia  wrote:

> Hi Varun,
>
>
> On Sun, Apr 12, 2020 at 09:08:37PM +0530, Varun wrote:
> > revision-id: 3ead2cea95c32b7ceaf6e6ec81f7afbd9137cfe9
> (mariadb-10.2.31-103-g3ead2cea95c)
> > parent(s): d565895bbd8e2a163be48b9bac51fccbf3949c80
> > author: Varun Gupta
> > committer: Varun Gupta
> > timestamp: 2020-04-12 21:05:36 +0530
> > message:
> >
> > MDEV-13266: Race condition in ANALYZE TABLE / statistics collection
> >
> > Fixing a race condition while collecting the engine independent
> statistics.
> > The issue here was when the statistics was collected on specific indexes
> > then because of some race condition the statistics for indexes was not
> > collected. The TABLE::keys_in_use_for_query was set to 0 in such cases.
> >
> > This happens when the table is opened from TABLE_SHARE instead of the
> > table share stored in table_cache.
>
> It would be nice to write down the race condition that we've caught (in
> case we
> need this info later)
>
> Is my understanding correct: the error scenario is as follows:
>
> Thread1> start running "ANALYZE TABLE t PERISTENT FOR COLUMNS (..) INDEXES
> ($list)"
> Thread1> Walk through $list and save it in TABLE::keys_in_use_for_query
>
> Thread1> Close/re-open tables
> Thread2> Make some use of table t. This involves taking table t from
> Thread2> the table cache, and putting it back (with
>  TABLE::keys_in_use_for_query reset to 0)
>
> Thread1> continue collecting EITS stats. Since
> TABLE::keys_in_use_for_query we
> will not collect statistics for indexes in $list.
>
> Please confirm (and if not, describe the race condition).
>
> Yes this is correct, I will add the above description to the commit message


> The patch also introduces this behaviour:
>
> analyze table ... persistent for ... indexes(no_such_index);
>
> will now cause engine statistics to be still collected. Before the patch
> it exited with an error.
>
> But then, this is consistent with what happens for
>
> analyze table ... persistent for columns(no_such_column) ...
>
> So I guess this is ok.
>

I am not able to understand what you mean here, for no_such_index I think
you mean an empty list and for such case I think collecting statistics fro
all the indexes is fine.
If this means something else, then lets discuss it


Regards,
Varun


> > ---
> >  sql/sql_admin.cc | 46 +-
> >  1 file changed, 21 insertions(+), 25 deletions(-)
> >
> > diff --git a/sql/sql_admin.cc b/sql/sql_admin.cc
> > index ab95fdc340c..0613495f202 100644
> > --- a/sql/sql_admin.cc
> > +++ b/sql/sql_admin.cc
> > @@ -769,31 +769,6 @@ static bool mysql_admin_table(THD* thd, TABLE_LIST*
> tables,
> >  (table->table->s->table_category == TABLE_CATEGORY_USER &&
> >   (get_use_stat_tables_mode(thd) > NEVER ||
> >lex->with_persistent_for_clause));
> > -
> > -
> > -  if (!lex->index_list)
> > -  {
> > -tab->keys_in_use_for_query.init(tab->s->keys);
> > -  }
> > -  else
> > -  {
> > -int pos;
> > -LEX_STRING *index_name;
> > -List_iterator_fast it(*lex->index_list);
> > -
> > -tab->keys_in_use_for_query.clear_all();
> > -while ((index_name= it++))
> > - {
> > -  if (tab->s->keynames.type_names == 0 ||
> > -  (pos= find_type(&tab->s->keynames, index_name->str,
> > -  index_name->length, 1)) <= 0)
> > -  {
> > -compl_result_code= result_code= HA_ADMIN_INVALID;
> > -break;
> > -  }
> > -  tab->keys_in_use_for_query.set_bit(--pos);
> > -}
> > -  }
> >  }
> >
> >  if (result_code == HA_ADMIN_OK)
> > @@ -878,6 +853,27 @@ static bool mysql_admin_table(THD* thd, TABLE_LIST*
> tables,
> >}
> >tab->file->column_bitmaps_signal();
> >  }
> > +if (!lex->index_list)
> > +  tab->keys_in_use_for_query.init(tab->s->keys);
> > +else
> > +{
> > +  int pos;
> > +  LEX_STRING *index_name;
> > +  List_iterator_fast it(*lex->index_list);
> > +
> > +  tab->keys_in_use_for_query.clear_all();
> > +  while ((index_name= it++))
> > +  {
> > +if (tab->s-&g

Re: [Maria-developers] [Commits] c776cad5f8e: MDEV-22702: Assertion `!field->is_null()' failed in my_decimal::my_decimal

[varun gupta]

Hi Sergey,


On Thu, Aug 6, 2020 at 4:31 PM Sergey Petrunia  wrote:

> Hi Varun,
>
> First: I can still get the assertion failure if I run this:
>
> set @@sql_mode='only_full_group_by';
> select min(1 mod a1), bit_or(a2) over () from t1;
>
> As far as I understand, the query is not valid for
> only_full_group_by mode. (Window functions as such are ok,
> but here window function is using a2, which cannot be accessed in
> the post-grouping context).
>
> Well this is a good catch. The code currently expects that if we have an
aggregate function and a non-aggregate field in the SELECT
then the query is not valid in ONLY_FULL_GROUP_BY mode. I investigated that
the argument to the window function (here column a2 was
not marked as a non-aggregated field). I have fixed this and the query gets
rejected in ONLY_FULL_GROUP_BY mode.


> More input below.
>
> On Wed, Jul 08, 2020 at 02:58:31PM +0530, Varun wrote:
> > revision-id: c776cad5f8ecf2675510deeb55724d7255a52503
> (mariadb-10.4.11-267-gc776cad5f8e)
> > parent(s): cc0dca366357651ddb549e31a12b1ecd39c7380e
> > author: Varun Gupta
> > committer: Varun Gupta
> > timestamp: 2020-07-08 14:58:17 +0530
> > message:
> >
> > MDEV-22702: Assertion `!field->is_null()' failed in
> my_decimal::my_decimal
> >
> > With implicit grouping with window functions, we need to make sure that
> all the
> > fields inside the window functions are nullable as any non-aggregated
> field can
> > produce a NULL value.
>
> ...
> > diff --git a/sql/sql_lex.h b/sql/sql_lex.h
> > index 0983cea44d0..16a0b3b08a6 100644
> > --- a/sql/sql_lex.h
> > +++ b/sql/sql_lex.h
> > @@ -1510,6 +1510,8 @@ class st_select_lex: public st_select_lex_node
> >}
> >
> >bool have_window_funcs() const { return (window_funcs.elements !=0); }
> > +  uint32 get_number_of_window_funcs() const
> > +  { return (uint32)window_funcs.elements; }
>
> Why use uint32 when n_sum_items is uint, and elements is also uint?
> I suggest just keep using uint.
>

Changed.

>
> >ORDER *find_common_window_func_partition_fields(THD *thd);
> >
> >bool cond_pushdown_is_allowed() const
> > diff --git a/sql/sql_select.cc b/sql/sql_select.cc
> > index 0ca5ab23288..05463efe9a5 100644
> > --- a/sql/sql_select.cc
> > +++ b/sql/sql_select.cc
> > @@ -1201,6 +1201,18 @@ JOIN::prepare(TABLE_LIST *tables_init,
> >  break;
> >}
> >  }
> > +/*
> > +  If the query has a window function with an aggregate function,
> > +  then also we have a mix of elements with and without grouping.
> > +  Window function can be in the ORDER BY clause too so the check
> > +  is made separately.
>
> Wording could be better.
> I don't understand what did you mean to say about ORDER BY. can you
> elaborate?
>

What i meant here was that window functions can be present both in the
SELECT LIST and ORDER BY clause, so a separate check is made
here. Just above this we iterate over the SELECT LIST for non-aggregate
fields and aggregate functions. If the window function is present
in the ORDER BY clause iterating over the SELECT LIST would not have helped
that is the check is done separately.

But looks like we don't do this check for aggregate functions in the ORDER
BY clause.
Example:
   SELECT a FROM t1 ORDER BY sum(a)
this does not set mixed_implicit_grouping to true. But looks like it is ok
if it is not set. We are just going to send a row with all NULL values to
the client. With window functions this is an issue because we need to
execute the window function for the one row in the output with implicit
grouping


>
> > +  Window function is inherited from Item_sum so each window
> function is
> > +  also registered as a sum item, so need to check that we have an
> > +  explicit aggregate function also in the query.
>
> what's "explicit aggregate" ?  A regular aggregate function (i.e. not a
> window
> function)?
>
Yes explicit aggregate means aggregate function here.

>
> > +*/
> > +if (select_lex->have_window_funcs() &&
> > +select_lex->get_number_of_window_funcs() <
> select_lex->n_sum_items)
> > +  mixed_implicit_grouping= true;
> >}
> >
> >table_count= select_lex->leaf_tables.elements;
>
> BR
>  Sergei
> --
> Sergei Petrunia, Software Developer
> MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog
>
>
>
___
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] MDEV-21829:Followup: On Unique object and variable-size keys

[varun gupta]

Hi Sergey,



On Sun, Sep 13, 2020 at 7:41 PM Sergey Petrunia  wrote:

> Hi Varun,
>
> I was looking at the code for MDEV-21829, trying to understand whether
> there
> are any issues with how Unique object was extended to handle variable-size
> keys.
>
> == TREE object (the RB tree) ==
>
> TREE (the structure and functions that operate on it) support
> variable-sized
> keys, however the keys "are expected to know" their size.
>
> tree_insert() has "uint key_size" argument. This way, tree_insert() knows
> how many bytes to allocate and copy when putting the key into the tree.
>
> Well for the tree for fixed size we also set the size during the
initialization phase
init_tree() function has
   tree->size_of_element= size > 0 ? (uint) size : 0;

For Unique with fixed size keys we use init_tree to pass the fixed size and
then we send 0 as the argument
to key_size in tree_insert().
The allocated size used in tree_insert is:
   alloc_size=sizeof(TREE_ELEMENT)+key_size+tree->size_of_element;


> The rest of the TREE code doesn't seem to care about the size of the keys.
>
> When TREE code needs to compare a couple of keys, it will call
> TREE::compare
> callack. The callback only receives key pointers as arguments, that is, it
> is
> expected to infer key size from the contents of the key.
>
> The same happens when when walking the tree. Only "uchar *key" is passed
> to
> tree_walk_action callback. The walk function has to figure out they key
> size
> on its own.
>
> == Unique ==
>
> Unique class cannot do the same what TREE functions function do. It needs
> to be
> aware of the sizes of the values it is storing:
> - It will need to pass value size to tree_insert()
> - It will need to know value size to write it into tmp file.
> - It will need to read it back when merging.
>
> How does it know it?
> Unique::merge() and Unique::walk() with merge_walk() functions do make
> certain
> assumptions about the data format being used.
>
> == The review points ==
>
> Is this bad?
>
> I think tight coupling between the Unique object and the specifics of the
> data format it is storing is bad. What is worse is that currently this
> coupling
> is implicit, it is not immediately apparent to somebody just looking at the
> code.
>

One important thing to keep in mind here is when Unique is flushed to disk
then the code that does the merging of the different chunks of file and
returning the results shares the code with filesort merge procedure.
So I think either we move the code for Unique separately or we will have to
make our data format be similar to the one used by packed sort keys
for filesort.

Currently the format for packed sort-keys is like:

..

where keypart_i_length is optional and only stored for packable key parts
like CHAR, VARCHAR.



> Possible ways out:
> 1. Explicitly declare in Unique class that variable-sized records must use
>   certain data format.


> 2. Isolate the Unique from the specifics of the data format used.
>
> 2A. Let Unique get the size of the key as argument of unique_add() and then
>   keep it together with the key. This creates some RAM/storage overhead.
>
> 2B. Pass a read_size() callback function which would get the size from the
> data?
>
>  The way packed keys are used is that the length is added as 4 bytes to
the record
in the beginning and then a static function is used to read its length
whenever needed.
Also I moved the implementation for packed keys in a separate class so that
we can extend unique in the future too if needed.


Regards,
Varun


> BR
>  Sergei
> --
> Sergei Petrunia, Software Developer
> MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog
>
>
>
> ___
> 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
>
___
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] [Commits] cdc305c8dd8: MDEV-19620: Changing join_buffer_size causes different results

[varun gupta]

On Tue, Dec 29, 2020 at 7:59 PM Sergey Petrunia  wrote:

> Hi Varun,
>
> Please find input below.
>
> On Mon, Dec 28, 2020 at 02:34:40PM +0530, varun wrote:
> > revision-id: cdc305c8dd89a726e09e5fe70ff890d06609cbfb
> (mariadb-10.3.21-309-gcdc305c8dd8)
> > parent(s): 043bd85a574a88856ab9c6d497e682ed06fe45e9
> > author: Varun Gupta
> > committer: Varun Gupta
> > timestamp: 2020-12-28 14:12:14 +0530
> > message:
> >
> > MDEV-19620: Changing join_buffer_size causes different results
> >
> > The scenario here is that query refinement phase decides to use a hash
> join.
> > When the join buffers are allocated in the JOIN::init_join_caches, for a
> table
> > the size exceeds the value for join_buffer_space_limit (which is the
> limit of the
> > space available for all join buffers). When this happens then we
> disallow join
> > buffering for the table, this is done in the revise_cache_usage and
> set_join_cache_denial.
> >
> > In this issue the hash key is created on an index for which ref access
> is possible, so
> > when we disallow hash join then instead of switching to REF access we
> switch to a table
> > scan. This is a problem because the equijoin conditions for which a
> lookup can be made
> > are not attached to the table(or are not evaluated for the table). This
> leads to incorrect
> > results.
> >
> > The fix here would be to switch to using a lookup because it was picked
> by the join planner
> > to be more efficient than the table scan.
> >
> > ---
> >  mysql-test/main/join_cache.result | 138
> ++
> >  mysql-test/main/join_cache.test   | 105 +
> >  sql/sql_select.cc |  69 +++
> >  sql/sql_select.h  |   6 ++
> >  4 files changed, 304 insertions(+), 14 deletions(-)
> >
> > diff --git a/mysql-test/main/join_cache.result
> b/mysql-test/main/join_cache.result
> > index 3d1d91df997..e58503f422f 100644
> > --- a/mysql-test/main/join_cache.result
> > +++ b/mysql-test/main/join_cache.result
> > @@ -6128,4 +6128,142 @@ EXPLAIN
> >}
> >  }
> >  drop table t1,t2,t3;
> > +#
> > +#  MDEV-19620: Changing join_buffer_size causes different results
> > +#
> > +SET @save_join_cache_level= @@join_cache_level;
> > +SET @save_join_buffer_size= @@join_buffer_size;
> > +SET @save_join_buffer_space_limit= @@join_buffer_space_limit;
> > +SET join_cache_level = 3;
> > +CREATE TABLE t1 (i2 VARCHAR(500), c2 VARCHAR(500)) ENGINE=MyISAM;
> > +INSERT INTO t1 VALUES ('1','v'),('7','s');
> > +CREATE TABLE t2 (pk3 INT PRIMARY KEY, i3 VARCHAR(300), c3 VARCHAR(500))
> ENGINE=MyISAM;
> > +INSERT INTO t2 VALUES (1,'7','g'),(2,'4','p'),(3,'1','q');
> > +INSERT INTO t2 VALUES (4,'7','g'),(5,'4','p'),(6,'1','q');
> > +INSERT INTO t2 VALUES (16,'7','g'),(17,'4','p'),(28,'1','q');
> > +#
> > +# Hash join + table Scan on t2
> > +#
> > +set join_buffer_size=1024;
> > +set join_buffer_space_limit=2048;
> > +EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON (c3=c2);
> > +id   select_type table   typepossible_keys   key key_len
> ref rowsExtra
> > +1SIMPLE  t1  ALL NULLNULLNULLNULL2
> > +1SIMPLE  t2  ALL NULLNULLNULLNULL9
>  Using where
> > +SELECT * FROM t1 LEFT JOIN t2 ON (c3=c2);
> > +i2   c2  pk3 i3  c3
> > +1v   NULLNULLNULL
> > +7s   NULLNULLNULL
> > +set join_buffer_space_limit=262144;
> > +EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON (c3=c2);
> > +id   select_type table   typepossible_keys   key key_len
> ref rowsExtra
> > +1SIMPLE  t1  ALL NULLNULLNULLNULL2
> > +1SIMPLE  t2  hash_ALLNULL#hash#$hj   503
>  test.t1.c2  9   Using where; Using join buffer (flat, BNLH join)
> > +SELECT * FROM t1 LEFT JOIN t2 ON (c3=c2);
> > +i2   c2  pk3 i3  c3
> > +1v   NULLNULLNULL
> > +7s   NULLNULLNULL
> > +#
> > +# HASH join + ref access on t2
> > +#
> > +ALTER TABLE t2 ADD KEY k1(c3);
> > +set join_buffer_size=1024;
> > +set join_buffer_space_limit=2048;
> > +EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON (c3=c2);
> > +id   select_type table   typ

[Maria-developers] Getting started

[Varun Gupta]

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
___
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

[Maria-developers] Aggregate stored functions [MDEV-7773]

[Varun Gupta]

Well i have checked out the syntax for the databases for aggregate
functions. I thought using the syntax similar to that of HSQL would be
good. In it if we are having N tuples then we are computing the values for
the N rows and then after that we make another call and return the value
for the function. Well this seems very reasonable to do. Using PostgreSQL
  we
need to have two regular functions but if we have an aggregate function
like Count(column_name) , then this would be done in just a single function
, I guess we would not need two functions for it .

Regards,
Varun
___
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] Aggregate stored functions [MDEV-7773]

[Varun Gupta]

Well in HSQL we have the limitation of having maximum of 4 arguments , but
in our syntax we can increase the number of arguments.The main thing is
what I understood from HSQL and PostgreSQL is that an aggregate function
would have 2 states.
1)  when we compute the result that is the result keeps on getting updated
on various calls to the function
2)  when we return the result

HSQL does that using the flag so we need to call an additional time with
flag == TRUE so that result is returned .
While in PostgreSQL we split the work into two regular functions . The
problem is that the second function in PostgreSQL is optional so that adds
to a bit of confusion.

The syntax for ORACLE I did not understand properly, would give it a try
again .

For SYBASE I have been going through:
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc01034.1510/doc/html/asc1238775307180.html

For SQL server
https://msdn.microsoft.com/en-in/library/ms182741.aspx

DB2 does not support user-defined aggregate functions
http://www.ibm.com/developerworks/data/library/techarticle/0309stolze/0309stolze.html





On Wed, Mar 2, 2016 at 4:10 PM, Sergei Golubchik  wrote:

> Hi, Varun!
>
> On Mar 02, Varun Gupta wrote:
> > Well i have checked out the syntax for the databases for aggregate
> > functions. I thought using the syntax similar to that of HSQL would be
> > good. In it if we are having N tuples then we are computing the values
> for
> > the N rows and then after that we make another call and return the value
> > for the function. Well this seems very reasonable to do. Using PostgreSQL
> > <http://www.postgresql.org/docs/8.3/static/sql-createaggregate.html>  we
> > need to have two regular functions but if we have an aggregate function
> > like Count(column_name) , then this would be done in just a single
> function
> > , I guess we would not need two functions for it .
>
> Yes, I agree about PostgreSQL syntax. An aggregate function there
> has "artefacts" - two regular functions that show up in the list of
> functions and can be called directly too. I think this is confusing.
>
> HSQL syntax, I mean, exactly as in HSQL, is full of random limitations.
> http://hsqldb.org/doc/guide/sqlroutines-chapt.html#src_aggregate_functions
>
> In HSQL an aggregate function can have only *one* argument, and the
> state is, always, exactly *two* variables. We, of course, won't have
> these limitation, if we'll do HSQL-style syntax.
>
> But this syntax is also kind of hackish. One function that has two
> different semantics and the 'flag' argument that selects which one to
> use. And many parameters in the function declaration, while only one is
> in the function invocation.
>
> Oracle uses an object-oriented syntax.
>
> Did you find any other DBMS that support this feature?
>
> Regards,
> Sergei
> Chief Architect MariaDB
> and secur...@mariadb.org
>
___
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] Aggregate stored functions [MDEV-7773]

[Varun Gupta]

With the cursor approach, I think it looks more easier to understand . Also
it covers the point that the state has to be saved when the function is
called for different values of x ,so we know the approach is correct. We
are using the function attribute X in calculating the sum. I don't
understand what you mean by not using declared argument directly.
Well at least the cursor method looks far simpler from the ones we
discussed earlier .

On Wed, Mar 2, 2016 at 6:41 PM, Sergei Golubchik  wrote:

> Hi, Varun!
>
> On Mar 02, Varun Gupta wrote:
> > Well in HSQL we have the limitation of having maximum of 4 arguments ,
> but
> > in our syntax we can increase the number of arguments.The main thing is
> > what I understood from HSQL and PostgreSQL is that an aggregate function
> > would have 2 states.
> > 1)  when we compute the result that is the result keeps on getting
> updated
> > on various calls to the function
> > 2)  when we return the result
> >
> > HSQL does that using the flag so we need to call an additional time with
> > flag == TRUE so that result is returned .
> > While in PostgreSQL we split the work into two regular functions . The
> > problem is that the second function in PostgreSQL is optional so that
> adds
> > to a bit of confusion.
>
> What about other ideas presented in MDEV-7773? I like the cursor one.
> Compare the standard SQL function:
>
>   CREATE FUNCTION avg() RETURNS DOUBLE
>   BEGIN
> DECLARE count INT DEFAULT 0;
> DECLARE sum DOUBLE DEFAULT 0;
> DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' RETURN sum/count;
> DECLARE x DOUBLE;
> DECLARE cur1 CURSOR FOR SELECT col1 FROM t1;
> OPEN cur1;
> LOOP
>   FETCH cur1 INTO x;
>   SET count:=count+1;
>   SET sum:=sum+x;
> END LOOP;
>   END
>
> This is a regular standard non-aggregate function that returns an
> average of all values in a column 'col1' of the table 't1'.
> The cursor idea of a syntax for an aggregate function uses the same
> familiar logic:
>
>   CREATE AGGREGATE FUNCTION avg(x DOUBLE) RETURNS DOUBLE
>   BEGIN
> DECLARE count INT DEFAULT 0;
> DECLARE sum DOUBLE DEFAULT 0;
> DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' RETURN sum/count;
> LOOP
>   FETCH GROUP NEXT ROW;
>   SET count:=count+1;
>   SET sum:=sum+x;
> END LOOP;
>   END
>
> This looks simple, familiar, natural, leaves no artefacts.
> What I don't like is that a function's declared argument is not used
> directly. Looks a bit artificial :(
>
> > The syntax for ORACLE I did not understand properly, would give it a try
> > again.
>
> Don't bother, it's an object oriented approach. An aggregate function is
> an *object*. Object has different methods for result and for getting
> updated. Object data fields used to store the intermediate state.
> This is quite natural, if the DBMS supports objects. But we don't.
>
> > For SYBASE I have been going through:
> >
> http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc01034.1510/doc/html/asc1238775307180.html
>
> Okay, so "External C/C++."
>
> > For SQL server
> > https://msdn.microsoft.com/en-in/library/ms182741.aspx
>
> Same, "implementation is defined in a class of an assembly in the .NET
> Framework"
>
> > DB2 does not support user-defined aggregate functions
> >
> http://www.ibm.com/developerworks/data/library/techarticle/0309stolze/0309stolze.html
>
> Right.
>
> Regards,
> Sergei
> Chief Architect MariaDB
> and secur...@mariadb.org
>
___
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] Aggregate stored functions [MDEV-7773]

[Varun Gupta]

With the syntax more or less clear to us , can we have a discussion about
how we have to go on with the implementation for the aggregate functions.
Firstly I thought I should look at the implementation of CREATE FUNCTION,
any other suggestions ?

On Thu, Mar 3, 2016 at 4:56 PM, Varun Gupta 
wrote:

> With the cursor approach, I think it looks more easier to understand .
> Also it covers the point that the state has to be saved when the function
> is called for different values of x ,so we know the approach is correct. We
> are using the function attribute X in calculating the sum. I don't
> understand what you mean by not using declared argument directly.
> Well at least the cursor method looks far simpler from the ones we
> discussed earlier .
>
> On Wed, Mar 2, 2016 at 6:41 PM, Sergei Golubchik  wrote:
>
>> Hi, Varun!
>>
>> On Mar 02, Varun Gupta wrote:
>> > Well in HSQL we have the limitation of having maximum of 4 arguments ,
>> but
>> > in our syntax we can increase the number of arguments.The main thing is
>> > what I understood from HSQL and PostgreSQL is that an aggregate function
>> > would have 2 states.
>> > 1)  when we compute the result that is the result keeps on getting
>> updated
>> > on various calls to the function
>> > 2)  when we return the result
>> >
>> > HSQL does that using the flag so we need to call an additional time with
>> > flag == TRUE so that result is returned .
>> > While in PostgreSQL we split the work into two regular functions . The
>> > problem is that the second function in PostgreSQL is optional so that
>> adds
>> > to a bit of confusion.
>>
>> What about other ideas presented in MDEV-7773? I like the cursor one.
>> Compare the standard SQL function:
>>
>>   CREATE FUNCTION avg() RETURNS DOUBLE
>>   BEGIN
>> DECLARE count INT DEFAULT 0;
>> DECLARE sum DOUBLE DEFAULT 0;
>> DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' RETURN sum/count;
>> DECLARE x DOUBLE;
>> DECLARE cur1 CURSOR FOR SELECT col1 FROM t1;
>> OPEN cur1;
>> LOOP
>>   FETCH cur1 INTO x;
>>   SET count:=count+1;
>>   SET sum:=sum+x;
>> END LOOP;
>>   END
>>
>> This is a regular standard non-aggregate function that returns an
>> average of all values in a column 'col1' of the table 't1'.
>> The cursor idea of a syntax for an aggregate function uses the same
>> familiar logic:
>>
>>   CREATE AGGREGATE FUNCTION avg(x DOUBLE) RETURNS DOUBLE
>>   BEGIN
>> DECLARE count INT DEFAULT 0;
>> DECLARE sum DOUBLE DEFAULT 0;
>> DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' RETURN sum/count;
>> LOOP
>>   FETCH GROUP NEXT ROW;
>>   SET count:=count+1;
>>   SET sum:=sum+x;
>> END LOOP;
>>   END
>>
>> This looks simple, familiar, natural, leaves no artefacts.
>> What I don't like is that a function's declared argument is not used
>> directly. Looks a bit artificial :(
>>
>> > The syntax for ORACLE I did not understand properly, would give it a try
>> > again.
>>
>> Don't bother, it's an object oriented approach. An aggregate function is
>> an *object*. Object has different methods for result and for getting
>> updated. Object data fields used to store the intermediate state.
>> This is quite natural, if the DBMS supports objects. But we don't.
>>
>> > For SYBASE I have been going through:
>> >
>> http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc01034.1510/doc/html/asc1238775307180.html
>>
>> Okay, so "External C/C++."
>>
>> > For SQL server
>> > https://msdn.microsoft.com/en-in/library/ms182741.aspx
>>
>> Same, "implementation is defined in a class of an assembly in the .NET
>> Framework"
>>
>> > DB2 does not support user-defined aggregate functions
>> >
>> http://www.ibm.com/developerworks/data/library/techarticle/0309stolze/0309stolze.html
>>
>> Right.
>>
>> Regards,
>> Sergei
>> Chief Architect MariaDB
>> and secur...@mariadb.org
>>
>
>
___
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] Aggregate stored functions [MDEV-7773]

[Varun Gupta]

Hi Sergei,
I understand that having a cursor like syntax we would have  to write some
new code . What I would have liked to know is that the more details should
be discussed which need to be incorporated in the implementation of the
aggregate functions.  Basically what I am asking is what all details have
to be there in the implementation of the aggregate functions.


On Fri, Mar 4, 2016 at 10:21 PM, Sergei Golubchik  wrote:

> Hi, Varun!
>
> On Mar 04, Varun Gupta wrote:
> > With the syntax more or less clear to us, can we have a discussion about
> > how we have to go on with the implementation for the aggregate functions.
> > Firstly I thought I should look at the implementation of CREATE FUNCTION,
> > any other suggestions ?
>
> Sure, why not. But, as you've seen, that suggested syntax is almost
> identical to the standard one, so hopefully it'll need very little
> changes to the current CREATE FUNCTION implementation.
>
> More important would be to look at the function execution (not
> definition). This mainly happens in sql/sp_head.cc but other sql/sp_*
> files are also used.
>
> This cursor-like syntax introduces an new mode of executing stored
> routines. Currently, a routine (a procedure or a function) is executed
> completely from the beginning to the end. But with this cursor-like
> syntax, the server will need to start executing a stored function, stop
> at the FETCH statement, postpone the execution, then later continue from
> that point. There is no code to do it now.
>
> Regards,
> Sergei
> Chief Architect MariaDB
> and secur...@mariadb.org
>
___
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] Aggregate stored functions [MDEV-7773]

[Varun Gupta]

Hi Sergei,
Thanks that answers my question,  I will soon get back with questions .

On Sun, Mar 6, 2016 at 2:52 PM, Sergei Golubchik  wrote:

> Hi, Varun!
>
> On Mar 06, Varun Gupta wrote:
> > Hi Sergei,
> > I understand that having a cursor like syntax we would have  to write
> some
> > new code . What I would have liked to know is that the more details
> should
> > be discussed which need to be incorporated in the implementation of the
> > aggregate functions.  Basically what I am asking is what all details have
> > to be there in the implementation of the aggregate functions.
>
> I don't think I understand your question.
> But anyway to implement aggregate stored functions one would need
> 1. fix the parser to support the new syntax
> 2. fix the saving/loading code to store additional attributes (like,
>store the fact that the function is aggregate)
> 3. fix stored function execution code to be able to pause and resume the
>execution
> 4. create an Item_sum_sp to be able to use aggregate stored functions in
>queries.
>
> optional:
>
> 5. add support for window functions
>
> Regards,
> Sergei
> Chief Architect MariaDB
> and secur...@mariadb.org
>
___
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] Aggregate stored functions [MDEV-7773]

[Varun Gupta]

Hi Sergei,
I have gone through the code you suggested. I have got a good understanding
of what the code actually does. So now I wanted to start writing the
proposal for the idea. Can you tell me what all is needed to written in the
proposal. I am asking for a template or something that could guide me to
write the proposal.

Regards,
Varun
___
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] Aggregate stored functions [MDEV-7773]

[Varun Gupta]

Hi Sergei ,
 I have created a draft proposal and submitted to summerofcode website. I
haven't written the project timeline yet. Please I would appreciate if you
could go through it and leave comments so that I can make any necessary
corrections.

On Mon, Mar 21, 2016 at 2:34 PM, Sergei Golubchik  wrote:

> Hi, Varun!
>
> On Mar 21, Varun Gupta wrote:
> > Hi Sergei,
> >
> > 4) :create an Item_sum_sp to be able to use aggregate stored functions in
> >queries.
> > Does this mean we can have the aggregate stored functions in the SELECT ,
> > WHERE etc clauses?can you give me more details about this.
>
> Yes, of course. That's how functions are used, right? :)
>
>   SELECT AVG(x), COUNT(y), MAX(z) FROM t1 WHERE aaa=12 GROUP BY bbb;
>
> for example.
>
> Regards,
> Sergei
> Chief Architect MariaDB
> and secur...@mariadb.org
>
___
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] Aggregate stored functions [MDEV-7773]

[Varun Gupta]

Hi Sergei,
Sorry for the inconvenience , now you can comment on the document :)

On Tue, Mar 22, 2016 at 1:25 PM, Sergei Golubchik  wrote:

> Hi, Varun!
>
> On Mar 22, Varun Gupta wrote:
> > Hi Sergei ,
> > I have created a draft proposal and submitted to summerofcode website.
> > I haven't written the project timeline yet. Please I would appreciate
> > if you could go through it and leave comments so that I can make any
> > necessary corrections.
>
> I've looked it through, but I cannot comment, because your document is
> read-only. Open it for comments, please, then I'll leave some :)
>
> Regards,
> Sergei
> Chief Architect MariaDB
> and secur...@mariadb.org
>
___
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] Aggregate Stored Functions

[Varun Gupta]

Hi,
I have added the AGGREGATE keyword to the parser . Here is the link to the
repository https://github.com/varunraiko/aggregate-functions .


On Wed, May 18, 2016 at 9:50 PM, Sanja  wrote:

> Hi!
>
> If we get it automatically then of course it should be done, but I
> doubts... We will see.
>
> On Wed, May 18, 2016 at 6:18 PM, Sergei Golubchik 
> wrote:
>
>> Hi, Sanja!
>>
>> On May 18, Sanja wrote:
>> > >
>> > > No, sorry, this doesn't work. It works for procedures, but not for
>> > > functions. See:
>> > >
>> > > CREATE FUNCTION f1 (a INT) RETURNS INT
>> > > BEGIN
>> > >   RETURN SELECT f2(val) FROM t1 WHERE id=a;
>> > > END;
>> > >
>> > > CREATE FUNCTION f2 (b INT) RETURNS INT
>> > > BEGIN
>> > >   ...
>> > >   FETCH GROUP NEXT ROW;
>> > >   ...
>> > >   RETURN something;
>> > > END;
>> > >
>> > > Here, depending on what function is declared aggregate you will have
>> > > different results.
>> >
>> > I think in the first implementation we can have only one level
>> > functions.  if we will have time we can then expand it for calls of
>> > other functions.  But first the mechanism of temporary leaving then
>> > entering functions should be created (then it can be reused for
>> > recursive calls.
>>
>> First implementation - may be (althought I don't understand why - this
>> requires no extra coding, nested function calls will just work
>> automatically). But the first implementation should not choose to use
>> the syntax that makes this extension impossible.
>>
>> For example, Varun's project does not include window function support.
>> At all. But we will be able to add it later without redoing everything,
>> exising syntax can accomodate this new feature.
>>
>> Regards,
>> Sergei
>> Chief Architect MariaDB
>> and secur...@mariadb.org
>>
>
>
___
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] Aggregate Stored Functions

[Varun Gupta]

Hi,
I have added FETCH GROUP NEXT ROW to the parser and have tested it. I have
also made changes according to the review to my first.
New commit

https://github.com/MariaDB/server/commit/65782a2a6b5ad3db466953b6ea46f87de2616723

On Fri, May 20, 2016 at 11:18 AM, Vicențiu Ciorbaru 
wrote:

> Hi Sanja,
>
> Since we have not officially started the coding period, I figured that an
> exercise in adding a parser syntax would be a good first step.
>
> We can do automatic detection by looking at the cursor without to much
> extra work.
>
> Vicentiu
>
>
> On Fri, 20 May 2016 at 08:29, Sanja  wrote:
>
>> Hi!
>>
>> Have you read our discussion about automatic aggregate functions
>> detection?
>> Am 20.05.2016 07:15 schrieb "Varun Gupta" :
>>
>>> Hi,
>>> I have added the AGGREGATE keyword to the parser . Here is the link to
>>> the repository https://github.com/varunraiko/aggregate-functions .
>>>
>>>
>>> On Wed, May 18, 2016 at 9:50 PM, Sanja  wrote:
>>>
>>>> Hi!
>>>>
>>>> If we get it automatically then of course it should be done, but I
>>>> doubts... We will see.
>>>>
>>>> On Wed, May 18, 2016 at 6:18 PM, Sergei Golubchik 
>>>> wrote:
>>>>
>>>>> Hi, Sanja!
>>>>>
>>>>> On May 18, Sanja wrote:
>>>>> > >
>>>>> > > No, sorry, this doesn't work. It works for procedures, but not for
>>>>> > > functions. See:
>>>>> > >
>>>>> > > CREATE FUNCTION f1 (a INT) RETURNS INT
>>>>> > > BEGIN
>>>>> > >   RETURN SELECT f2(val) FROM t1 WHERE id=a;
>>>>> > > END;
>>>>> > >
>>>>> > > CREATE FUNCTION f2 (b INT) RETURNS INT
>>>>> > > BEGIN
>>>>> > >   ...
>>>>> > >   FETCH GROUP NEXT ROW;
>>>>> > >   ...
>>>>> > >   RETURN something;
>>>>> > > END;
>>>>> > >
>>>>> > > Here, depending on what function is declared aggregate you will
>>>>> have
>>>>> > > different results.
>>>>> >
>>>>> > I think in the first implementation we can have only one level
>>>>> > functions.  if we will have time we can then expand it for calls of
>>>>> > other functions.  But first the mechanism of temporary leaving then
>>>>> > entering functions should be created (then it can be reused for
>>>>> > recursive calls.
>>>>>
>>>>> First implementation - may be (althought I don't understand why - this
>>>>> requires no extra coding, nested function calls will just work
>>>>> automatically). But the first implementation should not choose to use
>>>>> the syntax that makes this extension impossible.
>>>>>
>>>>> For example, Varun's project does not include window function support.
>>>>> At all. But we will be able to add it later without redoing everything,
>>>>> exising syntax can accomodate this new feature.
>>>>>
>>>>> Regards,
>>>>> Sergei
>>>>> Chief Architect MariaDB
>>>>> and secur...@mariadb.org
>>>>>
>>>>
>>>>
>>>
___
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] Aggregate Stored Functions

[Varun Gupta]

Hi,
As in my previous mail I have added the FETCH statement to the parser and
have tested it, when the syntax is correct . Now I am writing test that
would also give an error for incorrect syntax. Also I would like how to
proceed further :).
___
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] Aggregate Stored Functions

[Varun Gupta]

Hi,
I had been going through the LEX struct and could not find any flag member
there which could be used to specify if a function is aggregate or not. So
i created the new flag inside sp_head, so as to make sure it could be used
for stored procedures too in the future.
I have committed the changes on GitHub :)

On Mon, May 23, 2016 at 4:21 PM, Vicențiu Ciorbaru 
wrote:

> Hi Varun,
>
> Getting the parser to accept the syntax is a good first step. Writing
> tests is the correct way to go also.
>
> Now we need to have a way to pass this extra information to the part of
> the code that stores / executes this procedure. When we encounter this
> AGGREGATE_SYM syntax we have to record it somewhere. We generally use the
> LEX structure for this. See if there is any flag member within it that you
> can use for this purpose. If you can't find any, you can potentially create
> one yourself.
>
> Now, it would be a good time to try and familiarize yourself with how we
> get from having a regular parsed function to storing it and afterwards
> executing it. This is the main logic that we have to deal with. I'm not
> going to suggest you any specific thing to do right now as there are
> multiple ways to do this. Try and come up with a simple plan on how to
> extend this functionality for our use case. You don't have to code it all,
> just yet :). We'll improve (or perhaps change it) afterwards. It doesn't
> have to be perfect the first time, but this way you'll get a try at
> designing an implementation idea.
>
> Great job so far!
> Vicentiu
>
> On Mon, 23 May 2016 at 09:04 Varun Gupta  wrote:
>
>> Hi,
>> As in my previous mail I have added the FETCH statement to the parser and
>> have tested it, when the syntax is correct . Now I am writing test that
>> would also give an error for incorrect syntax. Also I would like how to
>> proceed further :).
>> ___
>> 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
>>
>
___
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] Aggregate Stored Functions

[Varun Gupta]

Hi,
Regarding the plan for pausing and restarting the execution of the function
when we have to fetch a row,
1) is that we escape from the function when we come across a FETCH GROUP
statement , a state could be defined in thd->lex->sphead which would tell
that the function is paused and then we proceed with fetching the next row .
2) After we fetch the next row we return to the function and change the
state to running and then with the help of instruction pointer fetch the
next instruction to be executed .
3) We continue with this as long as their are rows available to be fetched.
As soon as there are no more rows available we return the result for the
aggregate function.

This is the basic design of what I am thinking and now I am going through
the code to see where all do I have to add code to get the desired results
:) .


On Tue, May 24, 2016 at 3:15 PM, Sanja  wrote:

> Hi!
>
> I reviewed yet another time the code and have following toughts:
>
> a) if we store the tag separately (like deterministic or not) then
> separate field is better
>
> b) If we get the value form function content then flag is what we need.
>
> for now we are going by a) i.e. we clearly in the header of the function
> state that it is aggregate function independently of the function body.
>
>
> The other question where to store the tag:
>
> a) to have different type (function/procedure/trigger/aggregate function)
> b) to add new field
> c) use deterministic fiield
>
> a) IMHO it is bad way, at the moment we find the fucntion name we know
> that it is function name and nothing more
> b) good way, also require adding upgrade procedure
> c) a bit confusing and alsi we probably could have aggregate
> non-deterministic functions
>
>
> On Tue, May 24, 2016 at 10:59 AM, Vicențiu Ciorbaru 
> wrote:
>
>> Hi Varun,
>>
>> I've reviewed your patch. Looks good from my side. Just stylistic
>> comments. Feel free to keep your own version if you don't agree with them.
>>
>> I think that you could have used the m_flags field, but having a specific
>> member makes things a lot clearer in my opinion. Perhaps Sanja has a
>> different opinion.
>>
>> Next step is to figure out how to use this new flag from sp_head in the
>> execution part. If you get completely stuck, let us know. :)
>>
>> Vicentiu
>>
>> On Tue, 24 May 2016 at 11:30 Sanja  wrote:
>>
>>> Yes, the decision is right. I'll check later the code on github.
>>>
>>> On Tue, May 24, 2016 at 10:27 AM, Varun Gupta 
>>> wrote:
>>>
>>>> Hi,
>>>> I had been going through the LEX struct and could not find any flag
>>>> member there which could be used to specify if a function is aggregate or
>>>> not. So i created the new flag inside sp_head, so as to make sure it could
>>>> be used for stored procedures too in the future.
>>>> I have committed the changes on GitHub :)
>>>>
>>>> On Mon, May 23, 2016 at 4:21 PM, Vicențiu Ciorbaru >>> > wrote:
>>>>
>>>>> Hi Varun,
>>>>>
>>>>> Getting the parser to accept the syntax is a good first step. Writing
>>>>> tests is the correct way to go also.
>>>>>
>>>>> Now we need to have a way to pass this extra information to the part
>>>>> of the code that stores / executes this procedure. When we encounter this
>>>>> AGGREGATE_SYM syntax we have to record it somewhere. We generally use the
>>>>> LEX structure for this. See if there is any flag member within it that you
>>>>> can use for this purpose. If you can't find any, you can potentially 
>>>>> create
>>>>> one yourself.
>>>>>
>>>>> Now, it would be a good time to try and familiarize yourself with how
>>>>> we get from having a regular parsed function to storing it and afterwards
>>>>> executing it. This is the main logic that we have to deal with. I'm not
>>>>> going to suggest you any specific thing to do right now as there are
>>>>> multiple ways to do this. Try and come up with a simple plan on how to
>>>>> extend this functionality for our use case. You don't have to code it all,
>>>>> just yet :). We'll improve (or perhaps change it) afterwards. It doesn't
>>>>> have to be perfect the first time, but this way you'll get a try at
>>>>> designing an implementation idea.
>>>>>
>>>>> Great job so far!
>>>>> Vicentiu
>>>>>
>>>

Re: [Maria-developers] Aggregate Stored Functions

[Varun Gupta]

Hi,
I have added the test where I have put both ALTER,CREATE and SHOW FUNCTION
queries. Should I make separate test for the queries or clubbing them in
one is fine .



On Sat, Jun 4, 2016 at 7:25 PM, Varun Gupta 
wrote:

> Hi,
> I have also written a test for the alter, show and create functions. I
> have pushed it, also I am about to go through some tests already written
> and try to make the tests similar to them.
>
> On Sat, Jun 4, 2016 at 5:34 PM, Varun Gupta 
> wrote:
>
>> Hi,
>> I am done with ALTER FUNCTION, i have done this by adding new field. I
>> have committed the code. Please review.
>> Also I wanted to discuss more about the new filed which i added to the
>> sp_chistics struct.
>> Also I am writing tests for the alter queries. I would also need some
>> more suggestions about what I should do next .
>>
>>
>> On Sat, Jun 4, 2016 at 10:49 AM, Varun Gupta 
>> wrote:
>>
>>> I do but to update the function, the function prototype of
>>>  sp_update_routine() is
>>>sp_update_routine(THD *thd, stored_procedure_type
>>> type, sp_name *name,st_sp_chistics *chistics), so to pass if the field to
>>> be updated is AGGREGATE I have to have some way to send the updated value
>>> to the above function.
>>>
>>> On Sat, Jun 4, 2016 at 2:24 AM, Sanja  wrote:
>>>
>>>> Don't you have it in sp_head?
>>>> Am 03.06.2016 22:33 schrieb "Varun Gupta" :
>>>>
>>>>> Hi,
>>>>> I need a bit of suggestion on how to pass that we are changing the
>>>>> aggregate field, from the parser I need to send the field so in the
>>>>> mysql_execute_function I can call the sp_update_routine.
>>>>>
>>>>> My ideas is
>>>>> 1) addition of one field to lex structure for aggregate.
>>>>> 2) instead of having is_aggregate in sp_head, we could put that field
>>>>> in in sp_name.
>>>>>
>>>>> On Fri, Jun 3, 2016 at 11:41 PM, Varun Gupta >>>> > wrote:
>>>>>
>>>>>> Hi,
>>>>>> I have added the syntax , patch committed, please review it :)
>>>>>>
>>>>>> On Fri, Jun 3, 2016 at 10:35 PM, Varun Gupta <
>>>>>> varungupta1...@gmail.com> wrote:
>>>>>>
>>>>>>> Hi,
>>>>>>>
>>>>>>> After going through the code I come to the conclusion that a rule
>>>>>>> for AGGREGATE field needs to be added to the  ALTER FUNCTION
>>>>>>> characteristics. So I am going forward with adding this syntax.
>>>>>>>
>>>>>>> On Fri, Jun 3, 2016 at 6:14 PM, Sanja 
>>>>>>> wrote:
>>>>>>>
>>>>>>>> Hi!
>>>>>>>>
>>>>>>>> ALTER TABLE usually has the same syntax as CREATE (at least I do
>>>>>>>> not remember adding option like here) so I doubts that it is correct.
>>>>>>>>
>>>>>>>> On Fri, Jun 3, 2016 at 2:40 PM, Varun Gupta <
>>>>>>>> varungupta1...@gmail.com> wrote:
>>>>>>>>
>>>>>>>>> Hi,
>>>>>>>>> For the alter function func_name field value,
>>>>>>>>> the syntax we have is
>>>>>>>>> ALTER FUNCTION_SYM sp_name sp_a_chistics
>>>>>>>>>
>>>>>>>>> sp_a_chistics:
>>>>>>>>>   /* Empty */ {}
>>>>>>>>> | sp_a_chistics sp_chistic {}
>>>>>>>>>
>>>>>>>>> sp_a_chistics:
>>>>>>>>>| AGGREGSTE_SYM option
>>>>>>>>> option:
>>>>>>>>>    | YES
>>>>>>>>>| NO
>>>>>>>>>
>>>>>>>>> or option could be any string.
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> On Fri, Jun 3, 2016 at 1:22 PM, Varun Gupta <
>>>>>>>>> varungupta1...@gmail.com> wrote:
>>>>>>>>>
>>>>>>>>>> Hi,
>>>>>>>>>> The error is fixed , now show works for aggregate functions too :)
>>>>>>>>>>
>>

[Maria-developers] GSoc weekly reports (Aggregate Functions)

[Varun Gupta]

Hi,

Here is the week 2 report
WEEK 2

dire = mariadb/server
task - adding 'aggregate' field to the mysql.proc table.



sub tasks
1) aggregate field --->  mysql.proc table
2)create function -> aggregate and non-aggregate
3)show create function >  aggregate and non-aggregate
4)drop function  -> aggregate and non-aggregate
5)alter function -> aggregate and non-aggregate



For the user he has two options for AGGREGATE (TRUE,FALSE);


details


1) Added the field 'aggregate' to the proc table .
Order should be maintained for the fields added.

2) Also included the column in the enumberation of db storage.

3) Added a new is_aggregate characteristic to the lex structure, which
would help in identifying if a function is aggregate or not.

4) So after parsing the CREATE FUNCTION query ,adding to the table YES if
function is aggregate else adding NO to the proc table.

5) SHOW FUNCTION query to load the aggregate field. We have the
is_aggregate field to load the value in it.

6) DROP FUNCTION, required no changes to be made as the primary key does
not have the aggregate field

7) ALTER FUNCTION , required adding the syntax for the AGGREGATE
characteristic , so rules were added to add this additional alter
characteristic. Then we had to update the routine row in the proc
table for all the field that are going to be altered . So we store the new
altered fields to the proc table for the stated fields.
___
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

[Maria-developers] Aggregate Functions

[Varun Gupta]

Hi,
As GSOC is nearing its end, I have written a blog which contains how can
aggregate functions be written and executed to get the desired results.In
the blog I also have added the technical details of this project on
aggregate functions.

Here are the links:

Jira issue: https://jira.mariadb.org/browse/MDEV-7773
Blog Link: http://varunraiko.github.io/aggregate-functions/

Disclaimer:  I hereby declare that all my work done so far (and future) by
me on the Aggregate Functions’ project, is released under the NEW BSD
licence.


Regards,
Varun
___
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] MDEV-8306: EXPLAIN JSON output is not JSON-ish

[varun gupta]

Hi Sergey,


On Sat, Feb 13, 2021 at 5:52 PM Sergey Petrunia  wrote:

> Hi Varun,
>
> (This email is not the complete input, and not the most important part of
> the
> input. More to follow. I think it's better to split input into multiple
> smaller
> pieces, it's easier to track, and you get to see the first portions of the
> input sooner)
>
> EXPLAIN EXTENDED SELECT t3.b, t2.a, t1.b, t1.a
> FROM t1,t2,t3
> WHERE t1.b=t3.b
> ORDER BY t1.b DESC, t2.a DESC
> LIMIT 3;
>
>
> +--+-+-+--+---+--+-+--+--+--++
> | id   | select_type | table   | type | possible_keys | key  | key_len
> | ref  | rows | filtered | Extra  |
>
> +--+-+-+--+---+--+-+--+--+--++
> |1 | SIMPLE  | t2  | ALL  | NULL  | NULL | NULL
> | NULL | 10   |   100.00 ||
> |1 | SIMPLE  | t1  | ALL  | NULL  | NULL | NULL
> | NULL | 20   |   100.00 | Using join buffer (flat, BNL join) |
> |1 | SIMPLE  |  | ALL  | NULL  | NULL | NULL
> | NULL | 3|   100.00 | Using filesort |
> |1 | SIMPLE  | t3  | ALL  | NULL  | NULL | NULL
> | NULL | 100  | 5.00 | Using where|
>
> +--+-+-+--+---+--+-+--+--+--++
>
> So, here  includes t2 and t1, and then t3 is joined with it.
>
> However in EXPLAIN FORMAT=JSON output they all look like peers:
>
> {
>   "query_block": {
> "select_id": 1,
> "table": {
>   "table_name": "t2",
>   "access_type": "ALL",
>   "rows": 10,
>   "filtered": 100
> },
> "block-nl-join": {
>   "table": {
> "table_name": "t1",
> "access_type": "ALL",
> "rows": 20,
> "filtered": 100
>   },
>   "buffer_type": "flat",
>   "buffer_size": "65",
>   "join_type": "BNL"
> },
> "read_sorted_file": {
>   "filesort": {
> "sort_key": "`sort-nest`.b desc, `sort-nest`.a desc",
> "table": {
>   "table_name": "",
>   "access_type": "ALL",
>   "rows": 3,
>   "filtered": 100
> }
>   }
> },
> "table": {
>   "table_name": "t3",
>   "access_type": "ALL",
>   "rows": 100,
>   "filtered": 5,
>   "attached_condition": "t3.b = `sort-nest`.b"
> }
>   }
> }
>
> which is counter-intuitive.


Well i didn't make changes to how to display the sort-nest in
EXPLAIN/ANALYZE FORMAT=JSON.
But i think we can use do it in the way that inside :
"table": {
  "table_name": "",
  "access_type": "ALL",
  "rows": 3,
  "filtered": 100
}
we put in the inner tables too as it is done with materialization.

>
>
> Also, `sort-nest` is quoted. I assume it is because of the '-' in it?
> Maybe we
> should change the name to avoid the quoting?
>
Well i think we  can have just *sortnest* in the naming.

>
> BR
>  Sergei
> --
> Sergei Petrunia, Software Developer
> MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net
>
>
On Sat, Feb 13, 2021 at 5:52 PM Sergey Petrunia  wrote:

> Hi Varun,
>
> (This email is not the complete input, and not the most important part of
> the
> input. More to follow. I think it's better to split input into multiple
> smaller
> pieces, it's easier to track, and you get to see the first portions of the
> input sooner)
>
> EXPLAIN EXTENDED SELECT t3.b, t2.a, t1.b, t1.a
> FROM t1,t2,t3
> WHERE t1.b=t3.b
> ORDER BY t1.b DESC, t2.a DESC
> LIMIT 3;
>
>
> +--+-+-+--+---+--+-+--+--+--++
> | id   | select_type | table   | type | possible_keys | key  | key_len
> | ref  | rows | filtered | Extra  |
>
> +--+-+-+--+---+--+-+--+--+--++
> |1 | SIMPLE  | t2  | ALL  | NULL  | NULL | NULL
> | NULL | 10   |   100.00 ||
> |1 | SIMPLE  | t1  | ALL  | NULL  | NULL | NULL
> | NULL | 20   |   100.00 | Using join buffer (flat, BNL join) |
> |1 | SIMPLE  |  | ALL  | NULL  | NULL | NULL
> | NULL | 3|   100.00 | Using filesort |
> |1 | SIMPLE  | t3  | ALL  | NULL  | NULL | NULL
> | NULL | 100  | 5.00 | Using where|
>
> +--+-+-+--+---+--+-+--+--+--++
>
> So, here  includes t2 and t1, and then t3 is joined with it.
>
> Howev