[MDEV-3685] LP:983423 - Different execution plans for subquery Created: 2012-04-16  Updated: 2012-10-04  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug
Reporter: jason clifton (Inactive) Assignee: Timour Katchaounov (Inactive)
Resolution: Not a Bug Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug983423.xml    

 Description   

I'm not sure this is actually a bug or expected behavior....
I'm using maria 5.5.22 and you can see my table structures below.
When I execute the following query

SELECT SUM(`PAIDAMT`) as total, SUM(`DISCOUNT`) as discount from `ap_invoices` WHERE `VENDNO`='UPS' AND `INVNO` IN(SELECT `INVNO` FROM `checks_written` WHERE `CHECKNO`='42356')

I get the following explain extended

------------------------------------------------------------------------------------------------------------------------------------------------------

id select_type table type possible_keys key key_len ref rows filtered Extra

------------------------------------------------------------------------------------------------------------------------------------------------------

1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 1 100.00  
1 PRIMARY ap_invoices ref INVNO,VENDNO INVNO 13 citydiesel_inventory.checks_written.INVNO 1 100.00 Using index condition; Using where
2 MATERIALIZED checks_written ref CHECKNO CHECKNO 9 const 1 100.00 Using index condition; Using where

------------------------------------------------------------------------------------------------------------------------------------------------------

However if I remove the single quote marks from CHECKNO so the query looks like
SELECT SUM(`PAIDAMT`) as total, SUM(`DISCOUNT`) as discount from `ap_invoices` WHERE `VENDNO`='UPS' AND `INVNO` IN(SELECT `INVNO` FROM `checks_written` WHERE `CHECKNO`=42356)

I get an explain that looks like

-------------------------------------------------------------------------------------------------------------

id select_type table type possible_keys key key_len ref rows filtered Extra

-------------------------------------------------------------------------------------------------------------

1 PRIMARY ap_invoices ref INVNO,VENDNO VENDNO 7 const 835 100.00 Using index condition
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func 1 100.00 Using where
2 MATERIALIZED checks_written ALL CHECKNO NULL NULL NULL 56507 100.00 Using where

-------------------------------------------------------------------------------------------------------------

table structures

CREATE TABLE `ap_invoices` (
`Id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`INVNO` char(12) DEFAULT NULL,
`VENDNO` char(6) DEFAULT NULL,
`PPRIORITY` char(1) DEFAULT NULL,
`PDISC` decimal(12,2) DEFAULT NULL,
`PDAYS` int(11) DEFAULT NULL,
`PNET` int(11) DEFAULT NULL,
`PURDATE` char(10) DEFAULT NULL,
`DUEDATE` char(10) DEFAULT NULL,
`DISDATE` char(10) DEFAULT NULL,
`DISCOUNT` decimal(8,2) DEFAULT NULL,
`PURAMT` decimal(8,2) DEFAULT NULL,
`PAIDAMT` decimal(8,2) DEFAULT NULL,
`DISAMT` decimal(8,2) DEFAULT NULL,
`ADJAMT` decimal(8,2) DEFAULT NULL,
`APRPAY` decimal(8,2) DEFAULT NULL,
`APRDIS` decimal(8,2) DEFAULT NULL,
`APRADJ` decimal(8,2) DEFAULT NULL,
`AMT1099` decimal(8,2) DEFAULT NULL,
`BUYER` char(2) DEFAULT NULL,
`CURRENT` char(1) DEFAULT NULL,
`REF` char(8) DEFAULT NULL,
`CHECKNO` char(8) DEFAULT NULL,
`CHECKDATE` char(10) DEFAULT NULL,
`APACC` char(9) DEFAULT NULL,
`CHKACC` char(9) DEFAULT NULL,
`TYP1099` char(5) DEFAULT NULL,
`APSTAT` char(1) DEFAULT NULL,
`APTYPE` char(1) DEFAULT NULL,
`TOSW` char(1) DEFAULT NULL,
`SIGNATURE` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`Id`),
KEY `INVNO` (`INVNO`,`CURRENT`),
KEY `VENDNO` (`VENDNO`,`CURRENT`),
KEY `PPRIORITY` (`PPRIORITY`,`CURRENT`),
KEY `CURRENT` (`CURRENT`),
KEY `SIGNATURE` (`SIGNATURE`),
KEY `APRPAY` (`APRPAY`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

–
-- Table structure for table `checks_written`
–

CREATE TABLE `checks_written` (
`Id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`INVNO` char(8) DEFAULT NULL,
`VENDNO` char(6) DEFAULT NULL,
`COMPANY` char(35) DEFAULT NULL,
`PPRIORITY` char(1) NOT NULL,
`APRPAY` decimal(8,2) DEFAULT NULL,
`AMT1099` decimal(8,2) DEFAULT NULL,
`REF` char(8) DEFAULT NULL,
`CHECKNO` char(8) DEFAULT NULL,
`CHECKDATE` char(10) DEFAULT NULL,
`VOIDDATE` char(10) DEFAULT NULL,
`CHKACC` char(9) DEFAULT NULL,
`TYP1099` char(5) DEFAULT NULL,
`APSTAT` char(1) NOT NULL,
`CKSTAT` enum('','V') NOT NULL,
`CKTYPE` char(1) DEFAULT NULL,
PRIMARY KEY (`Id`),
KEY `APSTAT` (`APSTAT`,`PPRIORITY`),
KEY `CHECKNO` (`CHECKNO`),
KEY `VENDNO` (`VENDNO`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 Comments   
Comment by Elena Stepanova [ 2012-04-17 ]

Re: Different execution plans for subquery
Hi,

What is the actual problem? Is it just the observation that the plans are different (which of course can happen), or is one of the plans badly inefficient, so the query is executed too slowly? If so, which one is it and how slow it gets (comparing to the other one)?

Thank you.

Comment by jason clifton (Inactive) [ 2012-04-17 ]

Re: Different execution plans for subquery
Sorry I should have been more clear... The first plan is very efficient and almost instant regardless of table size, and the second seems to be doing a full table scan on checks_written regardless of the table size and index hints. In this case ~50000 records it takes .5 seconds however on the actual production machine where the table has ~300000 rows it was taking over 3 seconds.

Comment by Elena Stepanova [ 2012-04-17 ]

Re: Different execution plans for subquery
Assigning to Timour to confirm that the behavior is expected (or to say it's not).

Comment by Timour Katchaounov (Inactive) [ 2012-04-18 ]

Re: Different execution plans for subquery
As far as I can see, the only difference is in the following subquery predicate:

`CHECKNO`='42356' vs `CHECKNO`=42356

In the second case a numeric constant is implicitly converted to a string.

This is not a bug. AFAIK for instance Oracle has the same behavior
with respect to implicitly converted constants.

Try the following simple example:
create table t1 (c1 char(1), c2 char(1), key(c1));
insert into t1 values (1,1);
insert into t1 values (2,1);
insert into t1 values (3,2);

If we supply a string constant of the same length, then the index is used:

explain select * from t1 where '1' = c1;
--------------------------------------------------------------------------------+

id select_type table type possible_keys key key_len ref rows Extra

--------------------------------------------------------------------------------+

1 SIMPLE t1 ref c1 c1 2 const 1 Using index condition

--------------------------------------------------------------------------------+

However, if the constant needs to be converted implicitly, the index cannot be used
any more:

explain select * from t1 where 1 = c1;
---------------------------------------------------------------------+

id select_type table type possible_keys key key_len ref rows Extra

---------------------------------------------------------------------+

1 SIMPLE t1 ALL c1 NULL NULL NULL 3 Using where

---------------------------------------------------------------------+

Comment by Rasmus Johansson (Inactive) [ 2012-04-18 ]

Launchpad bug id: 983423

Generated at Thu Feb 08 06:50:27 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.