Hi, Alexander!
On Apr 26, Alexander Barkov wrote:
> revision-id: 8a990ad1774
> author: Alexander Barkov <b...@mariadb.com>
> committer: Alexander Barkov <b...@mariadb.com>
> timestamp: 2019-03-25 19:19:48 +0400
> message: MDEV-18319 BIGINT UNSIGNED Performance issue
>
> diff --git a/mysql-test/main/errors.result b/mysql-test/main/errors.result
> index ba05a2b37d45..96ad96395aab 100644
> --- a/mysql-test/main/errors.result
> +++ b/mysql-test/main/errors.result
> @@ -32,16 +32,21 @@ set sql_mode=default;
> CREATE TABLE t1 (a INT);
> SELECT a FROM t1 WHERE a IN(1, (SELECT IF(1=0,1,2/0)));
> a
> +Warnings:
> +Warning 1365 Division by 0
Interesting. There was no warning before.
> INSERT INTO t1 VALUES(1);
> SELECT a FROM t1 WHERE a IN(1, (SELECT IF(1=0,1,2/0)));
> a
> 1
> diff --git a/mysql-test/main/func_in.result b/mysql-test/main/func_in.result
> index 65313148bf80..59822ae1049c 100644
> --- a/mysql-test/main/func_in.result
> +++ b/mysql-test/main/func_in.result
> @@ -489,6 +489,7 @@ SELECT id FROM t1 WHERE id IN(4564, (SELECT
> IF(1=0,1,1/0)) );
> id
> Warnings:
> Warning 1365 Division by 0
> +Warning 1365 Division by 0
This is not very nice. May be we should use Item_cache in these cases?
> DROP TABLE t1;
> End of 5.0 tests
> create table t1(f1 char(1));
> @@ -909,3 +910,71 @@ Warnings:
> Warning 1292 Truncated incorrect time value: ''
> Warning 1292 Truncated incorrect time value: ''
> Warning 1292 Truncated incorrect time value: ''
> +#
> +# MDEV-18319 BIGINT UNSIGNED Performance issue
> +#
> +CREATE TABLE t1 (
> +id bigint(20) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY
> +);
> +FOR i IN 0..255
> +DO
> +INSERT INTO t1 VALUES ();
> +END FOR
> +$$
> +SELECT MIN(id), MAX(id), COUNT(*) FROM t1;
> +MIN(id) MAX(id) COUNT(*)
> +1 256 256
interesting. why it's min=1 and max=255, if your loop is in 0..255?
> +EXPLAIN SELECT id FROM t1 WHERE id IN (1,2);
> +id select_type table type possible_keys key key_len ref
> rows Extra
> +1 SIMPLE t1 range PRIMARY PRIMARY 8 NULL 2 Using
> where; Using index
> +EXPLAIN SELECT id FROM t1 WHERE id IN (9223372036854775806,
> 9223372036854775807);
> +id select_type table type possible_keys key key_len ref
> rows Extra
> +1 SIMPLE t1 range PRIMARY PRIMARY 8 NULL 2 Using
> where; Using index
> +EXPLAIN SELECT id FROM t1 WHERE id IN (9223372036854775807,
> 9223372036854775808);
> +id select_type table type possible_keys key key_len ref
> rows Extra
> +1 SIMPLE t1 range PRIMARY PRIMARY 8 NULL 2 Using
> where; Using index
> +EXPLAIN SELECT id FROM t1 WHERE id IN (1.0,2.0);
> +id select_type table type possible_keys key key_len ref
> rows Extra
> +1 SIMPLE t1 range PRIMARY PRIMARY 8 NULL 2 Using
> where; Using index
> +EXPLAIN SELECT id FROM t1 WHERE id IN (9223372036854775806.0,
> 9223372036854775807.0);
> +id select_type table type possible_keys key key_len ref
> rows Extra
> +1 SIMPLE t1 range PRIMARY PRIMARY 8 NULL 2 Using
> where; Using index
> +# Cannot compare this as INT (yet)
> +EXPLAIN SELECT id FROM t1 WHERE id IN (9223372036854775807.0,
> 9223372036854775808.0);
> +id select_type table type possible_keys key key_len ref
> rows Extra
> +1 SIMPLE t1 index PRIMARY PRIMARY 8 NULL 256 Using
> where; Using index
> +DROP TABLE t1;
> +CREATE TABLE t1 (
> +id bigint(20) NOT NULL AUTO_INCREMENT PRIMARY KEY
> +);
> +FOR i IN 0..255
> +DO
> +INSERT INTO t1 VALUES ();
> +END FOR
> +$$
> +SELECT MIN(id), MAX(id), COUNT(*) FROM t1;
> +MIN(id) MAX(id) COUNT(*)
> +1 256 256
> +EXPLAIN SELECT id FROM t1 WHERE id IN (-1,-2);
> +id select_type table type possible_keys key key_len ref
> rows Extra
> +1 SIMPLE t1 range PRIMARY PRIMARY 8 NULL 2 Using
> where; Using index
> +EXPLAIN SELECT id FROM t1 WHERE id IN (-9223372036854775806,
> -9223372036854775807);
> +id select_type table type possible_keys key key_len ref
> rows Extra
> +1 SIMPLE t1 range PRIMARY PRIMARY 8 NULL 2 Using
> where; Using index
> +EXPLAIN SELECT id FROM t1 WHERE id IN (-9223372036854775807,
> -9223372036854775808);
> +id select_type table type possible_keys key key_len ref
> rows Extra
> +1 SIMPLE t1 range PRIMARY PRIMARY 8 NULL 2 Using
> where; Using index
> +EXPLAIN SELECT id FROM t1 WHERE id IN (-1.0,-2.0);
> +id select_type table type possible_keys key key_len ref
> rows Extra
> +1 SIMPLE t1 range PRIMARY PRIMARY 8 NULL 2 Using
> where; Using index
> +EXPLAIN SELECT id FROM t1 WHERE id IN (-9223372036854775806.0,
> -9223372036854775807.0);
> +id select_type table type possible_keys key key_len ref
> rows Extra
> +1 SIMPLE t1 range PRIMARY PRIMARY 8 NULL 2 Using
> where; Using index
> +EXPLAIN SELECT id FROM t1 WHERE id IN (-9223372036854775807.0,
> -9223372036854775808.0);
> +id select_type table type possible_keys key key_len ref
> rows Extra
> +1 SIMPLE t1 range PRIMARY PRIMARY 8 NULL 2 Using
> where; Using index
> +# Cannot compare this as INT (yet)
> +EXPLAIN SELECT id FROM t1 WHERE id IN (-9223372036854775808.0,
> -9223372036854775809.0);
> +id select_type table type possible_keys key key_len ref
> rows Extra
> +1 SIMPLE t1 index PRIMARY PRIMARY 8 NULL 256 Using
> where; Using index
> +DROP TABLE t1;
> diff --git a/mysql-test/main/func_in.test b/mysql-test/main/func_in.test
> index b99fad159c22..04b0b328c27b 100644
> --- a/mysql-test/main/func_in.test
> +++ b/mysql-test/main/func_in.test
> @@ -690,3 +690,51 @@ SELECT
> TIME'00:00:00'='' AS c1_true,
> TIME'00:00:00' IN ('', TIME'10:20:30') AS c2_true,
> TIME'00:00:00' NOT IN ('', TIME'10:20:30') AS c3_false;
> +
> +--echo #
> +--echo # MDEV-18319 BIGINT UNSIGNED Performance issue
> +--echo #
> +
> +CREATE TABLE t1 (
> + id bigint(20) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY
> +);
> +DELIMITER $$;
> +FOR i IN 0..255
> +DO
> + INSERT INTO t1 VALUES ();
> +END FOR
> +$$
> +DELIMITER ;$$
A couple of one-liners instead:
insert t1 select seq from seq_1_to_256;
or, in pure SQL
insert t1 with recursive g(i) as (select 1 union select i+1 from g where i <
257) select * from g;
> +SELECT MIN(id), MAX(id), COUNT(*) FROM t1;
> +EXPLAIN SELECT id FROM t1 WHERE id IN (1,2);
> +EXPLAIN SELECT id FROM t1 WHERE id IN (9223372036854775806,
> 9223372036854775807);
> +EXPLAIN SELECT id FROM t1 WHERE id IN (9223372036854775807,
> 9223372036854775808);
> +
> +EXPLAIN SELECT id FROM t1 WHERE id IN (1.0,2.0);
> +EXPLAIN SELECT id FROM t1 WHERE id IN (9223372036854775806.0,
> 9223372036854775807.0);
> +--echo # Cannot compare this as INT (yet)
> +EXPLAIN SELECT id FROM t1 WHERE id IN (9223372036854775807.0,
> 9223372036854775808.0);
> +DROP TABLE t1;
> +
> +
> +CREATE TABLE t1 (
> + id bigint(20) NOT NULL AUTO_INCREMENT PRIMARY KEY
> +);
> +DELIMITER $$;
> +FOR i IN 0..255
> +DO
> + INSERT INTO t1 VALUES ();
> +END FOR
> +$$
> +DELIMITER ;$$
> +SELECT MIN(id), MAX(id), COUNT(*) FROM t1;
> +EXPLAIN SELECT id FROM t1 WHERE id IN (-1,-2);
> +EXPLAIN SELECT id FROM t1 WHERE id IN (-9223372036854775806,
> -9223372036854775807);
> +EXPLAIN SELECT id FROM t1 WHERE id IN (-9223372036854775807,
> -9223372036854775808);
> +
> +EXPLAIN SELECT id FROM t1 WHERE id IN (-1.0,-2.0);
> +EXPLAIN SELECT id FROM t1 WHERE id IN (-9223372036854775806.0,
> -9223372036854775807.0);
> +EXPLAIN SELECT id FROM t1 WHERE id IN (-9223372036854775807.0,
> -9223372036854775808.0);
> +--echo # Cannot compare this as INT (yet)
> +EXPLAIN SELECT id FROM t1 WHERE id IN (-9223372036854775808.0,
> -9223372036854775809.0);
> +DROP TABLE t1;
> diff --git a/sql/item.h b/sql/item.h
> index 883cc791f384..9f215c74864b 100644
> --- a/sql/item.h
> +++ b/sql/item.h
> @@ -70,6 +70,11 @@ class Value: public st_value
> bool is_temporal() const { return m_type == DYN_COL_DATETIME; }
> bool is_string() const { return m_type == DYN_COL_STRING; }
> bool is_decimal() const { return m_type == DYN_COL_DECIMAL; }
> + Longlong_hybrid to_longlong_hybrid_native() const
> + {
> + DBUG_ASSERT(is_longlong());
> + return Longlong_hybrid(value.m_longlong, m_type == DYN_COL_UINT);
> + }
> };
>
>
> diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
> index b2fa753f2bd2..c7176df43d6a 100644
> --- a/sql/item_cmpfunc.cc
> +++ b/sql/item_cmpfunc.cc
> @@ -3747,11 +3748,45 @@ bool Predicant_to_list_comparator::add_value(const
> char *funcname,
> Item *tmpargs[2];
> tmpargs[0]= args->arguments()[m_predicant_index];
> tmpargs[1]= args->arguments()[value_index];
> - if (tmp.aggregate_for_comparison(funcname, tmpargs, 2, true))
> + if (tmp.aggregate_for_comparison(funcname, tmpargs, 2, false))
How would that work? Are you going to compare signed to unsigned as
longlongs?
> {
> DBUG_ASSERT(current_thd->is_error());
> return true;
> }
> + /*
> + Try to compare using type handler of the predicant when possible,
> + as this can use indexes for conditions like:
> + WHERE field IN (const1, .. constN)
> + */
> + if (prefer_predicant_type_handler &&
> + args->arguments()[value_index]->const_item() &&
> + !args->arguments()[value_index]->is_expensive() &&
> + tmp.type_handler()->cmp_type() == DECIMAL_RESULT &&
> + args->arguments()[m_predicant_index]->cmp_type() == INT_RESULT)
> + {
> + /*
> + For now we catch only one special case:
> + an INT predicant can be compared to a DECIMAL constant
> + using Longlong_hybrid comparison, when the DECIMAL constant:
> + a. has no significant fractional digits, and
> + b. is within the signed longlong range
> + For DECIMAL constants in the range LONGLONG_MAX..ULONGLONG_MAX
> + we cannot call to_longlong_hybrid() safely, because DECIMAL type
> + Items usually set their "unsigned_flag" to "false", so val_int()
> + will truncate such constants to LONGLONG_MAX (instead of
> ULONGLONG_MAX).
> + TODO: fix to_longlong_hybrid() for DECIMAL LONGLONG_MAX..ULONGLONG_MAX
> + TODO: move this code to Type_handler (will be done by MDEV-18898)
> + TODO: skip constants outside of LONGLONG_MIN..ULONGLONG_MAX, as
> + such conditon can never be true, e.g.:
> + WHERE int_expr IN (.. -9223372036854775809 ..)
> + WHERE int_expr IN (.. 18446744073709551616 ..)
> + */
> + my_decimal buf, *dec= args->arguments()[value_index]->val_decimal(&buf);
> + longlong res;
> + if (dec && decimal_actual_fraction(dec) == 0 &&
> + my_decimal2int(0, dec, false /*SIGNED*/, &res) == 0)
> + tmp.set_handler(&type_handler_longlong);
why not to replace the item with Item_int or Item_cache_int here?
Then you'll be able to handle decimals up to ULONGLONG_MAX, that's your
first TODO.
For the last TODO, you can simply remove the always-false element from
the arguments[] array.
> + }
> m_comparators[m_comparator_count].m_handler= tmp.type_handler();
> m_comparators[m_comparator_count].m_arg_index= value_index;
> m_comparator_count++;
> diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
> index 06f15503258a..4e84c3008578 100644
> --- a/sql/item_cmpfunc.h
> +++ b/sql/item_cmpfunc.h
> @@ -1588,29 +1588,28 @@ class cmp_item_sort_string :public cmp_item_string
>
> class cmp_item_int : public cmp_item_scalar
> {
> - longlong value;
> + Longlong_hybrid m_value;
> public:
> - cmp_item_int() {} /* Remove gcc warning */
> + cmp_item_int(): m_value(0, false) {}
> void store_value(Item *item)
> {
> - value= item->val_int();
> + m_value= item->to_longlong_hybrid();
I wonder if to_longlong_hybrid() returns a Longlong_hybrid like that,
where the compiler will allocate it? Could you check it, please?
> m_null_value= item->null_value;
> }
Regards,
Sergei
VP of MariaDB Server Engineering
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
