1
Select * From Table_Name Where MyColumnID NOT IN (1,2,3,4,5);

The above sql statement works perfectly fine, but when I try increasing the elements specifically pass 1000 (for my case) for e.g.

Select * From Table_Name Where MyColumnID NOT IN (1,2,3,4,5,...1000 and more);

It only returns a few rows or sometimes non at all. Any number of elements that is less than 1000 returns correctly for the NOT IN condition

Then, i tried using the IN condition as follows:

Select * From Table_Name Where MyColumnID IN (1,2,3,4,5,...9000 and more);

It works perfectly fine and as expected. if it works for the IN conditions, why would it not work for the NOT IN conditions?

I am really at a loss, could it be there is some variable I have missed somewhere?

Things that I have set or checked:

1) Max_allowed_packet = 1000000000;

2) ID contains no Null Values

3) Name of ID = 'StockID'

Ryan Tan
  • 320
  • 1
  • 10
  • Why are you adding the condition values hardcoded? They are too many to be hardcoded. – Lamar Aug 13 '18 at 06:39
  • my apologies, i was typing it real quick and i have missed out some important parts in the query, i have amended the question. @AnkitJaiswal – Ryan Tan Aug 13 '18 at 06:47
  • @Lamar, of course it would be a pretty big hassle to manually type all 8000 elements in. i am using Vb.net to loop all the elements in a and output it in a list. – Ryan Tan Aug 13 '18 at 06:49
  • 2
    My point is to that your approach might be the wrong way. Try and keep those values in a table or something, or try to reduce those values to fewer ranges or categories. – Lamar Aug 13 '18 at 07:11
  • @lamar, yes having such a range seems pretty ridiculous. but it just puzzles me, why would there be a limit for NOT IN condition and not for IN condition. Specifically for my case the limit was 1000 elements and not more. – Ryan Tan Aug 13 '18 at 07:22
  • 1
    @RyanTan I don't think there is a limit for NOT IN apart from max_allowed_packet. – Ankit Jaiswal Aug 13 '18 at 08:39
  • If max allowed packet limit were breached, then you would get an error message, not partial result. I would take a look at how you generate that sql query and how the final query looks like when it does not return all results you expect. – Shadow Aug 13 '18 at 08:59
  • Thanks for taking a look into this. if it would help, is there any way i could send the query of the: 1) Create Table Query 2) insert statement for all the data 3) query for the 1001 (problem) 4 ) query for the 1000th row(working) – Ryan Tan Aug 13 '18 at 09:10
  • What MySQL version are you using? – Paul Campbell Aug 13 '18 at 10:02
  • Server Information: MySQL 5.0.22-community-nt via TCP/IP Client Information: MYSQL Client Version 5.1.11 – Ryan Tan Aug 13 '18 at 10:11
  • Create a test scenario on sqlfiddle.com. Btw, your mysql is really old. – Shadow Aug 13 '18 at 10:35

1 Answers1

1

Looks like you may have tripped over a very old bug and you are going to have to upgrade your version of MySQL.

See

https://bugs.mysql.com/bug.php?id=15872

If you read to the bottom of the report and you'll see that this bug was fixed in 5.0.25

[15 Aug 2006 17:08] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/10495

ChangeSet@1.2257, 2006-08-15 21:08:22+04:00, sergefp@mysql.com +3 -0
  BUG#21282: Incorrect query results for "t.key NOT IN (<big const list>) 
  In fix for BUG#15872, a condition of type "t.key NOT IN (c1, .... cN)"
  where N>1000, was incorrectly converted to
    (-inf < X < c_min) OR (c_max < X)
  Now this conversion is removed, we dont produce any range lists for such
  conditions.

[29 Aug 2006 13:23] Evgeny Potemkin
Fixed in 5.0.25

[4 Sep 2006 11:41] Evgeny Potemkin
Fixed in 5.1.12
Paul Campbell
  • 1,712
  • 2
  • 10
  • 17
  • This was exactly it! I should have been more thorough.. Thanks for the guidance Paul. – Ryan Tan Aug 14 '18 at 02:41
  • When the problem just doesn't seem to make any sort of sense and especially with a very old version of the product it's always worth having a scan of the bugs database. Will you be upgrading your way out of this problem or adopting approach suggested by @lamar which seems very sensible to me? – Paul Campbell Aug 14 '18 at 07:56
  • Your suggestion is greatly appreciated! Yes, i will be upgrading to a later version and i have no intention on implementing such methods as in my question into a system (which proves to be inefficient). It was just an itch of just wanting to know why am i not getting the expected results. Thanks once again. – Ryan Tan Aug 14 '18 at 08:39