MySQL Bugs: #46749: Segfault in add_key_fields() with outer subquery level field references

Bug #46749	Segfault in add_key_fields() with outer subquery level field references
Submitted:	16 Aug 2009 19:51	Modified:	18 Dec 2009 13:18
Reporter:	Patrick Crews	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.0.84, 5.1.37, 5.4 / 6.0	OS:	Any
Assigned to:	Georgi Kodinov	CPU Architecture:	Any
Tags:	correlated subqueries, crashing bug, segmentation fault, subquery

Description:
The following query causes a crash / segfault  in Bitmap<64u>::merge (this=0xcc, map2=@0xb050fe54) at sql_bitmap.h:216

SELECT  COUNT( table2 .`varchar_nokey`  )  , (  
SELECT SUBQUERY1_t1 .`int_nokey`  
FROM D SUBQUERY1_t1  STRAIGHT_JOIN B SUBQUERY1_t2  ON SUBQUERY1_t2 .`pk`  
WHERE SUBQUERY1_t2 .`varchar_nokey`  <= table1 .`varchar_key`  )  
FROM D table1  JOIN D table2  ON table1 .`varchar_nokey`  
WHERE (  
SELECT `varchar_nokey`  
FROM D  
WHERE table2 .`varchar_nokey`  )  AND table1 .`pk`  =  230   ;

All parts of this query are required to trigger the crash - both subqueries and the pk=<constant> portion.  Removing any portion causes the query to process without incident.

Partial backtrace (the full output is attached as a separate file):

# 13:31:29 Thread 11 (core thread 10):
# 13:31:29 #0  0x90f03402 in __assert_rtn ()
# 13:31:29 #1  0x005aa6dc in my_write_core (sig=10) at stacktrace.c:309
# 13:31:29 #2  0x000fcbd3 in handle_segfault (sig=10) at mysqld.cc:2738
# 13:31:29 #3  <signal handler called>
# 13:31:29 #4  0x00175ff5 in Bitmap<64u>::merge (this=0xcc, map2=@0xb050fe54) at sql_bitmap.h:216
# 13:31:29 #5  0x001884de in add_key_field (key_fields=0xb0510210, and_level=0, cond=0x1321958, field=0x127e230, eq_func=false, value=0x13219b0, num_values=1, usable_tables=18446744073709551615, sargables=0xb0510508) at sql_select.cc:4886
# 13:31:29 #6  0x001888a8 in add_key_equal_fields (key_fields=0xb0510210, and_level=0, cond=0x1321958, field_item=0x13218c0, eq_func=false, val=0x13219b0, num_values=1, usable_tables=18446744073709551615, sargables=0xb0510508) at sql_select.cc:5027
# 13:31:29 #7  0x00189393 in add_key_fields (join=0x2c31028, key_fields=0xb0510210, and_level=0xb0510214, cond=0x1321958, usable_tables=18446744073709551615, sargables=0xb0510508) at sql_select.cc:5181
# 13:31:29 #8  0x00188a8f in add_key_fields (join=0x2c31028, key_fields=0xb0510210, and_level=0xb0510214, cond=0x2c40878, usable_tables=18446744073709551615, sargables=0xb0510508) at sql_select.cc:5064
# 13:31:29 #9  0x0018a25d in update_ref_and_keys (thd=0x1249018, keyuse=0x2c35d54, join_tab=0x2c41028, tables=3, cond=0x2c40878, cond_equal=0x2c4090c, normal_tables=18446744073709551615, select_lex=0x12561e0, sargables=0xb0510508) at sql_select.cc:5534
# 13:31:29 #10 0x001b1e9d in make_join_statistics (join=0x2c31028, tables_arg=0x1256f00, conds=0x2c40878, keyuse_array=0x2c35d54) at sql_select.cc:4176
# 13:31:29 #11 0x001b3e8f in JOIN::optimize (this=0x2c31028) at sql_select.cc:1609
# 13:31:29 #12 0x000ac532 in subselect_single_select_engine::exec (this=0x11bfdb0) at item_subselect.cc:2233
# 13:31:29 #13 0x000a6d1a in Item_subselect::exec (this=0x1321a48) at item_subselect.cc:285
# 13:31:29 #14 0x000a7bfc in Item_singlerow_subselect::val_str (this=0x1321a48, str=0xb0510e98) at item_subselect.cc:648
# 13:31:29 #15 0x000293e9 in Item::send (this=0x1321a48, protocol=0x124931c, buffer=0xb0510e98) at item.cc:5755
# 13:31:29 #16 0x000f103e in Protocol::send_result_set_row (this=0x124931c, row_items=0x124a4b0) at protocol.cc:830
# 13:31:29 #17 0x000e11c1 in select_send::send_data (this=0x13beae0, items=@0x124a4b0) at sql_class.cc:1818
# 13:31:29 #18 0x001b98d0 in return_zero_rows (join=0x19fa028, result=0x13beae0, tables=0x1338028, fields=@0x124a4b0, send_row=true, select_options=2147764737, info=0x6a15b8 "no matching row in const table", having=0x0) at sql_select.cc:10908
# 13:31:29 #19 0x001b9f73 in JOIN::exec (this=0x19fa028) at sql_select.cc:2404
# 13:31:29 #20 0x001bc09b in mysql_select (thd=0x1249018, rref_pointer_array=0x124a520, tables=0x1338028, wild_num=0, fields=@0x124a4b0, conds=0x13be290, og_num=6, order=0x13be620, group=0x0, having=0x0, proc_param=0x0, select_options=2147764737, result=0x13beae0, unit=0x1249f6c, select_lex=0x124a41c) at sql_select.cc:3091
# 13:31:29 #21 0x001bc3ed in handle_select (thd=0x1249018, lex=0x1249f10, result=0x13beae0, setup_tables_done_option=0) at sql_select.cc:306
# 13:31:29 #22 0x0010f550 in execute_sqlcom_select (thd=0x1249018, all_tables=0x1338028) at sql_parse.cc:4925
# 13:31:29 #23 0x001159de in mysql_execute_command (thd=0x1249018) at sql_parse.cc:2112
# 13:31:29 #24 0x0011f4bb in mysql_parse (thd=0x1249018, inBuf=0x1255428 "SELECT DISTINCT  table1 . `varchar_key` AS field1 , SUM( table1 . `int_key` ) AS field2 , COUNT( table2 . `varchar_nokey` ) AS field3 ,  ( SELECT   SUM( SUBQUERY1_t1 . `int_nokey` ) AS SUBQUERY1_field"..., length=1133, found_semicolon=0xb0512e14) at sql_parse.cc:5940
# 13:31:29 #25 0x00120038 in dispatch_command (command=COM_QUERY, thd=0x1249018, packet=0x19f0019 "", packet_length=1134) at sql_parse.cc:1062
# 13:31:29 #26 0x0012140e in do_command (thd=0x1249018) at sql_parse.cc:744
# 13:31:29 #27 0x0010ce7b in handle_one_connection (arg=0x1249018) at sql_connect.cc:1163
# 13:31:29 #28 0x90e53155 in _pthread_start ()
# 13:31:29 #29 0x90e53012 in thread_start ()

How to repeat:
Simplified test case.

The full test case with the original, more complex query is attached as a separate file, but this is sufficient to reproduce the crash.

/*!50400 SET SESSION optimizer_switch = 'firstmatch=on,index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,loosescan=on,materialization=off,semijoin=on' */;
/*!50400 SET SESSION optimizer_use_mrr = 'force' */;
/*!50400 SET SESSION engine_condition_pushdown = 'ON' */;
/*!50400 SET SESSION join_cache_level = 1 */;

#/* Begin test case for query 0 */

--disable_warnings
DROP TABLE /*! IF EXISTS */ D;
DROP TABLE /*! IF EXISTS */ B;
--enable_warnings

CREATE TABLE `D` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_nokey` int(11) DEFAULT NULL,
  `varchar_key` varchar(1) DEFAULT NULL,
  `varchar_nokey` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `varchar_key` (`varchar_key`)
) ENGINE=MyISAM AUTO_INCREMENT=101 DEFAULT CHARSET=latin1;
INSERT INTO `D` VALUES (1,8,'c','c'),(2,6,'o','o'),(3,6,'c','c'),(4,3,'d','d'),(5,9,'v','v'),(6,2,'m','m'),(7,1,'j','j'),(8,8,'f','f'),(9,0,'n','n'),(10,9,'z','z'),(11,8,'h','h'),(12,NULL,'q','q'),(13,0,'w','w'),(14,5,'z','z'),(15,1,'j','j'),(16,1,'a','a'),(17,6,'m','m'),(18,6,'n','n'),(19,1,'e','e'),(20,8,'u','u'),(21,1,'s','s'),(22,0,'u','u'),(23,4,'r','r'),(24,9,'g','g'),(25,8,'o','o'),(26,5,'w','w'),(27,9,'b','b'),(28,5,NULL,NULL),(29,NULL,'y','y'),(30,NULL,'y','y'),(31,105,'u','u'),(32,0,'p','p'),(33,3,'s','s'),(34,1,'e','e'),(35,75,'d','d'),(36,9,'d','d'),(37,7,'c','c'),(38,NULL,'b','b'),(39,NULL,'t','t'),(40,4,NULL,NULL),(41,0,'y','y'),(42,204,'c','c'),(43,0,'d','d'),(44,9,'x','x'),(45,8,'p','p'),(46,7,'e','e'),(47,8,'g','g'),(48,NULL,'x','x'),(49,6,'s','s'),(50,5,'e','e'),(51,2,'l','l'),(52,3,'p','p'),(53,7,'h','h'),(54,NULL,'m','m'),(55,145,'n','n'),(56,0,'v','v'),(57,1,'b','b'),(58,7,'x','x'),(59,3,'r','r'),(60,NULL,'t','t'),(61,2,'w','w'),(62,2,'w','w'),(63,2,'k','k'),(64,8,'a','a'),(65,6,'t','t'),(66,1,'z','z'),(67,NULL,'e','e'),(68,1,'q','q'),(69,0,'e','e'),(70,4,'v','v'),(71,1,'d','d'),(72,1,'u','u'),(73,27,'o','o'),(74,4,'b','b'),(75,6,'c','c'),(76,2,'q','q'),(77,248,NULL,NULL),(78,NULL,'h','h'),(79,9,'d','d'),(80,75,'w','w'),(81,2,'m','m'),(82,9,'i','i'),(83,4,'w','w'),(84,0,'f','f'),(85,0,'k','k'),(86,1,'v','v'),(87,119,'c','c'),(88,1,'y','y'),(89,7,'h','h'),(90,2,NULL,NULL),(91,7,'t','t'),(92,2,'l','l'),(93,6,'a','a'),(94,4,'r','r'),(95,5,'s','s'),(96,7,'z','z'),(97,1,'j','j'),(98,7,'c','c'),(99,2,'f','f'),(100,1,'g','g');
CREATE TABLE `B` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_nokey` int(11) DEFAULT NULL,
  `varchar_key` varchar(1) DEFAULT NULL,
  `varchar_nokey` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `varchar_key` (`varchar_key`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
INSERT INTO `B` VALUES (1,1,'f','f'),(2,NULL,'w','w');

 
SELECT  COUNT( table2 .`varchar_nokey`  )  , (  
SELECT SUBQUERY1_t1 .`int_nokey`  
FROM D SUBQUERY1_t1  STRAIGHT_JOIN B SUBQUERY1_t2  ON SUBQUERY1_t2 .`pk`  
WHERE SUBQUERY1_t2 .`varchar_nokey`  <= table1 .`varchar_key`  )  
FROM D table1  JOIN D table2  ON table1 .`varchar_nokey`  
WHERE (  
SELECT `varchar_nokey`  
FROM D  
WHERE table2 .`varchar_nokey`  )  AND table1 .`pk`  =  230   ;

DROP TABLE D;
DROP TABLE B;
#/* End of test case for query 0 */

Suggested fix:
Ensure correct, crash-free query processing.

full rqg-produced error output including backtrace

Attachment: bug46749_crash_output.txt (text/plain), 19.77 KiB.

full MTR test case for the bug, includes original and simplified queries

Attachment: bug46749_test.txt (text/plain), 9.71 KiB.

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/81531

2795 Georgi Kodinov	2009-08-25
      Bug #46749: Segfault in add_key_fields() with outer subquery level 
        field references
      
      This error requires a combination of factors : 
      1. An "impossible where" in the outermost SELECT
      2. An aggregate in the outermost SELECT
      3. A correlated subquery with a WHERE clause that includes an outer 
      field reference as a top level WHERE sargable predicate
      
      When JOIN::optimize detects an "impossible WHERE" it will bail out
      without doing the rest of the work and initializations. It will not
      call make_join_statistics() as well.  And make_join_statistics fills 
      in various structures for each table referenced.
      When processing the result of the "impossible WHERE" the query must
      send a single row of data if there are aggregate functions in it.
      In this case the server marks all the aggregates as having received 
      no rows and calls the relevant Item::val_xxx() method on the SELECT
      list. However if this SELECT list happens to contain a correlated 
      subquery this subquery is evaluated in a normal evaluation mode.
      And if this correlated subquery has a reference to a field from the 
      outermost "impossible where" SELECT the add_key_fields will mistakenly
      consider the outer field reference as a "local" field reference when 
      looking for sargable predicates.
      But since the SELECT where the outer field reference refers to is not
      completely initialized due to the "impossible WHERE" in this level
      we'll get a NULL pointer reference.
      Fixed by making a better condition for discovering if a field is "local"
      to the SELECT level being processed. 
      It's not enough to look for OUTER_REF_TABLE_BIT in this case since 
      for outer references to constant tables the Item_field::used_tables() 
      will return 0 regardless of whether the field reference is from the 
      local SELECT or not.

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/81705

2795 Georgi Kodinov	2009-08-27
      Bug #46749: Segfault in add_key_fields() with outer subquery level 
        field references
      
      This error requires a combination of factors : 
      1. An "impossible where" in the outermost SELECT
      2. An aggregate in the outermost SELECT
      3. A correlated subquery with a WHERE clause that includes an outer 
      field reference as a top level WHERE sargable predicate
      
      When JOIN::optimize detects an "impossible WHERE" it will bail out
      without doing the rest of the work and initializations. It will not
      call make_join_statistics() as well.  And make_join_statistics fills 
      in various structures for each table referenced.
      When processing the result of the "impossible WHERE" the query must
      send a single row of data if there are aggregate functions in it.
      In this case the server marks all the aggregates as having received 
      no rows and calls the relevant Item::val_xxx() method on the SELECT
      list. However if this SELECT list happens to contain a correlated 
      subquery this subquery is evaluated in a normal evaluation mode.
      And if this correlated subquery has a reference to a field from the 
      outermost "impossible where" SELECT the add_key_fields will mistakenly
      consider the outer field reference as a "local" field reference when 
      looking for sargable predicates.
      But since the SELECT where the outer field reference refers to is not
      completely initialized due to the "impossible WHERE" in this level
      we'll get a NULL pointer reference.
      Fixed by making a better condition for discovering if a field is "local"
      to the SELECT level being processed. 
      It's not enough to look for OUTER_REF_TABLE_BIT in this case since 
      for outer references to constant tables the Item_field::used_tables() 
      will return 0 regardless of whether the field reference is from the 
      local SELECT or not.

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/81711

2795 Georgi Kodinov	2009-08-27
      Bug #46749: Segfault in add_key_fields() with outer subquery level 
        field references
      
      This error requires a combination of factors : 
      1. An "impossible where" in the outermost SELECT
      2. An aggregate in the outermost SELECT
      3. A correlated subquery with a WHERE clause that includes an outer 
      field reference as a top level WHERE sargable predicate
      
      When JOIN::optimize detects an "impossible WHERE" it will bail out
      without doing the rest of the work and initializations. It will not
      call make_join_statistics() as well.  And make_join_statistics fills 
      in various structures for each table referenced.
      When processing the result of the "impossible WHERE" the query must
      send a single row of data if there are aggregate functions in it.
      In this case the server marks all the aggregates as having received 
      no rows and calls the relevant Item::val_xxx() method on the SELECT
      list. However if this SELECT list happens to contain a correlated 
      subquery this subquery is evaluated in a normal evaluation mode.
      And if this correlated subquery has a reference to a field from the 
      outermost "impossible where" SELECT the add_key_fields will mistakenly
      consider the outer field reference as a "local" field reference when 
      looking for sargable predicates.
      But since the SELECT where the outer field reference refers to is not
      completely initialized due to the "impossible WHERE" in this level
      we'll get a NULL pointer reference.
      Fixed by making a better condition for discovering if a field is "local"
      to the SELECT level being processed. 
      It's not enough to look for OUTER_REF_TABLE_BIT in this case since 
      for outer references to constant tables the Item_field::used_tables() 
      will return 0 regardless of whether the field reference is from the 
      local SELECT or not.

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/81992

2794 Georgi Kodinov	2009-08-27
      Bug #46749: Segfault in add_key_fields() with outer subquery level 
        field references
      
      This error requires a combination of factors : 
      1. An "impossible where" in the outermost SELECT
      2. An aggregate in the outermost SELECT
      3. A correlated subquery with a WHERE clause that includes an outer 
      field reference as a top level WHERE sargable predicate
      
      When JOIN::optimize detects an "impossible WHERE" it will bail out
      without doing the rest of the work and initializations. It will not
      call make_join_statistics() as well.  And make_join_statistics fills 
      in various structures for each table referenced.
      When processing the result of the "impossible WHERE" the query must
      send a single row of data if there are aggregate functions in it.
      In this case the server marks all the aggregates as having received 
      no rows and calls the relevant Item::val_xxx() method on the SELECT
      list. However if this SELECT list happens to contain a correlated 
      subquery this subquery is evaluated in a normal evaluation mode.
      And if this correlated subquery has a reference to a field from the 
      outermost "impossible where" SELECT the add_key_fields will mistakenly
      consider the outer field reference as a "local" field reference when 
      looking for sargable predicates.
      But since the SELECT where the outer field reference refers to is not
      completely initialized due to the "impossible WHERE" in this level
      we'll get a NULL pointer reference.
      Fixed by making a better condition for discovering if a field is "local"
      to the SELECT level being processed. 
      It's not enough to look for OUTER_REF_TABLE_BIT in this case since 
      for outer references to constant tables the Item_field::used_tables() 
      will return 0 regardless of whether the field reference is from the 
      local SELECT or not.

Pushed into 5.0.86 (revid:joro@sun.com-20090902102337-n5rw8227wwp5cpx8) (version source revid:azundris@mysql.com-20090831195601-n6vjz48chxgoz9qr) (merge vers: 5.0.86) (pib:11)

Pushed into 5.1.39 (revid:joro@sun.com-20090902154533-8actmfcsjfqovgsb) (version source revid:azundris@mysql.com-20090831195422-o13kn73s2hsnk0z0) (merge vers: 5.1.39) (pib:11)

Bug #46770 made a duplicate of this one

Pushed into 5.4.4-alpha (revid:alik@sun.com-20090914155317-m1g9wodmndzdj4l1) (version source revid:alik@sun.com-20090914155317-m1g9wodmndzdj4l1) (merge vers: 5.4.4-alpha) (pib:11)

Noted in 5.0.86, 5.1.39, 5.4.4 changelogs:

The server could crash for queries with the following elements:
      1. An "impossible where" in the outermost SELECT
      2. An aggregate in the outermost SELECT
      3. A correlated subquery with a WHERE clause that includes an outer 
      field reference as a top level WHERE sargable predicate

Pushed into 5.1.39-ndb-6.3.28 (revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (version source revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (merge vers: 5.1.39-ndb-6.3.28) (pib:11)

Pushed into 5.1.39-ndb-7.0.9 (revid:jonas@mysql.com-20091001072547-kv17uu06hfjhgjay) (version source revid:jonas@mysql.com-20091001071652-irejtnumzbpsbgk2) (merge vers: 5.1.39-ndb-7.0.9) (pib:11)

Pushed into 5.1.39-ndb-7.1.0 (revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (version source revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (merge vers: 5.1.39-ndb-7.1.0) (pib:11)

Moved 5.4 changelog entry from 5.4.4 to 5.4.3.

Pushed into 5.1.39-ndb-6.2.19 (revid:jonas@mysql.com-20091005103850-dwij2dojwpvf5hi6) (version source revid:jonas@mysql.com-20090930185117-bhud4ek1y0hsj1nv) (merge vers: 5.1.39-ndb-6.2.19) (pib:11)

Noted in 5.0.84sp1 changelog.

Pushed into 5.0.88 (revid:build@mysql.com-20091014081604-yhwy9zh6fq8kcurj) (version source revid:build@mysql.com-20091014081604-yhwy9zh6fq8kcurj) (merge vers: 5.0.88) (pib:13)

Pushed into 5.1.41 (revid:joro@sun.com-20091014143611-cphb0enjlx6lpat1) (version source revid:joro@sun.com-20091014143611-cphb0enjlx6lpat1) (merge vers: 5.1.41) (pib:13)

Already noted in earlier changelogs.

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091022063126-l0qzirh9xyhp0bpc) (version source revid:alik@sun.com-20091019135554-s1pvptt6i750lfhv) (merge vers: 6.0.14-alpha) (pib:13)

Pushed into 5.5.0-beta (revid:alik@sun.com-20091022060553-znkmxm0g0gm6ckvw) (version source revid:alik@sun.com-20091019131937-nchb8tjk88jpfjav) (merge vers: 5.5.0-beta) (pib:13)

Noted in 5.5.0, 6.0.14 changelogs.

Pushed into 5.1.41-ndb-7.1.0 (revid:jonas@mysql.com-20091218102229-64tk47xonu3dv6r6) (version source revid:jonas@mysql.com-20091218095730-26gwjidfsdw45dto) (merge vers: 5.1.41-ndb-7.1.0) (pib:15)

Pushed into 5.1.41-ndb-6.2.19 (revid:jonas@mysql.com-20091218100224-vtzr0fahhsuhjsmt) (version source revid:jonas@mysql.com-20091217101452-qwzyaig50w74xmye) (merge vers: 5.1.41-ndb-6.2.19) (pib:15)

Pushed into 5.1.41-ndb-6.3.31 (revid:jonas@mysql.com-20091218100616-75d9tek96o6ob6k0) (version source revid:jonas@mysql.com-20091217154335-290no45qdins5bwo) (merge vers: 5.1.41-ndb-6.3.31) (pib:15)

Pushed into 5.1.41-ndb-7.0.11 (revid:jonas@mysql.com-20091218101303-ga32mrnr15jsa606) (version source revid:jonas@mysql.com-20091218064304-ezreonykd9f4kelk) (merge vers: 5.1.41-ndb-7.0.11) (pib:15)

Already noted in earlier changelogs.