MySQL Bugs: #34384: Big quoted int in WHERE of UPDATE or DELETE causes drop in performance

Bug #34384	Big quoted int in WHERE of UPDATE or DELETE causes drop in performance
Submitted:	7 Feb 2008 16:45	Modified:	7 Oct 2010 23:09
Reporter:	Andrii Nikitin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.0.45, 5.1.22, 5.0.54	OS:	Any
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
When in WHERE clause of UPDATE or DELETE happens big quoted int (more than 2147483647), performance drops down.

update ... where id=9999999999999999;
Query OK, 0 rows affected (0 ms)

update ... where id='9999999999999999';
Query aborted by Ctrl+C
ERROR 1317 (70100): Query execution was interrupted (took forever on 40,000,000 row table)

update ... where id='2147483647';
Query OK, 0 rows affected (0.00 sec)

update ... where id='2147483648';
Query aborted by Ctrl+C
ERROR 1317 (70100): Query execution was interrupted (took forever on 40,000,000 row table)

How to repeat:
run attached script 

Suggested fix:
investigate where overflow happens

how to repeat

Attachment: csc23012_testcase.sql (application/unknown, text), 3.13 KiB.

Thank you for a bug report. Verified just as described:

...
mysql> insert into t (b) select b from t;
Query OK, 393216 rows affected (5.97 sec)
Records: 393216  Duplicates: 0  Warnings: 0

mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
|   786432 |
+----------+
1 row in set (0.48 sec)

mysql> select version();
+------------------------------+
| version()                    |
+------------------------------+
| 5.0.54-enterprise-gpl-nt-log |
+------------------------------+
1 row in set (0.00 sec)

mysql> explain select count(*) from t where a='999999999999'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Impossible WHERE noticed after reading const tables
1 row in set (0.02 sec)

mysql> select count(*) from t where a='999999999999';
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> update t set b='a' where a='999999999999';
Query OK, 0 rows affected (1.17 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> update t set b='a' where a=999999999999;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> update t set b='a' where a='999999999999';
Query OK, 0 rows affected (1.14 sec)
Rows matched: 0  Changed: 0  Warnings: 0

So, SELECT is obviously optimized differently than UPDATE in this case.

Workaround is to use proper data type in WHERE (same as column has).

it's a ten-year-old well known problem.
update is actually correct.
the manual explains how types are aggregated in operation.
when an integer is compared to a string, both are casted to doubles and operation is performed on doubles.

Thus, the second update is executed as

 update t set b='a' where CAST(a AS DOUBLE)=CAST('999999999999' AS DOUBLE)

The first update uses an index on (a), the second update cannot do that, because an index cannot be used to for function arguments.

Still there is a bug here.
Note that SELECT is fast, and explain says "Impossible WHERE noticed after reading const tables" which means that SELECT uses an index.
It's incorrect for the reason explained above - it apparently breaks type aggregation rules from the manual. Test case:

create table t(a bigint unsigned not null auto_increment primary key,b text);
insert into t (b) values ('a'),('b'),('c'),('d'),('e'),('f');
insert into t (b) select b from t;
insert into t (b) select b from t;
insert t values (18446744073709551609, 'v');
insert t values (18446744073709551610, 'u');
insert t values (18446744073709551611, 'w');
insert t values (18446744073709551612, 'x');
insert t values (18446744073709551613, 'y');
insert t values (18446744073709551614, 'z');
explain select count(*) from t where a='18446744073709551612';
select * from t where a='18446744073709551612';
select * from t where a+0='18446744073709551612';

the second SELECT aggregates correctly, BIGING->DOUBLE with precision loss.
The first one - incorrectly.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/83801

2864 Evgeny Potemkin	2009-09-19
      Bug#34384: Slow down on constant conversion.
      
      When values of different types are compared they're converted to a type that
      allows correct comparison. This conversion is done for each comparison and
      takes some time. When a constant is being compared it's possible to cache the
      value after conversion to speedup comparison.
      
      A test case isn't provided because all changes are internal and isn't visible
      outside.
      
      A flag named uncacheable is added to the Item class. It denies caching of the
      item.
      A flag named late_caching is added to the Item_cache class. It's set to TRUE
      when we need to postpone caching till the first use of cached value.
      A function named check_late_caching is added to the Item_cache flag. Its
      purpose is to check state of the late_caching flag and cache value accordingly.
      Item_cache_xxx::val_xxx functions are changed to call it prior to return
      cached value.
      The Arg_comparator::set_cmp_func function is changed to cache a value being
      compared if it's a constant and needs type conversion.
      Now the Item_func_like::fix_fields function denies caching arguments of the LIKE
      function.
      The Item_cache::get_cache function is overloaded to allow setting of the
      late_caching flag and cache type.
     @ mysql-test/r/range.result
        Adjusted a test case after fix for the bug#34384.
     @ sql/item.cc
        Bug#34384: Slow down on constant conversion.
        A flag named uncacheable is added to the Item class. It denies caching of the
        item.
        The Item_cache::get_cache function is overloaded to allow setting of the
        late_caching flag and cache type.
        Item_cache_xxx::val_xxx functions are changed to call it prior to return
        cached value.
     @ sql/item.h
        Bug#34384: Slow down on constant conversion.
        A function named check_late_caching is added to the Item_cache flag. Its
        purpose is to check state of the late_caching flag and cache value accordingly.
        The Item_cache::get_cache function is overloaded to allow setting of the
        late_caching flag and cache type.
     @ sql/item_cmpfunc.cc
        Bug#34384: Slow down on constant conversion.
        The Arg_comparator::set_cmp_func function is changed to cache a value being
        compared if it's a constant and needs type conversion.
     @ sql/item_cmpfunc.h
        Bug#34384: Slow down on constant conversion.
     @ sql/item_xmlfunc.cc
        Bug#34384: Slow down on constant conversion.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/86092

3147 Evgeny Potemkin	2009-10-08
      Bug#34384: Slow down on constant conversion.
      When values of different types are compared they're converted to a type that
      allows correct comparison. This conversion is done for each comparison and
      takes some time. When a constant is being compared it's possible to cache the
      value after conversion to speedup comparison.
      
      A test case isn't provided because all changes are internal and aren't visible
      outside.
      
      A flag named uncacheable is added to the Item class. It denies caching of the
      item.
      A flag named late_caching is added to the Item_cache class. It's set to TRUE
      when we need to postpone caching till the first use of cached value.
      Functions named init_late_caching and cache_value() are added to the Item_cache
      flag. Their purpose is to check state of the late_caching flag and cache value
      accordingly.
      Item_cache_xxx::store functions are changed to call init_late_caching prior to
      store cached value.
      Item_cache_xxx::val_xxx functions are changed to call cache_value prior to
      return cached value if late_caching is enabled.
      The Arg_comparator::set_cmp_func function is changed to cache a value being
      compared if it's a constant and needs type conversion.
      Now the Item_func_like::fix_fields function denies caching arguments of the LIKE
      function.
      The Item_cache::get_cache function is overloaded to allow setting of the
      late_caching flag and cache type.
      The cache_converted_constant function is added to the Arg_comparator class.
      It checks whether a value can and should be cached and if so caches it.
     @ sql/item.cc
        Bug#34384: Slow down on constant conversion.
        A flag named uncacheable is added to the Item class. It denies caching of the item.
        The Item_cache::get_cache function is overloaded to allow setting of the
        late_caching flag and cache type.
        Item_cache_xxx::val_xxx functions are changed to call cache_value prior to return a value.
        Item_cache_xxx::store functions are changed to call init_late_caching
        prior to store cached value.
     @ sql/item.h
        Bug#34384: Slow down on constant conversion.
        A flag named uncacheable is added to the Item class. It denies caching of the
        item.
        A flag named late_caching is added to the Item_cache class. It's set to TRUE
        when we need to postpone caching till the first use of cached value.
        The Item_cache::get_cache function is overloaded to allow setting of the
        late_caching flag and cache type.
        Functions named init_late_caching and cache_value() are added to the Item_cache
        flag. Their purpose is to check state of the late_caching flag and cache value
        accordingly.
     @ sql/item_cmpfunc.cc
        Bug#34384: Slow down on constant conversion.
        A helper function cache_converted_constant is added to thhe Arg_comparator class.
        It checks whether a given item can and should be cached and caches it if so.
     @ sql/item_cmpfunc.h
        Bug#34384: Slow down on constant conversion.
        The cache_converted_constant function is added to the Arg_comparator class.
        It checks whether a value can and should be cached and if so caches it.
     @ sql/item_xmlfunc.cc
        Bug#34384: Slow down on constant conversion.

Patch approved.  Still a bit uncertain whether the performance improvement of this edge case, justifies complicating the caching mechanism.  I will leave that to Sergei to decide.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/87624

3147 Evgeny Potemkin	2009-10-21
      Bug#34384: Slow down on constant conversion.
      When values of different types are compared they're converted to a type that
      allows correct comparison. This conversion is done for each comparison and
      takes some time. When a constant is being compared it's possible to cache the
      value after conversion to speedup comparison.
      
      A test case isn't provided because all changes are internal and isn't visible
      outside.
      
      The behaviour of the Item_cache is changed to cache values on the first request
      of cached value rather than at the moment of storing item to be cached.
      A flag named value_caching is added to the Item_cache class. It's set to TRUE
      when we need to start caching values when the store method is called.
      Functions named init_cache and cache_value() are added to the Item_cache
      class. Their purpose is to check state of the value_caching flag and store
      item to be cached or cache its value accordingly.
      Item_cache_xxx::store functions are changed to call init_caching prior to
      store cached value.
      Item_cache_xxx::val_xxx functions are changed to call cache_value prior to
      return cached value if value_caching is true.
      The Arg_comparator::set_cmp_func function is changed to cache a value being
      compared if it's a constant and needs type conversion.
      The Item_cache::get_cache function is overloaded to allow setting of the
      cache type.
      The cache_converted_constant function is added to the Arg_comparator class.
      It checks whether a value can and should be cached and if so caches it.
     @ sql/item.cc
        Bug#34384: Slow down on constant conversion.
        The Item_cache::get_cache function is overloaded to allow setting of the
        cache type.
        Item_cache_xxx::val_xxx functions are changed to call cache_value prior to return a value.
        Item_cache_xxx::store functions are changed to call init_cache
        prior to storing cached value.
     @ sql/item.h
        Bug#34384: Slow down on constant conversion.
        A flag named value_caching is added to the Item_cache class. It's set to TRUE
        when we need to start caching values when the store method is called.
        Functions named init_cache and cache_value() are added to the Item_cache
        class. Their purpose is to check state of the value_caching flag and store
        item to be cached or cache its value accordingly.
     @ sql/item_cmpfunc.cc
        Bug#34384: Slow down on constant conversion.
        A helper function cache_converted_constant is added to the Arg_comparator class.
        It checks whether a given item can and should be cached and caches it if so.
     @ sql/item_cmpfunc.h
        Bug#34384: Slow down on constant conversion.
        The cache_converted_constant function is added to the Arg_comparator class.
        It checks whether a value can and should be cached and if so caches it.
     @ sql/item_subselect.cc
        Bug#34384: Slow down on constant conversion.
        Force immediate caching of subselect result.
     @ sql/item_xmlfunc.cc
        Bug#34384: Slow down on constant conversion.
     @ sql/sp_rcontext.cc
        Bug#34384: Slow down on constant conversion.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/89581

3200 Evgeny Potemkin	2009-11-06
      Bug#34384: Slow down on constant conversion.
      When values of different types are compared they're converted to a type that
      allows correct comparison. This conversion is done for each comparison and
      takes some time. When a constant is being compared it's possible to cache the
      value after conversion to speedup comparison.
      
      A test case isn't provided because all changes are internal and isn't visible
      outside.
      
      The behavior of the Item_cache is changed to cache values on the first request
      of cached value rather than at the moment of storing item to be cached.
      A flag named value_cached is added to the Item_cache class. It's set to TRUE
      when cache holds the value of the last stored item.
      Function named cache_value() is added to the Item_cache class and derived classes.
      This function actually caches the value of the saved item.
      Item_cache_xxx::store functions now only store item to be cached and set
      value_cached flag to FALSE.
      Item_cache_xxx::val_xxx functions are changed to call cache_value function
      prior to returning cached value if value_cached is FALSE.
      The Arg_comparator::set_cmp_func function now calls cache_converted_constant
      to cache constants if they need a type conversion.
      The Item_cache::get_cache function is overloaded to allow setting of the
      cache type.
      The cache_converted_constant function is added to the Arg_comparator class.
      It checks whether a value can and should be cached and if so caches it.
     @ sql/item.cc
        Bug#34384: Slow down on constant conversion.
        Function named cache_value() is added to the Item_cache class and derived classes.
        This function actually caches the value of the saved item.
        Item_cache_xxx::store functions now only store item to be cached and set
        value_cached flag to FALSE.
        Item_cache_xxx::val_xxx functions are changed to call cache_value function
        prior to returning cached value if value_cached is FALSE.
        The Item_cache::get_cache function is overloaded to allow setting of the
        cache type.
     @ sql/item.h
        Bug#34384: Slow down on constant conversion.
        A flag named value_cached is added to the Item_cache class. It's set to TRUE
        when we need to start caching values when the store method is called.
        Function named cache_value() is added to the Item_cache class and derived classes.
     @ sql/item_cmpfunc.cc
        Bug#34384: Slow down on constant conversion.
        A helper function cache_converted_constant is added to the Arg_comparator class.
        It checks whether a given item can and should be cached and caches it if so.
        The Arg_comparator::set_cmp_func function now calls cache_converted_constant
        to cache constants if they need a type conversion.
     @ sql/item_cmpfunc.h
        Bug#34384: Slow down on constant conversion.
        The cache_converted_constant function is added to the Arg_comparator class.
        It checks whether a value can and should be cached and if so caches it.
     @ sql/item_subselect.cc
        Bug#34384: Slow down on constant conversion.
        Force immediate caching of subselect result.
     @ sql/item_xmlfunc.cc
        Bug#34384: Slow down on constant conversion.
     @ sql/sp_rcontext.cc
        Bug#34384: Slow down on constant conversion.
        Force immediate caching of values of an SP CASE function.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/89671

3200 Evgeny Potemkin	2009-11-06
      Bug#34384: Slow down on constant conversion.
      When values of different types are compared they're converted to a type that
      allows correct comparison. This conversion is done for each comparison and
      takes some time. When a constant is being compared it's possible to cache the
      value after conversion to speedup comparison. In some cases (large dataset,
      complex WHERE condition with many type conversions) query might be executed
      7% faster.
      
      A test case isn't provided because all changes are internal and isn't visible
      outside.
      
      The behavior of the Item_cache is changed to cache values on the first request
      of cached value rather than at the moment of storing item to be cached.
      A flag named value_cached is added to the Item_cache class. It's set to TRUE
      when cache holds the value of the last stored item.
      Function named cache_value() is added to the Item_cache class and derived classes.
      This function actually caches the value of the saved item.
      Item_cache_xxx::store functions now only store item to be cached and set
      value_cached flag to FALSE.
      Item_cache_xxx::val_xxx functions are changed to call cache_value function
      prior to returning cached value if value_cached is FALSE.
      The Arg_comparator::set_cmp_func function now calls cache_converted_constant
      to cache constants if they need a type conversion.
      The Item_cache::get_cache function is overloaded to allow setting of the
      cache type.
      The cache_converted_constant function is added to the Arg_comparator class.
      It checks whether a value can and should be cached and if so caches it.
     @ sql/item.cc
        Bug#34384: Slow down on constant conversion.
        Function named cache_value() is added to the Item_cache class and derived classes.
        This function actually caches the value of the saved item.
        Item_cache_xxx::store functions now only store item to be cached and set
        value_cached flag to FALSE.
        Item_cache_xxx::val_xxx functions are changed to call cache_value function
        prior to returning cached value if value_cached is FALSE.
        The Item_cache::get_cache function is overloaded to allow setting of the
        cache type.
     @ sql/item.h
        Bug#34384: Slow down on constant conversion.
        A flag named value_cached is added to the Item_cache class. It's set to TRUE
        when we need to start caching values when the store method is called.
        Function named cache_value() is added to the Item_cache class and derived classes.
     @ sql/item_cmpfunc.cc
        Bug#34384: Slow down on constant conversion.
        A helper function cache_converted_constant is added to the Arg_comparator class.
        It checks whether a given item can and should be cached and caches it if so.
        The Arg_comparator::set_cmp_func function now calls cache_converted_constant
        to cache constants if they need a type conversion.
     @ sql/item_cmpfunc.h
        Bug#34384: Slow down on constant conversion.
        The cache_converted_constant function is added to the Arg_comparator class.
        It checks whether a value can and should be cached and if so caches it.
     @ sql/item_subselect.cc
        Bug#34384: Slow down on constant conversion.
        Force immediate caching of subselect result.
     @ sql/item_xmlfunc.cc
        Bug#34384: Slow down on constant conversion.
     @ sql/sp_rcontext.cc
        Bug#34384: Slow down on constant conversion.
        Force immediate caching of values of an SP CASE function.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/89672

3201 Evgeny Potemkin	2009-11-06 [merge]
      Auto-merged fix for the bug#34384.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/92346

2911 Evgeny Potemkin	2009-12-01 [merge]
      Auto-merged fix for the bug#34384.

Pushed into 5.1.42 (revid:joro@sun.com-20091202080033-mndu4sxwx19lz2zs) (version source revid:kristofer.pettersson@sun.com-20091109223504-xvwgsdqiyuve6frt) (merge vers: 5.1.41) (pib:13)

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091211055901-yp18b3c7xuhl87rf) (version source revid:alik@sun.com-20091211055401-43rjwq7gjed6ds83) (merge vers: 6.0.14-alpha) (pib:13)

Pushed into 5.6.0-beta (revid:alik@sun.com-20091211055628-ltr7fero363uev7r) (version source revid:alik@sun.com-20091211055453-717czhtezc74u8db) (merge vers: 5.6.0-beta) (pib:13)

Noted in 5.1.42, 5.6.0, 6.0.14 changelogs.

If a comparison involved a constant value that required type
conversion, the converted value might not be cached, resulting in
repeated conversion and poorer performance.

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091216083311-xorsasf5kopjxshf) (version source revid:alik@sun.com-20091214191830-wznm8245ku8xo702) (merge vers: 6.0.14-alpha) (pib:14)

Pushed into 5.5.0-beta (revid:alik@sun.com-20091216082430-s0gtzibcgkv4pqul) (version source revid:alexey.kopytov@sun.com-20091124081906-6pqi7e7sajimog71) (merge vers: 5.5.0-beta) (pib:14)

Pushed into mysql-next-mr (revid:alik@sun.com-20091216083231-rp8ecpnvkkbhtb27) (version source revid:alik@sun.com-20091212203859-fx4rx5uab47wwuzd) (merge vers: 5.6.0-beta) (pib:14)

Retagged 5.6.0 entry to 5.5.1.

Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@mysql.com-20091211201717-03qf8ckwiw0np80p) (merge vers: 5.6.0-beta) (pib:16)

Pushed into 5.1.44-ndb-7.0.14 (revid:jonas@mysql.com-20100312135944-t0z8s1da2orvl66x) (version source revid:jonas@mysql.com-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)

Pushed into 5.1.44-ndb-6.2.19 (revid:jonas@mysql.com-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:jonas@mysql.com-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)

Pushed into 5.1.44-ndb-6.3.33 (revid:jonas@mysql.com-20100312135724-xcw8vw2lu3mijrhn) (version source revid:jonas@mysql.com-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)

Fixed in earlier 5.1.x, 5.5.x.

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@sun.com-20100928153607-tdsxkdm5cmuym5sq) (version source revid:alik@sun.com-20100928153508-0saa6v93dinqx1u7) (merge vers: 5.6.1-m4) (pib:21)

Pushed into mysql-next-mr (revid:alik@sun.com-20100928153646-pqp8o1a92mxtuj3h) (version source revid:alik@sun.com-20100928153532-lr3gtvnyp2en4y75) (pib:21)

Pushed into mysql-5.5 5.5.7-rc (revid:alik@sun.com-20100928153459-4nudf4zgzlou4s7q) (version source revid:alik@sun.com-20100928153459-4nudf4zgzlou4s7q) (merge vers: 5.5.7-rc) (pib:21)

Pushed into mysql-5.1 5.1.53 (revid:build@mysql.com-20101101184443-o2olipi8vkaxzsqk) (version source revid:build@mysql.com-20101101184443-o2olipi8vkaxzsqk) (merge vers: 5.1.53) (pib:21)