Sorting In Results Pane

[DonQuichote]

I wanted to sort the contents of a table in the result pane by the first (autonumber/primary key) column, but the sort order did not display correctly. The SQL query log of MySQL contains the following statements for that action:

Code:

070818 20:52:47 21 Query show full fields from `IpToCountry`.`IpLookUp`
21 Query show keys from `IpToCountry`.`IpLookUp`
070818 20:52:48 21 Query select * from `IpToCountry`.`IpLookUp` order by 1 desc

Instead of ORDER BY intIpLookUpId, the table is ordered by 1. It looks like the wrong column is read from either the show fields or the show keys queries.

My table definition was as follows:

Code:

create database if not exists IpToCountry charset=utf8;
use IpToCountry;
CREATE TABLE IF NOT EXISTS IpLookUp
(intIpLookUpId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
intIpFrom INTEGER UNSIGNED NOT NULL,
intIpTo INTEGER UNSIGNED NOT NULL,
vchIso2 VARCHAR(2) NOT NULL,
vchIso3 VARCHAR(3) NOT NULL,
vchCountryName VARCHAR(255),
UNIQUE INDEX idxIpFrom(intIpFrom),
UNIQUE INDEX idxIpTo(intIpTo),
INDEX idxIso2(vchIso2),
INDEX idxIso3(vchIso3),
INDEX idxCountryName(vchCountryName)
) ENGINE=InnoDB COMMENT='From ip-to-country.webhosting.com';

And I imported the data (CSV) from the above website.

More info:

I am running MySQL 5.0.38 on Xubuntu 7.04, and SQLyog 6.05 beta 3 with wine.

[peterlaursen]

hmmm ..

first: we do not at all yet (though it is planned) support sorting from RESULT pane/tab. Do you mean DATA pane/tab?

second: as `intIpLookUpId`is the 1st column according to the CREATE TABLE definition

this

… ORDER BY `intIpLookUpId`;

and this

… ORDER BY 1;

is exactly the same! So I believe this is NOT the issue! (note it is 1 not '1' !)

Please give a detailed example of this “but the sort order did not display correctly”

1) a DUMP of a small test table (small means SMALL – no more than 15-20 rows and 4-6 columns!)

2) tell EXACTLY what/how you are doing! Clicking on the column header, or … ?

3) screendump of how it sorted

4) description of what you expected

BTW: don't use a beta version when a more recent stable one is available!

[DonQuichote]

peterlaursen wrote on Aug 18 2007, 10:15 PM:

hmmm ..

first: we do not at all yet (though it is planned) support sorting from RESULT pane/tab. Do you mean DATA pane/tab?

Yes. I Select the table and click the Table Data tab.

Quote:

second: as `intIpLookUpId`is the 1st column according to the CREATE TABLE definition

this

… ORDER BY `intIpLookUpId`;

and this

… ORDER BY 1;

is exactly the same! So I believe this is NOT the issue! (note it is 1 not '1' !)

Thanks for the quick response.

I did not know that, and frankly it surprises me. I have used the order by clause with the random function often enough, so order by 1 would just be a special case of an expression. But you are probably right that it is the same.

Quote:

Please give a detailed example of this “but the sort order did not display correctly”

1) a DUMP of a small test table (small means SMALL – no more than 15-20 rows and 4-6 columns!)

2) tell EXACTLY what/how you are doing! Clicking on the column header, or … ?

3) screendump of how it sorted

4) description of what you expected

BTW: don't use a beta version when a more recent stable one is available!

OK. I did a further test and the results seem to be in the right order in the data pane after clicking the column header. But the data pane scrollbar behaves funny. I have to scroll very far down (I have to select “show all” to reproduce it) to see the primary key field drop back to small values again. This is after about 64000 records, so this could be a two-byte integer cast problem. After I release the mouse button, the scrollbar jumps back to the beginning, where those records really are (when still dragging it follows the mouse cursor like expected). Releasing the mouse button on a record before the “drop-back” point works like expected.

Again, I am using wine, so this could be a wine issue.

Alas, to make a screenshot I have to release the mouse button and this issue seems to need a large table. And I'll upgrade. I just searched the project issues and the forum for this issue and could not find it, so I assumed it still exists.

Best regards.

[peterlaursen]

“But the data pane scrollbar behaves funny.”

We know about such issues with 'funny' scrollbar behaviour. But I am not quite sure if this is a new 'materialisation'. I will ask a test engineer to record the details from this discussion and add to our task list.

We plan to solve all such issues in version 6.2 (my best guess: 2-3 months ahead)

[Author]

Posts