MariaDB

Crash in decimal2double with subquery cache

Bug #615378 reported by Philip Stoev on 2010-08-09

This bug affects 1 person

Affects		Status	Importance	Assigned to	Milestone
	MariaDB	Fix Released	High	Oleksandr "Sanja" Byelkin	MariaDB 5.3

Bug Description

Subuery cache crashed with the following backtrace:

# 2010-08-09T16:05:07 #3 0x0827e94a in handle_segfault (sig=11) at mysqld.cc:2703
# 2010-08-09T16:05:07 #4 <signal handler called>
# 2010-08-09T16:05:07 #5 0x0877a140 in decimal2double (from=0xaed867b0, to=0x9a1dc4d8) at decimal.c:961
# 2010-08-09T16:05:07 #6 0x081cdeac in my_decimal2double (mask=30, d=0xaed867b0, result=0x9a1dc4d8) at my_decimal.h:314
# 2010-08-09T16:05:07 #7 0x081cb865 in Item_cache_decimal::val_real (this=0xaed86740) at item.cc:7837
# 2010-08-09T16:05:07 #8 0x081c8942 in Item_cache_wrapper::val_real (this=0xaec17510) at item.cc:6808
# 2010-08-09T16:05:07 #9 0x081cf2de in Item::val_result (this=0xaec17510) at item.h:778
# 2010-08-09T16:05:07 #10 0x081c7a15 in Item_ref::val_real (this=0xaecb9778) at item.cc:6438
# 2010-08-09T16:05:07 #11 0x081f94f0 in Arg_comparator::compare_real (this=0xaecb9904) at item_cmpfunc.cc:1359
# 2010-08-09T16:05:07 #12 0x081dedc2 in Arg_comparator::compare (this=0xaecb9904) at item_cmpfunc.h:81
# 2010-08-09T16:05:07 #13 0x081fb16d in Item_func_le::val_int (this=0xaecb9888) at item_cmpfunc.cc:2015
# 2010-08-09T16:05:07 #14 0x081c4e7f in Item::save_in_field (this=0xaecb9888, field=0x9a051df8, no_conversions=true) at item.cc:5328
# 2010-08-09T16:05:07 #15 0x081d0c55 in Item_result_field::save_in_result_field (this=0xaecb9888, no_conversions=true) at item.h:2354
# 2010-08-09T16:05:07 #16 0x08328064 in copy_funcs (func_ptr=0xaecf5898) at sql_select.cc:17575
# 2010-08-09T16:05:07 #17 0x0831ff21 in end_write (join=0x9a06f840, join_tab=0xaece7548, end_of_records=false) at sql_select.cc:14252
# 2010-08-09T16:05:07 #18 0x0831d962 in evaluate_join_record (join=0x9a06f840, join_tab=0xaece7388, error=0) at sql_select.cc:13278
# 2010-08-09T16:05:07 #19 0x0831d309 in sub_select (join=0x9a06f840, join_tab=0xaece7388, end_of_records=false) at sql_select.cc:13087
# 2010-08-09T16:05:07 #20 0x0831d962 in evaluate_join_record (join=0x9a06f840, join_tab=0xaece71c8, error=0) at sql_select.cc:13278
# 2010-08-09T16:05:07 #21 0x0831d457 in sub_select (join=0x9a06f840, join_tab=0xaece71c8, end_of_records=false) at sql_select.cc:13126
# 2010-08-09T16:05:07 #22 0x0831d962 in evaluate_join_record (join=0x9a06f840, join_tab=0xaece7008, error=0) at sql_select.cc:13278
# 2010-08-09T16:05:07 #23 0x0831d309 in sub_select (join=0x9a06f840, join_tab=0xaece7008, end_of_records=false) at sql_select.cc:13087
# 2010-08-09T16:05:07 #24 0x0831c70f in do_select (join=0x9a06f840, fields=0x0, table=0xaecf4e00, procedure=0x0) at sql_select.cc:12633
# 2010-08-09T16:05:07 #25 0x08300e09 in JOIN::exec (this=0x9a06f840) at sql_select.cc:1933
# 2010-08-09T16:05:07 #26 0x08303209 in mysql_select (thd=0x9f715f0, rref_pointer_array=0xaec133ac, tables=0xaec8ebc0, wild_num=0, fields=..., conds=0xaedf7d98, og_num=12,
# 2010-08-09T16:05:07 order=0xaedd0f50, group=0xaedd98b8, having=0xaedd0d50, proc_param=0x0, select_options=2416200192, result=0xaec51228, unit=0x9a079630,
# 2010-08-09T16:05:07 select_lex=0xaec13290) at sql_select.cc:2556
# 2010-08-09T16:05:07 #27 0x084462d5 in mysql_derived_filling (thd=0x9f715f0, lex=0x9f72c70, orig_table_list=0xaec19380) at sql_derived.cc:295
# 2010-08-09T16:05:07 #28 0x08445bc7 in mysql_handle_derived (lex=0x9f72c70, processor=0x84460f0 <mysql_derived_filling(THD*, LEX*, TABLE_LIST*)>) at sql_derived.cc:56
# 2010-08-09T16:05:07 #29 0x082e4201 in open_and_lock_tables_derived (thd=0x9f715f0, tables=0xaec19380, derived=true) at sql_base.cc:5067
# 2010-08-09T16:05:07 #30 0x082a0865 in open_and_lock_tables (thd=0x9f715f0, tables=0xaec19380) at mysql_priv.h:1648
# 2010-08-09T16:05:07 #31 0x0829a01d in execute_sqlcom_select (thd=0x9f715f0, all_tables=0xaec19380) at sql_parse.cc:5046
# 2010-08-09T16:05:07 #32 0x08290c1c in mysql_execute_command (thd=0x9f715f0) at sql_parse.cc:2265
# 2010-08-09T16:05:07 #33 0x0829c405 in mysql_parse (thd=0x9f715f0,
# 2010-08-09T16:05:07 inBuf=0xaedad570 "SELECT * FROM ( SELECT table1 . `col_varchar_key` AS field1 , SUM( DISTINCT table2 . `pk` ) AS field2 , ( SELECT SUM( SUBQUERY1_t1 . `pk` ) AS SUBQUERY1_field1 FROM ( C AS SUBQUERY1_t1 STRAIGHT_JOIN ( D AS SUBQUERY1_t2 STRAIGHT_JOIN view_B AS SUBQUERY1_t3 ON (SUBQUERY1_t3 . `col_varchar_key` = SUBQUERY1_t2 . `col_varchar_key` ) ) ON (SUBQUERY1_t3 . `col_int_nokey` = SUBQUERY1_t2 . `col_int_key` ) ) WHERE SUBQUERY1_t2 . `col_varchar_key` <= table1 . `col_varchar_key` ) AS field3 , SUM( table1 . `col_varchar_key` ) AS field4 , table2 . `col_time_key` AS field5 , SUM( table1 . `col_varchar_key` ) AS field6 , ( SELECT COUNT( SUBQUERY2_t1 . `pk` ) AS SUBQUERY2_field1 FROM ( C AS SUBQUERY2_t1 STRAIGHT_JOIN CC AS SUBQUERY2_t2 ON (SUBQUERY2_t2 . `col_varchar_nokey` = SUBQUERY2_t1 . `col_varchar_key` ) ) WHERE SUBQUERY2_t2 . `col_int_key` = table1 . `col_int_nokey` AND SUBQUERY2_t1 . `pk` > table1 . `col_int_key` ) AS field7 FROM ( CC AS table1 LEFT JOIN ( ( D AS table2 STRAIGHT_JOIN C AS table3 ON (table3 . `pk` = table2 . `col_int_key` ) ) ) ON (table3 . `col_varchar_key` = table2 . `col_varchar_key` ) ) WHERE ( table3 . `pk` <= ALL ( SELECT 5 FROM DUAL ) ) OR ( table1 . `col_int_key` = 7 AND table1 . `pk` = 58 ) OR ( table1 . `col_varchar_nokey` IS NOT NULL OR table3 . `col_varchar_key` != table3 . `col_varchar_nokey` ) GROUP BY field1, field3, field5, field7 HAVING (field3 <= 'h' AND field2 != 4) ORDER BY table1 . `col_date_key` ASC , field1, field2, field3, field4, field5, field6, field7 ) AS FROM_SUBQUERY /* TRANSFORM_OUTCOME_UNORDERED_MATCH */", length=1589, found_semicolon=0x9a1de230) at sql_parse.cc:6027
# 2010-08-09T16:05:07 #34 0x0828e6d2 in dispatch_command (command=COM_QUERY, thd=0x9f715f0,
# 2010-08-09T16:05:07 packet=0x9fa7d61 " SELECT * FROM ( SELECT table1 . `col_varchar_key` AS field1 , SUM( DISTINCT table2 . `pk` ) AS field2 , ( SELECT SUM( SUBQUERY1_t1 . `pk` ) AS SUBQUERY1_field1 FROM ( C AS SUBQUERY1_t1 STRAIGHT_JOIN ( D AS SUBQUERY1_t2 STRAIGHT_JOIN view_B AS SUBQUERY1_t3 ON (SUBQUERY1_t3 . `col_varchar_key` = SUBQUERY1_t2 . `col_varchar_key` ) ) ON (SUBQUERY1_t3 . `col_int_nokey` = SUBQUERY1_t2 . `col_int_key` ) ) WHERE SUBQUERY1_t2 . `col_varchar_key` <= table1 . `col_varchar_key` ) AS field3 , SUM( table1 . `col_varchar_key` ) AS field4 , table2 . `col_time_key` AS field5 , SUM( table1 . `col_varchar_key` ) AS field6 , ( SELECT COUNT( SUBQUERY2_t1 . `pk` ) AS SUBQUERY2_field1 FROM ( C AS SUBQUERY2_t1 STRAIGHT_JOIN CC AS SUBQUERY2_t2 ON (SUBQUERY2_t2 . `col_varchar_nokey` = SUBQUERY2_t1 . `col_varchar_key` ) ) WHERE SUBQUERY2_t2 . `col_int_key` = table1 . `col_int_nokey` AND SUBQUERY2_t1 . `pk` > table1 . `col_int_key` ) AS field7 FROM ( CC AS table1 LEFT JOIN ( ( D AS table2 STRAIGHT_JOIN C AS table3 ON (table3 . `pk` = table2 . `col_int_key` ) ) ) ON (table3 . `col_varchar_key` = table2 . `col_varchar_key` ) ) WHERE ( table3 . `pk` <= ALL ( SELECT 5 FROM DUAL ) ) OR ( table1 . `col_int_key` = 7 AND table1 . `pk` = 58 ) OR ( table1 . `col_varchar_nokey` IS NOT NULL OR table3 . `col_varchar_key` != table3 . `col_varchar_nokey` ) GROUP BY field1, field3, field5, field7 HAVING (field3 <= 'h' AND field2 != 4) ORDER BY table1 . `col_date_key` ASC , field1, field2, field3, field4, field5, field6, field7 ) AS FROM_SUBQUERY /* TRANSFORM_OUTCOME_UNORDERED_MATCH */", packet_length=1591) at sql_parse.cc:1184
# 2010-08-09T16:05:07 #35 0x0828dbc0 in do_command (thd=0x9f715f0) at sql_parse.cc:890
# 2010-08-09T16:05:07 #36 0x0828ad58 in handle_one_connection (arg=0x9f715f0) at sql_connect.cc:1153
# 2010-08-09T16:05:07 #37 0x00a08919 in start_thread () from /lib/libpthread.so.0
# 2010-08-09T16:05:07 #38 0x00951e5e in clone () from /lib/libc.so.6

bzr version-info:

revision-id: <email address hidden>
date: 2010-08-09 13:00:58 +0300
build-date: 2010-08-09 16:55:50 +0300
revno: 2810
branch-nick: maria-5.3

a test case will be available shortly

Tags:

Philip Stoev (pstoev-askmonty) on 2010-08-09

Changed in maria:
milestone:	none → 5.3
importance:	Undecided → High

Revision history for this message

Philip Stoev (pstoev-askmonty) wrote on 2010-08-09:

core, binary, datadir, etc. Edit (54.3 MiB, application/zip)

This bug is sporadic , but does happen after a hundred thousand queries have been run.

Changed in maria:
assignee:	nobody → Oleksandr "Sanja" Byelkin (sanja-byelkin)

Revision history for this message

Philip Stoev (pstoev-askmonty) wrote on 2010-09-07:

Download full text (14.9 KiB)

This is expressed more easily as a valgrind warning, and not a crash:

==2571== Conditional jump or move depends on uninitialised value(s)
==2571== at 0x8DA8DCD: decimal2double (decimal.c:963)
==2571== by 0x8202DB9: my_decimal2double(unsigned int, my_decimal const*, double*) (my_decimal.h:314)
==2571== by 0x81FD816: Item_cache_decimal::val_real() (item.cc:7837)
==2571== by 0x81F6F4E: Item_cache_wrapper::val_real() (item.cc:6808)
==2571== by 0x8205843: Item::val_result() (item.h:778)
==2571== by 0x81F4F78: Item_ref::val_real() (item.cc:6438)
==2571== by 0x8263EF3: Arg_comparator::compare_real() (item_cmpfunc.cc:1359)
==2571== by 0x822944F: Arg_comparator::compare() (item_cmpfunc.h:81)
==2571== by 0x8267CFB: Item_func_le::val_int() (item_cmpfunc.cc:2015)
==2571== by 0x81EF200: Item::save_in_field(Field*, bool) (item.cc:5328)
==2571== by 0x8209CBA: Item_result_field::save_in_result_field(bool) (item.h:2354)
==2571== by 0x84F9C37: copy_funcs(Item**) (sql_select.cc:17575)
==2571== by 0x84E8237: end_write(JOIN*, st_join_table*, bool) (sql_select.cc:14252)
==2571== by 0x84E2D6D: evaluate_join_record(JOIN*, st_join_table*, int) (sql_select.cc:13278)
==2571== by 0x84E1E64: sub_select(JOIN*, st_join_table*, bool) (sql_select.cc:13087)
==2571== by 0x84E2D6D: evaluate_join_record(JOIN*, st_join_table*, int) (sql_select.cc:13278)

Test case:

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

This is expressed more easily as a valgrind warning, and not a crash:

==2571== Conditional jump or move depends on uninitialised value(s)
==2571==    at 0x8DA8DCD: decimal2double (decimal.c:963)
==2571==    by 0x8202DB9: my_decimal2double(unsigned int, my_decimal const*, double*) (my_decimal.h:314)
==2571==    by 0x81FD816: Item_cache_decimal::val_real() (item.cc:7837)
==2571==    by 0x81F6F4E: Item_cache_wrapper::val_real() (item.cc:6808)
==2571==    by 0x8205843: Item::val_result() (item.h:778)
==2571==    by 0x81F4F78: Item_ref::val_real() (item.cc:6438)
==2571==    by 0x8263EF3: Arg_comparator::compare_real() (item_cmpfunc.cc:1359)
==2571==    by 0x822944F: Arg_comparator::compare() (item_cmpfunc.h:81)
==2571==    by 0x8267CFB: Item_func_le::val_int() (item_cmpfunc.cc:2015)
==2571==    by 0x81EF200: Item::save_in_field(Field*, bool) (item.cc:5328)
==2571==    by 0x8209CBA: Item_result_field::save_in_result_field(bool) (item.h:2354)
==2571==    by 0x84F9C37: copy_funcs(Item**) (sql_select.cc:17575)
==2571==    by 0x84E8237: end_write(JOIN*, st_join_table*, bool) (sql_select.cc:14252)
==2571==    by 0x84E2D6D: evaluate_join_record(JOIN*, st_join_table*, int) (sql_select.cc:13278)
==2571==    by 0x84E1E64: sub_select(JOIN*, st_join_table*, bool) (sql_select.cc:13087)
==2571==    by 0x84E2D6D: evaluate_join_record(JOIN*, st_join_table*, int) (sql_select.cc:13278)

Test case:

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

CREATE TABLE `CC` (
  `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_time_key` time 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_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
INSERT INTO `CC` VALUES (10,7,8,NULL,'01:27:35','v','v');
INSERT INTO `CC` VALUES (11,1,9,'2006-06-14','19:48:31','r','r');
INSERT INTO `CC` VALUES (12,5,9,'2002-09-12','00:00:00','a','a');
INSERT INTO `CC` VALUES (13,3,186,'2005-02-15','19:53:05','m','m');
INSERT INTO `CC` VALUES (14,6,NULL,NULL,'19:18:56','y','y');
INSERT INTO `CC` VALUES (15,92,2,'2008-11-04','10:55:12','j','j');
INSERT INTO `CC` VALUES (16,7,3,'2004-09-04','00:25:00','d','d');
INSERT INTO `CC` VALUES (17,NULL,0,'2006-06-05','12:35:47','z','z');
INSERT INTO `CC` VALUES (18,3,133,'1900-01-01','19:53:03','e','e');
INSERT INTO `CC` VALUES (19,5,1,'1900-01-01','17:53:30','h','h');
INSERT INTO `CC` VALUES (20,1,8,'1900-01-01','11:35:49','b','b');
INSERT INTO `CC` VALUES (21,2,5,'2005-01-13',NULL,'s','s');
INSERT INTO `CC` VALUES (22,NULL,5,'2006-05-21','06:01:40','e','e');
INSERT INTO `CC` VALUES (23,1,8,'2003-09-08','05:45:11','j','j');
INSERT INTO `CC` VALUES (24,0,6,'2006-12-23','00:00:00','e','e');
INSERT INTO `CC` VALUES (25,210,51,'2006-10-15','00:00:00','f','f');
INSERT INTO `CC` VALUES (26,8,4,'2005-04-06','06:11:01','v','v');
INSERT INTO `CC` VALUES (27,7,7,'2008-04-07','13:02:46','x','x');
INSERT INTO `CC` VALUES (28,5,6,'2006-10-10','21:44:25','m','m');
INSERT INTO `CC` VALUES (29,NULL,4,'1900-01-01','22:43:58','c','c');
CREATE TABLE `D` (
  `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_time_key` time 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_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=latin1;
INSERT INTO `D` VALUES (1,6,NULL,'2003-05-12',NULL,'r','r');
INSERT INTO `D` VALUES (2,8,0,'2003-01-07','14:34:45','c','c');
INSERT INTO `D` VALUES (3,6,0,NULL,'11:49:48','o','o');
INSERT INTO `D` VALUES (4,6,7,'2005-03-12','18:12:55','c','c');
INSERT INTO `D` VALUES (5,3,8,'2000-08-02','18:30:05','d','d');
INSERT INTO `D` VALUES (6,9,4,'1900-01-01','14:19:30','v','v');
INSERT INTO `D` VALUES (7,2,6,'2006-07-06','05:20:04','m','m');
INSERT INTO `D` VALUES (8,1,5,'2006-12-24','20:29:31','j','j');
INSERT INTO `D` VALUES (9,8,NULL,'2004-11-16','07:08:09','f','f');
INSERT INTO `D` VALUES (10,0,NULL,'2002-09-09','14:49:14','n','n');
INSERT INTO `D` VALUES (11,9,8,NULL,'00:00:00','z','z');
INSERT INTO `D` VALUES (12,8,8,'2008-06-24','09:58:06','h','h');
INSERT INTO `D` VALUES (13,NULL,8,'2001-04-21',NULL,'q','q');
INSERT INTO `D` VALUES (14,0,1,'2003-11-22','18:24:16','w','w');
INSERT INTO `D` VALUES (15,5,1,'2004-09-12','17:39:57','z','z');
INSERT INTO `D` VALUES (16,1,5,'2006-06-20','08:23:21','j','j');
INSERT INTO `D` VALUES (17,1,2,NULL,NULL,'a','a');
INSERT INTO `D` VALUES (18,6,7,'2001-11-25','21:50:46','m','m');
INSERT INTO `D` VALUES (19,6,6,'2004-10-26','12:33:17','n','n');
INSERT INTO `D` VALUES (20,1,4,'2005-01-19','03:06:43','e','e');
INSERT INTO `D` VALUES (21,8,7,'2008-07-06','03:46:14','u','u');
INSERT INTO `D` VALUES (22,1,0,'1900-01-01','20:34:52','s','s');
INSERT INTO `D` VALUES (23,0,9,'1900-01-01',NULL,'u','u');
INSERT INTO `D` VALUES (24,4,3,'2004-06-08','10:41:20','r','r');
INSERT INTO `D` VALUES (25,9,5,'2007-02-20','08:43:11','g','g');
INSERT INTO `D` VALUES (26,8,1,'2008-06-18',NULL,'o','o');
INSERT INTO `D` VALUES (27,5,1,'2008-05-15','10:17:51','w','w');
INSERT INTO `D` VALUES (28,9,5,'2005-10-06','06:34:09','b','b');
INSERT INTO `D` VALUES (29,5,9,NULL,'21:22:47',NULL,NULL);
INSERT INTO `D` VALUES (30,NULL,2,'2006-10-12','04:02:32','y','y');
INSERT INTO `D` VALUES (31,NULL,5,'2005-01-24','02:33:14','y','y');
INSERT INTO `D` VALUES (32,105,248,'2009-06-27','16:32:56','u','u');
INSERT INTO `D` VALUES (33,0,0,NULL,'21:32:42','p','p');
INSERT INTO `D` VALUES (34,3,8,NULL,'23:04:47','s','s');
INSERT INTO `D` VALUES (35,1,1,'1900-01-01','22:05:43','e','e');
INSERT INTO `D` VALUES (36,75,255,'2005-12-22','02:05:45','d','d');
INSERT INTO `D` VALUES (37,9,9,'2005-05-03','00:00:00','d','d');
INSERT INTO `D` VALUES (38,7,9,'2003-05-27','18:09:07','c','c');
INSERT INTO `D` VALUES (39,NULL,3,'2006-05-25','10:54:06','b','b');
INSERT INTO `D` VALUES (40,NULL,9,NULL,'23:15:50','t','t');
INSERT INTO `D` VALUES (41,4,6,'2009-01-04','10:17:40',NULL,NULL);
INSERT INTO `D` VALUES (42,0,4,'2009-02-14','03:37:09','y','y');
INSERT INTO `D` VALUES (43,204,60,'2003-01-16','22:26:06','c','c');
INSERT INTO `D` VALUES (44,0,7,'1900-01-01','17:10:38','d','d');
INSERT INTO `D` VALUES (45,9,1,'2007-06-26','00:00:00','x','x');
INSERT INTO `D` VALUES (46,8,6,'2004-03-27','17:08:49','p','p');
INSERT INTO `D` VALUES (47,7,4,NULL,'19:04:40','e','e');
INSERT INTO `D` VALUES (48,8,NULL,'2005-06-06','20:53:28','g','g');
INSERT INTO `D` VALUES (49,NULL,8,'2003-03-02','11:46:03','x','x');
INSERT INTO `D` VALUES (50,6,0,'2004-05-13',NULL,'s','s');
INSERT INTO `D` VALUES (51,5,8,'2005-09-13','10:58:07','e','e');
INSERT INTO `D` VALUES (52,2,151,'2005-10-03','00:00:00','l','l');
INSERT INTO `D` VALUES (53,3,7,'2005-10-14','09:43:15','p','p');
INSERT INTO `D` VALUES (54,7,6,NULL,'21:40:32','h','h');
INSERT INTO `D` VALUES (55,NULL,NULL,'2005-09-16','00:17:44','m','m');
INSERT INTO `D` VALUES (56,145,23,'2005-03-10','16:47:26','n','n');
INSERT INTO `D` VALUES (57,0,2,'2000-06-19','00:00:00','v','v');
INSERT INTO `D` VALUES (58,1,4,'2002-11-03','05:25:59','b','b');
INSERT INTO `D` VALUES (59,7,NULL,'2009-01-05','00:00:00','x','x');
INSERT INTO `D` VALUES (60,3,NULL,'2003-05-22','20:33:04','r','r');
INSERT INTO `D` VALUES (61,NULL,77,'2005-07-02','00:46:12','t','t');
INSERT INTO `D` VALUES (62,2,NULL,'1900-01-01','00:00:00','w','w');
INSERT INTO `D` VALUES (63,2,NULL,'2006-06-21','02:13:59','w','w');
INSERT INTO `D` VALUES (64,2,7,NULL,'02:54:47','k','k');
INSERT INTO `D` VALUES (65,8,1,'2005-12-16','18:13:59','a','a');
INSERT INTO `D` VALUES (66,6,9,'2004-11-05','13:53:08','t','t');
INSERT INTO `D` VALUES (67,1,6,NULL,'22:21:30','z','z');
INSERT INTO `D` VALUES (68,NULL,2,'2004-09-14','11:41:50','e','e');
INSERT INTO `D` VALUES (69,1,3,'2002-04-06','15:20:02','q','q');
INSERT INTO `D` VALUES (70,0,0,NULL,NULL,'e','e');
INSERT INTO `D` VALUES (71,4,NULL,'2002-11-13',NULL,'v','v');
INSERT INTO `D` VALUES (72,1,6,'2006-05-27','07:51:52','d','d');
INSERT INTO `D` VALUES (73,1,3,'2000-12-22','00:00:00','u','u');
INSERT INTO `D` VALUES (74,27,195,'2004-02-21',NULL,'o','o');
INSERT INTO `D` VALUES (75,4,5,'2009-05-15',NULL,'b','b');
INSERT INTO `D` VALUES (76,6,2,'2008-12-12','12:31:05','c','c');
INSERT INTO `D` VALUES (77,2,7,'2000-04-15','00:00:00','q','q');
INSERT INTO `D` VALUES (78,248,25,NULL,'01:16:45',NULL,NULL);
INSERT INTO `D` VALUES (79,NULL,NULL,'2001-10-18','20:38:54','h','h');
INSERT INTO `D` VALUES (80,9,0,'2008-05-25','00:30:15','d','d');
INSERT INTO `D` VALUES (81,75,98,'2004-12-02','23:46:36','w','w');
INSERT INTO `D` VALUES (82,2,6,'2002-02-15','19:03:13','m','m');
INSERT INTO `D` VALUES (83,9,5,'2002-03-03','10:54:27','i','i');
INSERT INTO `D` VALUES (84,4,0,NULL,'00:25:47','w','w');
INSERT INTO `D` VALUES (85,0,3,'2003-01-26','08:44:27','f','f');
INSERT INTO `D` VALUES (86,0,1,'2001-12-19','08:15:38','k','k');
INSERT INTO `D` VALUES (87,1,1,'2001-08-07','19:56:21','v','v');
INSERT INTO `D` VALUES (88,119,147,'2005-02-16','00:00:00','c','c');
INSERT INTO `D` VALUES (89,1,3,'2006-06-10','20:50:52','y','y');
INSERT INTO `D` VALUES (90,7,3,NULL,'03:54:39','h','h');
INSERT INTO `D` VALUES (91,2,NULL,'2005-04-06','23:58:17',NULL,NULL);
INSERT INTO `D` VALUES (92,7,2,'2003-04-27','12:54:58','t','t');
INSERT INTO `D` VALUES (93,2,1,'2005-10-13','04:02:43','l','l');
INSERT INTO `D` VALUES (94,6,8,'2003-10-02','11:31:12','a','a');
INSERT INTO `D` VALUES (95,4,8,'2005-09-09','20:20:04','r','r');
INSERT INTO `D` VALUES (96,5,8,NULL,'00:22:24','s','s');
INSERT INTO `D` VALUES (97,7,0,'2006-02-15','10:09:31','z','z');
INSERT INTO `D` VALUES (98,1,1,'1900-01-01',NULL,'j','j');
INSERT INTO `D` VALUES (99,7,8,'2003-12-24','18:45:35','c','c');
INSERT INTO `D` VALUES (100,2,5,'2001-07-26','11:49:25','f','f');
CREATE TABLE `C` (
  `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_time_key` time 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_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (1,NULL,2,NULL,'11:28:45','w','w');
INSERT INTO `C` VALUES (2,7,9,'2001-09-19','20:25:14','m','m');
INSERT INTO `C` VALUES (3,9,3,'2004-09-12','13:47:24','m','m');
INSERT INTO `C` VALUES (4,7,9,NULL,'19:24:11','k','k');
INSERT INTO `C` VALUES (5,4,NULL,'2002-07-19','15:59:13','r','r');
INSERT INTO `C` VALUES (6,2,9,'2002-12-16','00:00:00','t','t');
INSERT INTO `C` VALUES (7,6,3,'2006-02-08','15:15:04','j','j');
INSERT INTO `C` VALUES (8,8,8,'2006-08-28','11:32:06','u','u');
INSERT INTO `C` VALUES (9,NULL,8,'2001-04-14','18:32:33','h','h');
INSERT INTO `C` VALUES (10,5,53,'2000-01-05','15:19:25','o','o');
INSERT INTO `C` VALUES (11,NULL,0,'2003-12-06','19:03:19',NULL,NULL);
INSERT INTO `C` VALUES (12,6,5,'1900-01-01','00:39:46','k','k');
INSERT INTO `C` VALUES (13,188,166,'2002-11-27',NULL,'e','e');
INSERT INTO `C` VALUES (14,2,3,NULL,'00:00:00','n','n');
INSERT INTO `C` VALUES (15,1,0,'2003-05-27','13:12:11','t','t');
INSERT INTO `C` VALUES (16,1,1,'2005-05-03','04:56:48','c','c');
INSERT INTO `C` VALUES (17,0,9,'2001-04-18','19:56:05','m','m');
INSERT INTO `C` VALUES (18,9,5,'2005-12-27','19:35:19','y','y');
INSERT INTO `C` VALUES (19,NULL,6,'2004-08-20','05:03:03','f','f');
INSERT INTO `C` VALUES (20,4,2,'1900-01-01','18:38:59','d','d');

CREATE TABLE B (
                        pk INTEGER AUTO_INCREMENT,
                        col_int_nokey INTEGER /*! NULL */,
                        col_int_key INTEGER /*! NULL */,

col_date_key DATE /*! NULL */,
                        col_date_nokey DATE /*! NULL */,

col_time_key TIME /*! NULL */,
                        col_time_nokey TIME /*! NULL */,

col_datetime_key DATETIME /*! NULL */,
                        col_datetime_nokey DATETIME /*! NULL */,

col_varchar_key VARCHAR(1) /*! NULL */,
                        col_varchar_nokey VARCHAR(1) /*! NULL */,

PRIMARY KEY (pk),
                        KEY (col_int_key),
                        KEY (col_date_key),
                        KEY (col_time_key),
                        KEY (col_datetime_key),
                        KEY (col_varchar_key, col_int_key)
                )  ENGINE=innodb;

INSERT /*! IGNORE */ INTO B (
                                col_int_key, col_int_nokey,
                                col_date_key, col_date_nokey,
                                col_time_key, col_time_nokey,
                                col_datetime_key, col_datetime_nokey,
                                col_varchar_key, col_varchar_nokey
                        ) VALUES (7, 1, '1900-01-01', '1900-01-01', '01:13:38', '01:13:38', '2005-02-05 00:00:00', '2005-02-05 00:00:00', 'f', 'f');

CREATE VIEW view_B AS SELECT * FROM B;

SELECT *
FROM (
SELECT table1 . `col_varchar_key` AS field1 , SUM( DISTINCT table2 . `pk` ) AS field2 , (
SELECT SUM( SUBQUERY1_t1 . `pk` ) AS SUBQUERY1_field1
FROM ( C AS SUBQUERY1_t1 STRAIGHT_JOIN ( D AS SUBQUERY1_t2 STRAIGHT_JOIN view_B AS SUBQUERY1_t3 ON (SUBQUERY1_t3 . `col_varchar_key` = SUBQUERY1_t2 . `col_varchar_key` ) ) ON (SUBQUERY1_t3 . `col_int_nokey` = SUBQUERY1_t2 . `col_int_key` ) )
WHERE SUBQUERY1_t2 . `col_varchar_key` <= table1 . `col_varchar_key` ) AS field3 , SUM( table1 . `col_varchar_key` ) AS field4 , table2 . `col_time_key` AS field5 , SUM( table1 . `col_varchar_key` ) AS field6 , (
SELECT COUNT( SUBQUERY2_t1 . `pk` ) AS SUBQUERY2_field1
FROM ( C AS SUBQUERY2_t1 STRAIGHT_JOIN CC AS SUBQUERY2_t2 ON (SUBQUERY2_t2 . `col_varchar_nokey` = SUBQUERY2_t1 . `col_varchar_key` ) )
WHERE SUBQUERY2_t2 . `col_int_key` = table1 . `col_int_nokey` AND SUBQUERY2_t1 . `pk` > table1 . `col_int_key` ) AS field7
FROM ( CC AS table1 LEFT JOIN ( ( D AS table2 STRAIGHT_JOIN C AS table3 ON (table3 . `pk` = table2 . `col_int_key` ) ) ) ON (table3 . `col_varchar_key` = table2 . `col_varchar_key` ) )
WHERE ( table3 . `pk` <= ALL (
SELECT 5
FROM DUAL ) ) OR ( table1 . `col_int_key` = 7 AND table1 . `pk` = 58 ) OR ( table1 . `col_varchar_nokey` IS NOT NULL OR table3 . `col_varchar_key` != table3 . `col_varchar_nokey` )
GROUP BY field1, field3, field5, field7
HAVING (field3 <= 'h' AND field2 != 4)
ORDER BY table1 . `col_date_key` ASC , field1, field2, field3, field4, field5, field6, field7 ) AS
FROM_SUBQUERY /* TRANSFORM_OUTCOME_UNORDERED_MATCH */
;

Oleksandr "Sanja" Byelkin (sanja-byelkin) on 2010-09-07