| Bug #48295 | explain extended crash with subquery and ONLY_FULL_GROUP_BY sql_mode | ||
|---|---|---|---|
| Submitted: | 25 Oct 2009 21:10 | Modified: | 18 Jun 2010 12:57 |
| Reporter: | Shane Bester (Platinum Quality Contributor) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S1 (Critical) |
| Version: | 5.0.86,5.1.40 | OS: | Any |
| Assigned to: | Tatiana Azundris Nuernberg | CPU Architecture: | Any |
| Tags: | ONLY_FULL_GROUP_BY | ||
[25 Oct 2009 22:01]
Peter Laursen
without EXTENDED:
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1`(`a` INT);
SET SESSION sql_mode='ONLY_FULL_GROUP_BY';
EXPLAIN SELECT 1 FROM `t1` WHERE `a` > ALL
(
SELECT `t`.`a`
FROM `t1`,`t1` AS `t`
);
/*
Error Code : 1140
Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
*/
[25 Oct 2009 22:11]
MySQL Verification Team
Thank you for the bug report.
c:\dbs>c:\dbs\5.1\bin\mysqld --defaults-file=c:\dbs\5.1\my.ini --standalone --console
091025 20:09:12 [Note] Plugin 'FEDERATED' is disabled.
091025 20:09:13 InnoDB: Started; log sequence number 0 44233
091025 20:09:13 [Note] Event Scheduler: Loaded 0 events
091025 20:09:13 [Note] c:\dbs\5.1\bin\mysqld: ready for connections.
Version: '5.1.41-Win X64-debug' socket: '' port: 3510 Source distribution
091025 20:09:40 - mysqld got exception 0xc0000005 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.
key_buffer_size=8384512
read_buffer_size=131072
max_used_connections=1
max_threads=151
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 338112 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
thd: 0x3a0bab8
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
0000000140312C71 mysqld.exe!Item_subselect::print()[item_subselect.cc:315]
000000014031888A mysqld.exe!Item_allany_subselect::print()[item_subselect.cc:1652]
0000000140144A33 mysqld.exe!Item_func::print_args()[item_func.cc:393]
0000000140144970 mysqld.exe!Item_func::print()[item_func.cc:383]
000000014009CF21 mysqld.exe!Item_func_not_all::print()[item_cmpfunc.cc:337]
00000001402D931C mysqld.exe!st_select_lex::print()[sql_select.cc:16687]
0000000140140091 mysqld.exe!st_select_lex_unit::print()[sql_lex.cc:2014]
000000014020C447 mysqld.exe!execute_sqlcom_select()[sql_parse.cc:5028]
00000001402026F5 mysqld.exe!mysql_execute_command()[sql_parse.cc:2238]
000000014020F34D mysqld.exe!mysql_parse()[sql_parse.cc:5967]
00000001401FFCCC mysqld.exe!dispatch_command()[sql_parse.cc:1226]
00000001401FEFDA mysqld.exe!do_command()[sql_parse.cc:865]
00000001400C48B5 mysqld.exe!handle_one_connection()[sql_connect.cc:1127]
00000001405FCEB5 mysqld.exe!pthread_start()[my_winthread.c:85]
00000001405D30F5 mysqld.exe!_callthreadstart()[thread.c:295]
00000001405D30C7 mysqld.exe!_threadstart()[thread.c:277]
00000000770EC3BD kernel32.dll!BaseThreadInitThunk()
0000000077714581 ntdll.dll!RtlUserThreadStart()
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 00000000042A5398=explain extended
select 1 from `t1` where `a` > all
(
select `t`.`a`
from `t1`,`t1` as `t`
)
thd->thread_id=1
thd->killed=NOT_KILLED
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
c:\dbs>
[27 Oct 2009 18:39]
Tatiana Azundris Nuernberg
can repeat on 5.1.41/OSX
[27 Oct 2009 18:54]
Tatiana Azundris Nuernberg
--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings
CREATE TABLE t1 (f1 INT);
SET SESSION sql_mode='ONLY_FULL_GROUP_BY';
--error ER_MIX_OF_GROUP_FUNC_AND_FIELDS
EXPLAIN SELECT 1 FROM t1
WHERE f1 > ALL( SELECT t.f1 FROM t1,t1 AS t );
--error ER_MIX_OF_GROUP_FUNC_AND_FIELDS
EXPLAIN EXTENDED SELECT 1 FROM t1
WHERE f1 > ALL( SELECT t.f1 FROM t1,t1 AS t );
SET SESSION sql_mode='';
EXPLAIN SELECT 1 FROM t1
WHERE f1 > ALL( SELECT t.f1 FROM t1,t1 AS t );
EXPLAIN EXTENDED SELECT 1 FROM t1
WHERE f1 > ALL( SELECT t.f1 FROM t1,t1 AS t );
DROP TABLE t1;
[27 Oct 2009 19:29]
Tatiana Azundris Nuernberg
sql_parse.cc::execute_sqlcom_select()
res= mysql_explain_union(thd, &thd->lex->unit, result);
if (lex->describe & DESCRIBE_EXTENDED)
{
char buff[1024];
String str(buff,(uint32) sizeof(buff), system_charset_info);
str.length(0);
thd->lex->unit.print(&str, QT_ORDINARY);
str.append('\0');
push_warning(thd, MYSQL_ERROR::WARN_LEVEL_NOTE,
ER_YES, str.ptr());
}
(gdb) print thd->main_da
$2 = {
is_sent = false,
can_overwrite_status = false,
m_message = "Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause\000", 'Z' <repeats 398 times>,
m_sql_errno = 1140,
m_server_status = 2,
m_affected_rows = 0,
m_last_insert_id = 0,
m_total_warn_count = 0,
m_status = Diagnostics_area::DA_ERROR
}
[27 Oct 2009 19:37]
Tatiana Azundris Nuernberg
Of
rpl_get_lock rpl_master_pos_wait auto_increment bench_count_distinct case cast compare ctype_collate date_formats explain fulltext func_compress func_crypt func_default func_encrypt func_gconcat func_group func_if func_in func_like func_math func_op func_regexp func_set func_str func_system func_test func_time gis group_by group_min_max having information_schema insert_update join_nested negation_elimination null olap partition_pruning query_cache row select subselect subselect3 type_blob type_datetime udf union varbinary variables view # blackhole katz
only subselect3 expects us to go on after failing the above clause ("res").
[29 Oct 2009 22:40]
Tatiana Azundris Nuernberg
REVIEWERS Problem: In EXPLAIN EXTENDED with a subquery, we try to have the latter print itself using engine->print(). If we detected at an earlier stage that the query isn't even valid, we might not ever have set up an engine. This is what happens here; the query is not valid due to the ONLY_FULL_GROUP_BY constraint, so engine is never set up, yet we later try to call engine->print(), which gives us a nice little NULL deref. Solution/Rationale: Three solutions come to mind. 1) What: If we detect an error in the query, don't even try print() anything (i.e., never get to EXTENDED if we already failed on regular). Pro: Nice and clean. Con: This breaks existing behaviour. There is actually one test-case -- yes, just the one (last 5.0 test in subselect3, if you have to know :)) -- that actually prints EXTENDED Info on an invalid query, so arguably, we should a) not break that and b) do the same thing for our GROUP problem. 2) What: Only print() EXTENDED Info if no GROUP ... error has occurred. Pro: Preserves existing behaviour (i.e. subselect3.test passes), doesn't crash on our GROUP ... problem. Doesn't print Info for GROUP ... that may be confusing to the user. Does not go to lengths to print Info on a query that's broken, anyway. Con: Different behaviour for different errors (some have Info, others don't). We'd be supplying the user with less debug info than we could be. Putting in a little effort to generate that Info line won't hurt; this isn't a query that will get executed a million times. 3) What: Print Info, but elide the sub-query part which we don't have, but which isn't relevant anyway. Pro: Gives the user a little extra info (making it clearer, perhaps, which query the problem is in), at no effort for us. Con: "Elide" is kinda daft, we're putting an ellipsis, is there "ellipsize"? 4) What: Print Info with complete query. Pro: Complete query. Con: Provides additional info that doesn't add anything, but makes it less obvious where the problem occurred. Might necessitate touching critical code path frivolously. Upshot: You've guessed it, I implemented #3. That seems a good compromise; it tries to furnish the user with possibly-useful Info, but not an excess of it, while only touching the non-critical EXPLAIN/print code path, not the actual execution path. I could MAYBE be persuaded to downgrade to #2, but I think it's the worse solution (but not as bad as #4). #1 is nice and clean, but can you justify needlessly breaking existing that some users may find beneficial?
[29 Oct 2009 23:02]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/88660 3158 Tatiana A. Nurnberg 2009-10-29 Bug#48295: explain extended crash with subquery and ONLY_FULL_GROUP_BY sql_mode If an outer query is broken, a subquery might not even get set up. EXPLAIN EXTENDED did not expect this and merrily tried to de-ref all of the half-setup info. We now catch this case and print as much as we have, as it doesn't cost us anything (doesn't make regular execution slower). @ mysql-test/r/explain.result Show that EXPLAIN EXTENDED with subquery and illegal out query doesn't crash. Show also that SHOW WARNINGS will render an additional Note in the hope of being, well, helpful. @ mysql-test/t/explain.test If we have only half a query for EXPLAIN EXTENDED to print (i.e., incomplete subquery info as outer query is illegal), we should provide the user with as much info as we easily can if they ask for it. What we should not do is crash when they come asking for help, that violates etiquette in some countries. @ sql/item_subselect.cc If the sub-query's actually set up, print it. Otherwise, elide.
[2 Nov 2009 8:19]
Tatiana Azundris Nuernberg
queued for 5.1.41, 6.0.14 in -bugteam
[2 Nov 2009 13:00]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/88929 3647 Tatiana A. Nurnberg 2009-11-02 Post merge fixies. Also makes 6.0 "explain extended" behaviour more like 5.1, id est, more verbose. If this blows up in unforeseen ways in the future, this is the CS to revert. We'll lose some minor debug info for the customers this way ("explain extended" even for invalid queries, partial info if that's all we can do), but maybe, that'll be necessary. Doesn't look it so far though. cf. Bug#48295
[4 Nov 2009 9:24]
Bugs System
Pushed into 5.1.41 (revid:joro@sun.com-20091104092152-qz96bzlf2o1japwc) (version source revid:kristofer.pettersson@sun.com-20091103162305-08l4gkeuif2ozsoj) (merge vers: 5.1.41) (pib:13)
[11 Nov 2009 6:49]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091110093407-rw5g8dys2baqkt67) (version source revid:alik@sun.com-20091109080109-7dxapd5y5pxlu08w) (merge vers: 6.0.14-alpha) (pib:13)
[11 Nov 2009 6:57]
Bugs System
Pushed into 5.5.0-beta (revid:alik@sun.com-20091109115615-nuohp02h8mdrz8m2) (version source revid:alik@sun.com-20091105105958-fmvjrkgi5x6r666g) (merge vers: 5.5.0-beta) (pib:13)
[18 Nov 2009 16:47]
Paul DuBois
Noted in 5.1.41, 5.5.0, 6.0.14 changelogs. If an outer query was invalid, a subquery might not even be set up. EXPLAIN EXTENDED did not expect this and caused a crash by trying to dereference improperly set up information.
[7 Dec 2009 16:46]
Paul DuBois
Noted in 5.1.40sp1 changelog.
[8 Dec 2009 9:30]
Bugs System
Pushed into 5.1.43 (revid:build@mysql.com-20091208092611-pbno5awyb0v38hs7) (version source revid:build@mysql.com-20091208092611-pbno5awyb0v38hs7) (merge vers: 5.1.43) (pib:13)
[16 Dec 2009 8:35]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091216083311-xorsasf5kopjxshf) (version source revid:alik@sun.com-20091215065750-5m04ogppd5l0pol5) (merge vers: 6.0.14-alpha) (pib:14)
[16 Dec 2009 8:42]
Bugs System
Pushed into 5.5.0-beta (revid:alik@sun.com-20091216082430-s0gtzibcgkv4pqul) (version source revid:alik@sun.com-20091211070127-kl8uvlrv9cr11kva) (merge vers: 5.5.0-beta) (pib:14)
[16 Dec 2009 8:48]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20091216083231-rp8ecpnvkkbhtb27) (version source revid:alik@sun.com-20091212203859-fx4rx5uab47wwuzd) (merge vers: 5.6.0-beta) (pib:14)
[18 Dec 2009 10:26]
Bugs System
Pushed into 5.1.41-ndb-7.1.0 (revid:jonas@mysql.com-20091218102229-64tk47xonu3dv6r6) (version source revid:jonas@mysql.com-20091218095730-26gwjidfsdw45dto) (merge vers: 5.1.41-ndb-7.1.0) (pib:15)
[18 Dec 2009 10:42]
Bugs System
Pushed into 5.1.41-ndb-6.2.19 (revid:jonas@mysql.com-20091218100224-vtzr0fahhsuhjsmt) (version source revid:jonas@mysql.com-20091217101452-qwzyaig50w74xmye) (merge vers: 5.1.41-ndb-6.2.19) (pib:15)
[18 Dec 2009 10:57]
Bugs System
Pushed into 5.1.41-ndb-6.3.31 (revid:jonas@mysql.com-20091218100616-75d9tek96o6ob6k0) (version source revid:jonas@mysql.com-20091217154335-290no45qdins5bwo) (merge vers: 5.1.41-ndb-6.3.31) (pib:15)
[18 Dec 2009 11:12]
Bugs System
Pushed into 5.1.41-ndb-7.0.11 (revid:jonas@mysql.com-20091218101303-ga32mrnr15jsa606) (version source revid:jonas@mysql.com-20091218064304-ezreonykd9f4kelk) (merge vers: 5.1.41-ndb-7.0.11) (pib:15)
[2 Mar 2010 14:20]
MySQL Verification Team
this still crashes 5.0.90 !
[9 Mar 2010 6:25]
Tatiana Azundris Nuernberg
backported in -bugteam for 5.0.91 (and NULL-merged upwards)
[12 Mar 2010 14:06]
Bugs System
Pushed into 5.1.44-ndb-7.0.14 (revid:jonas@mysql.com-20100312135944-t0z8s1da2orvl66x) (version source revid:jonas@mysql.com-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)
[12 Mar 2010 14:22]
Bugs System
Pushed into 5.1.44-ndb-6.2.19 (revid:jonas@mysql.com-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:jonas@mysql.com-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)
[12 Mar 2010 14:36]
Bugs System
Pushed into 5.1.44-ndb-6.3.33 (revid:jonas@mysql.com-20100312135724-xcw8vw2lu3mijrhn) (version source revid:jonas@mysql.com-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)
[12 Mar 2010 17:15]
Paul DuBois
Setting report to Need Merge pending push to 5.0.x release tree.
[26 Mar 2010 8:20]
Bugs System
Pushed into 5.5.4-m3 (revid:alik@sun.com-20100326080914-2pz8ns984e0spu03) (version source revid:alexey.kopytov@sun.com-20100307164059-cri8typa32cypq0l) (merge vers: 5.5.3-m2) (pib:16)
[26 Mar 2010 8:24]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100326081116-m3v4l34yhr43mtsv) (version source revid:alik@sun.com-20100325072612-4sds00ix8ajo1e84) (pib:16)
[26 Mar 2010 8:29]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100326081944-qja07qklw1p2w7jb) (version source revid:alik@sun.com-20100325073410-4t4i9gu2u1pge7xb) (merge vers: 6.0.14-alpha) (pib:16)
[26 Mar 2010 15:51]
Paul DuBois
Setting report to Need Merge pending push to 5.0.x release tree.
[6 Apr 2010 7:53]
Bugs System
Pushed into 5.0.91 (revid:joro@sun.com-20100406075152-flz4btqirl9hly31) (version source revid:azundris@mysql.com-20100304123929-ud9c6koyo5rsn0cc) (merge vers: 5.0.91) (pib:16)
[6 Apr 2010 7:56]
Bugs System
Pushed into 5.1.46 (revid:sergey.glukhov@sun.com-20100405111026-7kz1p8qlzglqgfmu) (version source revid:azundris@mysql.com-20100305164437-a7a6hgmahfq1c5r1) (merge vers: 5.1.45) (pib:16)
[6 Apr 2010 12:02]
Jon Stephens
Added 5.0.91 changelog entry. Closed.
[17 Jun 2010 11:45]
Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:23]
Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:martin.skold@mysql.com-20100609211156-tsac5qhw951miwtt) (merge vers: 5.1.46-ndb-6.2.19) (pib:16)
[17 Jun 2010 13:10]
Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
Description: 5.1.40 stack trace: mysqld-debug.exe!Item_subselect::print()[item_subselect.cc:315] mysqld-debug.exe!Item_allany_subselect::print()[item_subselect.cc:1652] mysqld-debug.exe!Item_func::print_args()[item_func.cc:393] mysqld-debug.exe!Item_func::print()[item_func.cc:383] mysqld-debug.exe!Item_func_not_all::print()[item_cmpfunc.cc:337] mysqld-debug.exe!st_select_lex::print()[sql_select.cc:16687] mysqld-debug.exe!st_select_lex_unit::print()[sql_lex.cc:2014] mysqld-debug.exe!execute_sqlcom_select()[sql_parse.cc:5028] mysqld-debug.exe!mysql_execute_command()[sql_parse.cc:2238] mysqld-debug.exe!mysql_parse()[sql_parse.cc:5967] mysqld-debug.exe!dispatch_command()[sql_parse.cc:1226] mysqld-debug.exe!do_command()[sql_parse.cc:865] mysqld-debug.exe!handle_one_connection()[sql_connect.cc:1127] mysqld-debug.exe!pthread_start()[my_winthread.c:85] mysqld-debug.exe!_callthreadstart()[thread.c:295] mysqld-debug.exe!_threadstart()[thread.c:277] kernel32.dll!BaseThreadStart() How to repeat: drop table if exists `t1`; create table `t1`(`a` int); set session sql_mode='ONLY_FULL_GROUP_BY'; explain extended select 1 from `t1` where `a` > all ( select `t`.`a` from `t1`,`t1` as `t` );