MySQL Bugs: #27795: patch for explain extended to show outer aggregates like outer fields

Bug #27795	patch for explain extended to show outer aggregates like outer fields
Submitted:	12 Apr 2007 22:26	Modified:	12 Apr 2007 23:12
Reporter:	Martin Friebe (Gold Quality Contributor) (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S4 (Feature request)
Version:	5.0.40, 5.1	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Contribution, explain extended, qc

Description:
explain extended on a query with subqueries can produce usefull notes such as:

Note	1276	Field or reference 'test.t1.b1' of SELECT #2 was resolved in SELECT #1

Since mysql aggregates some aggregate function in outer context too, it would be great to see this info to:

explain extended
 select a1,
   (select count(b1) from t2 limit 1)
 from t1 group by a1;

Warnings:
 Note	1276
 Field or reference 'test.t1.b1' of SELECT #2 was resolved in SELECT #1

 Note	1472
 Aggregate function 'count(b1)' of SELECT #2 will be aggregated in SELECT #1

OR, with the current text:

 Note	1276
 Field or reference 'count(b1)' of SELECT #2 was resolved in SELECT #1

How to repeat:
-

Suggested fix:
There are 2 patches, tests:

1) for mysql 5.0, as far as I understand no new messages can be added, as numbers have already been allocated in 5.1. So the patch uses the old message (which says "field" instead of "aggr-func"

2) the Same, but with a new message.
Unfortunatly, I had to test this against 5.0 (not 5.1), but it should work for 5.1 or 5.2 all the same (except for the number of the msg will change)

In both case I added full_name_or _print to item.h.
Since aggr-functions can appear outside the select list (like in where, order, having), where they do not have a name. Without this function, mysql would print "???" instead of a name.
There may be other warnings and error that could benefit from it?

patch with old msg

Attachment: explain.patch (text/x-patch), 1.46 KiB.

patch with new msg

Attachment: explain_new_msg.patch (text/x-patch), 1.97 KiB.

test

Attachment: subselect_explain.test (application/octet-stream, text), 1.55 KiB.

result for old name patch

Attachment: subselect_explain.result (application/octet-stream, text), 8.16 KiB.

result for new name patch (but with numbers from 5.0 / so numbers must be searched and replaced)

Attachment: subselect_explain.result2 (application/octet-stream, text), 8.23 KiB.

Thank you for the bug report and contribution.