maria-discuss team mailing list archive

[Peter Laursen <peter_laursen@xxxxxxxxxx>]

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@xxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
>
>