On 10/08/2013 01:50 PM, Brian Wong wrote:
I'm posting this question to
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