MySQL Bugs: #47754: Assertion fail - mark_full_partition_used_with_parts() at at opt

Bug #47754	Assertion fail - mark_full_partition_used_with_parts() at at opt_range.cc:2914
Submitted:	30 Sep 2009 22:06	Modified:	12 Nov 2009 12:03
Reporter:	Patrick Crews	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	5.5 WL#3352	OS:	Any
Assigned to:	Mikael Ronström	CPU Architecture:	Any
Tags:	assertion, crashing bug, LIST COLUMN_LIST

Description:
The following query:
SELECT `int_signed_key`  FROM `PP_D`  WHERE `int_signed`  NOT  BETWEEN  8  AND  8   ;

Crashes when table PP_D uses LIST COLUMN_LIST partitioning:

Partial crash ouput (full text attached as separate file)
# 17:45:34 Thread 1 (process 27746):
# 17:45:34 #0  0xb7f0b430 in __kernel_vsyscall ()
# 17:45:34 #1  0xb7eee1c8 in pthread_kill () from /lib/tls/i686/cmov/libpthread.so.0
# 17:45:34 #2  0x090af893 in my_write_core (sig=6) at stacktrace.c:310
# 17:45:34 #3  0x0845194f in handle_segfault (sig=6) at mysqld.cc:2569
# 17:45:34 #4  <signal handler called>
# 17:45:34 #5  0xb7f0b430 in __kernel_vsyscall ()
# 17:45:34 #6  0xb7d356d0 in raise () from /lib/tls/i686/cmov/libc.so.6
# 17:45:34 #7  0xb7d37098 in abort () from /lib/tls/i686/cmov/libc.so.6
# 17:45:34 #8  0xb7d2e5ce in __assert_fail () from /lib/tls/i686/cmov/libc.so.6
# 17:45:34 #9  0x0876c5f4 in bitmap_set_bit (map=0xe281378, bit=1263225674) at ../include/my_bitmap.h:122
# 17:45:34 #10 0x08772c42 in mark_full_partition_used_with_parts (part_info=0xe2812e0, part_id=2779096485) at opt_range.cc:2914
# 17:45:34 #11 0x087a7dd0 in find_used_partitions (ppar=0x6975492c, key_tree=0xe294e98) at opt_range.cc:3443
# 17:45:34 #12 0x087a91fe in prune_partitions (thd=0xe27ec30, table=0xe2bf718, pprune_cond=0xe2c5f10) at opt_range.cc:2771
# 17:45:34 #13 0x08615dd8 in JOIN::optimize (this=0xe2d95e0) at sql_select.cc:881
# 17:45:34 #14 0x08620707 in mysql_select (thd=0xe27ec30, rref_pointer_array=0xe2800c8, tables=0xe2c5588, wild_num=0, fields=@0xe280064, conds=0xe2c5f10, 
# 17:45:34     og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0xe2c6010, unit=0xe27fd60, select_lex=0xe27ffd0)
# 17:45:34     at sql_select.cc:2387
# 17:45:34 #15 0x0862f243 in handle_select (thd=0xe27ec30, lex=0xe27fd04, result=0xe2c6010, setup_tables_done_option=0) at sql_select.cc:270
# 17:45:34 #16 0x084810e9 in execute_sqlcom_select (thd=0xe27ec30, all_tables=0xe2c5588) at sql_parse.cc:5074
# 17:45:34 #17 0x08486798 in mysql_execute_command (thd=0xe27ec30) at sql_parse.cc:2236
# 17:45:34 #18 0x084a4df7 in mysql_parse (thd=0xe27ec30, 
# 17:45:34     inBuf=0xe2c5188 "SELECT table1 . `date_key` AS field1, table1 . `int_signed_key` AS field2 FROM `PP_D` AS table1 WHERE ( ( table1 . `int_signed` NOT BETWEEN 8 AND ( 8 + 8 ) ) OR table1 . `int_signed`  BETWEEN 8 AND ( "..., length=209, found_semicolon=0x69758054) at sql_parse.cc:6002
# 17:45:34 #19 0x084a759c in dispatch_command (command=COM_QUERY, thd=0xe27ec30, 
# 17:45:34     packet=0xe290b71 " SELECT table1 . `date_key` AS field1, table1 . `int_signed_key` AS field2 FROM `PP_D` AS table1 WHERE ( ( table1 . `int_signed` NOT BETWEEN 8 AND ( 8 + 8 ) ) OR table1 . `int_signed`  BETWEEN 8 AND ("..., packet_length=210) at sql_parse.cc:1222
# 17:45:34 #20 0x084ab1f1 in do_command (thd=0xe27ec30) at sql_parse.cc:855
# 17:45:34 #21 0x0846fe77 in handle_one_connection (arg=0xe27ec30) at sql_connect.cc:1131
# 17:45:34 #22 0xb7ee94ff in start_thread () from /lib/tls/i686/cmov/libpthread.so.0
# 17:45:34 #23 0xb7dee49e in clone () from /lib/tls/i686/cmov/libc.so.6
# 17:45:34 The last 100 lines from <path>/mysql-5.0/mysql-test/var/master-data/../log/master.err :
CURRENT_TEST: alias
091001  0:45:06 [Warning] <path>/mysql-5.0/sql/mysqld: unknown variable 'loose-falcon-debug-mask=2'
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
091001  0:45:06  InnoDB: Setting file ./ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
091001  0:45:06  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
091001  0:45:06  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
091001  0:45:06  InnoDB: Started; log sequence number 0 0
091001  0:45:06 [Note] <path>/mysql-5.0/sql/mysqld: ready for connections.
Version: '5.0.87-debug-log'  socket: '<path>/mysql-5.0/mysql-test/var/tmp/master.sock'  port: 19508  Source distribution
# 17:45:34 datadir is <path>/mysql-5.0/mysql-test/var/master-data/
# 17:45:34 Core file appears to be 
"<path>/rqg/mysql-test/gentest/" is not a core dump: Is a directory
No stack.
# 17:45:35 
"<path>/rqg/mysql-test/gentest/" is not a core dump: Is a directory
<path>/rqg/mysql-test/gentest/backtrace-all.gdb:1: Error in sourced command file:
No registers.
# 17:45:35 
# 17:45:35 Test completed with failure status 101.
# 17:45:35 gentest.pl exited with exit status 101
Wed Sep 30 17:45:35 2009 [27588] ./runall.pl will exit with exit status 101

How to repeat:
MTR test case:

#/* Begin test case for query 0 */

--disable_warnings
DROP TABLE /*! IF EXISTS */ PP_D;
--enable_warnings

CREATE TABLE `PP_D` (
  `int_signed_key` int(11) DEFAULT NULL,
  `int_signed` int(11) DEFAULT NULL,
  `date` date DEFAULT NULL,
  KEY `int_signed_key` (`int_signed_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY LIST  COLUMN_LIST(int_signed)
SUBPARTITION BY HASH ( to_seconds(`date`))
SUBPARTITIONS 2
(PARTITION p0 VALUES IN ( COLUMN_LIST(NULL), COLUMN_LIST(0), COLUMN_LIST(1), COLUMN_LIST(2), COLUMN_LIST(3), COLUMN_LIST(4), COLUMN_LIST(5), COLUMN_LIST(6), COLUMN_LIST(7), COLUMN_LIST(8), COLUMN_LIST(9)) ENGINE = MyISAM) */;
INSERT INTO `PP_D` VALUES (1,1,'2003-09-20');
INSERT INTO `PP_D` VALUES (0,2,'2005-09-11');
INSERT INTO `PP_D` VALUES (6,8,'2001-03-25');
INSERT INTO `PP_D` VALUES (7,NULL,NULL);
INSERT INTO `PP_D` VALUES (4,3,'2008-05-24');
INSERT INTO `PP_D` VALUES (9,7,NULL);
INSERT INTO `PP_D` VALUES (1,7,'2008-10-22');
INSERT INTO `PP_D` VALUES (9,3,'0000-00-00');
INSERT INTO `PP_D` VALUES (NULL,3,'0000-00-00');
INSERT INTO `PP_D` VALUES (1,6,'2000-02-10');

 
SELECT `int_signed_key`  
FROM `PP_D`  
WHERE `int_signed`  NOT  BETWEEN  8  AND  8   ;

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

#/* Begin test case for query 1 */

--disable_warnings
DROP TABLE /*! IF EXISTS */ PP_D;
--enable_warnings

CREATE TABLE `PP_D` (
  `int_signed_key` int(11) DEFAULT NULL,
  `int_signed` int(11) DEFAULT NULL,
  `date_key` date DEFAULT NULL,
  `date` date DEFAULT NULL,
  KEY `int_signed_key` (`int_signed_key`),
  KEY `date_key` (`date_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY LIST  COLUMN_LIST(int_signed)
SUBPARTITION BY HASH ( to_seconds(`date`))
SUBPARTITIONS 2
(PARTITION p0 VALUES IN ( COLUMN_LIST(NULL), COLUMN_LIST(0), COLUMN_LIST(1), COLUMN_LIST(2), COLUMN_LIST(3), COLUMN_LIST(4), COLUMN_LIST(5), COLUMN_LIST(6), COLUMN_LIST(7), COLUMN_LIST(8), COLUMN_LIST(9)) ENGINE = MyISAM) */;
INSERT INTO `PP_D` VALUES (1,1,NULL,'2003-09-20');
INSERT INTO `PP_D` VALUES (0,2,'2001-07-26','2005-09-11');
INSERT INTO `PP_D` VALUES (6,8,'2006-06-14','2001-03-25');
INSERT INTO `PP_D` VALUES (7,NULL,NULL,NULL);
INSERT INTO `PP_D` VALUES (4,3,'0000-00-00','2008-05-24');
INSERT INTO `PP_D` VALUES (9,7,'2001-10-23',NULL);
INSERT INTO `PP_D` VALUES (1,7,'2001-11-14','2008-10-22');
INSERT INTO `PP_D` VALUES (9,3,NULL,'0000-00-00');
INSERT INTO `PP_D` VALUES (NULL,3,'0000-00-00','0000-00-00');
INSERT INTO `PP_D` VALUES (1,6,'2003-06-18','2000-02-10');

SELECT table1 . `date_key` AS field1, table1 . `int_signed_key` AS field2 
FROM `PP_D` AS table1 
WHERE ( ( table1 . `int_signed` NOT BETWEEN 8 AND ( 8 + 8 ) ) OR table1 . `int_signed`  BETWEEN 8 AND ( 8 + 5 ) );

DROP TABLE PP_D;
#/* End of test case for query 1 */

Suggested fix:
Ensure crash-free query processing regardless of partitioning scheme

Full RQG-produced crash output

Attachment: bug47754_crash_output.txt (text/plain), 33.81 KiB.

This appears to be contingent on having a NOT <range> portion of the query as I stumbled upon this bug again via a NOT IN ((0)) clause.

Even more generically it crashes on
select * from t1 where a > 8;
Should be simple to fix, it sets the end partition
to scan to -1 it seems.

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

2883 Mikael Ronstrom	2009-10-02
      BUG#47754, used number of parts instead of number of list values as end part for list partitioning in column list partitioning
      modified:
        mysql-test/r/partition_column.result
        mysql-test/t/partition_column.test
        sql/sql_partition.cc

Pushed into 6.0.14-alpha (revid:mikael@mysql.com-20091104090210-om5lq1v39ppduu0e) (version source revid:mikael@mysql.com-20091030163450-387z4yevx0lrj3fb) (merge vers: 6.0.14-alpha) (pib:13)

Discussed with Mikael; he confirmed that this fix was pushed together with main push for WL#3352; closed without further action.

Pushed into 5.5.0-beta (revid:alik@sun.com-20091110093229-0bh5hix780cyeicl) (version source revid:mikael@mysql.com-20091028172236-0v5j962mh2opxpkj) (merge vers: 5.5.0-beta) (pib:13)

Re-closed; see previous comments.