Brian Wong-2 wrote
> 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.
No it does not. Your general case is flawed in that adding an OR condition
will indeed cause more rows to be added. In this case you are adding an AND
clause but since it is at the same level as the existing conditions all
possible records must evaluate against this new clause even if one of the
other clauses returns false. There is no short-circuiting. This may be
confusing but that is part of the education process. If that expression
(the one in the where clause) did not cause an error there would be at most
the same number of records output as the original query. And the same
number of rows are being processed at the WHERE clause in both cases. Since
one of the conditions only makes sense on a sub-set of valid rows there must
be two levels of WHERE clauses in the query - or use CASE regexp THEN test
ELSE false END so the substring test only is performed against valid table
names.
David J.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/ERROR-invalid-value-for-YYYY-tp5773787p5773944.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.