[MDEV-3606] LP:887468 - Second assertion `keypart_map' failed in maria_rkey with semijoin - JIRA

Details

Type: Bug
Status: Closed
Priority: Critical
Resolution: Fixed
Affects Version/s: None
Fix Version/s: None
Component/s: None
Labels:
- Launchpad

Description

Even with the fix for bug 860535

this query:

SELECT *
FROM t1
JOIN t2
ON ( t2.col_int_key = t1.pk )
WHERE t1.col_varchar_key IN (
SELECT t3.col_varchar_key FROM t3, t4
);

asserts as follows:

mysqld: ma_rkey.c:69: maria_rkey: Assertion `keypart_map' failed.

#5 0x00000035a6e340d5 in abort () from /lib64/libc.so.6
#6 0x00000035a6e2b8b5 in __assert_fail () from /lib64/libc.so.6
#7 0x00000000009e6625 in maria_rkey (info=0x7f80600f53c8,
buf=0x7f8060041d70 "\377\377\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217", inx=5, key_data=0x7f80600c3990 "", keypart_map=0, search_flag=HA_READ_KEY_EXACT) at ma_rkey.c:69
#8 0x00000000009c4975 in ha_maria::index_read_map (this=0x7f8060041670,
buf=0x7f8060041d70 "\377\377\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217", key=0x7f80600c3990 "", keypart_map=0, find_flag=HA_READ_KEY_EXACT) at ha_maria.cc:2266
#9 0x00000000005a9a36 in handler::ha_index_read_map (this=0x7f8060041670,
buf=0x7f8060041d70 "\377\377\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217", key=0x7f80600c3990 "", keypart_map=0, find_flag=HA_READ_KEY_EXACT) at sql_class.h:3655
#10 0x0000000000766fea in join_read_always_key (tab=0x7f80600c2d10) at sql_select.cc:15803
#11 0x000000000076557d in sub_select (join=0x7f80600b5c70, join_tab=0x7f80600c2d10, end_of_records=false) at sql_select.cc:15129
#12 0x0000000000764d52 in do_select (join=0x7f80600b5c70, fields=0x254bca8, table=0x0, procedure=0x0) at sql_select.cc:14795
#13 0x0000000000744f90 in JOIN::exec (this=0x7f80600b5c70) at sql_select.cc:2679
#14 0x0000000000745811 in mysql_select (thd=0x2549218, rref_pointer_array=0x254bdf0, tables=0x7f80600188b8, wild_num=1, fields=..., conds=0x7f806001a440,
og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0x7f80600a9410, unit=0x254b6b8, select_lex=0x254bba0)
at sql_select.cc:2900
#15 0x000000000073c6c7 in handle_select (thd=0x2549218, lex=0x254b618, result=0x7f80600a9410, setup_tables_done_option=0) at sql_select.cc:283
#16 0x00000000006c88cb in execute_sqlcom_select (thd=0x2549218, all_tables=0x7f80600188b8) at sql_parse.cc:5112
#17 0x00000000006bf62a in mysql_execute_command (thd=0x2549218) at sql_parse.cc:2250
#18 0x00000000006cb417 in mysql_parse (thd=0x2549218,
rawbuf=0x7f8060018600 "SELECT * \nFROM t1\nJOIN t2\nON ( t2.col_int_key = t1.pk )\nWHERE t1.col_varchar_key IN (\nSELECT t3.col_varchar_key FROM t3, t4\n)", length=126, found_semicolon=0x7f806fffdc68) at sql_parse.cc:6113
#19 0x00000000006bce67 in dispatch_command (command=COM_QUERY, thd=0x2549218, packet=0x25c3f09 "", packet_length=126) at sql_parse.cc:1221
#20 0x00000000006bc1e7 in do_command (thd=0x2549218) at sql_parse.cc:916
#21 0x00000000006b90c3 in handle_one_connection (arg=0x2549218) at sql_connect.cc:1191
#22 0x00000035a7207761 in start_thread () from /lib64/libpthread.so.0
#23 0x00000035a6ee098d in clone () from /lib64/libc.so.6

Explain:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 ref col_varchar_key NULL NULL NULL 3 Using where; Using index; Start temporary
1 PRIMARY t4 index NULL PRIMARY 4 NULL 3 Using index; Using join buffer (flat, BNL join)
1 PRIMARY t1 ref PRIMARY,col_varchar_key col_varchar_key 4 test.t3.col_varchar_key 3 End temporary
1 PRIMARY t2 ref col_int_key col_int_key 5 test.t1.pk 3

minimal switch: semijoin=on?
full switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on

bzr version-info:
revision-id: <email address hidden>
date: 2011-11-07 16:39:02 +0400
build-date: 2011-11-08 11:03:47 +0200
revno: 3273
branch-nick: maria-5.3

Gliffy Diagrams

Attachments

Options
- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

Attachments

LPexportBug887468.xml
32 kB
2012-10-03 11:32

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Philip Stoev added a comment - 2011-11-08 09:05

Re: Second assertion `keypart_map' failed in maria_rkey with semijoin
Test case. Due to the instability of the plan and the impossibility to force particular semijoin plans, further simplification was not possible.

CREATE TABLE t1 ( pk int(11) NOT NULL AUTO_INCREMENT, col_int_nokey int(11) DEFAULT NULL, col_int_key int(11) DEFAULT NULL, col_date_key date DEFAULT NULL, col_date_nokey date DEFAULT NULL, col_time_key time DEFAULT NULL, col_time_nokey time DEFAULT NULL, col_datetime_key datetime DEFAULT NULL, col_datetime_nokey datetime DEFAULT NULL, col_varchar_key varchar(1) DEFAULT NULL, col_varchar_nokey varchar(1) DEFAULT NULL, PRIMARY KEY (pk), KEY col_int_key (col_int_key), KEY col_date_key (col_date_key), KEY col_time_key (col_time_key), KEY col_datetime_key (col_datetime_key), KEY col_varchar_key (col_varchar_key,col_int_key)) ENGINE=Aria AUTO_INCREMENT=30 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1;
INSERT INTO t1 VALUES (10,2,0,'2002-10-20','2002-10-20','03:47:16','03:47:16',NULL,NULL,NULL,NULL),(11,5,4,'2008-09-12','2008-09-12','01:41:48','01:41:48','1900-01-01 00:00:00','1900-01-01 00:00:00','d','d'),(12,7,8,'2006-06-16','2006-06-16','00:00:00','00:00:00',NULL,NULL,'g','g'),(13,6,NULL,'2004-09-18','2004-09-18','22:32:04','22:32:04','2007-08-22 14:10:22','2007-08-22 14:10:22','x','x'),(14,6,NULL,'1900-01-01','1900-01-01','16:44:14','16:44:14','2005-01-07 12:15:05','2005-01-07 12:15:05','f','f'),(15,2,0,'2005-09-13','2005-09-13','17:38:37','17:38:37','2008-07-18 00:00:00','2008-07-18 00:00:00','p','p'),(16,9,NULL,'2007-04-09','2007-04-09','08:46:48','08:46:48','2005-03-24 07:33:11','2005-03-24 07:33:11','j','j'),(17,6,8,'2000-09-20','2000-09-20','14:11:27','14:11:27','2003-06-13 23:19:49','2003-06-13 23:19:49','c','c'),(18,0,8,'1900-01-01','1900-01-01','07:55:54','07:55:54','2008-11-09 06:28:05','2008-11-09 06:28:05','z','z'),(19,3,6,NULL,NULL,'14:18:47','14:18:47','2009-02-11 10:59:50','2009-02-11 10:59:50','j','j'),(20,8,2,'2009-09-06','2009-09-06','00:00:00','00:00:00','2000-02-23 00:39:41','2000-02-23 00:39:41',NULL,NULL),(21,8,3,'2008-08-23','2008-08-23','01:57:54','01:57:54','1900-01-01 00:00:00','1900-01-01 00:00:00','p','p'),(22,6,1,'2009-03-24','2009-03-24','21:24:27','21:24:27','2009-07-03 13:47:15','2009-07-03 13:47:15','w','w'),(23,0,NULL,'2009-11-15','2009-11-15',NULL,NULL,'2001-04-11 12:39:06','2001-04-11 12:39:06','c','c'),(24,NULL,1,'2001-12-23','2001-12-23','21:34:24','21:34:24','2007-01-26 15:51:38','2007-01-26 15:51:38','j','j'),(25,158,10,'2007-02-11','2007-02-11','18:11:35','18:11:35','2003-04-27 19:48:50','2003-04-27 19:48:50','f','f'),(26,5,2,'2005-04-16','2005-04-16','09:21:51','09:21:51','2001-11-15 10:51:46','2001-11-15 10:51:46','v','v'),(27,163,103,'2007-05-12','2007-05-12','09:02:21','09:02:21','1900-01-01 00:00:00','1900-01-01 00:00:00','f','f'),(28,2,3,'2000-07-20','2000-07-20','22:40:03','22:40:03','2000-10-20 17:09:41','2000-10-20 17:09:41','q','q'),(29,8,6,'2008-08-14','2008-08-14','19:29:48','19:29:48','2005-10-03 00:00:00','2005-10-03 00:00:00','y','y');

CREATE TABLE t2 ( pk int(11) NOT NULL AUTO_INCREMENT, col_int_nokey int(11) DEFAULT NULL, col_int_key int(11) DEFAULT NULL, col_date_key date DEFAULT NULL, col_date_nokey date DEFAULT NULL, col_time_key time DEFAULT NULL, col_time_nokey time DEFAULT NULL, col_datetime_key datetime DEFAULT NULL, col_datetime_nokey datetime DEFAULT NULL, col_varchar_key varchar(1) DEFAULT NULL, col_varchar_nokey varchar(1) DEFAULT NULL, PRIMARY KEY (pk), KEY col_int_key (col_int_key), KEY col_date_key (col_date_key), KEY col_time_key (col_time_key), KEY col_datetime_key (col_datetime_key), KEY col_varchar_key (col_varchar_key,col_int_key)) ENGINE=Aria AUTO_INCREMENT=101 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1;
INSERT INTO t2 VALUES (1,7,8,'2002-06-05','2002-06-05',NULL,NULL,'2009-01-24 15:15:41','2009-01-24 15:15:41','e','e'),(2,4,2,'2009-08-09','2009-08-09','00:00:00','00:00:00','2004-04-25 15:55:43','2004-04-25 15:55:43','l','l'),(3,7,9,'2001-03-22','2001-03-22','02:43:46','02:43:46','2006-05-21 00:00:00','2006-05-21 00:00:00',NULL,NULL),(4,7,6,'2002-10-08','2002-10-08','23:08:56','23:08:56','2007-11-19 18:59:17','2007-11-19 18:59:17','v','v'),(5,8,NULL,NULL,NULL,'21:14:43','21:14:43','2004-10-11 00:00:00','2004-10-11 00:00:00','c','c'),(6,6,NULL,'2007-09-13','2007-09-13','10:37:15','10:37:15','1900-01-01 00:00:00','1900-01-01 00:00:00','u','u'),(7,3,48,'2003-12-08','2003-12-08','00:50:58','00:50:58','2003-11-22 00:42:24','2003-11-22 00:42:24','x','x'),(8,210,228,'2006-06-01','2006-06-01','15:11:05','15:11:05','2009-05-06 15:57:54','2009-05-06 15:57:54','x','x'),(9,1,3,'2001-10-04','2001-10-04','17:56:53','17:56:53','2000-12-26 00:33:56','2000-12-26 00:33:56','x','x'),(10,2,5,'2004-11-16','2004-11-16','01:40:28','01:40:28','2002-03-12 00:00:00','2002-03-12 00:00:00','l','l'),(11,251,39,NULL,NULL,'01:21:50','01:21:50','2005-06-12 11:13:17','2005-06-12 11:13:17','e','e'),(12,4,6,'2005-06-22','2005-06-22','21:17:36','21:17:36','2009-06-10 00:00:00','2009-06-10 00:00:00','s','s'),(13,4,8,'2003-04-19','2003-04-19','09:16:37','09:16:37','2002-04-07 00:00:00','2002-04-07 00:00:00','k','k'),(14,9,3,'2006-03-23','2006-03-23','16:41:37','16:41:37','2002-08-01 01:33:30','2002-08-01 01:33:30','m','m'),(15,4,NULL,NULL,NULL,'03:13:46','03:13:46','2003-04-05 11:02:01','2003-04-05 11:02:01','x','x'),(16,NULL,2,'2008-10-22','2008-10-22','17:27:08','17:27:08','2003-10-25 12:46:30','2003-10-25 12:46:30','s','s'),(17,4,6,'2009-04-04','2009-04-04','19:27:17','19:27:17','2007-08-10 00:00:00','2007-08-10 00:00:00','h','h'),(18,NULL,3,NULL,NULL,'00:51:44','00:51:44','2002-07-12 19:18:28','2002-07-12 19:18:28','u','u'),(19,1,1,'2001-07-15','2001-07-15','10:57:22','10:57:22','2004-06-24 00:00:00','2004-06-24 00:00:00','x','x'),(20,6,4,'2004-09-25','2004-09-25','13:28:27','13:28:27','2009-06-10 09:54:42','2009-06-10 09:54:42','l','l'),(21,2,3,'2001-02-20','2001-02-20','06:40:59','06:40:59','1900-01-01 00:00:00','1900-01-01 00:00:00','p','p'),(22,NULL,1,'1900-01-01','1900-01-01','22:56:45','22:56:45','2002-03-09 11:26:35','2002-03-09 11:26:35','i','i'),(23,4,NULL,'2004-08-02','2004-08-02','12:51:42','12:51:42','2004-02-22 21:45:20','2004-02-22 21:45:20','u','u'),(24,248,97,'2009-02-26','2009-02-26','01:52:32','01:52:32',NULL,NULL,'i','i'),(25,4,0,'2002-11-23','2002-11-23','20:42:07','20:42:07','2000-08-12 00:00:00','2000-08-12 00:00:00','i','i'),(26,8,0,'2008-12-17','2008-12-17','12:53:30','12:53:30','2003-05-24 18:11:13','2003-05-24 18:11:13','e','e'),(27,4,9,'2005-08-26','2005-08-26','02:10:47','02:10:47','2000-12-21 15:20:30','2000-12-21 15:20:30','h','h'),(28,5,5,'2001-03-10','2001-03-10','02:13:00','02:13:00','2006-10-27 05:05:38','2006-10-27 05:05:38','f','f'),(29,9,9,'1900-01-01','1900-01-01','08:21:57','08:21:57','2008-08-04 14:29:37','2008-08-04 14:29:37',NULL,NULL),(30,2,0,'2003-08-04','2003-08-04','01:48:05','01:48:05','2006-11-03 00:00:00','2006-11-03 00:00:00','p','p'),(31,4,2,'2007-11-06','2007-11-06','00:00:00','00:00:00','2009-11-26 19:28:11','2009-11-26 19:28:11','n','n'),(32,211,172,'2009-04-23','2009-04-23','00:00:00','00:00:00','2000-12-07 10:17:40','2000-12-07 10:17:40','h','h'),(33,2,NULL,'2002-10-06','2002-10-06','00:50:49','00:50:49',NULL,NULL,'m','m'),(34,4,5,'2008-02-12','2008-02-12',NULL,NULL,'1900-01-01 00:00:00','1900-01-01 00:00:00','x','x'),(35,125,119,'2007-09-18','2007-09-18',NULL,NULL,'2007-11-27 00:00:00','2007-11-27 00:00:00','d','d'),(36,9,1,'2007-06-26','2007-06-26','07:26:24','07:26:24','2007-11-17 06:51:08','2007-11-17 06:51:08','d','d'),(37,4,4,NULL,NULL,'13:41:48','13:41:48','2009-12-03 06:39:37','2009-12-03 06:39:37','t','t'),(38,8,8,'2009-05-05','2009-05-05','04:03:35','04:03:35','2004-03-11 16:35:26','2004-03-11 16:35:26',NULL,NULL),(39,8,NULL,'2003-05-04','2003-05-04','03:45:26','03:45:26','2008-09-08 17:10:40','2008-09-08 17:10:40',NULL,NULL),(40,NULL,6,'2003-11-10','2003-11-10','00:00:00','00:00:00','2004-04-15 20:15:45','2004-04-15 20:15:45','v','v'),(41,8,5,'2009-02-19','2009-02-19','17:06:44','17:06:44','2005-04-19 21:52:38','2005-04-19 21:52:38','u','u'),(42,4,5,'2006-11-17','2006-11-17','18:47:54','18:47:54','2000-02-03 00:00:00','2000-02-03 00:00:00','p','p'),(43,8,1,'2000-02-23','2000-02-23','14:35:14','14:35:14','2002-06-10 17:21:09','2002-06-10 17:21:09','o','o'),(44,NULL,7,'1900-01-01','1900-01-01','07:50:59','07:50:59',NULL,NULL,'v','v'),(45,1,2,'2003-04-08','2003-04-08',NULL,NULL,'2005-02-09 17:44:31','2005-02-09 17:44:31','m','m'),(46,3,8,'2006-07-08','2006-07-08','12:35:26','12:35:26','2002-12-19 09:51:49','2002-12-19 09:51:49','x','x'),(47,5,9,'2001-05-21','2001-05-21','05:30:51','05:30:51',NULL,NULL,'n','n'),(48,8,NULL,NULL,NULL,'00:00:00','00:00:00','2005-06-27 07:34:53','2005-06-27 07:34:53','b','b'),(49,7,NULL,'2009-01-05','2009-01-05','12:42:04','12:42:04','1900-01-01 00:00:00','1900-01-01 00:00:00',NULL,NULL),(50,2,3,'2001-08-05','2001-08-05',NULL,NULL,'1900-01-01 00:00:00','1900-01-01 00:00:00','r','r'),(51,NULL,172,'2000-11-01','2000-11-01','17:54:39','17:54:39','2003-07-08 10:33:13','2003-07-08 10:33:13','v','v'),(52,3,NULL,'2004-12-16','2004-12-16','17:47:16','17:47:16','2006-07-18 04:16:49','2006-07-18 04:16:49','a','a'),(53,NULL,6,'2003-02-06','2003-02-06','00:15:18','00:15:18',NULL,NULL,'u','u'),(54,9,6,'2008-04-23','2008-04-23','01:39:11','01:39:11','2004-05-19 17:08:35','2004-05-19 17:08:35',NULL,NULL),(55,1,5,'2005-12-20','2005-12-20','14:46:31','14:46:31',NULL,NULL,'b','b'),(56,0,4,'2002-03-13','2002-03-13','17:05:47','17:05:47','2006-04-27 12:42:58','2006-04-27 12:42:58','s','s'),(57,0,3,NULL,NULL,'05:32:59','05:32:59','2007-06-12 00:00:00','2007-06-12 00:00:00','t','t'),(58,8,2,'2006-07-20','2006-07-20','13:41:32','13:41:32','1900-01-01 00:00:00','1900-01-01 00:00:00','b','b'),(59,NULL,7,'2002-12-19','2002-12-19',NULL,NULL,'2001-05-18 02:54:34','2001-05-18 02:54:34','m','m'),(60,9,4,'2001-07-09','2001-07-09','04:25:19','04:25:19','2008-06-07 08:08:32','2008-06-07 08:08:32','v','v'),(61,1,6,'2006-05-27','2006-05-27','07:51:52','07:51:52','1900-01-01 00:00:00','1900-01-01 00:00:00','n','n'),(62,9,0,'2007-02-26','2007-02-26','16:19:53','16:19:53','2005-05-11 00:00:00','2005-05-11 00:00:00','j','j'),(63,7,8,NULL,NULL,'00:00:00','00:00:00','2003-12-17 21:47:57','2003-12-17 21:47:57',NULL,NULL),(64,2,5,'2007-02-07','2007-02-07','02:59:31','02:59:31',NULL,NULL,NULL,NULL),(65,1,8,'2005-01-22','2005-01-22','09:04:17','09:04:17','1900-01-01 00:00:00','1900-01-01 00:00:00','h','h'),(66,9,2,'2006-04-25','2006-04-25','19:44:22','19:44:22',NULL,NULL,'k','k'),(67,5,9,'2002-11-13','2002-11-13','00:00:00','00:00:00','2009-06-08 21:21:31','2009-06-08 21:21:31','k','k'),(68,4,7,'2007-10-26','2007-10-26','00:00:00','00:00:00','2008-05-25 23:36:30','2008-05-25 23:36:30',NULL,NULL),(69,6,5,'2003-06-16','2003-06-16','22:16:47','22:16:47','2009-10-17 23:46:36','2009-10-17 23:46:36','n','n'),(70,5,7,'2006-01-18','2006-01-18','06:12:32','06:12:32','2009-02-28 00:00:00','2009-02-28 00:00:00','e','e'),(71,2,0,'1900-01-01','1900-01-01',NULL,NULL,'2004-04-14 13:27:34','2004-04-14 13:27:34','s','s'),(72,4,4,'2000-09-13','2000-09-13','02:38:48','02:38:48','2007-10-20 00:00:00','2007-10-20 00:00:00','w','w'),(73,0,3,'2003-01-26','2003-01-26','00:00:00','00:00:00','2007-11-11 20:10:29','2007-11-11 20:10:29','y','y'),(74,3,1,'2002-09-09','2002-09-09','02:02:49','02:02:49','2001-03-08 00:00:00','2001-03-08 00:00:00','z','z'),(75,0,0,'2001-09-06','2001-09-06','23:43:38','23:43:38','1900-01-01 00:00:00','1900-01-01 00:00:00','b','b'),(76,6,6,'2006-02-23','2006-02-23',NULL,NULL,'2006-07-21 19:26:57','2006-07-21 19:26:57','f','f'),(77,1,2,'2004-06-21','2004-06-21','12:35:01','12:35:01',NULL,NULL,'s','s'),(78,9,NULL,'2006-12-02','2006-12-02','00:18:04','00:18:04',NULL,NULL,'d','d'),(79,4,8,'2005-05-17','2005-05-17','11:53:29','11:53:29','2002-10-24 03:10:15','2002-10-24 03:10:15',NULL,NULL),(80,9,NULL,'2001-12-27','2001-12-27','13:53:19','13:53:19','2002-08-16 00:00:00','2002-08-16 00:00:00','d','d'),(81,4,NULL,'2008-04-11','2008-04-11',NULL,NULL,NULL,NULL,'n','n'),(82,1,NULL,'2000-09-24','2000-09-24','08:53:20','08:53:20','2005-09-09 12:56:20','2005-09-09 12:56:20','i','i'),(83,2,3,'2004-04-20','2004-04-20','10:38:45','10:38:45','2002-06-09 00:00:00','2002-06-09 00:00:00',NULL,NULL),(84,8,7,'2008-07-10','2008-07-10','22:27:36','22:27:36','1900-01-01 00:00:00','1900-01-01 00:00:00','h','h'),(85,1,3,'2007-08-14','2007-08-14','18:06:56','18:06:56','2009-01-21 23:43:07','2009-01-21 23:43:07','d','d'),(86,0,5,'2008-06-08','2008-06-08','03:44:30','03:44:30',NULL,NULL,'c','c'),(87,2,5,'2001-07-26','2001-07-26','11:49:25','11:49:25','2007-09-06 20:49:33','2007-09-06 20:49:33','i','i'),(88,0,1,'2008-09-17','2008-09-17','22:16:07','22:16:07','2005-09-20 10:11:02','2005-09-20 10:11:02','t','t'),(89,1,2,NULL,NULL,'17:14:13','17:14:13','2007-10-11 00:00:00','2007-10-11 00:00:00','g','g'),(90,2,1,'2004-02-25','2004-02-25','14:49:31','14:49:31','2005-06-13 00:26:39','2005-06-13 00:26:39','q','q'),(91,NULL,7,'2009-11-02','2009-11-02','00:27:36','00:27:36','2001-06-18 08:40:49','2001-06-18 08:40:49','l','l'),(92,3,1,'2001-12-14','2001-12-14','13:59:24','13:59:24','2006-06-07 00:00:00','2006-06-07 00:00:00','n','n'),(93,8,9,NULL,NULL,'01:48:46','01:48:46','2004-03-18 22:35:04','2004-03-18 22:35:04','z','z'),(94,4,9,'1900-01-01','1900-01-01',NULL,NULL,NULL,NULL,'n','n'),(95,4,8,'2002-04-12','2002-04-12','11:07:46','11:07:46','2001-07-15 12:46:49','2001-07-15 12:46:49','r','r'),(96,NULL,3,'2000-02-09','2000-02-09',NULL,NULL,'2001-03-27 06:53:22','2001-03-27 06:53:22','p','p'),(97,9,7,'2005-07-23','2005-07-23','15:01:53','15:01:53','2004-05-13 01:01:39','2004-05-13 01:01:39','t','t'),(98,2,4,'2009-11-01','2009-11-01','15:44:41','15:44:41','2003-12-24 07:39:29','2003-12-24 07:39:29','h','h'),(99,6,9,NULL,NULL,NULL,NULL,'2008-07-03 05:32:22','2008-07-03 05:32:22','l','l'),(100,6,0,'2007-07-16','2007-07-16','13:32:31','13:32:31','2008-08-28 18:46:11','2008-08-28 18:46:11','j','j');

CREATE TABLE t3 ( pk int(11) NOT NULL AUTO_INCREMENT, col_int_nokey int(11) DEFAULT NULL, col_int_key int(11) DEFAULT NULL, col_date_key date DEFAULT NULL, col_date_nokey date DEFAULT NULL, col_time_key time DEFAULT NULL, col_time_nokey time DEFAULT NULL, col_datetime_key datetime DEFAULT NULL, col_datetime_nokey datetime DEFAULT NULL, col_varchar_key varchar(1) DEFAULT NULL, col_varchar_nokey varchar(1) DEFAULT NULL, PRIMARY KEY (pk), KEY col_int_key (col_int_key), KEY col_date_key (col_date_key), KEY col_time_key (col_time_key), KEY col_datetime_key (col_datetime_key), KEY col_varchar_key (col_varchar_key,col_int_key)) ENGINE=Aria AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1;
INSERT INTO t3 VALUES (1,150,62,'2005-03-27','2005-03-27','14:26:02','14:26:02','2008-01-03 10:33:32','2008-01-03 10:33:32','v','v'),(2,NULL,7,'2004-04-09','2004-04-09','14:03:03','14:03:03','2001-11-28 00:50:27','2001-11-28 00:50:27','c','c'),(3,2,1,'2006-05-13','2006-05-13','01:46:09','01:46:09','2007-10-09 19:53:04','2007-10-09 19:53:04',NULL,NULL);

CREATE TABLE t4 ( pk int(11) NOT NULL AUTO_INCREMENT, col_int_nokey int(11) DEFAULT NULL, col_int_key int(11) DEFAULT NULL, col_date_key date DEFAULT NULL, col_date_nokey date DEFAULT NULL, col_time_key time DEFAULT NULL, col_time_nokey time DEFAULT NULL, col_datetime_key datetime DEFAULT NULL, col_datetime_nokey datetime DEFAULT NULL, col_varchar_key varchar(1) DEFAULT NULL, col_varchar_nokey varchar(1) DEFAULT NULL, PRIMARY KEY (pk), KEY col_int_key (col_int_key), KEY col_date_key (col_date_key), KEY col_time_key (col_time_key), KEY col_datetime_key (col_datetime_key), KEY col_varchar_key (col_varchar_key,col_int_key)) ENGINE=Aria AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1;
INSERT INTO t4 VALUES (1,150,62,'2005-03-27','2005-03-27','14:26:02','14:26:02','2008-01-03 10:33:32','2008-01-03 10:33:32','v','v'),(2,NULL,7,'2004-04-09','2004-04-09','14:03:03','14:03:03','2001-11-28 00:50:27','2001-11-28 00:50:27','c','c'),(3,2,1,'2006-05-13','2006-05-13','01:46:09','01:46:09','2007-10-09 19:53:04','2007-10-09 19:53:04',NULL,NULL);

SELECT *
FROM t1
JOIN t2
ON ( t2.col_int_key = t1.pk )
WHERE t1.col_varchar_key IN (
SELECT t3.col_varchar_key FROM t3, t4
);

Show

Philip Stoev added a comment - 2011-11-08 09:05 Re: Second assertion `keypart_map' failed in maria_rkey with semijoin Test case. Due to the instability of the plan and the impossibility to force particular semijoin plans, further simplification was not possible. CREATE TABLE t1 ( pk int(11) NOT NULL AUTO_INCREMENT, col_int_nokey int(11) DEFAULT NULL, col_int_key int(11) DEFAULT NULL, col_date_key date DEFAULT NULL, col_date_nokey date DEFAULT NULL, col_time_key time DEFAULT NULL, col_time_nokey time DEFAULT NULL, col_datetime_key datetime DEFAULT NULL, col_datetime_nokey datetime DEFAULT NULL, col_varchar_key varchar(1) DEFAULT NULL, col_varchar_nokey varchar(1) DEFAULT NULL, PRIMARY KEY (pk), KEY col_int_key (col_int_key), KEY col_date_key (col_date_key), KEY col_time_key (col_time_key), KEY col_datetime_key (col_datetime_key), KEY col_varchar_key (col_varchar_key,col_int_key)) ENGINE=Aria AUTO_INCREMENT=30 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1; INSERT INTO t1 VALUES (10,2,0,'2002-10-20','2002-10-20','03:47:16','03:47:16',NULL,NULL,NULL,NULL),(11,5,4,'2008-09-12','2008-09-12','01:41:48','01:41:48','1900-01-01 00:00:00','1900-01-01 00:00:00','d','d'),(12,7,8,'2006-06-16','2006-06-16','00:00:00','00:00:00',NULL,NULL,'g','g'),(13,6,NULL,'2004-09-18','2004-09-18','22:32:04','22:32:04','2007-08-22 14:10:22','2007-08-22 14:10:22','x','x'),(14,6,NULL,'1900-01-01','1900-01-01','16:44:14','16:44:14','2005-01-07 12:15:05','2005-01-07 12:15:05','f','f'),(15,2,0,'2005-09-13','2005-09-13','17:38:37','17:38:37','2008-07-18 00:00:00','2008-07-18 00:00:00','p','p'),(16,9,NULL,'2007-04-09','2007-04-09','08:46:48','08:46:48','2005-03-24 07:33:11','2005-03-24 07:33:11','j','j'),(17,6,8,'2000-09-20','2000-09-20','14:11:27','14:11:27','2003-06-13 23:19:49','2003-06-13 23:19:49','c','c'),(18,0,8,'1900-01-01','1900-01-01','07:55:54','07:55:54','2008-11-09 06:28:05','2008-11-09 06:28:05','z','z'),(19,3,6,NULL,NULL,'14:18:47','14:18:47','2009-02-11 10:59:50','2009-02-11 10:59:50','j','j'),(20,8,2,'2009-09-06','2009-09-06','00:00:00','00:00:00','2000-02-23 00:39:41','2000-02-23 00:39:41',NULL,NULL),(21,8,3,'2008-08-23','2008-08-23','01:57:54','01:57:54','1900-01-01 00:00:00','1900-01-01 00:00:00','p','p'),(22,6,1,'2009-03-24','2009-03-24','21:24:27','21:24:27','2009-07-03 13:47:15','2009-07-03 13:47:15','w','w'),(23,0,NULL,'2009-11-15','2009-11-15',NULL,NULL,'2001-04-11 12:39:06','2001-04-11 12:39:06','c','c'),(24,NULL,1,'2001-12-23','2001-12-23','21:34:24','21:34:24','2007-01-26 15:51:38','2007-01-26 15:51:38','j','j'),(25,158,10,'2007-02-11','2007-02-11','18:11:35','18:11:35','2003-04-27 19:48:50','2003-04-27 19:48:50','f','f'),(26,5,2,'2005-04-16','2005-04-16','09:21:51','09:21:51','2001-11-15 10:51:46','2001-11-15 10:51:46','v','v'),(27,163,103,'2007-05-12','2007-05-12','09:02:21','09:02:21','1900-01-01 00:00:00','1900-01-01 00:00:00','f','f'),(28,2,3,'2000-07-20','2000-07-20','22:40:03','22:40:03','2000-10-20 17:09:41','2000-10-20 17:09:41','q','q'),(29,8,6,'2008-08-14','2008-08-14','19:29:48','19:29:48','2005-10-03 00:00:00','2005-10-03 00:00:00','y','y'); CREATE TABLE t2 ( pk int(11) NOT NULL AUTO_INCREMENT, col_int_nokey int(11) DEFAULT NULL, col_int_key int(11) DEFAULT NULL, col_date_key date DEFAULT NULL, col_date_nokey date DEFAULT NULL, col_time_key time DEFAULT NULL, col_time_nokey time DEFAULT NULL, col_datetime_key datetime DEFAULT NULL, col_datetime_nokey datetime DEFAULT NULL, col_varchar_key varchar(1) DEFAULT NULL, col_varchar_nokey varchar(1) DEFAULT NULL, PRIMARY KEY (pk), KEY col_int_key (col_int_key), KEY col_date_key (col_date_key), KEY col_time_key (col_time_key), KEY col_datetime_key (col_datetime_key), KEY col_varchar_key (col_varchar_key,col_int_key)) ENGINE=Aria AUTO_INCREMENT=101 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1; INSERT INTO t2 VALUES (1,7,8,'2002-06-05','2002-06-05',NULL,NULL,'2009-01-24 15:15:41','2009-01-24 15:15:41','e','e'),(2,4,2,'2009-08-09','2009-08-09','00:00:00','00:00:00','2004-04-25 15:55:43','2004-04-25 15:55:43','l','l'),(3,7,9,'2001-03-22','2001-03-22','02:43:46','02:43:46','2006-05-21 00:00:00','2006-05-21 00:00:00',NULL,NULL),(4,7,6,'2002-10-08','2002-10-08','23:08:56','23:08:56','2007-11-19 18:59:17','2007-11-19 18:59:17','v','v'),(5,8,NULL,NULL,NULL,'21:14:43','21:14:43','2004-10-11 00:00:00','2004-10-11 00:00:00','c','c'),(6,6,NULL,'2007-09-13','2007-09-13','10:37:15','10:37:15','1900-01-01 00:00:00','1900-01-01 00:00:00','u','u'),(7,3,48,'2003-12-08','2003-12-08','00:50:58','00:50:58','2003-11-22 00:42:24','2003-11-22 00:42:24','x','x'),(8,210,228,'2006-06-01','2006-06-01','15:11:05','15:11:05','2009-05-06 15:57:54','2009-05-06 15:57:54','x','x'),(9,1,3,'2001-10-04','2001-10-04','17:56:53','17:56:53','2000-12-26 00:33:56','2000-12-26 00:33:56','x','x'),(10,2,5,'2004-11-16','2004-11-16','01:40:28','01:40:28','2002-03-12 00:00:00','2002-03-12 00:00:00','l','l'),(11,251,39,NULL,NULL,'01:21:50','01:21:50','2005-06-12 11:13:17','2005-06-12 11:13:17','e','e'),(12,4,6,'2005-06-22','2005-06-22','21:17:36','21:17:36','2009-06-10 00:00:00','2009-06-10 00:00:00','s','s'),(13,4,8,'2003-04-19','2003-04-19','09:16:37','09:16:37','2002-04-07 00:00:00','2002-04-07 00:00:00','k','k'),(14,9,3,'2006-03-23','2006-03-23','16:41:37','16:41:37','2002-08-01 01:33:30','2002-08-01 01:33:30','m','m'),(15,4,NULL,NULL,NULL,'03:13:46','03:13:46','2003-04-05 11:02:01','2003-04-05 11:02:01','x','x'),(16,NULL,2,'2008-10-22','2008-10-22','17:27:08','17:27:08','2003-10-25 12:46:30','2003-10-25 12:46:30','s','s'),(17,4,6,'2009-04-04','2009-04-04','19:27:17','19:27:17','2007-08-10 00:00:00','2007-08-10 00:00:00','h','h'),(18,NULL,3,NULL,NULL,'00:51:44','00:51:44','2002-07-12 19:18:28','2002-07-12 19:18:28','u','u'),(19,1,1,'2001-07-15','2001-07-15','10:57:22','10:57:22','2004-06-24 00:00:00','2004-06-24 00:00:00','x','x'),(20,6,4,'2004-09-25','2004-09-25','13:28:27','13:28:27','2009-06-10 09:54:42','2009-06-10 09:54:42','l','l'),(21,2,3,'2001-02-20','2001-02-20','06:40:59','06:40:59','1900-01-01 00:00:00','1900-01-01 00:00:00','p','p'),(22,NULL,1,'1900-01-01','1900-01-01','22:56:45','22:56:45','2002-03-09 11:26:35','2002-03-09 11:26:35','i','i'),(23,4,NULL,'2004-08-02','2004-08-02','12:51:42','12:51:42','2004-02-22 21:45:20','2004-02-22 21:45:20','u','u'),(24,248,97,'2009-02-26','2009-02-26','01:52:32','01:52:32',NULL,NULL,'i','i'),(25,4,0,'2002-11-23','2002-11-23','20:42:07','20:42:07','2000-08-12 00:00:00','2000-08-12 00:00:00','i','i'),(26,8,0,'2008-12-17','2008-12-17','12:53:30','12:53:30','2003-05-24 18:11:13','2003-05-24 18:11:13','e','e'),(27,4,9,'2005-08-26','2005-08-26','02:10:47','02:10:47','2000-12-21 15:20:30','2000-12-21 15:20:30','h','h'),(28,5,5,'2001-03-10','2001-03-10','02:13:00','02:13:00','2006-10-27 05:05:38','2006-10-27 05:05:38','f','f'),(29,9,9,'1900-01-01','1900-01-01','08:21:57','08:21:57','2008-08-04 14:29:37','2008-08-04 14:29:37',NULL,NULL),(30,2,0,'2003-08-04','2003-08-04','01:48:05','01:48:05','2006-11-03 00:00:00','2006-11-03 00:00:00','p','p'),(31,4,2,'2007-11-06','2007-11-06','00:00:00','00:00:00','2009-11-26 19:28:11','2009-11-26 19:28:11','n','n'),(32,211,172,'2009-04-23','2009-04-23','00:00:00','00:00:00','2000-12-07 10:17:40','2000-12-07 10:17:40','h','h'),(33,2,NULL,'2002-10-06','2002-10-06','00:50:49','00:50:49',NULL,NULL,'m','m'),(34,4,5,'2008-02-12','2008-02-12',NULL,NULL,'1900-01-01 00:00:00','1900-01-01 00:00:00','x','x'),(35,125,119,'2007-09-18','2007-09-18',NULL,NULL,'2007-11-27 00:00:00','2007-11-27 00:00:00','d','d'),(36,9,1,'2007-06-26','2007-06-26','07:26:24','07:26:24','2007-11-17 06:51:08','2007-11-17 06:51:08','d','d'),(37,4,4,NULL,NULL,'13:41:48','13:41:48','2009-12-03 06:39:37','2009-12-03 06:39:37','t','t'),(38,8,8,'2009-05-05','2009-05-05','04:03:35','04:03:35','2004-03-11 16:35:26','2004-03-11 16:35:26',NULL,NULL),(39,8,NULL,'2003-05-04','2003-05-04','03:45:26','03:45:26','2008-09-08 17:10:40','2008-09-08 17:10:40',NULL,NULL),(40,NULL,6,'2003-11-10','2003-11-10','00:00:00','00:00:00','2004-04-15 20:15:45','2004-04-15 20:15:45','v','v'),(41,8,5,'2009-02-19','2009-02-19','17:06:44','17:06:44','2005-04-19 21:52:38','2005-04-19 21:52:38','u','u'),(42,4,5,'2006-11-17','2006-11-17','18:47:54','18:47:54','2000-02-03 00:00:00','2000-02-03 00:00:00','p','p'),(43,8,1,'2000-02-23','2000-02-23','14:35:14','14:35:14','2002-06-10 17:21:09','2002-06-10 17:21:09','o','o'),(44,NULL,7,'1900-01-01','1900-01-01','07:50:59','07:50:59',NULL,NULL,'v','v'),(45,1,2,'2003-04-08','2003-04-08',NULL,NULL,'2005-02-09 17:44:31','2005-02-09 17:44:31','m','m'),(46,3,8,'2006-07-08','2006-07-08','12:35:26','12:35:26','2002-12-19 09:51:49','2002-12-19 09:51:49','x','x'),(47,5,9,'2001-05-21','2001-05-21','05:30:51','05:30:51',NULL,NULL,'n','n'),(48,8,NULL,NULL,NULL,'00:00:00','00:00:00','2005-06-27 07:34:53','2005-06-27 07:34:53','b','b'),(49,7,NULL,'2009-01-05','2009-01-05','12:42:04','12:42:04','1900-01-01 00:00:00','1900-01-01 00:00:00',NULL,NULL),(50,2,3,'2001-08-05','2001-08-05',NULL,NULL,'1900-01-01 00:00:00','1900-01-01 00:00:00','r','r'),(51,NULL,172,'2000-11-01','2000-11-01','17:54:39','17:54:39','2003-07-08 10:33:13','2003-07-08 10:33:13','v','v'),(52,3,NULL,'2004-12-16','2004-12-16','17:47:16','17:47:16','2006-07-18 04:16:49','2006-07-18 04:16:49','a','a'),(53,NULL,6,'2003-02-06','2003-02-06','00:15:18','00:15:18',NULL,NULL,'u','u'),(54,9,6,'2008-04-23','2008-04-23','01:39:11','01:39:11','2004-05-19 17:08:35','2004-05-19 17:08:35',NULL,NULL),(55,1,5,'2005-12-20','2005-12-20','14:46:31','14:46:31',NULL,NULL,'b','b'),(56,0,4,'2002-03-13','2002-03-13','17:05:47','17:05:47','2006-04-27 12:42:58','2006-04-27 12:42:58','s','s'),(57,0,3,NULL,NULL,'05:32:59','05:32:59','2007-06-12 00:00:00','2007-06-12 00:00:00','t','t'),(58,8,2,'2006-07-20','2006-07-20','13:41:32','13:41:32','1900-01-01 00:00:00','1900-01-01 00:00:00','b','b'),(59,NULL,7,'2002-12-19','2002-12-19',NULL,NULL,'2001-05-18 02:54:34','2001-05-18 02:54:34','m','m'),(60,9,4,'2001-07-09','2001-07-09','04:25:19','04:25:19','2008-06-07 08:08:32','2008-06-07 08:08:32','v','v'),(61,1,6,'2006-05-27','2006-05-27','07:51:52','07:51:52','1900-01-01 00:00:00','1900-01-01 00:00:00','n','n'),(62,9,0,'2007-02-26','2007-02-26','16:19:53','16:19:53','2005-05-11 00:00:00','2005-05-11 00:00:00','j','j'),(63,7,8,NULL,NULL,'00:00:00','00:00:00','2003-12-17 21:47:57','2003-12-17 21:47:57',NULL,NULL),(64,2,5,'2007-02-07','2007-02-07','02:59:31','02:59:31',NULL,NULL,NULL,NULL),(65,1,8,'2005-01-22','2005-01-22','09:04:17','09:04:17','1900-01-01 00:00:00','1900-01-01 00:00:00','h','h'),(66,9,2,'2006-04-25','2006-04-25','19:44:22','19:44:22',NULL,NULL,'k','k'),(67,5,9,'2002-11-13','2002-11-13','00:00:00','00:00:00','2009-06-08 21:21:31','2009-06-08 21:21:31','k','k'),(68,4,7,'2007-10-26','2007-10-26','00:00:00','00:00:00','2008-05-25 23:36:30','2008-05-25 23:36:30',NULL,NULL),(69,6,5,'2003-06-16','2003-06-16','22:16:47','22:16:47','2009-10-17 23:46:36','2009-10-17 23:46:36','n','n'),(70,5,7,'2006-01-18','2006-01-18','06:12:32','06:12:32','2009-02-28 00:00:00','2009-02-28 00:00:00','e','e'),(71,2,0,'1900-01-01','1900-01-01',NULL,NULL,'2004-04-14 13:27:34','2004-04-14 13:27:34','s','s'),(72,4,4,'2000-09-13','2000-09-13','02:38:48','02:38:48','2007-10-20 00:00:00','2007-10-20 00:00:00','w','w'),(73,0,3,'2003-01-26','2003-01-26','00:00:00','00:00:00','2007-11-11 20:10:29','2007-11-11 20:10:29','y','y'),(74,3,1,'2002-09-09','2002-09-09','02:02:49','02:02:49','2001-03-08 00:00:00','2001-03-08 00:00:00','z','z'),(75,0,0,'2001-09-06','2001-09-06','23:43:38','23:43:38','1900-01-01 00:00:00','1900-01-01 00:00:00','b','b'),(76,6,6,'2006-02-23','2006-02-23',NULL,NULL,'2006-07-21 19:26:57','2006-07-21 19:26:57','f','f'),(77,1,2,'2004-06-21','2004-06-21','12:35:01','12:35:01',NULL,NULL,'s','s'),(78,9,NULL,'2006-12-02','2006-12-02','00:18:04','00:18:04',NULL,NULL,'d','d'),(79,4,8,'2005-05-17','2005-05-17','11:53:29','11:53:29','2002-10-24 03:10:15','2002-10-24 03:10:15',NULL,NULL),(80,9,NULL,'2001-12-27','2001-12-27','13:53:19','13:53:19','2002-08-16 00:00:00','2002-08-16 00:00:00','d','d'),(81,4,NULL,'2008-04-11','2008-04-11',NULL,NULL,NULL,NULL,'n','n'),(82,1,NULL,'2000-09-24','2000-09-24','08:53:20','08:53:20','2005-09-09 12:56:20','2005-09-09 12:56:20','i','i'),(83,2,3,'2004-04-20','2004-04-20','10:38:45','10:38:45','2002-06-09 00:00:00','2002-06-09 00:00:00',NULL,NULL),(84,8,7,'2008-07-10','2008-07-10','22:27:36','22:27:36','1900-01-01 00:00:00','1900-01-01 00:00:00','h','h'),(85,1,3,'2007-08-14','2007-08-14','18:06:56','18:06:56','2009-01-21 23:43:07','2009-01-21 23:43:07','d','d'),(86,0,5,'2008-06-08','2008-06-08','03:44:30','03:44:30',NULL,NULL,'c','c'),(87,2,5,'2001-07-26','2001-07-26','11:49:25','11:49:25','2007-09-06 20:49:33','2007-09-06 20:49:33','i','i'),(88,0,1,'2008-09-17','2008-09-17','22:16:07','22:16:07','2005-09-20 10:11:02','2005-09-20 10:11:02','t','t'),(89,1,2,NULL,NULL,'17:14:13','17:14:13','2007-10-11 00:00:00','2007-10-11 00:00:00','g','g'),(90,2,1,'2004-02-25','2004-02-25','14:49:31','14:49:31','2005-06-13 00:26:39','2005-06-13 00:26:39','q','q'),(91,NULL,7,'2009-11-02','2009-11-02','00:27:36','00:27:36','2001-06-18 08:40:49','2001-06-18 08:40:49','l','l'),(92,3,1,'2001-12-14','2001-12-14','13:59:24','13:59:24','2006-06-07 00:00:00','2006-06-07 00:00:00','n','n'),(93,8,9,NULL,NULL,'01:48:46','01:48:46','2004-03-18 22:35:04','2004-03-18 22:35:04','z','z'),(94,4,9,'1900-01-01','1900-01-01',NULL,NULL,NULL,NULL,'n','n'),(95,4,8,'2002-04-12','2002-04-12','11:07:46','11:07:46','2001-07-15 12:46:49','2001-07-15 12:46:49','r','r'),(96,NULL,3,'2000-02-09','2000-02-09',NULL,NULL,'2001-03-27 06:53:22','2001-03-27 06:53:22','p','p'),(97,9,7,'2005-07-23','2005-07-23','15:01:53','15:01:53','2004-05-13 01:01:39','2004-05-13 01:01:39','t','t'),(98,2,4,'2009-11-01','2009-11-01','15:44:41','15:44:41','2003-12-24 07:39:29','2003-12-24 07:39:29','h','h'),(99,6,9,NULL,NULL,NULL,NULL,'2008-07-03 05:32:22','2008-07-03 05:32:22','l','l'),(100,6,0,'2007-07-16','2007-07-16','13:32:31','13:32:31','2008-08-28 18:46:11','2008-08-28 18:46:11','j','j'); CREATE TABLE t3 ( pk int(11) NOT NULL AUTO_INCREMENT, col_int_nokey int(11) DEFAULT NULL, col_int_key int(11) DEFAULT NULL, col_date_key date DEFAULT NULL, col_date_nokey date DEFAULT NULL, col_time_key time DEFAULT NULL, col_time_nokey time DEFAULT NULL, col_datetime_key datetime DEFAULT NULL, col_datetime_nokey datetime DEFAULT NULL, col_varchar_key varchar(1) DEFAULT NULL, col_varchar_nokey varchar(1) DEFAULT NULL, PRIMARY KEY (pk), KEY col_int_key (col_int_key), KEY col_date_key (col_date_key), KEY col_time_key (col_time_key), KEY col_datetime_key (col_datetime_key), KEY col_varchar_key (col_varchar_key,col_int_key)) ENGINE=Aria AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1; INSERT INTO t3 VALUES (1,150,62,'2005-03-27','2005-03-27','14:26:02','14:26:02','2008-01-03 10:33:32','2008-01-03 10:33:32','v','v'),(2,NULL,7,'2004-04-09','2004-04-09','14:03:03','14:03:03','2001-11-28 00:50:27','2001-11-28 00:50:27','c','c'),(3,2,1,'2006-05-13','2006-05-13','01:46:09','01:46:09','2007-10-09 19:53:04','2007-10-09 19:53:04',NULL,NULL); CREATE TABLE t4 ( pk int(11) NOT NULL AUTO_INCREMENT, col_int_nokey int(11) DEFAULT NULL, col_int_key int(11) DEFAULT NULL, col_date_key date DEFAULT NULL, col_date_nokey date DEFAULT NULL, col_time_key time DEFAULT NULL, col_time_nokey time DEFAULT NULL, col_datetime_key datetime DEFAULT NULL, col_datetime_nokey datetime DEFAULT NULL, col_varchar_key varchar(1) DEFAULT NULL, col_varchar_nokey varchar(1) DEFAULT NULL, PRIMARY KEY (pk), KEY col_int_key (col_int_key), KEY col_date_key (col_date_key), KEY col_time_key (col_time_key), KEY col_datetime_key (col_datetime_key), KEY col_varchar_key (col_varchar_key,col_int_key)) ENGINE=Aria AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1; INSERT INTO t4 VALUES (1,150,62,'2005-03-27','2005-03-27','14:26:02','14:26:02','2008-01-03 10:33:32','2008-01-03 10:33:32','v','v'),(2,NULL,7,'2004-04-09','2004-04-09','14:03:03','14:03:03','2001-11-28 00:50:27','2001-11-28 00:50:27','c','c'),(3,2,1,'2006-05-13','2006-05-13','01:46:09','01:46:09','2007-10-09 19:53:04','2007-10-09 19:53:04',NULL,NULL); SELECT * FROM t1 JOIN t2 ON ( t2.col_int_key = t1.pk ) WHERE t1.col_varchar_key IN ( SELECT t3.col_varchar_key FROM t3, t4 );

Hide

Permalink

Sergei Petrunia added a comment - 2011-11-25 20:58

Re: Second assertion `keypart_map' failed in maria_rkey with semijoin
CREATE TABLE t1 (
pk int(11) NOT NULL AUTO_INCREMENT,
col_int_key int(11) DEFAULT NULL,
col_varchar_key varchar(1) DEFAULT NULL,
dummy char(30),
PRIMARY KEY (pk),
KEY col_varchar_key (col_varchar_key,col_int_key)
) ENGINE=Aria AUTO_INCREMENT=30 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1;

INSERT INTO t1
(
pk,
col_varchar_key,
col_int_key
)

VALUES
(10,NULL,0),
(11,'d',4),
(12,'g',8),
(13,'x',NULL),
(14,'f',NULL),
(15,'p',0),
(16,'j',NULL),
(17,'c',8),
(18,'z',8),
(19,'j',6),
(20,NULL,2),
(21,'p',3),
(22,'w',1),
(23,'c',NULL),
(24,'j',1),
(25,'f',10),
(26,'v',2),
(27,'f',103),
(28,'q',3),
(29,'y',6);

CREATE TABLE t2 (
pk int(11) NOT NULL AUTO_INCREMENT,
col_int_key int(11) DEFAULT NULL,
dummy char(36),
PRIMARY KEY (pk),
KEY col_int_key (col_int_key)
) ENGINE=Aria AUTO_INCREMENT=101 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1;

INSERT INTO t2 ( pk, col_int_key) VALUES
(1,8),
(2,2),
(3,9),
(4,6),
(5,NULL),
(6,NULL),
(7,48),
(8,228),
(9,3),
(10,5),
(11,39),
(12,6),
(13,8),
(14,3),
(15,NULL),
(16,2),
(17,6),
(18,3),
(19,1),
(20,4),
(21,3),
(22,1),
(23,NULL),
(24,97),
(25,0),
(26,0),
(27,9),
(28,5),
(29,9),
(30,0),
(31,2),
(32,172),
(33,NULL),
(34,5),
(35,119),
(36,1),
(37,4),
(38,8),
(39,NULL),
(40,6),
(41,5),
(42,5),
(43,1),
(44,7),
(45,2),
(46,8),
(47,9),
(48,NULL),
(49,NULL),
(50,3),
(51,172),
(52,NULL),
(53,6),
(54,6),
(55,5),
(56,4),
(57,3),
(58,2),
(59,7),
(60,4),
(61,6),
(62,0),
(63,8),
(64,5),
(65,8),
(66,2),
(67,9),
(68,7),
(69,5),
(70,7),
(71,0),
(72,4),
(73,3),
(74,1),
(75,0),
(76,6),
(77,2),
(78,NULL),
(79,8),
(80,NULL),
(81,NULL),
(82,NULL),
(83,3),
(84,7),
(85,3),
(86,5),
(87,5),
(88,1),
(89,2),
(90,1),
(91,7),
(92,1),
(93,9),
(94,9),
(95,8),
(96,3),
(97,7),
(98,4),
(99,9),
(100,0);

CREATE TABLE t3 (
pk int(11) NOT NULL AUTO_INCREMENT,
dummy char(34),
col_varchar_key varchar(1) DEFAULT NULL,
col_int_key int(11) DEFAULT NULL,
PRIMARY KEY (pk),
KEY col_varchar_key (col_varchar_key,col_int_key)
) ENGINE=Aria AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1;

INSERT INTO t3 (pk, col_varchar_key) VALUES
(1,'v'),
(2,'c'),
(3,NULL);

CREATE TABLE t4 (
pk int(11) NOT NULL AUTO_INCREMENT,
dummy char (38),
PRIMARY KEY (pk)
) ENGINE=Aria AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1;

INSERT INTO t4 (pk) VALUES
(1),
(2),
(3);

SELECT *
FROM t1
JOIN t2
ON ( t2.col_int_key = t1.pk )
WHERE t1.col_varchar_key IN (
SELECT t3.col_varchar_key FROM t3, t4
);

Show

Sergei Petrunia added a comment - 2011-11-25 20:58 Re: Second assertion `keypart_map' failed in maria_rkey with semijoin CREATE TABLE t1 ( pk int(11) NOT NULL AUTO_INCREMENT, col_int_key int(11) DEFAULT NULL, col_varchar_key varchar(1) DEFAULT NULL, dummy char(30), PRIMARY KEY (pk), KEY col_varchar_key (col_varchar_key,col_int_key) ) ENGINE=Aria AUTO_INCREMENT=30 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1; INSERT INTO t1 ( pk, col_varchar_key, col_int_key ) VALUES (10,NULL,0), (11,'d',4), (12,'g',8), (13,'x',NULL), (14,'f',NULL), (15,'p',0), (16,'j',NULL), (17,'c',8), (18,'z',8), (19,'j',6), (20,NULL,2), (21,'p',3), (22,'w',1), (23,'c',NULL), (24,'j',1), (25,'f',10), (26,'v',2), (27,'f',103), (28,'q',3), (29,'y',6); CREATE TABLE t2 ( pk int(11) NOT NULL AUTO_INCREMENT, col_int_key int(11) DEFAULT NULL, dummy char(36), PRIMARY KEY (pk), KEY col_int_key (col_int_key) ) ENGINE=Aria AUTO_INCREMENT=101 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1; INSERT INTO t2 ( pk, col_int_key) VALUES (1,8), (2,2), (3,9), (4,6), (5,NULL), (6,NULL), (7,48), (8,228), (9,3), (10,5), (11,39), (12,6), (13,8), (14,3), (15,NULL), (16,2), (17,6), (18,3), (19,1), (20,4), (21,3), (22,1), (23,NULL), (24,97), (25,0), (26,0), (27,9), (28,5), (29,9), (30,0), (31,2), (32,172), (33,NULL), (34,5), (35,119), (36,1), (37,4), (38,8), (39,NULL), (40,6), (41,5), (42,5), (43,1), (44,7), (45,2), (46,8), (47,9), (48,NULL), (49,NULL), (50,3), (51,172), (52,NULL), (53,6), (54,6), (55,5), (56,4), (57,3), (58,2), (59,7), (60,4), (61,6), (62,0), (63,8), (64,5), (65,8), (66,2), (67,9), (68,7), (69,5), (70,7), (71,0), (72,4), (73,3), (74,1), (75,0), (76,6), (77,2), (78,NULL), (79,8), (80,NULL), (81,NULL), (82,NULL), (83,3), (84,7), (85,3), (86,5), (87,5), (88,1), (89,2), (90,1), (91,7), (92,1), (93,9), (94,9), (95,8), (96,3), (97,7), (98,4), (99,9), (100,0); CREATE TABLE t3 ( pk int(11) NOT NULL AUTO_INCREMENT, dummy char(34), col_varchar_key varchar(1) DEFAULT NULL, col_int_key int(11) DEFAULT NULL, PRIMARY KEY (pk), KEY col_varchar_key (col_varchar_key,col_int_key) ) ENGINE=Aria AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1; INSERT INTO t3 (pk, col_varchar_key) VALUES (1,'v'), (2,'c'), (3,NULL); CREATE TABLE t4 ( pk int(11) NOT NULL AUTO_INCREMENT, dummy char (38), PRIMARY KEY (pk) ) ENGINE=Aria AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1; INSERT INTO t4 (pk) VALUES (1), (2), (3); SELECT * FROM t1 JOIN t2 ON ( t2.col_int_key = t1.pk ) WHERE t1.col_varchar_key IN ( SELECT t3.col_varchar_key FROM t3, t4 );

Hide

Permalink

Rasmus Johansson added a comment - 2011-12-13 16:24

Launchpad bug id: 887468

Show

Rasmus Johansson added a comment - 2011-12-13 16:24 Launchpad bug id: 887468

People

Assignee:

Sergei Petrunia

Reporter:

Philip Stoev

Votes:

0 Vote for this issue

Watchers:

0 Start watching this issue

Dates

Created:

2011-11-08 09:04

Updated:

2015-02-02 21:55

Resolved:

2012-10-04 12:52

Agile

View on Board