Postgres Pro
Downloads
Home   >   mailing lists

Thread: date_part/extract parse curiosity

date_part/extract parse curiosity

From
Erik Rijkers
Date:
Hi,

I noticed that
   select date_part('millennium', now()); --> 3

will execute also, unperturbed, in this form:
   select date_part('millennium xxxxx', now()); --> 3

By the same token

   select extract(millennium from now()) --> 3
   select extract(millenniumxxxxxxxxx from now()) --> 3

This laxness occurs in all releases, and with 'millennium', 
'millisecond', and 'microsecond' (at least).

Even though it's not likely to cause much real-life headaches, and I 
hesitate to call it a real bug, perhaps it would be better if it could 
be a bit stricter.

Thanks,

Erik Rijkers

Re: date_part/extract parse curiosity

From
Japin Li
Date:
On Thu, 20 Oct 2022 at 20:45, Erik Rijkers <er@xs4all.nl> wrote:
> Hi,
>
> I noticed that
>   select date_part('millennium', now()); --> 3
>
> will execute also, unperturbed, in this form:
>   select date_part('millennium xxxxx', now()); --> 3
>
> By the same token
>
>   select extract(millennium from now()) --> 3
>   select extract(millenniumxxxxxxxxx from now()) --> 3
>
> This laxness occurs in all releases, and with 'millennium',
> 'millisecond', and 'microsecond' (at least).
>
> Even though it's not likely to cause much real-life headaches, and I
> hesitate to call it a real bug, perhaps it would be better if it could
> be a bit stricter.
>

According to the documentation [1], the extract() only has some field names,
however, the code use strncmp() to compare the units and tokens.

    int
    DecodeUnits(int field, char *lowtoken, int *val)
    {
        int         type;
        const datetkn *tp;
    
        tp = deltacache[field];
        /* use strncmp so that we match truncated tokens */              <---- here
        if (tp == NULL || strncmp(lowtoken, tp->token, TOKMAXLEN) != 0)
        {
            tp = datebsearch(lowtoken, deltatktbl, szdeltatktbl);
        }
        if (tp == NULL)
        {
            type = UNKNOWN_FIELD;
            *val = 0;
        }
        else
        {
            deltacache[field] = tp;
            type = tp->type;
            *val = tp->value;
        }
    
        return type;
    }

This is convenient for field names such as millennium and millenniums,
however it also valid for millenniumxxxxxxxxxxxx, which is looks strange.

Maybe we should document this.  I'd be inclined to change the code to
match the certain valid field names.

Any thoughts?

[1] https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT

-- 
Regrads,
Japin Li.
ChengDu WenWu Information Technology Co.,Ltd.

Re: date_part/extract parse curiosity

From
Tom Lane
Date:
Japin Li <japinli@hotmail.com> writes:
> On Thu, 20 Oct 2022 at 20:45, Erik Rijkers <er@xs4all.nl> wrote:
>> I noticed that
>> select date_part('millennium', now()); --> 3
>> 
>> will execute also, unperturbed, in this form:
>> select date_part('millennium xxxxx', now()); --> 3

> Maybe we should document this.  I'd be inclined to change the code to
> match the certain valid field names.

I think changing this behavior has a significant chance of drawing
complaints and zero chance of making anyone happier.

The current state of affairs (including the lack of unnecessary
documentation detail) is likely quite intentional.

            regards, tom lane

Re: date_part/extract parse curiosity

From
Japin Li
Date:
On Thu, 20 Oct 2022 at 22:12, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Japin Li <japinli@hotmail.com> writes:
>> On Thu, 20 Oct 2022 at 20:45, Erik Rijkers <er@xs4all.nl> wrote:
>>> I noticed that
>>> select date_part('millennium', now()); --> 3
>>> 
>>> will execute also, unperturbed, in this form:
>>> select date_part('millennium xxxxx', now()); --> 3
>
>> Maybe we should document this.  I'd be inclined to change the code to
>> match the certain valid field names.
>
> I think changing this behavior has a significant chance of drawing
> complaints and zero chance of making anyone happier.
>

Maybe.

> The current state of affairs (including the lack of unnecessary
> documentation detail) is likely quite intentional.
>

I'm curious about why not document this?

-- 
Regrads,
Japin Li.
ChengDu WenWu Information Technology Co.,Ltd.