Optimizing error with IN clause?

Hello,

First off, I'm using MySQL 4.0.12-nt and am doing a simple query on the
database 'dps':

"SELECT * FROM Production,ProductionStatusTypes
WHERE Production.ProStatusID=ProductionStatusTypes.StatusID AND JobID IN
('49,50,51,52,53,54,55,56,57,58,48,43,44,45,46,12,17,18,19,2 0,21,22,23,24,25
,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,2,3,4,5,6,7 ,8,9,10,11,13,14
,15,16,1,47') ;"

I am given this error message like this in the MySQL Control Center
0.9.1-beta:

"[localhost] ERROR 1146: Table 'dps.impossible where noticed after reading
const tables' doesn't exist"

The table structure of Production is:

CREATE TABLE `production` (
`ProductionID` int(10) unsigned NOT NULL auto_increment,
`JobID` int(10) unsigned NOT NULL default '0',
`ProStatusID` tinyint(3) unsigned NOT NULL default '0',
`ShippingID` int(10) unsigned NOT NULL default '0',
`Preorder_IndividualID` mediumint(8) unsigned default NULL,
`Takeoff_IndividualID` mediumint(8) unsigned default NULL,
`MatListDist` date default NULL,
`MatListSign` date default NULL,
PRIMARY KEY (`ProductionID`)
) TYPE=MyISAM

and Production Status types:

CREATE TABLE `productionstatustypes` (
`StatusID` tinyint(3) unsigned NOT NULL auto_increment,
`ProductionStatus` varchar(100) NOT NULL default '',
PRIMARY KEY (`StatusID`)
) TYPE=MyISAM

The contents of the production table is:

'ProductionID','JobID','ProStatusID','ShippingID','Preorder_ IndividualID','T
akeoff_IndividualID','MatListDist','MatListSign'
'4','27','1','0','[NULL]','[NULL]','[NULL]','[NULL]'

So the only entry is the JobID entry of '27' which should be picked up in
the IN clause. The query works if I put '27' as the first number in the IN
clause.


Just for interest (or not :) the productionstatustypes contents are simply:
'StatusID','ProductionStatus'
'1','Not Assigned'
'2','WIP'
'3','Problem'
'4','Complete'


It looks like possibly it is expecting the values in the IN statement to be
sorted?

Is this a requirement?

Thank you,
Jamie Maher


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Optimizing error with IN clause?

On Wed, 2003-05-14 at 03:22, Jamie Maher wrote:
> Hello,
>
> First off, I'm using MySQL 4.0.12-nt and am doing a simple query on the
> database 'dps':
>
> "SELECT * FROM Production,ProductionStatusTypes
> WHERE Production.ProStatusID=ProductionStatusTypes.StatusID AND JobID IN
> ('49,50,51,52,53,54,55,56,57,58,48,43,44,45,46,12,17,18,19,2 0,21,22,23,24,25
> ,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,2,3,4,5,6,7 ,8,9,10,11,13,14
> ,15,16,1,47') ;"
>
> I am given this error message like this in the MySQL Control Center
> 0.9.1-beta:
>

>
>
> It looks like possibly it is expecting the values in the IN statement to be
> sorted?
>
> Is this a requirement?


Jamie,

There is no such a requirement as values to be sorted, so it could be a
bug.

As the error you're getting looks like coming from MySQL server it could
be good to create repeatable example. I've tried to repeat the error
with your data but failed. Could you please try creating repeatable test
case which we could use to demonstrate the problem just as

mysql < queries.sql


If table data required to repeat test case is very large you could
upload the tables involved to ftp://support.mysql.com/pub/mysql/secret

Thank you for cooperation.





--
Are you MySQL certified?, http://www.mysql.com/certification/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Peter Zaitsev
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Moscow, Russia
<___/ www.mysql.com


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

RE: Optimizing error with IN clause?

------=_NextPart_000_000C_01C31AC8.018683C0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

Hi Peter,

Here is a test case which works for me every time.

mysql < tables.sql
mysql < works.sql
mysql < error.sql

In the works.sql I simply rearranged the order of the values in the IN
clause. The error.sql doesn't produce any results, and the EXPLAIN used on
that query states: "Impossible WHERE noticed after reading const tables"

Hope this helps,

Jamie Maher

-----Original Message-----
From: Peter Zaitsev [mailto:peter@mysql.com]
Sent: Thursday, May 15, 2003 3:39 AM
To: jmaher@linwoodhomes.com
Cc: bugs@lists.mysql.com
Subject: Re: Optimizing error with IN clause?


On Wed, 2003-05-14 at 03:22, Jamie Maher wrote:
> Hello,
>
> First off, I'm using MySQL 4.0.12-nt and am doing a simple query on the
> database 'dps':
>
> "SELECT * FROM Production,ProductionStatusTypes
> WHERE Production.ProStatusID=ProductionStatusTypes.StatusID AND JobID IN
>
('49,50,51,52,53,54,55,56,57,58,48,43,44,45,46,12,17,18,19,2 0,21,22,23,24,25
>
,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,2,3,4,5,6,7 ,8,9,10,11,13,14
> ,15,16,1,47') ;"
>
> I am given this error message like this in the MySQL Control Center
> 0.9.1-beta:
>

>
>
> It looks like possibly it is expecting the values in the IN statement to
be
> sorted?
>
> Is this a requirement?


Jamie,

There is no such a requirement as values to be sorted, so it could be a
bug.

As the error you're getting looks like coming from MySQL server it could
be good to create repeatable example. I've tried to repeat the error
with your data but failed. Could you please try creating repeatable test
case which we could use to demonstrate the problem just as

mysql < queries.sql


If table data required to repeat test case is very large you could
upload the tables involved to ftp://support.mysql.com/pub/mysql/secret

Thank you for cooperation.


------=_NextPart_000_000C_01C31AC8.018683C0
Content-Type: text/plain; charset=us-ascii

--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org
------=_NextPart_000_000C_01C31AC8.018683C0--

RE: Optimizing error with IN clause?

For those of you on the mailing list:

I've included contents of the files listed below. I attached them
previously, which naturally wouldn't work with a mailing list! :)

Here is a test case which works for me every time.

mysql < tables.sql
mysql < works.sql
mysql < error.sql

In the works.sql I simply rearranged the order of the values in the IN
clause. The error.sql doesn't produce any results, and the EXPLAIN used on
that query states: "Impossible WHERE noticed after reading const tables"

Hope this helps,

Jamie Maher


File content listing follows:

File: tables.sql
--BEGIN

-- MySQL dump 9.07
--
-- Host: localhost Database: DPS
---------------------------------------------------------
-- Server version 4.0.12-nt

--
-- Table structure for table 'Production'
--

CREATE DATABASE IF NOT EXISTS TEST;

USE TEST;

CREATE TABLE production (
ProductionID int(10) unsigned NOT NULL auto_increment,
JobID int(10) unsigned NOT NULL default '0',
ProStatusID tinyint(3) unsigned NOT NULL default '0',
ShippingID int(10) unsigned NOT NULL default '0',
Preorder_IndividualID mediumint(8) unsigned default NULL,
Takeoff_IndividualID mediumint(8) unsigned default NULL,
MatListDist date default NULL,
MatListSign date default NULL,
PRIMARY KEY (ProductionID)
) TYPE=MyISAM;

--
-- Dumping data for table 'Production'
--

INSERT INTO Production VALUES (4,27,1,0,NULL,NULL,NULL,NULL);

--
-- Table structure for table 'ProductionStatusTypes'
--

CREATE TABLE productionstatustypes (
StatusID tinyint(3) unsigned NOT NULL auto_increment,
ProductionStatus varchar(100) NOT NULL default '',
PRIMARY KEY (StatusID)
) TYPE=MyISAM;

--
-- Dumping data for table 'ProductionStatusTypes'
--

INSERT INTO ProductionStatusTypes VALUES (1,'Not Assigned');
INSERT INTO ProductionStatusTypes VALUES (2,'WIP');
INSERT INTO ProductionStatusTypes VALUES (3,'Problem');
INSERT INTO ProductionStatusTypes VALUES (4,'Complete');

--END

File: works.sql
--BEGIN
USE TEST;

SELECT * FROM Production,ProductionStatusTypes
WHERE Production.ProStatusID=ProductionStatusTypes.StatusID AND JobID IN
('27,49,50') ;

EXPLAIN SELECT * FROM Production,ProductionStatusTypes
WHERE Production.ProStatusID=ProductionStatusTypes.StatusID AND JobID IN
('27,49,50') ;
--END


File: error.sql
--BEGIN
USE TEST;

SELECT * FROM Production,ProductionStatusTypes
WHERE Production.ProStatusID=ProductionStatusTypes.StatusID AND JobID IN
('49,50,27') ;

EXPLAIN SELECT * FROM Production,ProductionStatusTypes
WHERE Production.ProStatusID=ProductionStatusTypes.StatusID AND JobID IN
('49,50,27') ;
--END


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

RE: Optimizing error with IN clause?

On Thu, 2003-05-15 at 20:54, Jamie Maher wrote:
> Hi Peter,
>
> Here is a test case which works for me every time.
>
> mysql < tables.sql
> mysql < works.sql
> mysql < error.sql
>
> In the works.sql I simply rearranged the order of the values in the IN
> clause. The error.sql doesn't produce any results, and the EXPLAIN used on
> that query states: "Impossible WHERE noticed after reading const tables"

OK. Thank you for good report which allows to quickly confirm this is
not a bug.

Take a look at the IN clause:

JobID IN ('49,50,27')

Basically you have One String value in the list. If you would like 3
integers it would be (49,50,27)

MySQL does silent conversion of the strings to integer if required and
in this case the string is converted to 49 and the rest is ignored. In
working case it is converted to 27.

So both queries are actually doing not what you would think they to do,
first selecting JobID=27, second JobID=49.

Impossible Where Noticed is correct behavior as optimizer discovered
where clause can't be satisfied on optimization phase.

So no bugs here.




--
Are you MySQL certified?, http://www.mysql.com/certification/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Peter Zaitsev
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Moscow, Russia
<___/ www.mysql.com


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

RE: Optimizing error with IN clause?

Hello folks,

I think I solved my own problem.. I was surrounding my IN clause with
quotes.

To summarize it looks like this works:

SELECT * FROM Production,ProductionStatusTypes
WHERE Production.ProStatusID=ProductionStatusTypes.StatusID AND JobID IN
('27,49,50') ;

SELECT * FROM Production,ProductionStatusTypes
WHERE Production.ProStatusID=ProductionStatusTypes.StatusID AND JobID IN
(49,50,27) ;

BUT this doesn't:

SELECT * FROM Production,ProductionStatusTypes
WHERE Production.ProStatusID=ProductionStatusTypes.StatusID AND JobID IN
('49,50,27') ;


It looks like when it is comparing a passed in String of what should be
integer-type values, it doesn't do sorting after it splits them into their
proper integer values. If you don't use quotes then the integers passed in
are sorted correctly.
This should possibly be fixed, because it is misleading if you can
successfully use queries with the '' quoted integers when they are sorted,
and then forget to sort them in another section you will not retrieve
anything and no errors are thrown.

Cheers,

Jamie Maher




-----Original Message-----
From: Jamie Maher (Names changed to protect the guilty!)
Sent: Thursday, May 15, 2003 10:06 AM
To: bugs@lists.mysql.com
Subject: RE: Optimizing error with IN clause?


For those of you on the mailing list:

I've included contents of the files listed below. I attached them
previously, which naturally wouldn't work with a mailing list! :)

Here is a test case which works for me every time.

mysql < tables.sql
mysql < works.sql
mysql < error.sql

In the works.sql I simply rearranged the order of the values in the IN
clause. The error.sql doesn't produce any results, and the EXPLAIN used on
that query states: "Impossible WHERE noticed after reading const tables"

Hope this helps,

Jamie Maher




--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Optimizing error with IN clause?

Hello,

Jamie Maher wrote:
> Hello folks,
>
> I think I solved my own problem.. I was surrounding my IN clause with
> quotes.
>
> To summarize it looks like this works:
>
> SELECT * FROM Production,ProductionStatusTypes
> WHERE Production.ProStatusID=ProductionStatusTypes.StatusID AND JobID IN
> ('27,49,50') ;
>
> SELECT * FROM Production,ProductionStatusTypes
> WHERE Production.ProStatusID=ProductionStatusTypes.StatusID AND JobID IN
> (49,50,27) ;
>
> BUT this doesn't:
>
> SELECT * FROM Production,ProductionStatusTypes
> WHERE Production.ProStatusID=ProductionStatusTypes.StatusID AND JobID IN
> ('49,50,27') ;
>
>
> It looks like when it is comparing a passed in String of what should be
> integer-type values, it doesn't do sorting after it splits them into their
> proper integer values. If you don't use quotes then the integers passed in
> are sorted correctly.
> This should possibly be fixed, because it is misleading if you can
> successfully use queries with the '' quoted integers when they are sorted,
> and then forget to sort them in another section you will not retrieve
> anything and no errors are thrown.

Nothing should be fixed in this case.

.... JobID IN > ('49,50,27') ...

you have IN() with single String parameter which makes it equivalent to:

.... JobID = '49,50,27' ...

Now because JobID is integer conversion rules are applied before comparison.

String '49,50,27' is converted to integer 49 as MySQL tries to intepret string as
number starting from beginning.


As you say you can use quoted numbers, but it is not what you are doing in your
query. What you quote is string consisting of digits and commas not separate numbers.

If it is:

.... JobID IN > ('49','50','27') ...

Now we have 3 different quoted numbers separated by comma which is same as:

.... JobID = '49' OR JobID = '50' OR JobID = '27' ...


Please note also that it is not good idea to quote numbers because MySQL compares
Integer against Integer faster than Integer against String value.


> Cheers,
>
> Jamie Maher

Best regards

--
I'm MySQL certified. Are you? -- http://www.mysql.com/certification
For technical support contracts, visit https://order.mysql.com/?ref=msal
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
<___/ www.mysql.com




--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

RE: Optimizing error with IN clause?

Hi Alexander,

Thanks, I wasn't aware exactly was going on, and now that I know I will not
make that mistake again. :)

Thanks for the prompt and informed replies!

Eagerly awaiting subqueries,

Jamie

P.s. Keep up the good work!

-----Original Message-----
From: Alexander Keremidarski
Sent: Thursday, May 15, 2003 5:08 PM
To: jmaher@linwoodhomes.com
Cc: peter@mysql.com; bugs@lists.mysql.com
Subject: Re: Optimizing error with IN clause?


Hello,

Jamie Maher wrote:



> This should possibly be fixed, because it is misleading if you can
> successfully use queries with the '' quoted integers when they are sorted,
> and then forget to sort them in another section you will not retrieve
> anything and no errors are thrown.

Nothing should be fixed in this case.

.... JobID IN > ('49,50,27') ...

you have IN() with single String parameter which makes it equivalent to:

.... JobID = '49,50,27' ...

Now because JobID is integer conversion rules are applied before comparison.

String '49,50,27' is converted to integer 49 as MySQL tries to intepret
string as
number starting from beginning.


As you say you can use quoted numbers, but it is not what you are doing in
your
query. What you quote is string consisting of digits and commas not separate
numbers.

If it is:

.... JobID IN > ('49','50','27') ...

Now we have 3 different quoted numbers separated by comma which is same as:

.... JobID = '49' OR JobID = '50' OR JobID = '27' ...


Please note also that it is not good idea to quote numbers because MySQL
compares
Integer against Integer faster than Integer against String value.


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org