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

But from a user's perspective, why would it ever make sense that by adding
an additional where clause, it actually brings in more data into the
picture? If I have query returning 100 rows. Adding an additional where
clause should only cut down the number of rows, not increase it. And the
extra data that's showing up is being added to the resultset cuz without
the additional where clause, the result set did not contain any of those
rows like pg_statistics/etc.

Brian

On Tue, Oct 8, 2013 at 4:10 PM, Steve Crawford <
scrawford(at)pinpointresearch(dot)com> wrote:

> 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
>
>