← Back to team overview

maria-discuss team mailing list archive

Re: Wrong ordering in MariaDB 5.2.10

 

After testing inserting the full data into a new table,  it seems the
fault is in PBXT somewhere.
This is a very old table that is constantly accessed.  I can only
reproduce it in the existing table. 
Any attempt to create it in a new one fails to have the same results.

Also, data appears in the range version that seems to be transactional
that is rolled back and not in the table by the indexes.

This is a bad sign for PBXT.

Brian

On 3/16/2012 11:53 AM, Peter Laursen wrote:
> 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
> <mailto: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
>     <https://launchpad.net/%7Emaria-discuss>
>     Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
>     <mailto:maria-discuss@xxxxxxxxxxxxxxxxxxx>
>     Unsubscribe : https://launchpad.net/~maria-discuss
>     <https://launchpad.net/%7Emaria-discuss>
>     More help   : https://help.launchpad.net/ListHelp
>
>


Follow ups

References