408172 - [Oracle] Bug lists longer than 1000 bugs fail

Status: RESOLVED FIXED

(Bugzilla :: Query/Bug List, defect)

Description

[Guenter Huerkamp]

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

Comment 1

[Jeremy Baron]

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

Comment 2

[Xiaoou]

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

[Max Kanat-Alexander]

Oh, that is SO LAME.

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

Comment 4

[Xiaoou]

Attachment: v1

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

Comment 5

[c1541]

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 6

[timeless]

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 7

[timeless]

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 8

[timeless]

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.

Comment 9

[Max Kanat-Alexander]

Yes, it's only the number of parameters.

Comment 10

[Xiaoou]

Attachment: v2

Comment 11

[Xiaoou]

Attachment: v3

Comment 12

[Max Kanat-Alexander]

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 13

[Xiaoou]

Attachment: v4

Comment 14

[Max Kanat-Alexander]

Comment on attachment 297766 [details] [diff] [review]
v4

This doesn't apply to the current CVS HEAD.

Comment 15

[Xiaoou]

Attachment: v5

Comment 16

[Max Kanat-Alexander]

Comment on attachment 297770 [details] [diff] [review]
v5

Okay--passes tests and looks fine.

Comment 17

[Max Kanat-Alexander]

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