[MDEV-6876] Limit in subquery changes behavior, even when arbitrarily high limit is used Created: 2014-10-16  Updated: 2015-09-10  Resolved: 2015-09-10

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 5.5.40
Fix Version/s: 10.1.8

Type: Bug Priority: Major
Reporter: adipose Assignee: Oleksandr Byelkin
Resolution: Won't Fix Votes: 0
Labels: upstream
Environment:

Windows Server 2008 R2 64 bit.

Attachments: File MDEV-6876.sql.bz2    
Sprint: 10.1.8-1

 Description   

Issue described here: http://stackoverflow.com/questions/26393529/why-does-mariadb-behave-differentyly-when-an-arbitrarily-high-limit-is-placed-on

A limit of 2^64-1 on a subquery changes the optimization for the better, even though it theoretically has no effect on the subquery.

explain
 
select 
`quotes`.`dnum`,
 
`quotes`.`rev`,
 
`quotes`.`QuoteName`
 
FROM 
(select * from `worknet`.`quotes`  where `quotes`.`deleted` IS NULL ORDER BY `quotes`.`dateModified` DESC limit 100) as `quotes`
 
LEFT JOIN 
(SELECT CAST(`dnum` as UNSIGNED) as `anum` from `worknet`.`nsos`)  AS `sosStatusDb`
 
ON `quotes`.`dnum`=`sosStatusDB`.`anum` 
where `quotes`.`deleted` IS NULL
 
ORDER BY `quotes`.`dateModified` DESC 
LIMIT 100

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 100 Using where; Using temporary; Using filesort
1 PRIMARY nsos index NULL PRIMARY 18 NULL 62243 Using where; Using index; Using join buffer (flat, BNL join)
2 DERIVED quotes index NULL date_modified 5 NULL 100 Using where

Changing the subquery to the following results in a different result

(SELECT CAST(`dnum` as UNSIGNED) as `anum` from `worknet`.`sos` LIMIT 18446744073709551615)  AS `sosStatusDb`

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 100 Using where; Using filesort
1 PRIMARY <derived3> ref key0 key0 5 quotes.dnum 622
3 DERIVED nsos index NULL PRIMARY 18 NULL 62243 Using index
2 DERIVED quotes index NULL date_modified 5 NULL 100 Using where

The query time changes from 9.1 seconds to .078.

 Comments   
Comment by Elena Stepanova [ 2014-10-16 ]

Hi,

Could you please send SHOW CREATE TABLE and SHOW INDEX IN output for quotes, sostatus and sos?

I tried to create the tables based on queries, but I'm getting different plans (also changing upon adding LIMIT, but still different from yours).

Thanks.

Comment by adipose [ 2014-10-16 ]

I updated the queries above to have a more watered down query, and to include a cast statement. Without the cast statement, the explain still changes, but the performance doesn't change as I originally reported. So this should more accurately describe the situation.

'nsos', 'CREATE TABLE `nsos` (
 
  `dnum` int(11) NOT NULL DEFAULT \'1\',
 
  `dateModified` int(10) unsigned NOT NULL,
 
  PRIMARY KEY (`dnum`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC'
 

'quotes', 'CREATE TABLE `quotes` (
 
  `dnum` int(10) unsigned NOT NULL,
 
  `rev` int(11) unsigned NOT NULL AUTO_INCREMENT,
 
  `dateModified` int(11) DEFAULT NULL,
 
  `SalesID` varchar(50) DEFAULT NULL,
 
  `userDnum` int(11) DEFAULT NULL,
 
  `QuoteName` varchar(99) DEFAULT NULL,
 
  `Customer` varchar(60) DEFAULT NULL,
 
  `accountId` int(11) DEFAULT NULL,
 
  `Product` text,
 
  `Price` float(16,2) DEFAULT NULL,
 
  `importedPrice` float(16,2) DEFAULT NULL ,
 
  `currency` varchar(3) DEFAULT NULL ,
 
  `Status` varchar(20) DEFAULT NULL,
 
  `safetyStandard` varchar(45) DEFAULT NULL,
 
  `Comments` text,
 
  `BidDate` int(11) DEFAULT NULL,
 
  `BidDateApptId` varchar(255) DEFAULT NULL,
 
  `DelegateBidDateApptId` varchar(255) DEFAULT NULL,
 
  `accessLog` longtext,
 
  `deleted` varchar(45) DEFAULT NULL,
 
  `deletedDate` int(11) DEFAULT NULL,
 
  `lockedBy` varchar(45) DEFAULT NULL,
 
  `template` varchar(45) DEFAULT NULL,
 
  `additionalcomments` text,
 
  `bayAreaJob` varchar(45) DEFAULT NULL,
 
  `requestedBy` varchar(99) DEFAULT NULL,
 
  `estimator` varchar(99) DEFAULT NULL,
 
  `estimatorDnum` int(11) DEFAULT NULL,
 
  `requestedDate` varchar(99) DEFAULT NULL,
 
  `so` text,
 
  `origin` int(11) DEFAULT NULL ,
 
  `attn` varchar(255) DEFAULT NULL ,
 
  `drawingLeadTime` varchar(255) DEFAULT NULL,
 
  `shipLeadTime` varchar(255) DEFAULT NULL,
 
  `location` text,
 
  `region` int(11) DEFAULT \'0\' ,
 
  `rvp` text,
 
  `rep` text,
 
  `repDnum` int(11) DEFAULT NULL,
 
  `highChance` text,
 
  `importedFromExcel` bit(1) DEFAULT NULL,
 
  `importedFileTimestamp` int(11) DEFAULT NULL,
 
  `importedTime` int(11) DEFAULT NULL,
 
  `bidComplete` int(11) DEFAULT NULL ,
 
  `fromRev` int(11) DEFAULT \'-1\' ,
 
  `clonedFromRev` int(11) DEFAULT \'-1\' ,
 
  `type` int(11) DEFAULT NULL ,
 
  `suppliers` varchar(255) DEFAULT NULL,
 
  PRIMARY KEY (`dnum`,`rev`),
 
  KEY `salesID-origin-deleted` (`SalesID`,`origin`,`deleted`),
 
  KEY `date_modified` (`dateModified`) USING BTREE,
 
  KEY `quoteNum` (`dnum`)
 
) ENGINE=MyISAM AUTO_INCREMENT=45388 DEFAULT CHARSET=utf8 MAX_ROWS=100000'

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
nsos 0 PRIMARY 1 dnum A 60753 NULL NULL   BTREE
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
quotes 0 PRIMARY 1 dnum A NULL NULL NULL   BTREE
quotes 0 PRIMARY 2 rev A 34365 NULL NULL   BTREE
quotes 1 salesID-origin-deleted 1 SalesID A 1909 NULL NULL YES BTREE
quotes 1 salesID-origin-deleted 2 origin A 34365 NULL NULL YES BTREE
quotes 1 salesID-origin-deleted 3 deleted A 34365 NULL NULL YES BTREE
quotes 1 date_modified 1 dateModified A 34365 NULL NULL YES BTREE
quotes 1 quoteNum 1 dnum A 34365 NULL NULL   BTREE
Comment by Elena Stepanova [ 2014-10-16 ]

Thank you.

Assigning to psergey to evaluate how much of a bug it is and what it takes to fix it.
Even if it's a bug, I don't expect it to be fixed in 5.5, but ultimately it's psergey's decision, maybe it's something simple and basic.

Comment by Oleksandr Byelkin [ 2015-09-10 ]

As far as I can see limit just prevent merging and in this case materialization is more efficient.

Comment by Oleksandr Byelkin [ 2015-09-10 ]

I attached dump of database where I was experimenting.

Comment by Oleksandr Byelkin [ 2015-09-10 ]

In discussion with Sergey Petrunia we figured our that in case of merge expression CAST(`dnum` as UNSIGNED) prevent using hash join. In case of materialization we already has 2 fields and hash join works.

At the moment optimizer this situation is beyond cases it can detect. So only manual optimization can help. Sorry.

Generated at Thu Feb 08 07:15:15 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.