Re: ERROR: invalid value "????" for "YYYY"

On 10/08/2013 01:50 PM, Brian Wong wrote:
> I'm posting this question to pgsql-general
> <http://www.postgresql.org/list/pgsql-general/>. Hopefully someone can
> share some insights with me.
>
> I have a bunch of tables in the database and in a separate schema.
> The tables' names are in this format:
>
> ???_???_???_YYYYMMDD
>
> where the last 8 characters is a date.
>
> *When I query either the information_schema.tables or pg_tables
> extracting the last 8 characters out and converting it to a date, it
> works:*
>
> select table_name, to_date(right(table_name, 8), 'YYYYMMDD') blah
> from information_schema.tables
> where table_schema = '????'
> and table_catalog = '????';
>
> *But as soon as I reference it in the where clause, it gives a weird
> error:*
>
> select table_name, to_date(right(table_name, 8), 'YYYYMMDD') blah
> from information_schema.tables
> where table_schema = '????'
> and table_catalog = '????'
> *and to_date(right(table_name, 8), 'YYYYMMDD') is not null;*
>
> *ERROR: invalid value "tati" for "YYYY"*
> DETAIL: Value must be an integer.
>
> It seems like some strange values were passed into the to_date
> function, but I'm seeing that the rightmost 8 characters of all the
> table names are dates. So only date strings are passed to the to_date
> function. Absolutely nothing containing the string "tati" is passed
> to the to_date function. What is going on? Is that a bug?

As Rowan said, you can't necessarily rely on the order of testing in the
where clause. In part, this is because one of the first things the
planner does is to take the whole shebang and rewrite it into a
consolidated statement that it can then refine and optimize. The tests
that take place in the view can ultimately happen before, after, or
intermixed with the tests in your where-clause as long as they are
logically equivalent.

In your example, you are querying information_schema.tables which is not
a table, it is a view that references, among other things, a subset of
the pg_catalog.pg_class table.

When the planner gets through with its first steps you won't be calling
information_schema.tables, you will be calling pg_catalog.pg_class and
doing some where-clause tests that logically combine your where-clause
with those in the view.

Why "tati"? When I query pg_class directly, the first row has a
"relname" of "pg_statistic" - it's not in the schema/catalog you seek
but the executor hasn't checked for that, yet. The right eight
characters of that relname are are "tatistic" thus the characters in the
"YYYY" position are "tati" so based on the plan and testing order this
just happens to be the first thing upon which the execution chokes.

Cheers,
Steve