I think specific data are required to reproduce wrong ordering. I cannot reproduce with a few inserted data. I do like this:

SELECT VERSION() #5.2.10-MariaDB

DROP TABLE IF EXISTS `tableinventory`;

CREATE TABLE `tableinventory` (
`StockNo` VARCHAR(64) NOT NULL DEFAULT '',
`ItemDesc` VARCHAR(96) NOT NULL DEFAULT '',
PRIMARY KEY (`StockNo`)
) ENGINE=PBXT DEFAULT CHARSET=latin1;

INSERT INTO `tableinventory`(`StockNo`,`ItemDesc`) VALUES ('JBccccccc','ccccccc');
INSERT INTO `tableinventory`(`StockNo`,`ItemDesc`) VALUES ('JGddddddd','ddddddd');
INSERT INTO `tableinventory`(`StockNo`,`ItemDesc`) VALUES ('JRaaaaaaa','aaaaaaa');
INSERT INTO `tableinventory`(`StockNo`,`ItemDesc`) VALUES ('VSbbbbbbb','bbbbbbb');

SELECT SUBSTRING(StockNo, 3, 3) AS $number FROM `tableinventory` WHERE LEFT(StockNo,2) IN('JR','VS','JB','JG') ORDER BY $number DESC;

SELECT SUBSTRING(StockNo, 3, 3) AS $number FROM `tableinventory` WHERE StockNo LIKE 'JR%' OR StockNo LIKE 'VS%' OR StockNo LIKE 'JB%' OR StockNo LIKE 'JG%'ORDER BY $number DESC;

/* and both SELECTs return the same expected ordering om my environment (Win7/64 - MariaDB 5.2.10):

$number  
---------
ddd
ccc
bbb
aaa
*/

Maybe characters used in 'StockNo' are a little less trivial than 'a', 'b' etc. in the environment where you see the problem?


Peter
(not a MP person)


On Fri, Mar 16, 2012 at 16:01, Brian Evans <grknight@scent-team.com> wrote:
We are hitting a wrong ordering in 5.2.10 but it does not happen on a test box using 5.3 series (tried 5.3.3 and 5.3.5).
The query can be rewritten and when we do, the range becomes an index scan and produces the correct results.

Here are the queries:
[Incorrect Order]
SELECT SUBSTRING(StockNo, 3, 3) AS $number FROM inv WHERE StockNo LIKE
'JR%' OR StockNo LIKE 'VS%' OR StockNo LIKE 'JB%' OR StockNo LIKE 'JG%'
ORDER BY $number DESC LIMIT 1;

EXPLAIN Result
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE inv range PRIMARY PRIMARY 66 (NULL) 4 Using where; Using index; Using filesort


[Correct Order]
SELECT SUBSTRING(StockNo, 3, 3) AS $number FROM inv WHERE LEFT(StockNo,2) IN('JR','VS','JB','JG')
ORDER BY $number DESC LIMIT 1;

EXPLAIN Result

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE inv index (NULL) PRIMARY 66 (NULL) 2496 Using where; Using index; Using filesort


Simplified Table Structure:
CREATE TABLE `tableinventory` (
  `StockNo` varchar(64) NOT NULL DEFAULT '',
  `ItemDesc` varchar(96) NOT NULL DEFAULT '',
  PRIMARY KEY (`StockNo`)
) ENGINE=PBXT DEFAULT CHARSET=latin1;

All data is using a length of 7 even though it is defined as varchar(64).

_______________________________________________
Mailing list: https://launchpad.net/~maria-discuss
Post to     : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp