MySQL Bugs: #6002: Cursor not moving to LAST in a simulated crosstab query result recordset

Bug #6002	Cursor not moving to LAST in a simulated crosstab query result recordset
Submitted:	10 Oct 2004 0:03	Modified:	3 Nov 2004 13:47
Reporter:	Ioan Gelu Ionas	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Query Browser	Severity:	S3 (Non-critical)
Version:	1.0.8 beta	OS:	Windows (Windows XP SP2)
Assigned to:		CPU Architecture:	Any

Description:
After running a simulated crosstab query using SUM(IF(...)), the recordset cursor doesn't move to the last record when clicking the "Last" button on the bottom button bar of the query results tab. It is possitioned on the record previous to the last record in the set. 

How to repeat:
Create a table with the following structure:

CREATE TABLE `ctab` (
  `rowid` int(10) unsigned NOT NULL auto_increment,
  `key` int(10) unsigned NOT NULL default '0',
  `value` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`rowid`)
) TYPE=MyISAM; 

Fill it with some values:

INSERT INTO `ctab` (`rowid`,`key`,`value`) VALUES (1,1,1),(2,1,2),(3,1,3),(4,2,1),(5,2,2),(6,2,3),(7,2,4),(8,3,2),(9,3,4),(10,1,1);

Then run the following query:

SELECT c.key,sum(if(c.value=1,1,0)) as k1, sum(if(c.value=2,1,0)) as k2 FROM testdb.ctab c group by c.key

Use the "First" and "Last" buttons to navigate the recordset. "First" works ok, while "Last" moves the cursor to the record preceding the last record.

Thank you for the bug report.

The cursor is (and should be) placed on the last record, not the last row of the grid. You can use [Ctrl]+[End9 on the keyboard to go to the very last row.

If you want to add a row, select [Add Row] from the popup menu in the resultset grid.

Based on the described situation, the select query sends back 3 records (three lines of data in the grid). I don't have any intention of adding any row, I was just trying to navigate the obtained recordset (there is no blank line at the end). 

As far as I can tell, I can select each and every row individually by clicking on each of them with the mouse, but when I use the navigation buttons at the bottom of the screen the "|<" (first) button works (moves the cursor on the first line and disables the "|<" button), but ">|" (last) doesn't. It moves the cursor on the record above the LAST RECORD (issued by the select query and not blank line - I don't have a blank line anyway) and doesn't disable the >| button. If I click with the mouse on the last record, the >| button becomes disabled.

Need to be careful here. When you open a table using a SELECT statement in the regular way it works! The cursor behaves exactly the way you described. This is not the case of my query.

My obervations tell me that the behavior is correct when you HAVE a blank line at the bottom of the recordset, but it doesn't work for those results displayed without a blank line at the bottom (in my query, the last line in the grid IS the last record).

Query results and cursor after clicking on the >| (last) button

Attachment: SQLQueryBrowser.jpg (image/jpeg, text), 101.34 KiB.

I uploaded a file to show the exact situation of the resulted recordset after using the >| button.