User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9b3pre) Gecko/2007121204 Minefield/3.0b3pre Build Identifier: The following query is not working: https://landfill.bugzilla.org/bugzilla-tip-oracle/buglist.cgi?query_format=advanced&short_desc_type=allwordssubstr&short_desc=a&long_desc_type=allwordssubstr&long_desc=&bug_file_loc_type=allwordssubstr&bug_file_loc=&status_whiteboard_type=allwordssubstr&status_whiteboard=&keywords_type=allwords&keywords=&bug_status=NEW&bug_status=ASSIGNED&bug_status=REOPENED&emailassigned_to1=1&emailtype1=substring&email1=&emailassigned_to2=1&emailreporter2=1&emailqa_contact2=1&emailcc2=1&emailtype2=substring&email2=&bugidtype=include&bug_id=&votes=&chfieldfrom=&chfieldto=Now&chfieldvalue=&cmdtype=doit&order=Reuse+same+sort+as+last+time&field0-0-0=noop&type0-0-0=noop&value0-0-0= Error: Software error: DBD::Oracle::db prepare failed: ORA-01795: maximum number of expressions in a list is 1000 (DBD ERROR: error possibly near <*> indicator at char 5163 in 'SELECT DISTINCT bugs.bug_id, MIN(group_control_map.membercontrol) FROM bugs INNER JOIN bug_group_map ON bugs.bug_id = bug_group_map.bug_id LEFT JOIN group_control_map ON group_control_map.product_id = bugs.product_id AND group_control_map.group_id = bug_group_map.group_id WHERE bugs.bug_id IN (2518,4138,2621,2622,806,4549,373,505,895,3427,1326,5820,3618,1095,4189,4177,3844,5102,5232,5566,5862,2921,3383,4941,4832,1766,3771,6130,673,1045,1256,1289,1380,1483,1549,1554,1745,1884,1891,1928,2078,2079,2366,2594,2669,2940,2946,3078,3149,3156,3274,3366,3702,3869,3970,4119,4158,4217,4219,4247,4411,4520,4541,4583,4718,4742,5025,5041,5119,5144,5155,5202,5290,5364,5387,3334,2474,4188,5966,3772,4085,4089,1294,2278,4031,2559,5125,5715,5454,4069,4405,1871,2524,4241,1266,627,628,5926,4252,2222,4770,4925,2197,2525,370,452,468,494,538,669,670,738,924,958,994,1019,1034,1058,1143,1181,1187,1194,1409,1463,1553,1627,1675,1679,1839,2312,2386,2416,2418,2521,2880,2910,2932,2956,2967,3217,3265,3282,3406,3470,3487,3612,3851,3931,3948,3967,3995,4005,4025,4054,4194,4403,4428,4573,4605,4608,4642,4682,4777,4895,5056,5237,5248,5363,5465,5491,5755,6089,944,3123,4455,5040,6053,6063,6072,6083,6100,6107,6168,5590,5592,5595,3483,3971,42,598,3099,1155,4134,5213,3167,1702,2520,1703,4280,698,625,1249,5533,5819,4471,6170,4294,2257,3455,4035,3866,3471,6180,1538,4169,2971,1741,3571,5359,4738,4330,4719,5159,3943,1704,4441,4315,2529,2530,2531,2532,2533,2534,2535,2210,2374,4858,4868,1366,2618,2619,2620,890,4966,4967,4776,4778,4779,4780,4781,4782,4783,4784,4785,4786,4787,4788,4789,4792,4793,4794,4795,4796,4798,4799,4800,4801,578,2542,525,1693,3421,2298,2299,3803,3966,4829,5718,489,614,365,3313,1183,5677,4000,1587,676,1310,4256,5967,4494,631,3684,339,345,366,393,410,421,441,498,549,583,584,589,599,613,649,681,689,693,711,725,778,784,790,798,801,804,810,819,875,88 ... ,1406,1569,1969,2110,2124,2463,3319,3677,4747,4854,4864,4899,5004,943,5722,5795,2115,4790,5653,3954,1723,3531,747,1666,1897,5013,4859,5084,5660,2243,3439,5696,1496,5650,3138,2263,3544,4433,4956,4948,313,5662,5706,5700,1188,15,369,379,392,420,548,749,782,815,726,4533,2262,4429,5375,4855,5949,5953,2006,4439,2875,2876,1051,946,5287,3301,720,1600,2433,3120,3359,4671,2094,2527,5047,2070,1098,4553,2635,3505,4676,5529,349,4319,1270,4461,2655,1547,2012,2580,4206,579,763,1072,1132,1222,1342,1360,1361,1367,1405,1415,1459,1534,1566,1611,1699,1753,1976,1994,2010,2013,2015,2137,2206,2215,2294,2388,2456,2937,2972,3048,3055,3094,3210,3212,3403,3619,3736,3812,3840,4002,4830,4845,4928,5207,5286,5440,5476,5503,5702,5716,6172,6182,2140,1280,491,3002,1962,1963,1964,449,2607,458,4080,1171,766,4078,1331,1772,4860,1734,4377,3841,882,660,3006,4702,367,5214,2437,1439,2144,3053,4872,4339,1433,4338,4528,2961,1925,2362,1696,1018,731,6016,5690,3187,1341,5615,5616,461,462,4715,3802,551,814,991,1100,1125,1172,1211,1259,1338,1345,1349,1410,1507,1577,1578,1636,1971,1990,1991,2048,2196,2268,2285,2411,2441,2651,2841,2870,2924,3351,3414,3518,3559,3599,3613,3638,3663,3678,3823,4001,4211,4524,4546,4547,4556,4575,4580,4631,4727,4834,4893,4935,5097,5180,5185,5467,5512,5654,5655,5981,6164,543,5066,1561,2085,5027,5091,5215,5430,5432,5634,5667,5707,5808,5822,6054,6151,1224,4106,4416,6176,432,2630,4579,4384,4602,4712,2667,4313,1681,2373,5001,4945,318,753,839,1641,4283,4357,3188,2861,6162,309,4652,2390,4811,4123,5067,2892,785,3430,3865,4212,1552,1618,1902,5428,1542,3789,3549,4564,1329,2898,467,550,802,2487,5979,1555,3171,539,843,4056,1613,3017,4638,3341,4816,902,1229,1325,1505,2142,3021,3426,4484,4884,1869,3412,826,1191,873,2253,1900,2509,2936,6139,5243,2247,1436,618,4903,364,5887,74,888,675,1795,1466,1444,928,1924,2246,2513,2885,3370,3490,3824,3003,2291,4554,827,1141,4400,3810,1653,983,2242,3610,741,417,794,1089,2415,2989,3128,3582,4923,5026,5298,5169,887,575,3550,1931,1535,41,5204,1,3) GROUP BY bugs.bug_id"] at Bugzilla/DB/Oracle.pm line 391 Bugzilla::DB::Oracle::prepare('undef', 'SELECT DISTINCT bugs.bug_id, MIN(group_control_map.membercont...') called at /var/www/html/bugzilla-tip-oracle/buglist.cgi line 1079 Reproducible: Always Steps to Reproduce: 1. 2. 3. Actual Results: Error Expected Results: Buglist

looks like this is only happening when a buglist will have more than 1000 bugs.

Yes, we've hit is a hard limit to the number of parameters that Oracle allows in the IN(...) clause. I think we need to restructure the query to not use an IN(...) clause, or to ( IN(...) OR IN(...) ).

Oh, that is SO LAME. There's probably some way to write that query that avoids an IN clause entirely, though.

Add a new sub sql_in to work around the Oracle limitaion of in-list

Should the line > + return join(" OR ", @in_str); Have brackets around it just in case there are other terms in the where clause? + return "(" . join(" OR ", @in_str) . ")";

Comment on attachment 294007 [details] [diff] [review] v1 >+ $dbh->selectcol_arrayref( >+ 'SELECT DISTINCT new_status >+ FROM status_workflow sw1 >+ WHERE NOT EXISTS >+ (SELECT * FROM status_workflow sw2 >+ WHERE sw2.old_status != sw1.new_status AND ' >+ . $dbh->sql_in('sw2.old_status', @$bug_status_ids) >+ . ' AND NOT EXISTS >+ (SELECT * FROM status_workflow sw3 >+ WHERE sw3.new_status = sw1.new_status >+ AND sw3.old_status = sw2.old_status))'); couldn't you change this to match the style above? > my $records = Bugzilla->dbh->selectall_arrayref("SELECT $columns > FROM attachments >+ WHERE " >+ . Bugzilla->dbh->sql_in( >+ 'attach_id', @$ids) >+ . " ORDER BY attach_id", > { Slice => {} }); something like: >+ my $records = Bugzilla->dbh->selectall_arrayref( >+ "SELECT $columns >+ FROM attachments >+ WHERE " >+ . Bugzilla->dbh->sql_in('attach_id', @$ids) >+ . " ORDER BY attach_id", > { Slice => {} });

Comment on attachment 294007 [details] [diff] [review] v1 +sub sql_in { + my ($self, $column_name, @in_list) = @_; + return $self->SUPER::sql_in($column_name, @in_list) if $#in_list < 1000; From experience, this is wrong. Today, bug numbers are 6 digits, but I remember when bug numbers were 5 digits. And I know places where bug numbers or 7 or 8 digits. There's code in bugzilla which tries to warn you when the buglist won't fit in your cookie, but it breaks every time we add a digit to bugzilla, because it's based on the number of bugs, and does not consider how long a bug number can be. Your code needs to handle this case. I think 400 bugs with 7 digits should easily bust the limit you're actually working around. Safer code would probably be: $long = $self->SUPER::sql_in($column_name, @in_list); return $long if length $long < ACTUAL_DBAPI_LIMIT;

Comment on attachment 294007 [details] [diff] [review] v1 Sorry, I made a bad assumption. If my assumption is wrong, please add a comment before sql_in explaining precisely what you're working around (that the limit is on the number of parameters to IN - not the length of the parameters), preferably include a link to documentation / a tech note about it.

Yes, it's only the number of parameters.

Comment on attachment 295322 [details] [diff] [review] v3 >+ "WHERE " . $dbh->sql_in('bugs.bug_id', @bugidlist) . You should be passing an arrayref, not an array. >@@ -1161,19 +1161,24 @@ >+ $dbh->selectcol_arrayref( >+ 'SELECT DISTINCT new_status >+ FROM status_workflow sw1 Thanks for reformatting this. FROM should align with SELECT, not DISTINCT. >+ (SELECT * FROM status_workflow sw2 >+ WHERE sw2.old_status != sw1.new_status WHERE should align with SELECT, not with FROM. >Index: process_bug.cgi >+ @newprod_ids = @{$dbh->selectcol_arrayref( >+ "SELECT DISTINCT product_id >+ FROM bugs >+ WHERE " >+ . $dbh->sql_in('bug_id', @idlist))}; Nit: This would probably be better: @newprod_ids = @{$dbh->selectcol_arrayref( "SELECT DISTINCT product_id FROM bugs WHERE " . $dbh->sql_in('bug_id', \@idlist >Index: Bugzilla/Attachment.pm >+ my $records = Bugzilla->dbh->selectall_arrayref( >+ "SELECT $columns >+ FROM attachments >+ WHERE " >+ . Bugzilla->dbh->sql_in('attach_id', @$ids) Nit: It would be better to add a "my $dbh = Bugzilla->dbh" above this statement, and then just use $dbh. >Index: Bugzilla/DB.pm > our @_abstract_methods = qw(REQUIRED_VERSION PROGRAM_NAME DBD_VERSION > new sql_regexp sql_not_regexp sql_limit sql_to_days >- sql_date_format sql_interval >+ sql_date_format sql_interval sql_in The method is not abstract, and thus does not need to be there. >+=item B<Description> >+ >+Returns SQL syntax for operator IN() "for the C<IN ()> operator" Add: "Only necessary where an C<IN> clause can have more than 1000 items." >+Abstract method, should be overridden by database specific code. Untrue. >+=item C<$column_name> - column_name (e.g. 'bugid') (string) "Column name (e.g. C<bug_id>)" >+Formatted SQL for operator IN (scalar) "for the C<IN> operator" >Index: Bugzilla/DB/Oracle.pm >+sub sql_in { >+ my ($self, $column_name, @in_list) = @_; When that changes to $in_list, remember to copy it inside this function so that you don't modify the array from the caller's perspective.

Comment on attachment 297766 [details] [diff] [review] v4 This doesn't apply to the current CVS HEAD.

Comment on attachment 297770 [details] [diff] [review] v5 Okay--passes tests and looks fine.

Checking in buglist.cgi; /cvsroot/mozilla/webtools/bugzilla/buglist.cgi,v <-- buglist.cgi new revision: 1.369; previous revision: 1.368 done Checking in process_bug.cgi; /cvsroot/mozilla/webtools/bugzilla/process_bug.cgi,v <-- process_bug.cgi new revision: 1.398; previous revision: 1.397 done Checking in Bugzilla/Attachment.pm; /cvsroot/mozilla/webtools/bugzilla/Bugzilla/Attachment.pm,v <-- Attachment.pm new revision: 1.53; previous revision: 1.52 done Checking in Bugzilla/Bug.pm; /cvsroot/mozilla/webtools/bugzilla/Bugzilla/Bug.pm,v <-- Bug.pm new revision: 1.227; previous revision: 1.226 done Checking in Bugzilla/DB.pm; /cvsroot/mozilla/webtools/bugzilla/Bugzilla/DB.pm,v <-- DB.pm new revision: 1.108; previous revision: 1.107 done Checking in Bugzilla/Flag.pm; /cvsroot/mozilla/webtools/bugzilla/Bugzilla/Flag.pm,v <-- Flag.pm new revision: 1.88; previous revision: 1.87 done Checking in Bugzilla/Milestone.pm; /cvsroot/mozilla/webtools/bugzilla/Bugzilla/Milestone.pm,v <-- Milestone.pm new revision: 1.12; previous revision: 1.11 done Checking in Bugzilla/Object.pm; /cvsroot/mozilla/webtools/bugzilla/Bugzilla/Object.pm,v <-- Object.pm new revision: 1.22; previous revision: 1.21 done Checking in Bugzilla/Search.pm; /cvsroot/mozilla/webtools/bugzilla/Bugzilla/Search.pm,v <-- Search.pm new revision: 1.150; previous revision: 1.149 done Checking in Bugzilla/DB/Oracle.pm; /cvsroot/mozilla/webtools/bugzilla/Bugzilla/DB/Oracle.pm,v <-- Oracle.pm new revision: 1.5; previous revision: 1.4 done