461756 - [Oracle] collectstats.pl --regenerate uses PL/SQL reserved word in query

Status: RESOLVED FIXED

(Bugzilla :: Database, defect)

Description

[Dave Taylor]

User-Agent:       Mozilla/5.0 (Windows; U; Windows NT 5.1; en-GB; rv:1.9.0.3) Gecko/2008092417 Firefox/3.0.3
Build Identifier: Bugzilla 3.2rc1

The query within regenerate_stats collectstats.pl to determine the date range gives the columns the aliases START and END. These are reserved word in Oracle PL/SQL. In particular, using START (unquoted) as an alias produces a parsing error.

Reproducible: Always

Steps to Reproduce:
1. Install Bugzilla on Oracle (10gR2)
2. Enter some bugs
3. Run collectstats.pl --regenerate
Actual Results:  
The script dies with the following (slightly misleading) error message:

DBD::Oracle::db selectrow_array failed: ORA-00923: FROM keyword not found where expected (DBD ERROR: error possibly near <*> indicator at char 44 in 'SELECT  TO_CHAR(TO_DATE(creation_ts),'J')   <*>start,  TO_CHAR(TO_DATE(current_date),'J')   end,  TO_CHAR(TO_DATE('1970-01-01'),'J')  FROM bugs
                    WHERE  rownum <=1 AND   TO_CHAR(TO_DATE(creation_ts),'J')  IS NOT NULL
                ORDER BY start /* LIMIT 1 */') [for Statement "SELECT  TO_CHAR(TO_DATE(creation_ts),'J')   start,  TO_CHAR(TO_DATE(current_date),'J')   end,  TO_CHAR(TO_DATE('1970-01-01'),'J')  FROM bugs
                    WHERE  rownum <=1 AND   TO_CHAR(TO_DATE(creation_ts),'J')  IS NOT NULL
                ORDER BY start /* LIMIT 1 */"] at Bugzilla/DB/Oracle.pm line 335
        Bugzilla::DB::Oracle::selectrow_array('undef', 'SELECT  TO_CHAR(TO_DATE(creation_ts),\'J\')   start,  TO_CHAR...', 'undef') called at ./collectstats.pl line 412
        main::regenerate_stats('./data/mining', '-All-') called at ./collectstats.pl line 124


Expected Results:  
Stats files regenerated.

Perl 5.8.8, DBD::Oracle 1.19, Oracle 10.2.0.2 (Solaris 9 9/05, but I doubt that's relevant)

Changing them to quoted aliases would fix the problem, but as they're not used outside the query itself, I think they can just be renamed.

Comment 1

[Dave Taylor]

Attachment: Rename column aliases to avoid reserved words

Comment 2

[Frédéric Buclin]

Comment on attachment 344877 [details] [diff] [review]
Rename column aliases to avoid reserved words

Looks good to me. r=LpSolit

Comment 3

[Frédéric Buclin]

xiaoou, do you confirm both the issue and the fix?

Comment 4

[Max Kanat-Alexander]

Comment on attachment 344877 [details] [diff] [review]
Rename column aliases to avoid reserved words

Looks fine to me.

Comment 5

[Frédéric Buclin]

tip:

Checking in collectstats.pl;
/cvsroot/mozilla/webtools/bugzilla/collectstats.pl,v  <--  collectstats.pl
new revision: 1.67; previous revision: 1.66
done

3.2rc1:

Checking in collectstats.pl;
/cvsroot/mozilla/webtools/bugzilla/collectstats.pl,v  <--  collectstats.pl
new revision: 1.64.2.2; previous revision: 1.64.2.1
done

Comment 6

[Xiaoou]

Yeah, looks good.