302643 - [PostgreSQL] 'collectstats.pl --regenerate' fails with a TO_CHAR error

Status: RESOLVED DUPLICATE of bug 301141

(Bugzilla :: Database, defect)

Description

[Emmanuel Seyman]

Using the head of the 2.20 branch, I can't run 'collectstats --regenerate' when
using PostgreSQL as a database. The command fails with:

[manu@lora bugzilla-2.20]$ ./collectstats.pl --regenerate
DBD::Pg::st execute failed: ERREUR:  La fonction to_char("unknown", "unknown")
n'est pas unique
ASTUCE : n'a pas pu choisir un meilleur candidat dans les fonctions. Vous devez
ajouter une conversion explicite de type.
 [for Statement "SELECT TO_CHAR(creation_ts, 'J')::int AS start,
TO_CHAR(current_date, 'J')::int AS end, TO_CHAR('1970-01-01', 'J')::int FROM
bugs  WHERE TO_CHAR(creation_ts, 'J')::int != 'NULL' ORDER BY start LIMIT 1"] at
Bugzilla/DB.pm line 81
        Bugzilla::DB::SendSQL('SELECT TO_CHAR(creation_ts, \'J\')::int AS start,
TO_CHAR(cur...') called at ./collectstats.pl line 285
        main::regenerate_stats('./data/mining', '-All-') called at
./collectstats.pl line 77

(Apologies for the french)

I can reproduce this on tip as well.

Comment 1

[Max Kanat-Alexander]

I've looked into this a bit, and the code in collectstats.pl for --regenerate is
quite complex.

I think that this won't work for the initial release of 2.20.

Comment 2

[Shin-ichi Nagamura]

Attachment: fixes bug of collectstats --regenerate

Comment 3

[Max Kanat-Alexander]

Comment on attachment 191342 [details] [diff] [review]
fixes bug of collectstats --regenerate

Yeah, unfortunately we need a solution that will work on all databases.

Also, in the future, make sure to attach patches as a "diff -u"

Comment 4

[Shin-ichi Nagamura]

Sorry, I didn't write a comment for patch.

A cause of this error message is because it is not recognized character string
in TO_CHAR as 'TIMESTAMP WITHOUT TIME ZONE'. Then I add 'cast to timestamp
without time zone'.

Comment 5

[Shin-ichi Nagamura]

Thank you for your quick reply.

(In reply to comment #3)
> (From update of attachment 191342 [details] [diff] [review] [edit])
> Yeah, unfortunately we need a solution that will work on all databases.

Yes, I understand it. This patch has two following change points.

o cast to 'TIMESTAMP WITHOUT TIME ZONE' using CAST() in TO_CHARS.
  The CAST() syntax is specified in SQL99. and usable mysql and postgresql.

o I unquote 'quoted NULL'.

> Also, in the future, make sure to attach patches as a "diff -u"

ok, may i resend patch?

Comment 6

[Emmanuel Seyman]

(In reply to comment #5)
>
> ok, may i resend patch?

Please do.

Comment 7

[Shin-ichi Nagamura]

Attachment: fixes bug of collectstats --regenerate(resent)

OK, I attached a patch as a "diff -u".

Comment 8

[Emmanuel Seyman]

Comment on attachment 201002 [details] [diff] [review]
fixes bug of collectstats --regenerate(resent)

You also need to ask for review

Comment 9

[Max Kanat-Alexander]

Comment on attachment 201002 [details] [diff] [review]
fixes bug of collectstats --regenerate(resent)

No, definitely not. MySQL doesn't support CAST that way.

Comment 10

[Frédéric Buclin]

Another problem in collectstats.pl is that FROM_DATE is MySQL specific:

[Sat Nov  5 10:38:40 2005] collectstats.pl: DBD::Pg::st execute failed: ERREUR:  la fonction from_days(integer) n'existe pas
[Sat Nov  5 10:38:40 2005] collectstats.pl: ASTUCE : Aucune fonction ne correspond au nom donné et aux types d'arguments. Vous devez ajouter des conversions explicites de type.explicit type casts.
[Sat Nov  5 10:38:40 2005] collectstats.pl:  [for Statement "SELECT bug_id FROM bugs  WHERE bugs.creation_ts < from_days(2453277) AND bugs.creation_ts >= from_days(2453276)  ORDER BY bug_id"] at Bugzilla/DB.pm line 84
[Sat Nov  5 10:38:40 2005] collectstats.pl:     Bugzilla::DB::SendSQL('SELECT bug_id FROM bugs  WHEREbugs.creation_ts < from_days(2...') called at ./collectstats.pl line 318
[Sat Nov  5 10:38:40 2005] collectstats.pl:     main::regenerate_stats('./data/mining', '-All-') called at ./collectstats.pl line 77


The initial bug reported by manu is fixed by using my new param $cast defined in bug 301141, so that we now write:
$dbh->sql_to_days("'1970-01-01'", '::date')

Comment 11

[Max Kanat-Alexander]

We actually have a fix for this bug in this dup.

*** This bug has been marked as a duplicate of 301141 ***

Comment 12

[Max Kanat-Alexander]

Relnoted for 2.20.1 in bug 320319.