MySQL Bugs: #49311: Live editor sorting applies only to currently shown rows

Bug #49311	Live editor sorting applies only to currently shown rows
Submitted:	1 Dec 2009 22:53	Modified:	3 Dec 2009 9:59
Reporter:	Todd Farmer (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Workbench	Severity:	S2 (Serious)
Version:	5.2.10 r4680	OS:	Windows (XP)
Assigned to:		CPU Architecture:	Any

Description:
While testing Bug #49310, I created a table with more than 1000 rows.  This table has an auto-increment PK, and only values >= 1000 were showing.  I issued the following updates:

UPDATE tbl SET colB = 2;
UPDATE tbl SET colB = 1 WHERE colA > 1000;

Then I started the live editor, which again showed only the first 1000 rows (colA <= 1000).  I sorted on colB ASC, and none of the rows with colB=1 were shown.

How to repeat:
See above.

Suggested fix:
Make sorting apply across the entire result set, not just the displayed values.

Sorting recordset data by clicking column headers applies only to fetched data, in other words it triggers client-side sorting. To get a server-side sorting you'll need to add `order by` clause to a query.

Note, you have option to disable automatic limitation of result set or change the number of records to limit to by tailoring WB global parameters:
1) "SqlEditor:LimitRows" - global flag defining whether to apply limit clause to queries by default;
2) "SqlEditor:LimitRowsCount" - defines number of records to limit result set to when limitation is applied.

Sorry, no UI were added for editing these parameters yet. You can modify them be editing `\MySQL\Workbench\wb_options.xml` file, located in your home directory. WB instance must be closed first to avoid overwriting changes to the file.

Yet there is no way to add server-side ordering in a way which affects the live editor.

Hm, where in live editor is it possible to view table data?
Just tried with model table inserts editor and ensured that limitation for it is disabled. And for live table editor inserts page doesn't exist.

We're probably referring to two different things with "live editor".  I'm talking about the tab in SQL Editor which displays table data, and, if a PK exists, allows users to manipulate the data.

With "live editor" we refer to database object editors for schema/table/view/routine to create/alter objects in connected DBMS.
You are talking about "recordset" in WB terms. And it's possible to sort all records in it (no matter how many) in 3 possible ways:
1) Add "order by" clause to a query. This makes sense if you want to use existing indexes for quick sorting or get records sorted while keeping number of fetched records still limited.
2) Fetch all records to client-side by pressing "Fetch All" button in recordset's lower toolbar, then sort fetched data by clicking column headers. If "Fetch All" doesn't show up that means all records are already fetched.
3) Disable automatic limitation by adjusting parameters in wb_options.xml, that I cited in my first comment to this bug-report.

Wow.  This entire time, I was completely unaware that the "Fetch All" option existed.  I agree that this is not a bug in light of that; perhaps we can make a feature request to move this option out of the status area and more clearly identify this as a selectable option.