Bug #48458 simple query tries to allocate enormous amount of memory!
Submitted: 2 Nov 2009 6:55 Modified: 20 Jun 2010 1:08
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:5.0.85, 5.1.40 OS:Any
Assigned to: Georgi Kodinov
Triage: Triaged: D1 (Critical)

[2 Nov 2009 6:55] Shane Bester
Description:
simple query with particular column types in table tries to allocate too much memory:

mysql> select 1 from
    -> `t2` join `t1` on 1=1
    -> where `a` != '1'  and not
    -> `a` >= `b`  or not
    -> row(`b`,`a` )<> row(`a`,`a`);
ERROR 5 (HY000): Out of memory (Needed 4041059096 bytes)

How to repeat:
#on a 32-bit server, or 64-bit server with less than 4G memory, run this:

drop table if exists `t1`,`t2`;
create table `t1`(`a` int not null,`b` year)engine=myisam;
insert into `t1` values ();
create table `t2`(`c` int)engine=myisam;
select 1 from
`t2` join `t1` on 1=1
where `a` != '1'  and not
`a` >= `b`  or not
row(`b`,`a` )<> row(`a`,`a`);

if you dont get out of memory, at least check the maximum memory allocated or
use debug build so that it does initialize memory properly.
[2 Nov 2009 7:41] Valerii Kravchuk
Verified just as described with recent 5.1.41 from bzr on Linux. Even EXPLAIN allocates too much memory. 

Debug build gives out of memory error at line 201 of my_alloc.c.
[5 Nov 2009 11:19] Bugs System
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/89451

2840 Georgi Kodinov	2009-11-05
      Bug #48458: simple query tries to allocate enormous amount of
        memory
      
      The server was doing a bad class typecast causing setting of 
      wrong value for the maximum number of items in an internal
      structure used in equality propagation.
      Fixed by not doing the wrong typecast and asserting the type
      of the Item where it should be done.
[9 Nov 2009 13:42] Martin Hansson
Review sent by email.
[9 Nov 2009 14:10] Bugs System
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/89817

2840 Georgi Kodinov	2009-11-09
      Bug #48458: simple query tries to allocate enormous amount of
        memory
      
      The server was doing a bad class typecast causing setting of 
      wrong value for the maximum number of items in an internal
      structure used in equality propagation.
      Fixed by not doing the wrong typecast and asserting the type
      of the Item where it should be done.
[10 Nov 2009 10:47] Bugs System
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/89931

2844 Georgi Kodinov	2009-11-09
      Bug #48458: simple query tries to allocate enormous amount of
        memory
      
      The server was doing a bad class typecast causing setting of 
      wrong value for the maximum number of items in an internal
      structure used in equality propagation.
      Fixed by not doing the wrong typecast and asserting the type
      of the Item where it should be done.
[2 Dec 2009 8:01] Bugs System
Pushed into 5.0.89 (revid:joro@sun.com-20091202075830-mzl79q7mc1v72pf1) (version source revid:joro@sun.com-20091109140946-07wao5od7l1vn4x1) (merge vers: 5.0.88) (pib:13)
[2 Dec 2009 8:03] Bugs System
Pushed into 5.1.42 (revid:joro@sun.com-20091202080033-mndu4sxwx19lz2zs) (version source revid:joro@sun.com-20091110105902-js68v56ok7ve6a43) (merge vers: 5.1.41) (pib:13)
[10 Dec 2009 2:16] Paul Dubois
Noted in 5.0.89, 5.1.42 changelogs.

A bad typecast could cause query execution to allocate large amounts
of memory. 

Setting report to NDI pending push to 5.5.x+.
[16 Dec 2009 8:35] Bugs System
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)
[16 Dec 2009 8:43] Bugs System
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)
[16 Dec 2009 8:49] Bugs System
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)
[18 Dec 2009 2:00] Paul Dubois
Noted in 5.5.1, 6.0.14 changelogs.
[10 Feb 2010 8:58] Charon Me
-- simpler test case:

drop table if exists t;
create table t(a int not null)engine=myisam;
select 1 from t where 0 or ((NULL,a)=(a,a));

-- crashes with out of memory on 5.1.37-1ubuntu5
[17 Feb 2010 16:27] Paul Dubois
Noted in 5.0.87sp1 changelog.
[20 Feb 2010 17:10] Bugs System
Pushed into 5.0.91 (revid:build@mysql.com-20100220170835-5kr6ztsg25va7qzz) (version source revid:build@mysql.com-20100220170835-5kr6ztsg25va7qzz) (merge vers: 5.0.91) (pib:16)
[1 Mar 2010 8:42] Bugs System
Pushed into 5.1.45 (revid:joro@sun.com-20100301083827-xnimmrjg6bh33o1o) (version source revid:joro@sun.com-20100226131646-kpvzk740hxbtaexn) (merge vers: 5.1.45) (pib:16)
[2 Mar 2010 14:35] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100302142746-u1gxdf5yk2bjrq3e) (version source revid:alik@sun.com-20100301095421-4cz64ibem1h2quve) (merge vers: 6.0.14-alpha) (pib:16)
[2 Mar 2010 14:40] Bugs System
Pushed into 5.5.3-m2 (revid:alik@sun.com-20100302072233-t3uqgjzdukt1pyhe) (version source revid:alik@sun.com-20100301090215-63o2w2y16go8n53p) (merge vers: 5.5.3-m2) (pib:16)
[2 Mar 2010 14:45] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100302072432-k8xvfkgcggkwgi94) (version source revid:alik@sun.com-20100301094536-2zc4uqyy3os8san7) (pib:16)
[12 Mar 2010 14:08] Bugs System
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)
[12 Mar 2010 14:24] Bugs System
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)
[12 Mar 2010 14:37] Bugs System
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)
[17 Jun 2010 11:54] Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:32] Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:martin.skold@mysql.com-20100609140708-52rvuyq4q500sxkq) (merge vers: 5.1.45-ndb-6.2.19) (pib:16)
[17 Jun 2010 13:20] Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)