Thread: Postgres 7.3.5 and count('x')
Hello,
It appears that the count('x') will no longer work without a type
cast. Is this on purpose? I've already modified my code to use
count(*) instead, but I decided to mention it anyway.
warehouse=# select count('x') ;
ERROR: cannot accept a value of type any
warehouse=# select count('x'::text) ;count
------- 1
(1 row)
ramirez@idconcepts.org (Edwin S. Ramirez) writes:
> It appears that the count('x') will no longer work without a type
> cast. Is this on purpose?
> warehouse=# select count('x') ;
> ERROR: cannot accept a value of type any
Hm, that query seems like it should be legal. (You get the same
from "select count('x') from some_table", so it's not the lack of
a table to iterate over that's the issue.)
The most direct fix is probably to make any_in() return some random
value (may as well be ((Datum) 0)) instead of producing an error.
I can't offhand see any real downside to doing so, but I'm a little
worried that it might introduce a gap in the type system. Can anyone
see a reason not to do that? Or a better fix for Edwin's complaint?
regards, tom lane
Tom Lane wrote:
> ramirez@idconcepts.org (Edwin S. Ramirez) writes:
> > It appears that the count('x') will no longer work without a type
> > cast. Is this on purpose?
>
> > warehouse=# select count('x') ;
> > ERROR: cannot accept a value of type any
>
> Hm, that query seems like it should be legal. (You get the same
> from "select count('x') from some_table", so it's not the lack of
> a table to iterate over that's the issue.)
>
> The most direct fix is probably to make any_in() return some random
> value (may as well be ((Datum) 0)) instead of producing an error.
> I can't offhand see any real downside to doing so, but I'm a little
> worried that it might introduce a gap in the type system. Can anyone
> see a reason not to do that? Or a better fix for Edwin's complaint?
What is COUNT('x') supposed to return? 1? Is that legal SQL?
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square,
Pennsylvania19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> What is COUNT('x') supposed to return? 1? Is that legal SQL?
Why not? Vanilla SQL would assume the string is CHAR type.
regards, tom lane
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > What is COUNT('x') supposed to return? 1? Is that legal SQL?
>
> Why not?
Because there is nothing to count.
In general,
SELECT count(expr) FROM table1;
counts the number of rows in table1 where expr evaluates to not null.
If table1 is not specified, that rule no longer holds. At best you
could assume that table1 is empty and return 0. But a result of 1 I
cannot see justified.
Peter Eisentraut <peter_e@gmx.net> writes:
> In general,
> SELECT count(expr) FROM table1;
> counts the number of rows in table1 where expr evaluates to not null.
Right. Edwin obscured the datatype issue by leaving off a table, but
the issue is real anyway:
regression=# select count(1) from tenk1;count
-------10000
(1 row)
regression=# select count('x') from tenk1;
ERROR: cannot accept a value of type any
We need to do something about that, I think. The "long form" solution
would be to cause 'x' to be promoted to type text in this context, but
I think it may be enough to remove the elog() in any_in() and just let
a dummy value be returned.
> If table1 is not specified, that rule no longer holds. At best you
> could assume that table1 is empty and return 0. But a result of 1 I
> cannot see justified.
Mumble. An Oracle person would say that our locution
"SELECT expression" is a shorthand for "SELECT expression FROM dual"
(or whatever the name of that standard one-row table of theirs is).
With that understanding, the behavior of "SELECT count(1)" is entirely
proper. If you assume that "SELECT expression" means to select from
a dummy table of no rows, then it should produce no result rows,
which would be pretty useless. So I don't see the argument for saying
that count() should produce zero in that case.
I could see an argument for putting in a special case to error out if
an aggregate appears in this context ... but the current behavior seems
perfectly okay to me. Except for the datatype problem.
regards, tom lane
I tried :<br /> select count('x') from patients;<br /><br /> And I get the same error. <br /><br /> select
count('x');should return the same as select count(*); which returns 1.<br /><br /> Previous Postgres versions returned
1.<br/><br /> -ESR-<br /><br /> Peter Eisentraut wrote:<br /><blockquote cite="mid200312061746.37190.peter_e@gmx.net"
type="cite"><prewrap="">Tom Lane wrote: </pre><blockquote type="cite"><pre wrap="">Bruce Momjian <a
class="moz-txt-link-rfc2396E"href="mailto:pgman@candle.pha.pa.us"><pgman@candle.pha.pa.us></a> writes:
</pre><blockquotetype="cite"><pre wrap="">What is COUNT('x') supposed to return? 1? Is that legal SQL?
</pre></blockquote><prewrap="">Why not? </pre></blockquote><pre wrap="">
Because there is nothing to count.
In general,
SELECT count(expr) FROM table1;
counts the number of rows in table1 where expr evaluates to not null.
If table1 is not specified, that rule no longer holds. At best you
could assume that table1 is empty and return 0. But a result of 1 I
cannot see justified.
</pre></blockquote>
I said:
> ramirez@idconcepts.org (Edwin S. Ramirez) writes:
> [ "select count('x') from some_table" fails ]
> The most direct fix is probably to make any_in() return some random
> value (may as well be ((Datum) 0)) instead of producing an error.
After further investigation I have decided that this is really just an
issue of incorrect ordering of tests in parse_coerce.c. The attached
proposed patch arranges for the ANY/ANYELEMENT/ANYARRAY short-circuit
path to be taken before we check for UNKNOWN-type literals and Params.
The net effect is that coercion to ANY/ANYELEMENT/ANYARRAY will have
*no effect* on an UNKNOWN literal. This seems reasonable, since in
these contexts we don't really know any more than we did before about
the type of the literal; it's really still UNKNOWN.
One side effect besides fixing Edwin's gripe is that where before,
regression=# select 'x'::unknown::anyelement;
ERROR: cannot accept a value of type anyelement
now you get a result:
regression=# select 'x'::unknown::anyelement;anyelement
------------x
(1 row)
I think this is reasonable, since coercion of other objects besides
unknown literals to anyelement is a no-op:
regression=# select 'x'::text::anyelement;anyelement
------------x
(1 row)
regression=# select 2::anyelement;anyelement
------------ 2
(1 row)
Comments? Any objections to the patch?
regards, tom lane
*** src/backend/parser/parse_coerce.c~ Mon Dec 8 15:37:30 2003
--- src/backend/parser/parse_coerce.c Sat Dec 13 13:21:41 2003
***************
*** 153,158 ****
--- 153,166 ---- /* no conversion needed */ return node; }
+ if (targetTypeId == ANYOID ||
+ targetTypeId == ANYARRAYOID ||
+ targetTypeId == ANYELEMENTOID)
+ {
+ /* assume can_coerce_type verified that implicit coercion is okay */
+ /* NB: we do NOT want a RelabelType here */
+ return node;
+ } if (inputTypeId == UNKNOWNOID && IsA(node, Const)) { /*
***************
*** 260,273 **** param->paramtype = targetTypeId; return (Node *) param; }
- if (targetTypeId == ANYOID ||
- targetTypeId == ANYARRAYOID ||
- targetTypeId == ANYELEMENTOID)
- {
- /* assume can_coerce_type verified that implicit coercion is okay */
- /* NB: we do NOT want a RelabelType here */
- return node;
- } if (find_coercion_pathway(targetTypeId, inputTypeId, ccontext, &funcId))
{
--- 268,273 ----
***************
*** 372,388 **** if (!typeidIsValid(targetTypeId)) return false;
- /*
- * If input is an untyped string constant, assume we can convert
- * it to anything except a class type.
- */
- if (inputTypeId == UNKNOWNOID)
- {
- if (ISCOMPLEX(targetTypeId))
- return false;
- continue;
- }
- /* accept if target is ANY */ if (targetTypeId == ANYOID) continue;
--- 372,377 ----
***************
*** 396,401 ****
--- 385,401 ---- } /*
+ * If input is an untyped string constant, assume we can convert
+ * it to anything except a class type.
+ */
+ if (inputTypeId == UNKNOWNOID)
+ {
+ if (ISCOMPLEX(targetTypeId))
+ return false;
+ continue;
+ }
+
+ /* * If pg_cast shows that we can coerce, accept. This test now * covers both
binary-compatibleand coercion-function cases. */