Last Comment Bug 780053 - [Oracle] Oracle crashes when listing keywords, tags or flags in buglists with "ORA-30482: DISTINCT option not allowed"
: [Oracle] Oracle crashes when listing keywords, tags or flags in buglists with...
Status: RESOLVED FIXED
:
Product: Bugzilla
Classification: Server Software
Component: Query/Bug List (show other bugs)
: 4.3.2
: All All
: -- major (vote)
: Bugzilla 4.2
Assigned To: andershol
: default-qa
Mentors:
Depends on: 108243 616192 783879
Blocks: 858911
  Show dependency treegraph
 
Reported: 2012-08-02 18:15 PDT by Frédéric Buclin
Modified: 2013-04-06 04:00 PDT (History)
2 users (show)
LpSolit: approval+
LpSolit: approval4.4+
LpSolit: blocking4.4+
LpSolit: approval4.2+
See Also:
QA Whiteboard:
Iteration: ---
Points: ---


Attachments
Example patch (1.60 KB, patch)
2012-10-17 09:54 PDT, andershol
LpSolit: review+
Details | Diff | Splinter Review

Description Frédéric Buclin 2012-08-02 18:15:19 PDT
When displaying the "Tags" column in buglists, Oracle crashes with:

DBD::Oracle::db prepare failed: ORA-00909: invalid number of arguments (DBD ERROR: error possibly near <*> indicator at char 326

..., group_concat(T_CLOB_DELIM(<*>CONCAT(map_tag.name), ', '))  tag, ...
Comment 1 Frédéric Buclin 2012-08-02 18:36:23 PDT
Oracle also crashes when displaying flags in buglists:

DBD::Oracle::db prepare failed: ORA-30482: DISTINCT option not allowed for this function (DBD ERROR: error possibly near <*> indicator at char 313

..., group_concat(<*>T_CLOB_DELIM(DISTINCT CONCAT(map_flagtypes.name, map_flags.status), ', '))  flagtypes_name, ...

No idea if the root cause is the same as in comment 0 or not.
Comment 2 Frédéric Buclin 2012-08-19 06:29:27 PDT
(In reply to Frédéric Buclin from comment #0)
> When displaying the "Tags" column in buglists, Oracle crashes with:
> 
> DBD::Oracle::db prepare failed: ORA-00909: invalid number of arguments

Actually, this error message was my fault, and I fixed it in bug 783879. Now the error message is the same as for flags:

DBD::Oracle::db prepare failed: ORA-30482: DISTINCT option not allowed for this function (DBD ERROR: error possibly near <*> indicator at char 313

..., group_concat(<*>T_CLOB_DELIM(DISTINCT map_tag.name, ', ')) tag


So the problem is about DISTINCT appearing in group_concat(). But I have no idea how to fix this. This code is too cryptic for me: http://mxr.mozilla.org/mozilla/source/webtools/bugzilla/Bugzilla/DB/Oracle.pm#520
Comment 3 Frédéric Buclin 2012-08-19 06:34:12 PDT
Even if I remove DISTINCT from the SQL query, Oracle would still fail:

ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275
ORA-06512: at "SYS.DBMS_LOB", line 366
ORA-06512: at "BUGS.T_GROUP_CONCAT", line 19 (DBD ERROR: OCIStmtFetch)
Comment 4 David Taylor 2012-08-29 01:41:01 PDT
I apparently managed to bodge a fix for this some time ago, although I can't recall my thought process at the time.  Unfortunately I don't have much time to clean this up at the moment, but I'm logging it here for posterity in case it helps someone else (hopefully I haven't missed anything this depends on)...

sub sql_group_concat {
    my ($self, $text, $separator) = @_;
    $separator //= $self->quote(', ');

#DT INSERT START 2010-06-08
    my $distinct = '';
    if ($text =~ m/^\s*(DISTINCT)\b/i)
    {
        $distinct = 'DISTINCT';
        $text =~ s/DISTINCT//i;
    }
#DT INSERT  END  2010-06-08

#DT REPLACE START 2010-06-08
# XXX this is ugly - limits to 4k, but my DBI crashes randomly otherwise...
    return "dbms_lob.substr(group_concat($distinct T_CLOB_DELIM($text, $separator)), 4000, 1)";
#DT REPLACE  END  2010-06-08
}

and in bz_setup_database:

    if ( !@$t_clob_delim ) {
        $self->do("CREATE OR REPLACE TYPE T_CLOB_DELIM AS OBJECT "
              . "( p_CONTENT CLOB, p_DELIMITER VARCHAR2(256)"
#DT INSERT START 2010-07-09
              . ", MAP MEMBER FUNCTION T_CLOB_DELIM_ToInt return VARCHAR2"
#DT INSERT END   2010-07-09
              . ");");

#DT INSERT START 2010-07-09
        $self->do("CREATE OR REPLACE TYPE BODY T_CLOB_DELIM IS
                      MAP MEMBER FUNCTION T_CLOB_DELIM_ToInt return VARCHAR2 is
                      BEGIN
                          RETURN p_CONTENT;
                      END;
                  END;");
#DT INSERT  END  2010-07-09
    }
Comment 5 andershol 2012-10-17 09:54:35 PDT
Created attachment 672369 [details] [diff] [review]
Example patch

Oracle doesn't have a group_concat-funcion (until LISTAGG in verison 11), so bugzilla have a user defined aggregate that to add this. But this mean that the distinct isn't allowed where it is currently placed, so this fails (dual is just a build-in table with one row):
SQL> select group_concat(T_CLOB_DELIM(DISTINCT name, ', ')) tag
  2  from (select 'bar' name from dual union all select 'bar' from dual);
select group_concat(T_CLOB_DELIM(DISTINCT name, ', ')) tag
                    *
ERROR at line 1:
ORA-30482: DISTINCT option not allowed for this function

Moving the distinct before the type constructor T_CLOB_DELIM fails if the type is no comparible:
SQL> select group_concat(DISTINCT T_CLOB_DELIM(name, ', ')) tag
  2  from (select 'bar' name from dual union all select 'bar' from dual);
select group_concat(DISTINCT T_CLOB_DELIM(name, ', ')) tag
                             *
ERROR at line 1:
ORA-22901: cannot compare nested table or VARRAY or LOB attributes of an object type

Adding the "MAP MEMBER FUNCTION" fixes this. But the custom aggregate doesn't handle null values (e.g. if the left join doesn't return any rows), so this fails (in oracle empty string is the same as null):
SQL> select group_concat(DISTINCT T_CLOB_DELIM(name, ', ')) tag
  2  from (select '' name from dual union all select 'bar' from dual);
ERROR:
ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified:
ORA-22275
ORA-06512: at "SYS.DBMS_LOB", line 366
ORA-06512: at "BUGS.T_GROUP_CONCAT", line 19

This patch
- Moves the "distinct" (a bit differently that done by David)
- Adds the "MAP MEMBER FUNCTION" (copied from David), and makes the "CREATE OR REPLACE TYPE T_CLOB_DELIM" run on every run to update old installations, but this made it nessesary to add a "DROP TYPE T_GROUP_CONCAT" since this type depended on T_CLOB_DELIM.
- Includes the substring from David, that clips the result to 4000 characters. But this might be a problem since the sql_group_concat function seems to be used from filling the bugs_fulltext table.
- Replaces the null values with a space and trims the string. This is somewhat of a hack.
Comment 6 Frédéric Buclin 2012-10-17 10:15:33 PDT
(In reply to andershol from comment #5)
> - Includes the substring from David, that clips the result to 4000 characters.

Is this a hard limit in Oracle? I remember having seen this limit elsewhere in the Oracle code, but I don't remember why this limit exists.

I will test your patch with my local installation, and if it works, I will commit it. Thanks for your help! :)
Comment 7 andershol 2012-10-17 11:22:32 PDT
(In reply to Frédéric Buclin from comment #6)
> (In reply to andershol from comment #5)
> > - Includes the substring from David, that clips the result to 4000 characters.
> Is this a hard limit in Oracle?
Oracle have two string types varchar2, that can be manipulated and retrieved just like other datatypes, but can only hold 4000 characters, and clob (character large object), that can only be manipulated in limited ways and have special access methods, but can hold much more data. So dbms_lob.substr is a way to convert a clob to a varchar. By Davids comments it might be a dbi problem. Maybe it would be better to remove the substr and handle any errors (which might have been fixed in the oracle driver) in a followup.
Comment 8 Frédéric Buclin 2012-10-17 16:15:24 PDT
Comment on attachment 672369 [details] [diff] [review]
Example patch

>+    return "trim(dbms_lob.substr(group_concat($distinct T_CLOB_DELIM(NVL($rest, ' '), $separator)), 4000, 1))";

I tested the patch as is and without trim(dbms_lob.substr(.., 4000, 1)) and it works fine in both cases. As suggested in the previous comments, it seems safer to not cut the data at 4000 characters, so I'm going to commit the patch with the line above replaced by:

    return "group_concat($distinct T_CLOB_DELIM(NVL($rest, ' '), $separator))";

David, could you tell us which version of DBI and DBD::Oracle do you have, and which action(s) you did to trigger a crash?


Thanks to both of you for the help! I greatly appreciate. :) r=LpSolit
Comment 9 Frédéric Buclin 2012-10-17 16:17:28 PDT
@andershol: what's your real name so that I can add you (and David) as author of the patch?
Comment 10 Frédéric Buclin 2012-10-17 16:19:32 PDT
For the record, I tested the patch with:

Checking for                  DBI (v1.614)    ok: found v1.622 
Checking for           DBD-Oracle (v1.19)     ok: found v1.50 
Checking for               Oracle (v10.02.0)  ok: found v10.02.0000
Comment 11 Frédéric Buclin 2012-10-18 16:09:31 PDT
Committing to: bzr+ssh://lpsolit%40gmail.com@bzr.mozilla.org/bugzilla/trunk/
modified Bugzilla/DB/Oracle.pm
Committed revision 8441.

Committing to: bzr+ssh://lpsolit%40gmail.com@bzr.mozilla.org/bugzilla/4.4/
modified Bugzilla/DB/Oracle.pm
Committed revision 8428.
Comment 12 Frédéric Buclin 2012-10-18 16:20:02 PDT
I decided to also take the patch for 4.2.4 as it applies cleanly:

Committing to: bzr+ssh://lpsolit%40gmail.com@bzr.mozilla.org/bugzilla/4.2/
modified Bugzilla/DB/Oracle.pm
Committed revision 8155.

Note You need to log in before you can comment on or make changes to this bug.