MySQL Bugs: #58639: explain partitions retruns all partitions in wb SQL editor

Bug #58639	explain partitions retruns all partitions in wb SQL editor
Submitted:	1 Dec 2010 20:09	Modified:	15 Feb 2011 18:58
Reporter:	Brian Pontius	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S3 (Non-critical)
Version:	5.2.30 CE	OS:	Windows (Windows 7)
Assigned to:		CPU Architecture:	Any

Description:
In SQL Editor within WB (on my Windows 7 laptop) did 
  explain partitions select `date` from `<table>` 
  where `date` in ('2010-11-11','2010-11-12')
on a partitioned table. It returns all partitions available in the table. This session is connected to the same linux server, schema and references the same table as the next step.
When I execute the exact same command in mysql on the linux server it returns the expected 1 partition which the query should access.

The MySQL server is 5.5.6-rc

The table is partitioned using the 'range to_days(`date`)' function.
Tried creating another table with 'range columns (`date`)' and obtained the same results.
There are currently 14 partitions for this table.
I tried 2 date constructs in the query:
where `date` in ('2010-11-11','2010-11-12')
  and 
where `date` between '2010-11-10' and '2010-11-14'

I did try "where `date` = '2010-11-10'" and this did show only the one partition selected in the explain plan. (in WB and mysql)

How to repeat:
Create a table with multiple partitions on a date column.

Use the command to generate an explain plan for partitioned tables in MySQL Workbench SQL Editor.

explain partition select ...  where date in ('<date>','<date>');

Execute the same query in mysql on the mysql server.

Do not use the toolbar 'explain' button as this does not provide the same results as 'explain partitions'.

Please, send the results of

show variables like 'coll%';

from Workbench and from environment where results are correct. Send also the results of SHOW CREATE TABLE for the partitioned table used.

So, there is a difference in collation_connection, and it may lead to different plan.

Please, add

set names latin1;

before your EXPLAIN PARTITION in SQL Editor window and check if this will change anything.

Adding the 'set names latin1;' before the explain plan returned the correct result in WB.

Is the issue that WB does not determine what collation the table has as part of the process?
Since MySQL supports character sets at the server, database, table, and column level shouldn't WB determine these for a particular action?
Or does WB only use a set default?

Some of our schema tables do use utf-8, others latin1, depending on when they were created and/or depending on the customer.

Thank you for the feedback.

This looks very similar to server bug #52849 and its duplicate bug #53685 But these should be fixed now. Please try with 5.5.8 server and 5.2.31 WB and inform us if problem still exists.

We have entered a process to upgrade our MySQL servers to 5.5.8. I will update this again when complete and tested.  Thank you.

This is working fine now on the upgraded servers. (MySQL upgraded to 5.5.8)

Thank you for your attention to this item.

Thank you for the feedback.

Closed as "Can't repeat" because last comment.