Guilhem,
The patch looks like a good solution. Apart from the comments made by
Joro, I think it is enough to only use the simplified test cases,
alternatively to cut down a bit on the number of rows in the original
test case. As it is now, it is very difficult to read. (If you feel
otherwise, this will not make me reject the patch.)
I would like to take a look at the next version before approving.
Guilhem Bichot wrote:
> #At file:///home/mysql_src/bzrrepos/mysql-6.0-codebase-bugfixing/ based on
> revid:jorgen.loland@stripped
>
> 2841 Guilhem Bichot 2009-09-28
> Fix for BUG#46743 "Azalea processing correlated, aggregate SELECT
> subqueries incorrectly".
> Symptom: "=x" condition matched rows when x is NULL, which should
> never be.
> Summary of the problem: likely a failure of
> http://forge.mysql.com/wiki/MySQL_Internals_Optimizer#Late_NULLs_Filtering
> which didn't eliminate NULL values in some cases.
> Details below.
>
> Consider tables created and filled as in the minimal testcase of
> subselect4.test in this revision.
> Consider three queries:
> 1) SELECT (SELECT 1 FROM D WHERE d = c) FROM C ;
> 2) SELECT (SELECT 1 FROM D WHERE d = c) FROM C GROUP BY c ;
> 3) SELECT (SELECT 1 FROM D WHERE d = IFNULL(c,NULL)) FROM C ;
> C has one row, C.c is NULL, so all 3 queries are equivalent and should
> return NULL (because the subquery gives an empty result): but the
> second query returns "error 1242: Subquery returns more than 1 row",
> which is unexpected.
> It's all about what happens inside the subquery. Let's compare how
> the subquery is optimized and executed in each of those three queries.
>
> For 1) optimizer understands that d=c should eliminate NULL D.d values:
> it sets "null_rejecting" to "true" in add_key_field(). It also sees
> that the condition d=c will be a byproduct of using the "ref"
> index lookup method so does not need to be explicitely evaluated: this
> condition (COND object) is thus removed, this way: make_join_select()
> calls "tmp= make_cond_for_table()" which calls
> make_cond_for_table_from_pred(); around "EQ_FUNC" in that function, a
> zero COND is returned, so "tmp" is 0, thus we come to the "else
> tab->select_cond= sel->cond= NULL" branch. When the subquery
> is executed, join_read_always_key() does not even read keys of D
> because as C.c is NULL, null_rejecting shortcuts.
>
> For 3) because IFNULL is a function, optimizer does not understand
> that d = IFNULL(c,NULL)) should eliminate null D.d values:
> null_rejecting is set to false. The condition is not removed, also
> because IFNULL is a function (both equality members have too different
> types). join_read_always_key() reads the index, searching for NULL
> keys, finds two of them, but they are eliminated by the kept
> condition.
>
> For 2) optimizer does not understand that d=c should eliminate NULL
> D.d values: null_rejecting is false. But the condition is
> eliminated. So join_read_always_key() finds two NULLs and returns them
> (no condition, and null_rejecting=false): error. How did this happen:
> * setting null_rejecting to true requires that both members of d=c
> are Item_field. But as there is GROUP BY, "c" in d=c is an
> Item_outer_ref (built in Item_field::fix_outer_field() around "If an
> outer field is resolved in a grouping select etc"), not an
> Item_field, so null_rejecting is false.
> * make_cond_for_table() eliminates the COND condition because in d=c,
> test_if_ref() looks for __real__ items: it sees that inside
> Item_outer_ref "c", there is an Item_field, so it sees two Item_field
> on the sides of the equality and thus sees that "ref" will verify the
> condition naturally (see test_if_ref() calling eq_def()).
>
> Overall it looks like there is some assymetry between test_if_ref()
> (compares real_item()) and add_key_field() (compares plain item), the
> former catching a broader set of conditions (thus in some cases the condition
> is eliminated (COND removal) by the former but not "restored" (in the
> form of null_rejecting) by the latter). This assymetry was
> introduced by placing "right_item= right_item->real_item();" in
> test_in_ref() by
> sp1r-gkodinov/kgeorge@stripped (as a fix for
> BUG 30788; revert this line and the present bug goes away).
> Symmetry is put back by using real_item() in add_key_field() too, which the
> present patch does. It looks logical, because what matters in d=c is
> whether "c" comes from a field, it's ok if "c" goes through some
> intermediate steps like GROUP BY (which makes Item_outer_ref).
> @ mysql-test/r/subselect4.result
> result (before the fix, there would be an error in the first "GROUP BY"
> SELECT, and numbers instead of NULLs in the
> following SELECTs; each time because some rows would be said to match
> "=NULL").
> @ mysql-test/t/subselect4.test
> test for BUG#46743
> @ sql/sql_select.cc
> * making use of JOIN_TAB::set_select_cond() to track changes of
> JOIN_TAB::select_cond when debugging
> * stepping into get_best_combination() made easier (see commit comment of
> mi_scan.c)
> * the bugfix: when computing null_rejecting, use real_item() and not the base
> item,
> so that we can discover an Item_field inside an Item_outer_ref.
> I use
> if (a)
> null_rejecting= true;
> else
> null_rejecting=false;
> so that the wrong path ("=false") shows up in QA's differential coverage tool
> which spots lines
> executed only in failing tests (it's on the intranet, contact me for more
> details).
> * the assertion in add_not_null_conds() must then be updated, because it can
> now
> meet something else than FIELD_ITEM and must look at the real item. Note that
> add_not_null_conds()
> is executed in our subquery, but quits the iteration at the "continue;".
> * some DBUG printouts in evaluate_join_record() to track if it eliminates a
> record
> @ sql/sql_select.h
> I had to detect when JOIN_TAB::select_cond changed from non-NULL to NULL
> (COND elimination) so added this
> inline method to always have a DBUG printout when this condition is set. If
> reviewers prefer, I can remove
> it and add DBUG_PRINT after every "join_tab->select_cond=" line instead.
> @ storage/myisam/mi_scan.c
> Stepping into info->s->read_rnd() is made difficult by wrapping it
> directly in DBUG_RETURN
> (made me step into DBUG_RETURN() dbug internal code and not manage to step
> into read_rnd()).
>
> modified:
> mysql-test/r/subselect4.result
> mysql-test/t/subselect4.test
> sql/sql_select.cc
> sql/sql_select.h
> storage/myisam/mi_scan.c
> === modified file 'mysql-test/r/subselect4.result'
> --- a/mysql-test/r/subselect4.result 2009-09-03 15:03:46 +0000
> +++ b/mysql-test/r/subselect4.result 2009-09-28 09:26:01 +0000
> @@ -28,3 +28,404 @@ SELECT 1;
> 1
> DROP TABLE t1,t2,t3;
> End of 5.0 tests.
> +#
> +# BUG#46743 "Azalea processing correlated, aggregate SELECT
> +# subqueries incorrectly"
> +#
> +CREATE TABLE C (c int);
> +INSERT INTO C VALUES (NULL);
> +CREATE TABLE D (d int , KEY (d));
> +INSERT INTO D VALUES (NULL),(NULL);
> +0 rows in subquery
> +SELECT 1 AS RESULT FROM D,C WHERE d = c;
> +RESULT
> +base query
> +SELECT (SELECT 1 FROM D WHERE d = c) AS RESULT FROM C ;
> +RESULT
> +NULL
> +EXPLAIN EXTENDED SELECT (SELECT 1 FROM D WHERE d = c) AS RESULT FROM C ;
> +id select_type table type possible_keys key key_len ref rows filtered Extra
> +1 PRIMARY C system NULL NULL NULL NULL 1 100.00
> +2 DEPENDENT SUBQUERY D ref d d 5 const 1 100.00 Using index
> +Warnings:
> +Note 1276 Field or reference 'test.C.c' of SELECT #2 was resolved in SELECT #1
> +Note 1003 select (select 1 AS `1` from `test`.`D` where (`test`.`D`.`d` = '0')) AS
> `RESULT` from `test`.`C`
> +first equivalent variant
> +SELECT (SELECT 1 FROM D WHERE d = IFNULL(c,NULL)) AS RESULT FROM C ;
> +RESULT
> +NULL
> +EXPLAIN EXTENDED SELECT (SELECT 1 FROM D WHERE d = IFNULL(c,NULL)) AS RESULT FROM C
> ;
> +id select_type table type possible_keys key key_len ref rows filtered Extra
> +1 PRIMARY C system NULL NULL NULL NULL 1 100.00
> +2 DEPENDENT SUBQUERY D ref d d 5 const 1 100.00 Using where; Using index
> +Warnings:
> +Note 1276 Field or reference 'test.C.c' of SELECT #2 was resolved in SELECT #1
> +Note 1003 select (select 1 AS `1` from `test`.`D` where (`test`.`D`.`d` =
> ifnull('0',NULL))) AS `RESULT` from `test`.`C`
> +second equivalent variant
> +SELECT (SELECT 1 FROM D WHERE d = c) AS RESULT FROM C GROUP BY c ;
> +RESULT
> +NULL
> +EXPLAIN EXTENDED SELECT (SELECT 1 FROM D WHERE d = c) AS RESULT FROM C GROUP BY c ;
> +id select_type table type possible_keys key key_len ref rows filtered Extra
> +1 PRIMARY C system NULL NULL NULL NULL 1 100.00
> +2 DEPENDENT SUBQUERY D ref d d 5 const 1 100.00 Using index
> +Warnings:
> +Note 1276 Field or reference 'test.C.c' of SELECT #2 was resolved in SELECT #1
> +Note 1003 select (select 1 AS `1` from `test`.`D` where (`test`.`D`.`d` = '0')) AS
> `RESULT` from `test`.`C` group by '0'
> +DROP TABLE C,D;
> +CREATE TABLE `C` (
> +`int_nokey` int(11) DEFAULT NULL,
> +`int_key` int(11) DEFAULT NULL,
> +`varchar_nokey` varchar(1) DEFAULT NULL,
> +KEY `int_key` (`int_key`),
> +KEY `varchar_key` (`int_key`)
> +) DEFAULT CHARSET=latin1;
> +INSERT INTO `C` VALUES
> (7,9,'m'),(9,3,'m'),(7,9,'k'),(4,NULL,'r'),(2,9,'t'),(6,3,'j'),(8,8,'u'),(NULL,8,'h'),(5,53,'o'),(NULL,0,NULL),(6,5,'k'),(188,166,'e'),(2,3,'n'),(1,0,'t'),(1,1,'c'),(0,9,'m'),(9,5,'y'),(NULL,6,'f'),(4,2,'d'),(6,NULL,'r');
> +CREATE TABLE `D` (
> +`int_nokey` int(11) DEFAULT NULL,
> +`int_key` int(11) DEFAULT NULL,
> +`varchar_nokey` varchar(1) DEFAULT NULL,
> +KEY `int_key` (`int_key`),
> +KEY `varchar_key` (`int_key`)
> +) DEFAULT CHARSET=latin1;
> +INSERT INTO `D` VALUES
> (8,0,'c'),(6,0,'o'),(6,7,'c'),(3,8,'d'),(9,4,'v'),(2,6,'m'),(1,5,'j'),(8,NULL,'f'),(0,NULL,'n'),(9,8,'z'),(8,8,'h'),(NULL,8,'q'),(0,1,'w'),(5,1,'z'),(1,5,'j'),(1,2,'a'),(6,7,'m'),(6,6,'n'),(1,4,'e'),(8,7,'u'),(1,0,'s'),(0,9,'u'),(4,3,'r'),(9,5,'g'),(8,1,'o'),(5,1,'w'),(9,5,'b'),(5,9,NULL),(NULL,2,'y'),(NULL,5,'y'),(105,248,'u'),(0,0,'p'),(3,8,'s'),(1,1,'e'),(75,255,'d'),(9,9,'d'),(7,9,'c'),(NULL,3,'b'),(NULL,9,'t'),(4,6,NULL),(0,4,'y'),(204,60,'c'),(0,7,'d'),(9,1,'x'),(8,6,'p'),(7,4,'e'),(8,NULL,'g'),(NULL,8,'x'),(6,0,'s'),(5,8,'e'),(2,151,'l'),(3,7,'p'),(7,6,'h'),(NULL,NULL,'m'),(145,23,'n'),(0,2,'v'),(1,4,'b'),(7,NULL,'x'),(3,NULL,'r'),(NULL,77,'t'),(2,NULL,'w'),(2,NULL,'w'),(2,7,'k'),(8,1,'a'),(6,9,'t'),(1,6,'z'),(NULL,2,'e'),(1,3,'q'),(0,0,'e'),(4,NULL,'v'),(1,6,'d'),(1,3,'u'),(27,195,'o'),(4,5,'b'),(6,2,'c'),(2,7,'q'),(248,25,NULL),(NULL,NULL,'h'),(9,0,'d'),(75,98,'w'),(2,6,'m'),(9,5,'i'),(4,0,'w'),(0,3,'f'),(0,1,'k'),(1,1,'v'),(119,147,'c'),(1,
3,
>
> 'y'),(7,3,'h'),(2,NULL,NULL),(7,2,'t'),(2,1,'l'),(6,8,'a'),(4,8,'r'),(5,8,'s'),(7,0,'z'),(1,1,'j'),(7,8,'c'),(2,5,'f'),(1,4,'g');
> +SELECT table2 .`int_key` field12 , (
> +SELECT SUM( `int_nokey` )
> +FROM D
> +WHERE `int_key` = table1 .`int_key` )
> +FROM C table1 RIGHT JOIN C table2 ON table1 .`varchar_nokey` ;
> +field12 (
> +SELECT SUM( `int_nokey` )
> +FROM D
> +WHERE `int_key` = table1 .`int_key` )
> +NULL NULL
> +NULL NULL
> +0 NULL
> +0 NULL
> +1 NULL
> +2 NULL
> +3 NULL
> +3 NULL
> +3 NULL
> +5 NULL
> +5 NULL
> +6 NULL
> +8 NULL
> +8 NULL
> +9 NULL
> +9 NULL
> +9 NULL
> +9 NULL
> +53 NULL
> +166 NULL
> +Warnings:
> +Warning 1292 Truncated incorrect INTEGER value: 'm'
> +Warning 1292 Truncated incorrect INTEGER value: 'm'
> +Warning 1292 Truncated incorrect INTEGER value: 'k'
> +Warning 1292 Truncated incorrect INTEGER value: 'r'
> +Warning 1292 Truncated incorrect INTEGER value: 't'
> +Warning 1292 Truncated incorrect INTEGER value: 'j'
> +Warning 1292 Truncated incorrect INTEGER value: 'u'
> +Warning 1292 Truncated incorrect INTEGER value: 'h'
> +Warning 1292 Truncated incorrect INTEGER value: 'o'
> +Warning 1292 Truncated incorrect INTEGER value: 'k'
> +Warning 1292 Truncated incorrect INTEGER value: 'e'
> +Warning 1292 Truncated incorrect INTEGER value: 'n'
> +Warning 1292 Truncated incorrect INTEGER value: 't'
> +Warning 1292 Truncated incorrect INTEGER value: 'c'
> +Warning 1292 Truncated incorrect INTEGER value: 'm'
> +Warning 1292 Truncated incorrect INTEGER value: 'y'
> +Warning 1292 Truncated incorrect INTEGER value: 'f'
> +Warning 1292 Truncated incorrect INTEGER value: 'd'
> +Warning 1292 Truncated incorrect INTEGER value: 'r'
> +Warning 1292 Truncated incorrect INTEGER value: 'm'
> +Warning 1292 Truncated incorrect INTEGER value: 'm'
> +Warning 1292 Truncated incorrect INTEGER value: 'k'
> +Warning 1292 Truncated incorrect INTEGER value: 'r'
> +Warning 1292 Truncated incorrect INTEGER value: 't'
> +Warning 1292 Truncated incorrect INTEGER value: 'j'
> +Warning 1292 Truncated incorrect INTEGER value: 'u'
> +Warning 1292 Truncated incorrect INTEGER value: 'h'
> +Warning 1292 Truncated incorrect INTEGER value: 'o'
> +Warning 1292 Truncated incorrect INTEGER value: 'k'
> +Warning 1292 Truncated incorrect INTEGER value: 'e'
> +Warning 1292 Truncated incorrect INTEGER value: 'n'
> +Warning 1292 Truncated incorrect INTEGER value: 't'
> +Warning 1292 Truncated incorrect INTEGER value: 'c'
> +Warning 1292 Truncated incorrect INTEGER value: 'm'
> +Warning 1292 Truncated incorrect INTEGER value: 'y'
> +Warning 1292 Truncated incorrect INTEGER value: 'f'
> +Warning 1292 Truncated incorrect INTEGER value: 'd'
> +Warning 1292 Truncated incorrect INTEGER value: 'r'
> +Warning 1292 Truncated incorrect INTEGER value: 'm'
> +Warning 1292 Truncated incorrect INTEGER value: 'm'
> +Warning 1292 Truncated incorrect INTEGER value: 'k'
> +Warning 1292 Truncated incorrect INTEGER value: 'r'
> +Warning 1292 Truncated incorrect INTEGER value: 't'
> +Warning 1292 Truncated incorrect INTEGER value: 'j'
> +Warning 1292 Truncated incorrect INTEGER value: 'u'
> +Warning 1292 Truncated incorrect INTEGER value: 'h'
> +Warning 1292 Truncated incorrect INTEGER value: 'o'
> +Warning 1292 Truncated incorrect INTEGER value: 'k'
> +Warning 1292 Truncated incorrect INTEGER value: 'e'
> +Warning 1292 Truncated incorrect INTEGER value: 'n'
> +Warning 1292 Truncated incorrect INTEGER value: 't'
> +Warning 1292 Truncated incorrect INTEGER value: 'c'
> +Warning 1292 Truncated incorrect INTEGER value: 'm'
> +Warning 1292 Truncated incorrect INTEGER value: 'y'
> +Warning 1292 Truncated incorrect INTEGER value: 'f'
> +Warning 1292 Truncated incorrect INTEGER value: 'd'
> +Warning 1292 Truncated incorrect INTEGER value: 'r'
> +Warning 1292 Truncated incorrect INTEGER value: 'm'
> +Warning 1292 Truncated incorrect INTEGER value: 'm'
> +Warning 1292 Truncated incorrect INTEGER value: 'k'
> +Warning 1292 Truncated incorrect INTEGER value: 'r'
> +Warning 1292 Truncated incorrect INTEGER value: 't'
> +Warning 1292 Truncated incorrect INTEGER value: 'j'
> +Warning 1292 Truncated incorrect INTEGER value: 'u'
> +SELECT table2 .`int_key` field12 , (
> +SELECT SUM( `int_nokey` )
> +FROM D
> +WHERE `int_key` = table1 .`int_key` )
> +FROM C table1 RIGHT JOIN C table2 ON table1 .`varchar_nokey`
> +GROUP BY field12 ;
> +field12 (
> +SELECT SUM( `int_nokey` )
> +FROM D
> +WHERE `int_key` = table1 .`int_key` )
> +NULL NULL
> +0 NULL
> +1 NULL
> +2 NULL
> +3 NULL
> +5 NULL
> +6 NULL
> +8 NULL
> +9 NULL
> +53 NULL
> +166 NULL
> +Warnings:
> +Warning 1292 Truncated incorrect INTEGER value: 'm'
> +Warning 1292 Truncated incorrect INTEGER value: 'm'
> +Warning 1292 Truncated incorrect INTEGER value: 'k'
> +Warning 1292 Truncated incorrect INTEGER value: 'r'
> +Warning 1292 Truncated incorrect INTEGER value: 't'
> +Warning 1292 Truncated incorrect INTEGER value: 'j'
> +Warning 1292 Truncated incorrect INTEGER value: 'u'
> +Warning 1292 Truncated incorrect INTEGER value: 'h'
> +Warning 1292 Truncated incorrect INTEGER value: 'o'
> +Warning 1292 Truncated incorrect INTEGER value: 'k'
> +Warning 1292 Truncated incorrect INTEGER value: 'e'
> +Warning 1292 Truncated incorrect INTEGER value: 'n'
> +Warning 1292 Truncated incorrect INTEGER value: 't'
> +Warning 1292 Truncated incorrect INTEGER value: 'c'
> +Warning 1292 Truncated incorrect INTEGER value: 'm'
> +Warning 1292 Truncated incorrect INTEGER value: 'y'
> +Warning 1292 Truncated incorrect INTEGER value: 'f'
> +Warning 1292 Truncated incorrect INTEGER value: 'd'
> +Warning 1292 Truncated incorrect INTEGER value: 'r'
> +Warning 1292 Truncated incorrect INTEGER value: 'm'
> +Warning 1292 Truncated incorrect INTEGER value: 'm'
> +Warning 1292 Truncated incorrect INTEGER value: 'k'
> +Warning 1292 Truncated incorrect INTEGER value: 'r'
> +Warning 1292 Truncated incorrect INTEGER value: 't'
> +Warning 1292 Truncated incorrect INTEGER value: 'j'
> +Warning 1292 Truncated incorrect INTEGER value: 'u'
> +Warning 1292 Truncated incorrect INTEGER value: 'h'
> +Warning 1292 Truncated incorrect INTEGER value: 'o'
> +Warning 1292 Truncated incorrect INTEGER value: 'k'
> +Warning 1292 Truncated incorrect INTEGER value: 'e'
> +Warning 1292 Truncated incorrect INTEGER value: 'n'
> +Warning 1292 Truncated incorrect INTEGER value: 't'
> +Warning 1292 Truncated incorrect INTEGER value: 'c'
> +Warning 1292 Truncated incorrect INTEGER value: 'm'
> +Warning 1292 Truncated incorrect INTEGER value: 'y'
> +Warning 1292 Truncated incorrect INTEGER value: 'f'
> +Warning 1292 Truncated incorrect INTEGER value: 'd'
> +Warning 1292 Truncated incorrect INTEGER value: 'r'
> +Warning 1292 Truncated incorrect INTEGER value: 'm'
> +Warning 1292 Truncated incorrect INTEGER value: 'm'
> +Warning 1292 Truncated incorrect INTEGER value: 'k'
> +Warning 1292 Truncated incorrect INTEGER value: 'r'
> +Warning 1292 Truncated incorrect INTEGER value: 't'
> +Warning 1292 Truncated incorrect INTEGER value: 'j'
> +Warning 1292 Truncated incorrect INTEGER value: 'u'
> +Warning 1292 Truncated incorrect INTEGER value: 'h'
> +Warning 1292 Truncated incorrect INTEGER value: 'o'
> +Warning 1292 Truncated incorrect INTEGER value: 'k'
> +Warning 1292 Truncated incorrect INTEGER value: 'e'
> +Warning 1292 Truncated incorrect INTEGER value: 'n'
> +Warning 1292 Truncated incorrect INTEGER value: 't'
> +Warning 1292 Truncated incorrect INTEGER value: 'c'
> +Warning 1292 Truncated incorrect INTEGER value: 'm'
> +Warning 1292 Truncated incorrect INTEGER value: 'y'
> +Warning 1292 Truncated incorrect INTEGER value: 'f'
> +Warning 1292 Truncated incorrect INTEGER value: 'd'
> +Warning 1292 Truncated incorrect INTEGER value: 'r'
> +Warning 1292 Truncated incorrect INTEGER value: 'm'
> +Warning 1292 Truncated incorrect INTEGER value: 'm'
> +Warning 1292 Truncated incorrect INTEGER value: 'k'
> +Warning 1292 Truncated incorrect INTEGER value: 'r'
> +Warning 1292 Truncated incorrect INTEGER value: 't'
> +Warning 1292 Truncated incorrect INTEGER value: 'j'
> +Warning 1292 Truncated incorrect INTEGER value: 'u'
> +DROP TABLE C;
> +DROP TABLE D;
> +CREATE TABLE `CC` (
> +`pk` int(11) NOT NULL AUTO_INCREMENT,
> +`int_nokey` int(11) DEFAULT NULL,
> +`int_key` int(11) DEFAULT NULL,
> +`date_key` date DEFAULT NULL,
> +`time_key` time DEFAULT NULL,
> +`datetime_key` datetime DEFAULT NULL,
> +`varchar_key` varchar(1) DEFAULT NULL,
> +`varchar_nokey` varchar(1) DEFAULT NULL,
> +PRIMARY KEY (`pk`),
> +KEY `int_key` (`int_key`),
> +KEY `date_key` (`date_key`),
> +KEY `time_key` (`time_key`),
> +KEY `datetime_key` (`datetime_key`),
> +KEY `varchar_key` (`varchar_key`,`int_key`)
> +) AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
> +INSERT INTO `CC` VALUES (10,3,186,'2005-02-15','19:53:05','2008-05-26
> 12:27:10','m','m'),(11,6,NULL,NULL,'19:18:56','2004-12-14
> 16:37:30','y','y'),(12,92,2,'2008-11-04','10:55:12','2003-02-11
> 21:19:41','j','j'),(13,7,3,'2004-09-04','00:25:00','2009-10-18
> 02:27:49','d','d'),(14,NULL,0,'2006-06-05','12:35:47','2000-09-26
> 07:45:57','z','z'),(15,3,133,'1900-01-01','19:53:03',NULL,'e','e'),(16,5,1,'1900-01-01','17:53:30','2005-11-10
> 12:40:29','h','h'),(17,1,8,'1900-01-01','11:35:49','2009-04-25
> 00:00:00','b','b'),(18,2,5,'2005-01-13',NULL,'2002-11-27
> 00:00:00','s','s'),(19,NULL,5,'2006-05-21','06:01:40','2004-01-26
> 20:32:32','e','e'),(20,1,8,'2003-09-08','05:45:11','2007-10-26
> 11:41:40','j','j'),(21,0,6,'2006-12-23','00:00:00','2005-10-07
> 00:00:00','e','e'),(22,210,51,'2006-10-15','00:00:00','2000-07-15
> 05:00:34','f','f'),(23,8,4,'2005-04-06','06:11:01','2000-04-03
> 16:33:32','v','v'),(24,7,7,'2008-04-07','13:02:46',NULL,'x','x'),(25,5,6,'2006-10-10','21:44:25','2001-04-25
> 01:26
:1
> 2','m','m'),(26,NULL,4,'1900-01-01','22:43:58','2000-12-27
> 00:00:00','c','c'),(27,6,7,'1900-01-01',NULL,'2005-10-04
> 00:00:00','m','m'),(28,9,3,'2005-05-07','06:43:09','2004-03-08
> 02:50:35','b','b'),(29,3,2,'2000-07-19','12:20:22','2004-09-24 09:21:35','x','x');
> +CREATE TABLE `C` (
> +`pk` int(11) NOT NULL AUTO_INCREMENT,
> +`int_nokey` int(11) DEFAULT NULL,
> +`int_key` int(11) DEFAULT NULL,
> +`date_key` date DEFAULT NULL,
> +`time_key` time DEFAULT NULL,
> +`datetime_key` datetime DEFAULT NULL,
> +`varchar_key` varchar(1) DEFAULT NULL,
> +`varchar_nokey` varchar(1) DEFAULT NULL,
> +PRIMARY KEY (`pk`),
> +KEY `int_key` (`int_key`),
> +KEY `date_key` (`date_key`),
> +KEY `time_key` (`time_key`),
> +KEY `datetime_key` (`datetime_key`),
> +KEY `varchar_key` (`varchar_key`,`int_key`)
> +) AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
> +INSERT INTO `C` VALUES
> (1,7,9,'2001-09-19','20:25:14',NULL,'m','m'),(2,9,3,'2004-09-12','13:47:24','1900-01-01
> 00:00:00','m','m'),(3,7,9,NULL,'19:24:11','2009-07-25
> 00:00:00','k','k'),(4,4,NULL,'2002-07-19','15:59:13',NULL,'r','r'),(5,2,9,'2002-12-16','00:00:00','2008-07-27
> 00:00:00','t','t'),(6,6,3,'2006-02-08','15:15:04','2002-11-13
> 16:37:31','j','j'),(7,8,8,'2006-08-28','11:32:06','1900-01-01
> 00:00:00','u','u'),(8,NULL,8,'2001-04-14','18:32:33','2003-12-10
> 00:00:00','h','h'),(9,5,53,'2000-01-05','15:19:25','2001-12-21
> 22:38:22','o','o'),(10,NULL,0,'2003-12-06','19:03:19','2008-12-13
> 23:16:44',NULL,NULL),(11,6,5,'1900-01-01','00:39:46','2005-08-15
> 12:39:41','k','k'),(12,188,166,'2002-11-27',NULL,NULL,'e','e'),(13,2,3,NULL,'00:00:00','2006-09-11
> 12:06:14','n','n'),(14,1,0,'2003-05-27','13:12:11','2007-12-15
> 12:39:34','t','t'),(15,1,1,'2005-05-03','04:56:48','2005-08-09
> 00:00:00','c','c'),(16,0,9,'2001-04-18','19:56:05','2001-09-02
> 22:50:02','m','m'),(17,9,5,'2005-12-27','
19
> :35:19','2005-12-16
> 22:58:11','y','y'),(18,NULL,6,'2004-08-20','05:03:03','2007-04-19
> 00:19:53','f','f'),(19,4,2,'1900-01-01','18:38:59','1900-01-01
> 00:00:00','d','d'),(20,6,NULL,'2003-05-12',NULL,'2000-09-12 00:00:00','r','r');
> +CREATE TABLE `D` (
> +`pk` int(11) NOT NULL AUTO_INCREMENT,
> +`int_nokey` int(11) DEFAULT NULL,
> +`int_key` int(11) DEFAULT NULL,
> +`date_key` date DEFAULT NULL,
> +`time_key` time DEFAULT NULL,
> +`datetime_key` datetime DEFAULT NULL,
> +`varchar_key` varchar(1) DEFAULT NULL,
> +`varchar_nokey` varchar(1) DEFAULT NULL,
> +PRIMARY KEY (`pk`),
> +KEY `int_key` (`int_key`),
> +KEY `date_key` (`date_key`),
> +KEY `time_key` (`time_key`),
> +KEY `datetime_key` (`datetime_key`),
> +KEY `varchar_key` (`varchar_key`,`int_key`)
> +) AUTO_INCREMENT=101 DEFAULT CHARSET=latin1;
> +INSERT INTO `D` VALUES (1,8,0,'2003-01-07','14:34:45','2004-08-10
> 09:09:31','c','c'),(2,6,0,NULL,'11:49:48','2005-03-21
> 04:31:40','o','o'),(3,6,7,'2005-03-12','18:12:55','2002-10-25
> 23:50:35','c','c'),(4,3,8,'2000-08-02','18:30:05','2001-04-01
> 21:14:04','d','d'),(5,9,4,'1900-01-01','14:19:30','2005-03-12
> 06:02:34','v','v'),(6,2,6,'2006-07-06','05:20:04','2001-05-06
> 14:49:12','m','m'),(7,1,5,'2006-12-24','20:29:31','2004-04-25
> 00:00:00','j','j'),(8,8,NULL,'2004-11-16','07:08:09','2001-03-22
> 18:38:43','f','f'),(9,0,NULL,'2002-09-09','14:49:14','2006-04-25
> 21:03:02','n','n'),(10,9,8,NULL,'00:00:00','2009-09-07
> 18:40:43','z','z'),(11,8,8,'2008-06-24','09:58:06','2004-03-23
> 00:00:00','h','h'),(12,NULL,8,'2001-04-21',NULL,'2009-04-15
> 00:08:29','q','q'),(13,0,1,'2003-11-22','18:24:16','2000-04-21
> 00:00:00','w','w'),(14,5,1,'2004-09-12','17:39:57','2000-02-17
> 19:41:23','z','z'),(15,1,5,'2006-06-20','08:23:21','2003-09-20
> 07:38:14','j','j'),(16,1,2,NULL,NULL,'2000-11-28 20:42:12','
a'
> ,'a'),(17,6,7,'2001-11-25','21:50:46','2005-06-12
> 11:13:17','m','m'),(18,6,6,'2004-10-26','12:33:17','1900-01-01
> 00:00:00','n','n'),(19,1,4,'2005-01-19','03:06:43','2006-02-09
> 20:41:06','e','e'),(20,8,7,'2008-07-06','03:46:14','2004-05-22
> 01:05:57','u','u'),(21,1,0,'1900-01-01','20:34:52','2004-03-04
> 13:46:31','s','s'),(22,0,9,'1900-01-01',NULL,'1900-01-01
> 00:00:00','u','u'),(23,4,3,'2004-06-08','10:41:20','2004-10-20
> 07:20:19','r','r'),(24,9,5,'2007-02-20','08:43:11','2006-04-17
> 00:00:00','g','g'),(25,8,1,'2008-06-18',NULL,'2000-10-27
> 00:00:00','o','o'),(26,5,1,'2008-05-15','10:17:51','2007-04-14
> 08:54:06','w','w'),(27,9,5,'2005-10-06','06:34:09','2008-04-12
> 17:03:52','b','b'),(28,5,9,NULL,'21:22:47','2007-02-19
> 17:37:09',NULL,NULL),(29,NULL,2,'2006-10-12','04:02:32','1900-01-01
> 00:00:00','y','y'),(30,NULL,5,'2005-01-24','02:33:14','2001-10-10
> 08:32:27','y','y'),(31,105,248,'2009-06-27','16:32:56',NULL,'u','u'),(32,0,0,NULL,'21:32:42','2001-12-16
> 05:31:53','p','p'),(33,3,
8,
> NULL,'23:04:47','2003-07-19
> 18:03:28','s','s'),(34,1,1,'1900-01-01','22:05:43','2001-03-27
> 11:44:10','e','e'),(35,75,255,'2005-12-22','02:05:45','2008-06-15
> 02:13:00','d','d'),(36,9,9,'2005-05-03','00:00:00','2009-03-14
> 21:29:56','d','d'),(37,7,9,'2003-05-27','18:09:07','2005-01-02
> 00:00:00','c','c'),(38,NULL,3,'2006-05-25','10:54:06','2007-07-16
> 04:44:07','b','b'),(39,NULL,9,NULL,'23:15:50','2003-08-26
> 21:38:26','t','t'),(40,4,6,'2009-01-04','10:17:40','2004-04-19
> 04:18:47',NULL,NULL),(41,0,4,'2009-02-14','03:37:09','2000-01-06
> 20:32:48','y','y'),(42,204,60,'2003-01-16','22:26:06','2006-06-23
> 13:27:17','c','c'),(43,0,7,'1900-01-01','17:10:38','2007-11-27
> 00:00:00','d','d'),(44,9,1,'2007-06-26','00:00:00','2002-04-03
> 12:06:51','x','x'),(45,8,6,'2004-03-27','17:08:49','2008-12-28
> 09:47:42','p','p'),(46,7,4,NULL,'19:04:40','2002-04-04
> 10:07:54','e','e'),(47,8,NULL,'2005-06-06','20:53:28','2003-04-26
> 02:55:13','g','g'),(48,NULL,8,'2003-03-02','11:46:03',NULL,'x','x'),(49,6,0,
'2
> 004-05-13',NULL,'2009-02-19
> 03:17:06','s','s'),(50,5,8,'2005-09-13','10:58:07','1900-01-01
> 00:00:00','e','e'),(51,2,151,'2005-10-03','00:00:00','2000-11-10
> 08:20:01','l','l'),(52,3,7,'2005-10-14','09:43:15','2008-02-10
> 00:00:00','p','p'),(53,7,6,NULL,'21:40:32','1900-01-01
> 00:00:00','h','h'),(54,NULL,NULL,'2005-09-16','00:17:44',NULL,'m','m'),(55,145,23,'2005-03-10','16:47:26','2001-02-05
> 02:01:50','n','n'),(56,0,2,'2000-06-19','00:00:00','2000-10-28
> 08:44:25','v','v'),(57,1,4,'2002-11-03','05:25:59','2005-03-20
> 10:53:59','b','b'),(58,7,NULL,'2009-01-05','00:00:00','2001-06-02
> 13:54:13','x','x'),(59,3,NULL,'2003-05-22','20:33:04','1900-01-01
> 00:00:00','r','r'),(60,NULL,77,'2005-07-02','00:46:12','2009-07-16
> 13:05:43','t','t'),(61,2,NULL,'1900-01-01','00:00:00','2009-03-26
> 23:16:20','w','w'),(62,2,NULL,'2006-06-21','02:13:59','2003-02-06
> 18:12:15','w','w'),(63,2,7,NULL,'02:54:47','2006-06-05
> 03:22:51','k','k'),(64,8,1,'2005-12-16','18:13:59','2002-02-10 05:47:27','a','a'),(
65
> ,6,9,'2004-11-05','13:53:08','2001-08-01
> 08:50:52','t','t'),(66,1,6,NULL,'22:21:30','1900-01-01
> 00:00:00','z','z'),(67,NULL,2,'2004-09-14','11:41:50',NULL,'e','e'),(68,1,3,'2002-04-06','15:20:02','1900-01-01
> 00:00:00','q','q'),(69,0,0,NULL,NULL,'2000-09-23
> 00:00:00','e','e'),(70,4,NULL,'2002-11-13',NULL,'2007-07-09
> 08:32:49','v','v'),(71,1,6,'2006-05-27','07:51:52','2000-01-05
> 00:00:00','d','d'),(72,1,3,'2000-12-22','00:00:00','2000-09-24
> 00:00:00','u','u'),(73,27,195,'2004-02-21',NULL,'2005-05-06
> 00:00:00','o','o'),(74,4,5,'2009-05-15',NULL,'2000-03-11
> 00:00:00','b','b'),(75,6,2,'2008-12-12','12:31:05','2001-09-02
> 16:17:35','c','c'),(76,2,7,'2000-04-15','00:00:00','2006-04-25
> 05:43:44','q','q'),(77,248,25,NULL,'01:16:45','2009-10-25
> 22:04:02',NULL,NULL),(78,NULL,NULL,'2001-10-18','20:38:54','2004-08-06
> 00:00:00','h','h'),(79,9,0,'2008-05-25','00:30:15','2001-11-27
> 05:07:57','d','d'),(80,75,98,'2004-12-02','23:46:36','2009-06-28
> 03:18:39','w','w'),(81,2,6,'2002-02-15','19:
03
> :13','2000-03-12
> 00:00:00','m','m'),(82,9,5,'2002-03-03','10:54:27',NULL,'i','i'),(83,4,0,NULL,'00:25:47','2007-10-20
> 00:00:00','w','w'),(84,0,3,'2003-01-26','08:44:27','2009-09-27
> 00:00:00','f','f'),(85,0,1,'2001-12-19','08:15:38','2002-07-16
> 00:00:00','k','k'),(86,1,1,'2001-08-07','19:56:21','2005-02-20
> 00:00:00','v','v'),(87,119,147,'2005-02-16','00:00:00',NULL,'c','c'),(88,1,3,'2006-06-10','20:50:52','2001-07-16
> 00:00:00','y','y'),(89,7,3,NULL,'03:54:39','2009-05-20
> 21:04:12','h','h'),(90,2,NULL,'2005-04-06','23:58:17','2002-03-13
> 10:55:40',NULL,NULL),(91,7,2,'2003-04-27','12:54:58','2005-07-12
> 00:00:00','t','t'),(92,2,1,'2005-10-13','04:02:43','2006-07-22
> 09:46:34','l','l'),(93,6,8,'2003-10-02','11:31:12','2001-09-01
> 00:00:00','a','a'),(94,4,8,'2005-09-09','20:20:04','2002-05-27
> 18:38:45','r','r'),(95,5,8,NULL,'00:22:24',NULL,'s','s'),(96,7,0,'2006-02-15','10:09:31',NULL,'z','z'),(97,1,1,'1900-01-01',NULL,'2009-08-08
> 22:38:53','j','j'),(98,7,8,'2003-12-24','18:45:35',
NU
> LL,'c','c'),(99,2,5,'2001-07-26','11:49:25','2007-04-25
> 05:08:49','f','f'),(100,1,4,'2001-05-13','12:52:58','2006-12-15 00:00:00','g','g');
> +CREATE TABLE `BB` (
> +`pk` int(11) NOT NULL AUTO_INCREMENT,
> +`int_nokey` int(11) DEFAULT NULL,
> +`int_key` int(11) DEFAULT NULL,
> +`date_key` date DEFAULT NULL,
> +`time_key` time DEFAULT NULL,
> +`datetime_key` datetime DEFAULT NULL,
> +`varchar_key` varchar(1) DEFAULT NULL,
> +`varchar_nokey` varchar(1) DEFAULT NULL,
> +PRIMARY KEY (`pk`),
> +KEY `int_key` (`int_key`),
> +KEY `date_key` (`date_key`),
> +KEY `time_key` (`time_key`),
> +KEY `datetime_key` (`datetime_key`),
> +KEY `varchar_key` (`varchar_key`,`int_key`)
> +) AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;
> +INSERT INTO `BB` VALUES (10,1,9,'2006-06-14','19:48:31','1900-01-01
> 00:00:00','r','r'),(11,5,9,'2002-09-12','00:00:00','2006-12-03 09:37:26','a','a');
> +SELECT DISTINCT SQL_SMALL_RESULT (
> +SELECT MIN( SUBQUERY1_t1 . `pk` ) AS SUBQUERY1_field1
> +FROM ( BB AS SUBQUERY1_t1 )
> +WHERE SUBQUERY1_t1 . `varchar_key` < table1 . `varchar_nokey` AND SUBQUERY1_t1 .
> `varchar_key` != SUBQUERY1_t1 . `varchar_nokey` ) AS field1 , table2 . `date_key` AS
> field2 , table1 . `int_key` AS field3 , table1 . `pk` AS field4 , CONCAT ( table2 .
> `varchar_nokey` , table1 . `varchar_key` ) AS field5 , table2 . `int_key` AS field6 ,
> CONCAT ( table2 . `varchar_nokey` , table2 . `varchar_key` ) AS field7 , table1 .
> `datetime_key` AS field8 , table1 . `time_key` AS field9 , table1 . `date_key` AS field10
> , table2 . `varchar_nokey` AS field11 , ( ( table1 . `int_nokey` ) * ( table2 . `int_key`
> ) ) AS field12 , table1 . `time_key` AS field13 , (
> +SELECT SUM( SUBQUERY2_t1 . `int_nokey` ) AS SUBQUERY2_field1
> +FROM ( D AS SUBQUERY2_t1 LEFT JOIN CC AS SUBQUERY2_t2 ON (SUBQUERY2_t2 .
> `varchar_key` = SUBQUERY2_t1 . `varchar_key` ) )
> +WHERE SUBQUERY2_t1 . `int_key` = table1 . `int_key` AND SUBQUERY2_t2 . `pk` <=
> SUBQUERY2_t2 . `pk` ) AS field14
> +FROM ( C AS table1 RIGHT OUTER JOIN C AS table2 ON (table2 . `pk` = table1 .
> `varchar_nokey` ) )
> +WHERE ( table2 . `varchar_key` < ALL (
> +SELECT SUBQUERY3_t1 . `varchar_nokey` AS SUBQUERY3_field1
> +FROM ( BB AS SUBQUERY3_t1 )
> +WHERE SUBQUERY3_t1 . `int_nokey` >= table2 . `int_nokey` AND SUBQUERY3_t1 .
> `varchar_nokey` > table1 . `varchar_key` ) ) AND table1 . `varchar_key` = 'g' OR ( (
> 'm', 'k' ) IN (
> +SELECT SUBQUERY4_t2 . `varchar_nokey` AS SUBQUERY4_field1 , SUBQUERY4_t2 .
> `varchar_key` AS SUBQUERY4_field2
> +FROM ( CC AS SUBQUERY4_t1 RIGHT OUTER JOIN BB AS SUBQUERY4_t2 ON (SUBQUERY4_t2 .
> `int_nokey` = SUBQUERY4_t1 . `pk` ) )
> +WHERE SUBQUERY4_t1 . `varchar_key` <> SUBQUERY4_t1 . `varchar_nokey` ) OR
> table1 . `pk` IS NULL )
> +GROUP BY field1, field2, field3, field4, field5, field6, field7, field8, field9,
> field10, field11, field12, field13, field14
> +ORDER BY CONCAT ( table2 . `varchar_key`, table2 . `varchar_key` ) , table1 .
> `varchar_key` ASC , table1 . `pk` , table2 .`pk` ASC , field14 ASC , field13 ASC, field1,
> field2, field3, field4, field5, field6, field7, field8, field9, field10, field11, field12,
> field13, field14
> +LIMIT 2 OFFSET 1;
>
> +field1 field2 field3 field4 field5 field6 field7 field8 field9 field10 field11 field12 field13 field14
> +NULL 2005-05-03 NULL NULL NULL 1 cc NULL NULL NULL c NULL NULL NULL
> +NULL 1900-01-01 NULL NULL NULL 2 dd NULL NULL NULL d NULL NULL NULL
> +Warnings:
> +Warning 1292 Truncated incorrect DOUBLE value: 'm'
> +Warning 1292 Truncated incorrect DOUBLE value: 'm'
> +Warning 1292 Truncated incorrect DOUBLE value: 'k'
> +Warning 1292 Truncated incorrect DOUBLE value: 'r'
> +Warning 1292 Truncated incorrect DOUBLE value: 't'
> +Warning 1292 Truncated incorrect DOUBLE value: 'j'
> +Warning 1292 Truncated incorrect DOUBLE value: 'u'
> +Warning 1292 Truncated incorrect DOUBLE value: 'h'
> +Warning 1292 Truncated incorrect DOUBLE value: 'o'
> +Warning 1292 Truncated incorrect DOUBLE value: 'k'
> +Warning 1292 Truncated incorrect DOUBLE value: 'e'
> +Warning 1292 Truncated incorrect DOUBLE value: 'n'
> +Warning 1292 Truncated incorrect DOUBLE value: 't'
> +Warning 1292 Truncated incorrect DOUBLE value: 'c'
> +Warning 1292 Truncated incorrect DOUBLE value: 'm'
> +Warning 1292 Truncated incorrect DOUBLE value: 'y'
> +Warning 1292 Truncated incorrect DOUBLE value: 'f'
> +Warning 1292 Truncated incorrect DOUBLE value: 'd'
> +Warning 1292 Truncated incorrect DOUBLE value: 'r'
> +Warning 1292 Truncated incorrect DOUBLE value: 'm'
> +Warning 1292 Truncated incorrect DOUBLE value: 'm'
> +Warning 1292 Truncated incorrect DOUBLE value: 'k'
> +Warning 1292 Truncated incorrect DOUBLE value: 'r'
> +Warning 1292 Truncated incorrect DOUBLE value: 't'
> +Warning 1292 Truncated incorrect DOUBLE value: 'j'
> +Warning 1292 Truncated incorrect DOUBLE value: 'u'
> +Warning 1292 Truncated incorrect DOUBLE value: 'h'
> +Warning 1292 Truncated incorrect DOUBLE value: 'o'
> +Warning 1292 Truncated incorrect DOUBLE value: 'k'
> +Warning 1292 Truncated incorrect DOUBLE value: 'e'
> +Warning 1292 Truncated incorrect DOUBLE value: 'n'
> +Warning 1292 Truncated incorrect DOUBLE value: 't'
> +Warning 1292 Truncated incorrect DOUBLE value: 'c'
> +Warning 1292 Truncated incorrect DOUBLE value: 'm'
> +Warning 1292 Truncated incorrect DOUBLE value: 'y'
> +Warning 1292 Truncated incorrect DOUBLE value: 'f'
> +Warning 1292 Truncated incorrect DOUBLE value: 'd'
> +Warning 1292 Truncated incorrect DOUBLE value: 'r'
> +Warning 1292 Truncated incorrect DOUBLE value: 'm'
> +Warning 1292 Truncated incorrect DOUBLE value: 'm'
> +Warning 1292 Truncated incorrect DOUBLE value: 'k'
> +Warning 1292 Truncated incorrect DOUBLE value: 'r'
> +Warning 1292 Truncated incorrect DOUBLE value: 't'
> +Warning 1292 Truncated incorrect DOUBLE value: 'j'
> +Warning 1292 Truncated incorrect DOUBLE value: 'u'
> +Warning 1292 Truncated incorrect DOUBLE value: 'h'
> +Warning 1292 Truncated incorrect DOUBLE value: 'o'
> +Warning 1292 Truncated incorrect DOUBLE value: 'k'
> +Warning 1292 Truncated incorrect DOUBLE value: 'e'
> +Warning 1292 Truncated incorrect DOUBLE value: 'n'
> +Warning 1292 Truncated incorrect DOUBLE value: 't'
> +Warning 1292 Truncated incorrect DOUBLE value: 'c'
> +Warning 1292 Truncated incorrect DOUBLE value: 'm'
> +Warning 1292 Truncated incorrect DOUBLE value: 'y'
> +Warning 1292 Truncated incorrect DOUBLE value: 'f'
> +Warning 1292 Truncated incorrect DOUBLE value: 'd'
> +Warning 1292 Truncated incorrect DOUBLE value: 'r'
> +Warning 1292 Truncated incorrect DOUBLE value: 'm'
> +Warning 1292 Truncated incorrect DOUBLE value: 'm'
> +Warning 1292 Truncated incorrect DOUBLE value: 'k'
> +Warning 1292 Truncated incorrect DOUBLE value: 'r'
> +Warning 1292 Truncated incorrect DOUBLE value: 't'
> +Warning 1292 Truncated incorrect DOUBLE value: 'j'
> +Warning 1292 Truncated incorrect DOUBLE value: 'u'
> +DROP TABLE CC;
> +DROP TABLE C;
> +DROP TABLE D;
> +DROP TABLE BB;
>
> === modified file 'mysql-test/t/subselect4.test'
> --- a/mysql-test/t/subselect4.test 2009-09-03 15:03:46 +0000
> +++ b/mysql-test/t/subselect4.test 2009-09-28 09:26:01 +0000
> @@ -30,3 +30,161 @@ DROP TABLE t1,t2,t3;
>
>
> --echo End of 5.0 tests.
> +
> +--echo #
> +--echo # BUG#46743 "Azalea processing correlated, aggregate SELECT
> +--echo # subqueries incorrectly"
> +--echo #
> +
> +# First a minimal test case
> +
> +CREATE TABLE C (c int);
> +INSERT INTO C VALUES (NULL);
> +CREATE TABLE D (d int , KEY (d)); # index is needed for bug
> +INSERT INTO D VALUES (NULL),(NULL); # two rows needed for bug
> +# we see that subquery returns 0 rows
> +--echo 0 rows in subquery
> +SELECT 1 AS RESULT FROM D,C WHERE d = c;
> +# so here it ends up as NULL
> +--echo base query
> +SELECT (SELECT 1 FROM D WHERE d = c) AS RESULT FROM C ;
> +EXPLAIN EXTENDED SELECT (SELECT 1 FROM D WHERE d = c) AS RESULT FROM C ;
> +--echo first equivalent variant
> +SELECT (SELECT 1 FROM D WHERE d = IFNULL(c,NULL)) AS RESULT FROM C ;
> +EXPLAIN EXTENDED SELECT (SELECT 1 FROM D WHERE d = IFNULL(c,NULL)) AS RESULT FROM C
> ;
> +--echo second equivalent variant
> +# used to fail with 1242: Subquery returns more than 1 row
> +SELECT (SELECT 1 FROM D WHERE d = c) AS RESULT FROM C GROUP BY c ;
> +EXPLAIN EXTENDED SELECT (SELECT 1 FROM D WHERE d = c) AS RESULT FROM C GROUP BY c ;
> +
> +DROP TABLE C,D;
> +
> +# Then the original test case where extra wrong rows show up differently
> +
> +CREATE TABLE `C` (
> + `int_nokey` int(11) DEFAULT NULL,
> + `int_key` int(11) DEFAULT NULL,
> + `varchar_nokey` varchar(1) DEFAULT NULL,
> + KEY `int_key` (`int_key`),
> + KEY `varchar_key` (`int_key`)
> +) DEFAULT CHARSET=latin1;
> +INSERT INTO `C` VALUES
> (7,9,'m'),(9,3,'m'),(7,9,'k'),(4,NULL,'r'),(2,9,'t'),(6,3,'j'),(8,8,'u'),(NULL,8,'h'),(5,53,'o'),(NULL,0,NULL),(6,5,'k'),(188,166,'e'),(2,3,'n'),(1,0,'t'),(1,1,'c'),(0,9,'m'),(9,5,'y'),(NULL,6,'f'),(4,2,'d'),(6,NULL,'r');
> +CREATE TABLE `D` (
> + `int_nokey` int(11) DEFAULT NULL,
> + `int_key` int(11) DEFAULT NULL,
> + `varchar_nokey` varchar(1) DEFAULT NULL,
> + KEY `int_key` (`int_key`),
> + KEY `varchar_key` (`int_key`)
> +) DEFAULT CHARSET=latin1;
> +INSERT INTO `D` VALUES
> (8,0,'c'),(6,0,'o'),(6,7,'c'),(3,8,'d'),(9,4,'v'),(2,6,'m'),(1,5,'j'),(8,NULL,'f'),(0,NULL,'n'),(9,8,'z'),(8,8,'h'),(NULL,8,'q'),(0,1,'w'),(5,1,'z'),(1,5,'j'),(1,2,'a'),(6,7,'m'),(6,6,'n'),(1,4,'e'),(8,7,'u'),(1,0,'s'),(0,9,'u'),(4,3,'r'),(9,5,'g'),(8,1,'o'),(5,1,'w'),(9,5,'b'),(5,9,NULL),(NULL,2,'y'),(NULL,5,'y'),(105,248,'u'),(0,0,'p'),(3,8,'s'),(1,1,'e'),(75,255,'d'),(9,9,'d'),(7,9,'c'),(NULL,3,'b'),(NULL,9,'t'),(4,6,NULL),(0,4,'y'),(204,60,'c'),(0,7,'d'),(9,1,'x'),(8,6,'p'),(7,4,'e'),(8,NULL,'g'),(NULL,8,'x'),(6,0,'s'),(5,8,'e'),(2,151,'l'),(3,7,'p'),(7,6,'h'),(NULL,NULL,'m'),(145,23,'n'),(0,2,'v'),(1,4,'b'),(7,NULL,'x'),(3,NULL,'r'),(NULL,77,'t'),(2,NULL,'w'),(2,NULL,'w'),(2,7,'k'),(8,1,'a'),(6,9,'t'),(1,6,'z'),(NULL,2,'e'),(1,3,'q'),(0,0,'e'),(4,NULL,'v'),(1,6,'d'),(1,3,'u'),(27,195,'o'),(4,5,'b'),(6,2,'c'),(2,7,'q'),(248,25,NULL),(NULL,NULL,'h'),(9,0,'d'),(75,98,'w'),(2,6,'m'),(9,5,'i'),(4,0,'w'),(0,3,'f'),(0,1,'k'),(1,1,'v'),(119,147,'c'),(1,
3,
>
> 'y'),(7,3,'h'),(2,NULL,NULL),(7,2,'t'),(2,1,'l'),(6,8,'a'),(4,8,'r'),(5,8,'s'),(7,0,'z'),(1,1,'j'),(7,8,'c'),(2,5,'f'),(1,4,'g');
> +
> +SELECT table2 .`int_key` field12 , (
> +SELECT SUM( `int_nokey` )
> +FROM D
> +WHERE `int_key` = table1 .`int_key` )
> +FROM C table1 RIGHT JOIN C table2 ON table1 .`varchar_nokey` ;
> +
> +SELECT table2 .`int_key` field12 , (
> +SELECT SUM( `int_nokey` )
> +FROM D
> +WHERE `int_key` = table1 .`int_key` )
> +FROM C table1 RIGHT JOIN C table2 ON table1 .`varchar_nokey`
> +GROUP BY field12 ;
> +
> +DROP TABLE C;
> +DROP TABLE D;
> +
> +CREATE TABLE `CC` (
> + `pk` int(11) NOT NULL AUTO_INCREMENT,
> + `int_nokey` int(11) DEFAULT NULL,
> + `int_key` int(11) DEFAULT NULL,
> + `date_key` date DEFAULT NULL,
> + `time_key` time DEFAULT NULL,
> + `datetime_key` datetime DEFAULT NULL,
> + `varchar_key` varchar(1) DEFAULT NULL,
> + `varchar_nokey` varchar(1) DEFAULT NULL,
> + PRIMARY KEY (`pk`),
> + KEY `int_key` (`int_key`),
> + KEY `date_key` (`date_key`),
> + KEY `time_key` (`time_key`),
> + KEY `datetime_key` (`datetime_key`),
> + KEY `varchar_key` (`varchar_key`,`int_key`)
> +) AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
> +INSERT INTO `CC` VALUES (10,3,186,'2005-02-15','19:53:05','2008-05-26
> 12:27:10','m','m'),(11,6,NULL,NULL,'19:18:56','2004-12-14
> 16:37:30','y','y'),(12,92,2,'2008-11-04','10:55:12','2003-02-11
> 21:19:41','j','j'),(13,7,3,'2004-09-04','00:25:00','2009-10-18
> 02:27:49','d','d'),(14,NULL,0,'2006-06-05','12:35:47','2000-09-26
> 07:45:57','z','z'),(15,3,133,'1900-01-01','19:53:03',NULL,'e','e'),(16,5,1,'1900-01-01','17:53:30','2005-11-10
> 12:40:29','h','h'),(17,1,8,'1900-01-01','11:35:49','2009-04-25
> 00:00:00','b','b'),(18,2,5,'2005-01-13',NULL,'2002-11-27
> 00:00:00','s','s'),(19,NULL,5,'2006-05-21','06:01:40','2004-01-26
> 20:32:32','e','e'),(20,1,8,'2003-09-08','05:45:11','2007-10-26
> 11:41:40','j','j'),(21,0,6,'2006-12-23','00:00:00','2005-10-07
> 00:00:00','e','e'),(22,210,51,'2006-10-15','00:00:00','2000-07-15
> 05:00:34','f','f'),(23,8,4,'2005-04-06','06:11:01','2000-04-03
> 16:33:32','v','v'),(24,7,7,'2008-04-07','13:02:46',NULL,'x','x'),(25,5,6,'2006-10-10','21:44:25','2001-04-25
> 01:26
:1
> 2','m','m'),(26,NULL,4,'1900-01-01','22:43:58','2000-12-27
> 00:00:00','c','c'),(27,6,7,'1900-01-01',NULL,'2005-10-04
> 00:00:00','m','m'),(28,9,3,'2005-05-07','06:43:09','2004-03-08
> 02:50:35','b','b'),(29,3,2,'2000-07-19','12:20:22','2004-09-24 09:21:35','x','x');
> +CREATE TABLE `C` (
> + `pk` int(11) NOT NULL AUTO_INCREMENT,
> + `int_nokey` int(11) DEFAULT NULL,
> + `int_key` int(11) DEFAULT NULL,
> + `date_key` date DEFAULT NULL,
> + `time_key` time DEFAULT NULL,
> + `datetime_key` datetime DEFAULT NULL,
> + `varchar_key` varchar(1) DEFAULT NULL,
> + `varchar_nokey` varchar(1) DEFAULT NULL,
> + PRIMARY KEY (`pk`),
> + KEY `int_key` (`int_key`),
> + KEY `date_key` (`date_key`),
> + KEY `time_key` (`time_key`),
> + KEY `datetime_key` (`datetime_key`),
> + KEY `varchar_key` (`varchar_key`,`int_key`)
> +) AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
> +INSERT INTO `C` VALUES
> (1,7,9,'2001-09-19','20:25:14',NULL,'m','m'),(2,9,3,'2004-09-12','13:47:24','1900-01-01
> 00:00:00','m','m'),(3,7,9,NULL,'19:24:11','2009-07-25
> 00:00:00','k','k'),(4,4,NULL,'2002-07-19','15:59:13',NULL,'r','r'),(5,2,9,'2002-12-16','00:00:00','2008-07-27
> 00:00:00','t','t'),(6,6,3,'2006-02-08','15:15:04','2002-11-13
> 16:37:31','j','j'),(7,8,8,'2006-08-28','11:32:06','1900-01-01
> 00:00:00','u','u'),(8,NULL,8,'2001-04-14','18:32:33','2003-12-10
> 00:00:00','h','h'),(9,5,53,'2000-01-05','15:19:25','2001-12-21
> 22:38:22','o','o'),(10,NULL,0,'2003-12-06','19:03:19','2008-12-13
> 23:16:44',NULL,NULL),(11,6,5,'1900-01-01','00:39:46','2005-08-15
> 12:39:41','k','k'),(12,188,166,'2002-11-27',NULL,NULL,'e','e'),(13,2,3,NULL,'00:00:00','2006-09-11
> 12:06:14','n','n'),(14,1,0,'2003-05-27','13:12:11','2007-12-15
> 12:39:34','t','t'),(15,1,1,'2005-05-03','04:56:48','2005-08-09
> 00:00:00','c','c'),(16,0,9,'2001-04-18','19:56:05','2001-09-02
> 22:50:02','m','m'),(17,9,5,'2005-12-27','
19
> :35:19','2005-12-16
> 22:58:11','y','y'),(18,NULL,6,'2004-08-20','05:03:03','2007-04-19
> 00:19:53','f','f'),(19,4,2,'1900-01-01','18:38:59','1900-01-01
> 00:00:00','d','d'),(20,6,NULL,'2003-05-12',NULL,'2000-09-12 00:00:00','r','r');
> +CREATE TABLE `D` (
> + `pk` int(11) NOT NULL AUTO_INCREMENT,
> + `int_nokey` int(11) DEFAULT NULL,
> + `int_key` int(11) DEFAULT NULL,
> + `date_key` date DEFAULT NULL,
> + `time_key` time DEFAULT NULL,
> + `datetime_key` datetime DEFAULT NULL,
> + `varchar_key` varchar(1) DEFAULT NULL,
> + `varchar_nokey` varchar(1) DEFAULT NULL,
> + PRIMARY KEY (`pk`),
> + KEY `int_key` (`int_key`),
> + KEY `date_key` (`date_key`),
> + KEY `time_key` (`time_key`),
> + KEY `datetime_key` (`datetime_key`),
> + KEY `varchar_key` (`varchar_key`,`int_key`)
> +) AUTO_INCREMENT=101 DEFAULT CHARSET=latin1;
> +INSERT INTO `D` VALUES (1,8,0,'2003-01-07','14:34:45','2004-08-10
> 09:09:31','c','c'),(2,6,0,NULL,'11:49:48','2005-03-21
> 04:31:40','o','o'),(3,6,7,'2005-03-12','18:12:55','2002-10-25
> 23:50:35','c','c'),(4,3,8,'2000-08-02','18:30:05','2001-04-01
> 21:14:04','d','d'),(5,9,4,'1900-01-01','14:19:30','2005-03-12
> 06:02:34','v','v'),(6,2,6,'2006-07-06','05:20:04','2001-05-06
> 14:49:12','m','m'),(7,1,5,'2006-12-24','20:29:31','2004-04-25
> 00:00:00','j','j'),(8,8,NULL,'2004-11-16','07:08:09','2001-03-22
> 18:38:43','f','f'),(9,0,NULL,'2002-09-09','14:49:14','2006-04-25
> 21:03:02','n','n'),(10,9,8,NULL,'00:00:00','2009-09-07
> 18:40:43','z','z'),(11,8,8,'2008-06-24','09:58:06','2004-03-23
> 00:00:00','h','h'),(12,NULL,8,'2001-04-21',NULL,'2009-04-15
> 00:08:29','q','q'),(13,0,1,'2003-11-22','18:24:16','2000-04-21
> 00:00:00','w','w'),(14,5,1,'2004-09-12','17:39:57','2000-02-17
> 19:41:23','z','z'),(15,1,5,'2006-06-20','08:23:21','2003-09-20
> 07:38:14','j','j'),(16,1,2,NULL,NULL,'2000-11-28 20:42:12','
a'
> ,'a'),(17,6,7,'2001-11-25','21:50:46','2005-06-12
> 11:13:17','m','m'),(18,6,6,'2004-10-26','12:33:17','1900-01-01
> 00:00:00','n','n'),(19,1,4,'2005-01-19','03:06:43','2006-02-09
> 20:41:06','e','e'),(20,8,7,'2008-07-06','03:46:14','2004-05-22
> 01:05:57','u','u'),(21,1,0,'1900-01-01','20:34:52','2004-03-04
> 13:46:31','s','s'),(22,0,9,'1900-01-01',NULL,'1900-01-01
> 00:00:00','u','u'),(23,4,3,'2004-06-08','10:41:20','2004-10-20
> 07:20:19','r','r'),(24,9,5,'2007-02-20','08:43:11','2006-04-17
> 00:00:00','g','g'),(25,8,1,'2008-06-18',NULL,'2000-10-27
> 00:00:00','o','o'),(26,5,1,'2008-05-15','10:17:51','2007-04-14
> 08:54:06','w','w'),(27,9,5,'2005-10-06','06:34:09','2008-04-12
> 17:03:52','b','b'),(28,5,9,NULL,'21:22:47','2007-02-19
> 17:37:09',NULL,NULL),(29,NULL,2,'2006-10-12','04:02:32','1900-01-01
> 00:00:00','y','y'),(30,NULL,5,'2005-01-24','02:33:14','2001-10-10
> 08:32:27','y','y'),(31,105,248,'2009-06-27','16:32:56',NULL,'u','u'),(32,0,0,NULL,'21:32:42','2001-12-16
> 05:31:53','p','p'),(33,3,
8,
> NULL,'23:04:47','2003-07-19
> 18:03:28','s','s'),(34,1,1,'1900-01-01','22:05:43','2001-03-27
> 11:44:10','e','e'),(35,75,255,'2005-12-22','02:05:45','2008-06-15
> 02:13:00','d','d'),(36,9,9,'2005-05-03','00:00:00','2009-03-14
> 21:29:56','d','d'),(37,7,9,'2003-05-27','18:09:07','2005-01-02
> 00:00:00','c','c'),(38,NULL,3,'2006-05-25','10:54:06','2007-07-16
> 04:44:07','b','b'),(39,NULL,9,NULL,'23:15:50','2003-08-26
> 21:38:26','t','t'),(40,4,6,'2009-01-04','10:17:40','2004-04-19
> 04:18:47',NULL,NULL),(41,0,4,'2009-02-14','03:37:09','2000-01-06
> 20:32:48','y','y'),(42,204,60,'2003-01-16','22:26:06','2006-06-23
> 13:27:17','c','c'),(43,0,7,'1900-01-01','17:10:38','2007-11-27
> 00:00:00','d','d'),(44,9,1,'2007-06-26','00:00:00','2002-04-03
> 12:06:51','x','x'),(45,8,6,'2004-03-27','17:08:49','2008-12-28
> 09:47:42','p','p'),(46,7,4,NULL,'19:04:40','2002-04-04
> 10:07:54','e','e'),(47,8,NULL,'2005-06-06','20:53:28','2003-04-26
> 02:55:13','g','g'),(48,NULL,8,'2003-03-02','11:46:03',NULL,'x','x'),(49,6,0,
'2
> 004-05-13',NULL,'2009-02-19
> 03:17:06','s','s'),(50,5,8,'2005-09-13','10:58:07','1900-01-01
> 00:00:00','e','e'),(51,2,151,'2005-10-03','00:00:00','2000-11-10
> 08:20:01','l','l'),(52,3,7,'2005-10-14','09:43:15','2008-02-10
> 00:00:00','p','p'),(53,7,6,NULL,'21:40:32','1900-01-01
> 00:00:00','h','h'),(54,NULL,NULL,'2005-09-16','00:17:44',NULL,'m','m'),(55,145,23,'2005-03-10','16:47:26','2001-02-05
> 02:01:50','n','n'),(56,0,2,'2000-06-19','00:00:00','2000-10-28
> 08:44:25','v','v'),(57,1,4,'2002-11-03','05:25:59','2005-03-20
> 10:53:59','b','b'),(58,7,NULL,'2009-01-05','00:00:00','2001-06-02
> 13:54:13','x','x'),(59,3,NULL,'2003-05-22','20:33:04','1900-01-01
> 00:00:00','r','r'),(60,NULL,77,'2005-07-02','00:46:12','2009-07-16
> 13:05:43','t','t'),(61,2,NULL,'1900-01-01','00:00:00','2009-03-26
> 23:16:20','w','w'),(62,2,NULL,'2006-06-21','02:13:59','2003-02-06
> 18:12:15','w','w'),(63,2,7,NULL,'02:54:47','2006-06-05
> 03:22:51','k','k'),(64,8,1,'2005-12-16','18:13:59','2002-02-10 05:47:27','a','a'),(
65
> ,6,9,'2004-11-05','13:53:08','2001-08-01
> 08:50:52','t','t'),(66,1,6,NULL,'22:21:30','1900-01-01
> 00:00:00','z','z'),(67,NULL,2,'2004-09-14','11:41:50',NULL,'e','e'),(68,1,3,'2002-04-06','15:20:02','1900-01-01
> 00:00:00','q','q'),(69,0,0,NULL,NULL,'2000-09-23
> 00:00:00','e','e'),(70,4,NULL,'2002-11-13',NULL,'2007-07-09
> 08:32:49','v','v'),(71,1,6,'2006-05-27','07:51:52','2000-01-05
> 00:00:00','d','d'),(72,1,3,'2000-12-22','00:00:00','2000-09-24
> 00:00:00','u','u'),(73,27,195,'2004-02-21',NULL,'2005-05-06
> 00:00:00','o','o'),(74,4,5,'2009-05-15',NULL,'2000-03-11
> 00:00:00','b','b'),(75,6,2,'2008-12-12','12:31:05','2001-09-02
> 16:17:35','c','c'),(76,2,7,'2000-04-15','00:00:00','2006-04-25
> 05:43:44','q','q'),(77,248,25,NULL,'01:16:45','2009-10-25
> 22:04:02',NULL,NULL),(78,NULL,NULL,'2001-10-18','20:38:54','2004-08-06
> 00:00:00','h','h'),(79,9,0,'2008-05-25','00:30:15','2001-11-27
> 05:07:57','d','d'),(80,75,98,'2004-12-02','23:46:36','2009-06-28
> 03:18:39','w','w'),(81,2,6,'2002-02-15','19:
03
> :13','2000-03-12
> 00:00:00','m','m'),(82,9,5,'2002-03-03','10:54:27',NULL,'i','i'),(83,4,0,NULL,'00:25:47','2007-10-20
> 00:00:00','w','w'),(84,0,3,'2003-01-26','08:44:27','2009-09-27
> 00:00:00','f','f'),(85,0,1,'2001-12-19','08:15:38','2002-07-16
> 00:00:00','k','k'),(86,1,1,'2001-08-07','19:56:21','2005-02-20
> 00:00:00','v','v'),(87,119,147,'2005-02-16','00:00:00',NULL,'c','c'),(88,1,3,'2006-06-10','20:50:52','2001-07-16
> 00:00:00','y','y'),(89,7,3,NULL,'03:54:39','2009-05-20
> 21:04:12','h','h'),(90,2,NULL,'2005-04-06','23:58:17','2002-03-13
> 10:55:40',NULL,NULL),(91,7,2,'2003-04-27','12:54:58','2005-07-12
> 00:00:00','t','t'),(92,2,1,'2005-10-13','04:02:43','2006-07-22
> 09:46:34','l','l'),(93,6,8,'2003-10-02','11:31:12','2001-09-01
> 00:00:00','a','a'),(94,4,8,'2005-09-09','20:20:04','2002-05-27
> 18:38:45','r','r'),(95,5,8,NULL,'00:22:24',NULL,'s','s'),(96,7,0,'2006-02-15','10:09:31',NULL,'z','z'),(97,1,1,'1900-01-01',NULL,'2009-08-08
> 22:38:53','j','j'),(98,7,8,'2003-12-24','18:45:35',
NU
> LL,'c','c'),(99,2,5,'2001-07-26','11:49:25','2007-04-25
> 05:08:49','f','f'),(100,1,4,'2001-05-13','12:52:58','2006-12-15 00:00:00','g','g');
> +CREATE TABLE `BB` (
> + `pk` int(11) NOT NULL AUTO_INCREMENT,
> + `int_nokey` int(11) DEFAULT NULL,
> + `int_key` int(11) DEFAULT NULL,
> + `date_key` date DEFAULT NULL,
> + `time_key` time DEFAULT NULL,
> + `datetime_key` datetime DEFAULT NULL,
> + `varchar_key` varchar(1) DEFAULT NULL,
> + `varchar_nokey` varchar(1) DEFAULT NULL,
> + PRIMARY KEY (`pk`),
> + KEY `int_key` (`int_key`),
> + KEY `date_key` (`date_key`),
> + KEY `time_key` (`time_key`),
> + KEY `datetime_key` (`datetime_key`),
> + KEY `varchar_key` (`varchar_key`,`int_key`)
> +) AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;
> +INSERT INTO `BB` VALUES (10,1,9,'2006-06-14','19:48:31','1900-01-01
> 00:00:00','r','r'),(11,5,9,'2002-09-12','00:00:00','2006-12-03 09:37:26','a','a');
> +
> +
> +
> +SELECT DISTINCT SQL_SMALL_RESULT (
> +SELECT MIN( SUBQUERY1_t1 . `pk` ) AS SUBQUERY1_field1
> +FROM ( BB AS SUBQUERY1_t1 )
> +WHERE SUBQUERY1_t1 . `varchar_key` < table1 . `varchar_nokey` AND SUBQUERY1_t1 .
> `varchar_key` != SUBQUERY1_t1 . `varchar_nokey` ) AS field1 , table2 . `date_key` AS
> field2 , table1 . `int_key` AS field3 , table1 . `pk` AS field4 , CONCAT ( table2 .
> `varchar_nokey` , table1 . `varchar_key` ) AS field5 , table2 . `int_key` AS field6 ,
> CONCAT ( table2 . `varchar_nokey` , table2 . `varchar_key` ) AS field7 , table1 .
> `datetime_key` AS field8 , table1 . `time_key` AS field9 , table1 . `date_key` AS field10
> , table2 . `varchar_nokey` AS field11 , ( ( table1 . `int_nokey` ) * ( table2 . `int_key`
> ) ) AS field12 , table1 . `time_key` AS field13 , (
> +SELECT SUM( SUBQUERY2_t1 . `int_nokey` ) AS SUBQUERY2_field1
> +FROM ( D AS SUBQUERY2_t1 LEFT JOIN CC AS SUBQUERY2_t2 ON (SUBQUERY2_t2 .
> `varchar_key` = SUBQUERY2_t1 . `varchar_key` ) )
> +WHERE SUBQUERY2_t1 . `int_key` = table1 . `int_key` AND SUBQUERY2_t2 . `pk` <=
> SUBQUERY2_t2 . `pk` ) AS field14
> +FROM ( C AS table1 RIGHT OUTER JOIN C AS table2 ON (table2 . `pk` = table1 .
> `varchar_nokey` ) )
> +WHERE ( table2 . `varchar_key` < ALL (
> +SELECT SUBQUERY3_t1 . `varchar_nokey` AS SUBQUERY3_field1
> +FROM ( BB AS SUBQUERY3_t1 )
> +WHERE SUBQUERY3_t1 . `int_nokey` >= table2 . `int_nokey` AND SUBQUERY3_t1 .
> `varchar_nokey` > table1 . `varchar_key` ) ) AND table1 . `varchar_key` = 'g' OR ( (
> 'm', 'k' ) IN (
> +SELECT SUBQUERY4_t2 . `varchar_nokey` AS SUBQUERY4_field1 , SUBQUERY4_t2 .
> `varchar_key` AS SUBQUERY4_field2
> +FROM ( CC AS SUBQUERY4_t1 RIGHT OUTER JOIN BB AS SUBQUERY4_t2 ON (SUBQUERY4_t2 .
> `int_nokey` = SUBQUERY4_t1 . `pk` ) )
> +WHERE SUBQUERY4_t1 . `varchar_key` <> SUBQUERY4_t1 . `varchar_nokey` ) OR
> table1 . `pk` IS NULL )
> +GROUP BY field1, field2, field3, field4, field5, field6, field7, field8, field9,
> field10, field11, field12, field13, field14
> +ORDER BY CONCAT ( table2 . `varchar_key`, table2 . `varchar_key` ) , table1 .
> `varchar_key` ASC , table1 . `pk` , table2 .`pk` ASC , field14 ASC , field13 ASC, field1,
> field2, field3, field4, field5, field6, field7, field8, field9, field10, field11, field12,
> field13, field14
> +LIMIT 2 OFFSET 1;
> +
> +DROP TABLE CC;
> +DROP TABLE C;
> +DROP TABLE D;
> +DROP TABLE BB;
>
--
Jørgen Løland
