List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:February 25 2005 2:44am
Subject:RE: Is there something broken with IN()? -- SOLVED
View as plain text  
My bad. As it turns out, there are duplicates in the IN() listing. It must
be that mysql 'uniques' the list before operating on it. 

> -----Original Message-----
> From: Daevid Vincent [mailto:daevid@stripped] 
> Sent: Thursday, February 24, 2005 5:14 PM
> To: mysql@stripped
> Subject: Is there something broken with IN()? WAS: How can I 
> find the records that are NOT IN the list
> 
> Now I'm really confused. I decided to just brute force this 
> while I waited
> for a real answer. I threw in a couple test numbers I knew would fail
> 5555555 and 66666. How come none of the values aside from the 
> two I threw in
> for good measure are failing?! Is there something broken with 
> "IN()" on
> v4.0.18, for pc-linux-gnu on i686
> 
> <?php
> $foo = array (5555555, 11704, 10144, 11842, 11299, 11192, 
> 11563, 11378,
> 10343, 66666, 10752, 10626, 11916, 11456, 11813, 11337, 
> 10219, 11356, 11800,
> 10243, 10251, 11357, 11353, 11267, 12110, 200044, 11875, 11060, 10233,
> 10865, 10264, 10857, 10659, 10266, 10987, 11317, 11868, 
> 10858, 11087, 11088,
> 10260, 11321, 11350, 10247, 10258, 10248, 11232, 10588, 
> 11086, 11828, 10055,
> 11347, 10278, 11349, 10261, 11499, 11351, 11316, 12284, 
> 12240, 12265, 11340,
> 10708, 11041, 11853, 12255, 11507, 11788, 10067, 10888, 
> 11875, 11333, 10867,
> 10938, 11030, 10654, 10538, 10918, 11068, 11237, 11060, 
> 10127, 10495, 10035,
> 10294, 10173, 10098, 10282, 10383, 10049, 10076, 10277, 
> 10106, 10541, 10176,
> 10131, 10252, 11051, 11152, 11932, 11318, 10886, 10605, 
> 10029, 11857, 10549,
> 10329, 11510, 10539);
> echo "foo = ".count($foo)."<BR>";
> 
> $i = 1;
> foreach ($foo as $k => $v)
> {
> 	echo $i++." ";
> 	$sth = SQL_QUERY('SELECT id FROM mytable WHERE id = 
> '.$v.' LIMIT 1',
> true);
> 	if ($sth)
> 	{
> 		if (SQL_NUM_ROWS($sth) < 1) echo "<B>CANNOT FIND:
> ".$v."</B><BR>";
> 	}
> }
> ?>
> 
> foo = 109
> 1 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 5555555
> LIMIT 1
> CANNOT FIND VULN: 5555555
> 2 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 11704
> LIMIT 1
> 3 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 10144
> LIMIT 1
> 4 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 11842
> LIMIT 1
> 5 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 11299
> LIMIT 1
> 6 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 11192
> LIMIT 1
> 7 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 11563
> LIMIT 1
> 8 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 11378
> LIMIT 1
> 9 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 10343
> LIMIT 1
> 10 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 66666
> LIMIT 1
> CANNOT FIND VULN: 66666
> 11 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 10752
> LIMIT 1
> 12 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 10626
> LIMIT 1
> 13 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 11916
> LIMIT 1
> 14 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 11456
> LIMIT 1
> 15 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 11813
> LIMIT 1
> 16 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 11337
> LIMIT 1
> 17 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 10219
> LIMIT 1
> 18 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 11356
> LIMIT 1
> 19 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 11800
> LIMIT 1
> 20 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 10243
> LIMIT 1
> 21 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 10251
> LIMIT 1
> 22 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 11357
> LIMIT 1
> 23 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 11353
> LIMIT 1
> 24 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 11267
> LIMIT 1
> 25 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 12110
> LIMIT 1
> 26 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 200044
> LIMIT 1
> 27 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 11875
> LIMIT 1
> 28 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 11060
> LIMIT 1
> 29 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 10233
> LIMIT 1
> 30 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 10865
> LIMIT 1
> 31 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 10264
> LIMIT 1
> 32 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 10857
> LIMIT 1
> 33 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 10659
> LIMIT 1
> 34 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 10266
> LIMIT 1
> 35 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 10987
> LIMIT 1
> 36 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 11317
> LIMIT 1
> 37 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 11868
> LIMIT 1
> 38 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 10858
> LIMIT 1
> 39 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 11087
> LIMIT 1
> 40 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 11088
> LIMIT 1
> 41 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 10260
> LIMIT 1
> 42 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 11321
> LIMIT 1
> 43 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 11350
> LIMIT 1
> 44 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 10247
> LIMIT 1
> 45 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 10258
> LIMIT 1
> 46 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 10248
> LIMIT 1
> 47 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 11232
> LIMIT 1
> 48 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 10588
> LIMIT 1
> 49 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 11086
> LIMIT 1
> 50 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 11828
> LIMIT 1
> 51 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 10055
> LIMIT 1
> 52 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 11347
> LIMIT 1
> 53 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 10278
> LIMIT 1
> 54 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 11349
> LIMIT 1
> 55 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 10261
> LIMIT 1
> 56 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 11499
> LIMIT 1
> 57 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 11351
> LIMIT 1
> 58 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 11316
> LIMIT 1
> 59 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 12284
> LIMIT 1
> 60 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 12240
> LIMIT 1
> 61 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 12265
> LIMIT 1
> 62 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 11340
> LIMIT 1
> 63 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 10708
> LIMIT 1
> 64 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 11041
> LIMIT 1
> 65 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 11853
> LIMIT 1
> 66 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 12255
> LIMIT 1
> 67 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 11507
> LIMIT 1
> 68 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 11788
> LIMIT 1
> 69 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 10067
> LIMIT 1
> 70 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 10888
> LIMIT 1
> 71 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 11875
> LIMIT 1
> 72 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 11333
> LIMIT 1
> 73 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 10867
> LIMIT 1
> 74 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 10938
> LIMIT 1
> 75 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 11030
> LIMIT 1
> 76 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 10654
> LIMIT 1
> 77 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 10538
> LIMIT 1
> 78 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 10918
> LIMIT 1
> 79 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 11068
> LIMIT 1
> 80 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 11237
> LIMIT 1
> 81 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 11060
> LIMIT 1
> 82 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 10127
> LIMIT 1
> 83 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 10495
> LIMIT 1
> 84 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 10035
> LIMIT 1
> 85 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 10294
> LIMIT 1
> 86 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 10173
> LIMIT 1
> 87 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 10098
> LIMIT 1
> 88 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 10282
> LIMIT 1
> 89 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 10383
> LIMIT 1
> 90 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 10049
> LIMIT 1
> 91 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 10076
> LIMIT 1
> 92 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 10277
> LIMIT 1
> 93 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 10106
> LIMIT 1
> 94 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 10541
> LIMIT 1
> 95 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 10176
> LIMIT 1
> 96 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 10131
> LIMIT 1
> 97 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 10252
> LIMIT 1
> 98 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 11051
> LIMIT 1
> 99 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 11152
> LIMIT 1
> 100 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 11932
> LIMIT 1
> 101 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 11318
> LIMIT 1
> 102 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 10886
> LIMIT 1
> 103 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 10605
> LIMIT 1
> 104 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 10029
> LIMIT 1
> 105 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 11857
> LIMIT 1
> 106 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 10549
> LIMIT 1
> 107 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 10329
> LIMIT 1
> 108 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 11510
> LIMIT 1
> 109 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE 
> scan_id = 10539
> LIMIT 1
> total = 110
> 
> 
> > -----Original Message-----
> > From: Daevid Vincent [mailto:daevid@stripped] 
> > Sent: Thursday, February 24, 2005 4:20 PM
> > To: mysql@stripped
> > Subject: How can I find the records that are NOT IN the list
> > 
> > I have an "IN()" list of 107 IDs (PK) out of about 6000 possible.
> > 
> > I do this query and I get 105 rows back. 
> > 
> > I want to know which two [107 - 105 = 2] of the IDs in the 
> > "IN()" list are
> > absent?
> > 
> > # 105 rows 
> > SELECT count(*)
> > FROM mytable  
> > WHERE id IN (11704, 10144, 11842, 11299, 11192, 11563, 11378, 
> > 10343, 10752,
> > 10626, 11916, 11456, 11813, 11337, 10219, 11356, 11800, 
> > 10243, 10251, 11357,
> > 11353, 11267, 12110, 200044, 11875, 11060, 10233, 10865, 
> 10264, 10857,
> > 10659, 10266, 10987, 11317, 11868, 10858, 11087, 11088, 
> > 10260, 11321, 11350,
> > 10247, 10258, 10248, 11232, 10588, 11086, 11828, 10055, 
> > 11347, 10278, 11349,
> > 10261, 11499, 11351, 11316, 12284, 12240, 12265, 11340, 
> > 10708, 11041, 11853,
> > 12255, 11507, 11788, 10067, 10888, 11875, 11333, 10867, 
> > 10938, 11030, 10654,
> > 10538, 10918, 11068, 11237, 11060, 10127, 10495, 10035, 
> > 10294, 10173, 10098,
> > 10282, 10383, 10049, 10076, 10277, 10106, 10541, 10176, 
> > 10131, 10252, 11051,
> > 11152, 11932, 11318, 10886, 10605, 10029, 11857, 10549, 
> 10329, 11510,
> > 10539);
> > 
> > 
> > 
> > -- 
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:    
> > http://lists.mysql.com/mysql?unsub=1
> > 
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    
> http://lists.mysql.com/mysql?unsub=1
> 

Thread
How can I find the records that are NOT IN the listDaevid Vincent25 Feb
  • Is there something broken with IN()? WAS: How can I find the records that are NOT IN the listDaevid Vincent25 Feb
    • RE: Is there something broken with IN()? -- SOLVEDDaevid Vincent25 Feb