maria-developers team mailing list archive

[Sergey Petrunia <sergey@xxxxxxxxxxx>]

Hi Varun,

Looking at the testcase name, I have found Serg's patch for MDEV-4285. 

His patch skips sorting if the keys have zero length. He does it at a late
stage, in Filesort_buffer::sort_buffer().

His patch doesn't cover the example of MDEV-17020, because the testcase in
MDEV-17020 uses LIMIT and so uses a Priority Queue (and not
Filesort_buffer::sort_buffer which uses qsort).

However, your patch doesn't cover all cases either. There are other ways to
produce zero-length keys. I have found this one:

SELECT * FROM t1 ORDER BY 'foo', substring(pk, 0) LIMIT 2;

it crashes in the same way as the test for MDEV-17020 used to do.

Possible options:

1. Find all Items that might produce zero-length keys. It's difficult not to
   miss anything.

2. Instead of #1, add a universal logic "if sort key length is 0, don't sort".
   Is this doable?

3. Extend the Serg's approach. We cannot use his code directly ("dont sort"),
because Priority Queue API doesn't easily allow it, but perhaps we could add a
comparison function that will allow to compare zero-length keys? Or make them
1-byte long with a dummy value (do we need the sorting to be stable??)

On Fri, Aug 31, 2018 at 12:18:08AM +0530, Varun wrote:
> revision-id: 3c7e2540f63c6801a0d0a27fbea7c934bb61221f (mariadb-10.0.36-13-g3c7e2540f63)
> parent(s): b3c320bb0b93e516cda4db277cfa3efeef48c988
> author: Varun Gupta
> committer: Varun Gupta
> timestamp: 2018-08-31 00:14:20 +0530
> message:
> 
> MDEV-17020: Assertion `length > 0' failed in ptr_compare upon ORDER BY with bad conversion
> 
> We have a case here where the the entire sort key is empty  and so sorting is not needed.
> The crash happens because filesort expects the sort key to have length > 0.
> The case here is like ORDER BY CONVERT(a,char(0)), which if evaluated is a constant so
> can be removed from the ORDER BY clause
> 
> ---
>  mysql-test/r/order_by_zerolength-4285.result | 28 +++++++++++++++++-----------
>  mysql-test/t/order_by_zerolength-4285.test   | 13 +++++++++++++
>  sql/item_timefunc.h                          |  6 ++++++
>  3 files changed, 36 insertions(+), 11 deletions(-)
> 
> diff --git a/mysql-test/r/order_by_zerolength-4285.result b/mysql-test/r/order_by_zerolength-4285.result
> index f60ce7d90c7..f77b284df3c 100644
> --- a/mysql-test/r/order_by_zerolength-4285.result
> +++ b/mysql-test/r/order_by_zerolength-4285.result
> @@ -12,15 +12,21 @@ pk
>  8
>  9
>  10
> -Warnings:
> -Warning	1292	Truncated incorrect CHAR(0) value: '1'
> -Warning	1292	Truncated incorrect CHAR(0) value: '2'
> -Warning	1292	Truncated incorrect CHAR(0) value: '3'
> -Warning	1292	Truncated incorrect CHAR(0) value: '4'
> -Warning	1292	Truncated incorrect CHAR(0) value: '5'
> -Warning	1292	Truncated incorrect CHAR(0) value: '6'
> -Warning	1292	Truncated incorrect CHAR(0) value: '7'
> -Warning	1292	Truncated incorrect CHAR(0) value: '8'
> -Warning	1292	Truncated incorrect CHAR(0) value: '9'
> -Warning	1292	Truncated incorrect CHAR(0) value: '10'
> +drop table t1;
> +#
> +# MDEV-17020: Assertion `length > 0' failed in ptr_compare upon ORDER BY with bad conversion
> +#
> +set @save_sql_mode= @@sql_mode;
> +SET @@sql_mode= '';
> +CREATE TABLE t1 (pk INT PRIMARY KEY);
> +INSERT INTO t1 VALUES (1),(2);
> +explain
> +SELECT * FROM t1 ORDER BY 'foo', CONVERT(pk, CHAR(0)) LIMIT 2;
> +id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
> +1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	2	Using index
> +SELECT * FROM t1 ORDER BY 'foo', Cast(pk as CHAR(0)) LIMIT 2;
> +pk
> +1
> +2
> +set @@sql_mode= @save_sql_mode;
>  drop table t1;
> diff --git a/mysql-test/t/order_by_zerolength-4285.test b/mysql-test/t/order_by_zerolength-4285.test
> index 2fb58edd36d..f03d528320c 100644
> --- a/mysql-test/t/order_by_zerolength-4285.test
> +++ b/mysql-test/t/order_by_zerolength-4285.test
> @@ -6,3 +6,16 @@ insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
>  select * from t1 order by now(), cast(pk as char(0));
>  drop table t1;
>  
> +--echo #
> +--echo # MDEV-17020: Assertion `length > 0' failed in ptr_compare upon ORDER BY with bad conversion
> +--echo #
> +
> +set @save_sql_mode= @@sql_mode;
> +SET @@sql_mode= ''; 
> +CREATE TABLE t1 (pk INT PRIMARY KEY);
> +INSERT INTO t1 VALUES (1),(2);
> +explain
> +SELECT * FROM t1 ORDER BY 'foo', CONVERT(pk, CHAR(0)) LIMIT 2;
> +SELECT * FROM t1 ORDER BY 'foo', Cast(pk as CHAR(0)) LIMIT 2;
> +set @@sql_mode= @save_sql_mode;
> +drop table t1;
> diff --git a/sql/item_timefunc.h b/sql/item_timefunc.h
> index 80b98758fb7..461c3feed8c 100644
> --- a/sql/item_timefunc.h
> +++ b/sql/item_timefunc.h
> @@ -903,6 +903,12 @@ class Item_char_typecast :public Item_str_func
>    String *val_str(String *a);
>    void fix_length_and_dec();
>    void print(String *str, enum_query_type query_type);
> +  table_map used_tables() const
> +  {
> +    if (!cast_length)
> +      return (table_map) 0L;
> +    return Item_str_func::used_tables();
> +  }
>  };
>  
>  
> _______________________________________________
> commits mailing list
> commits@xxxxxxxxxxx
> https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits

-- 
BR
 Sergei
-- 
Sergei Petrunia, Software Developer
MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog