Closed
Bug 408172
Opened 17 years ago
Closed 17 years ago
[Oracle] Bug lists longer than 1000 bugs fail
Categories
(Bugzilla :: Query/Bug List, defect)
Tracking
()
RESOLVED
FIXED
Bugzilla 3.2
People
(Reporter: guenter-huerkamp, Assigned: xiaoou.wu)
References
()
Details
Attachments
(1 file, 4 obsolete files)
(deleted),
patch
|
mkanat
:
review+
|
Details | Diff | Splinter Review |
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
Reporter | ||
Updated•17 years ago
|
Version: unspecified → 3.1.2
Comment 1•17 years ago
|
||
looks like this is only happening when a buglist will have more than 1000 bugs.
Status: UNCONFIRMED → NEW
Ever confirmed: true
OS: Linux → All
Hardware: PC → All
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(...) ).
Comment 3•17 years ago
|
||
Oh, that is SO LAME.
There's probably some way to write that query that avoids an IN clause entirely, though.
Summary: Query bugs at https://landfill.bugzilla.org/bugzilla-tip-oracle/ not working → [Oracle] Bug lists longer than 1000 bugs fail
Add a new sub sql_in to work around the Oracle limitaion of in-list
Attachment #294007 -
Flags: review?(mkanat)
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;
Attachment #294007 -
Flags: review-
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.
Attachment #294007 -
Flags: review-
Comment 9•17 years ago
|
||
Yes, it's only the number of parameters.
Updated•17 years ago
|
Assignee: query-and-buglist → xiaoou.wu
Target Milestone: --- → Bugzilla 3.2
Assignee | ||
Comment 10•17 years ago
|
||
Attachment #294007 -
Attachment is obsolete: true
Attachment #295316 -
Flags: review?(mkanat)
Attachment #294007 -
Flags: review?(mkanat)
Assignee | ||
Comment 11•17 years ago
|
||
Attachment #295316 -
Attachment is obsolete: true
Attachment #295322 -
Flags: review?(mkanat)
Attachment #295316 -
Flags: review?(mkanat)
Comment 12•17 years ago
|
||
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.
Attachment #295322 -
Flags: review?(mkanat) → review-
Assignee | ||
Comment 13•17 years ago
|
||
Attachment #295322 -
Attachment is obsolete: true
Attachment #297766 -
Flags: review?(mkanat)
Comment 14•17 years ago
|
||
Comment on attachment 297766 [details] [diff] [review]
v4
This doesn't apply to the current CVS HEAD.
Attachment #297766 -
Flags: review?(mkanat) → review-
Assignee | ||
Comment 15•17 years ago
|
||
Attachment #297766 -
Attachment is obsolete: true
Attachment #297770 -
Flags: review?(mkanat)
Comment 16•17 years ago
|
||
Comment on attachment 297770 [details] [diff] [review]
v5
Okay--passes tests and looks fine.
Attachment #297770 -
Flags: review?(mkanat) → review+
Comment 17•17 years ago
|
||
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
Status: NEW → RESOLVED
Closed: 17 years ago
Resolution: --- → FIXED
Updated•17 years ago
|
Flags: approval+
You need to log in
before you can comment on or make changes to this bug.
Description
•