Details
-
Bug
-
Status: Closed (View Workflow)
-
Resolution: Not a Bug
-
None
-
None
-
None
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;