Обсуждение: the '::' cast doesn't work in the FROM clause
Hello,
The following statement produces an error message in PostgreSQL 8.4 - 9.2 (=
head):
postgres=3D# select val from random()::integer as val;
ERROR: syntax error at or near "::"
LINE 1: select val from random()::integer as val;
The same statement rewritten with CAST AS works as expected:
^
postgres=3D# select val from CAST(random() as integer) as val;
val
-----
1
(1 row)
The '::' cast works normally when used in a target list:
postgres=3D# select random()::integer as val;
val
--------
1
(1 row)
The documentation says these casts are equivalent, so either that's wrong, =
or this is a bug.
The target OS is Mac OS X 10.7.1 with llvm-gcc-4.2 used as a compiler.
--
Alexey Klyukin http://www.commandprompt.com
The PostgreSQL Company =96 Command Prompt, Inc.
Alexey Klyukin wrote: > The following statement produces an error message in PostgreSQL 8.4 > - 9.2 (head): > > postgres=# select val from random()::integer as val; > The same statement rewritten with CAST AS works as expected: > > postgres=# select val from CAST(random() as integer) as val; > The documentation says these casts are equivalent, so either that's > wrong, or this is a bug. Please point out where you think the documentation says that. The way I read it, this is the correct syntax: test=# select val from (select random()::integer) as x(val); val ----- 1 (1 row) Not only are you missing required parentheses and the SELECT keyword, you're returning a record rather than a scalar value. -Kevin
On Aug 29, 2011, at 3:49 PM, Kevin Grittner wrote: > Alexey Klyukin wrote: >=20 >> The following statement produces an error message in PostgreSQL 8.4 >> - 9.2 (head): >>=20 >> postgres=3D# select val from random()::integer as val; >=20 >> The same statement rewritten with CAST AS works as expected: >>=20 >> postgres=3D# select val from CAST(random() as integer) as val; >=20 >> The documentation says these casts are equivalent, so either that's >> wrong, or this is a bug. >=20 > Please point out where you think the documentation says that. Here: >=20 > A type cast specifies a conversion from one data type to another. Postgre= SQL accepts two equivalent syntaxes for type casts: >=20 > CAST ( expression AS type ) > expression::type >=20 http://www.postgresql.org/docs/9.0/interactive/sql-expressions.html#SQL-SYN= TAX-TYPE-CASTS > The way I read it, this is the correct syntax: >=20 > test=3D# select val from (select random()::integer) as x(val); > val=20 > ----- > 1 > (1 row) >=20 > Not only are you missing required parentheses and the SELECT keyword, > you're returning a record rather than a scalar value. SELECT val FROM random() AS val (same as the problematic query, but w/o cas= ts) doesn't produce any errors and IMO is a valid syntax. Here's a quote = from the SELECT documentation: > Function calls can appear in the FROM clause. (This is especially useful = for functions that return result sets, but any function can be used.) This = acts as though its output were created as a temporary table for the duratio= n of this single SELECT command.=20 http://www.postgresql.org/docs/9.0/interactive/sql-select.html The problem is that 2 types of casts behave differently when applied to ran= dom() in this query. -- Alexey Klyukin http://www.commandprompt.com The PostgreSQL Company =96 Command Prompt, Inc.
On Mon, Aug 29, 2011 at 7:49 AM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: > Alexey Klyukin =A0wrote: > >> The following statement produces an error message in PostgreSQL 8.4 >> - 9.2 (head): >> >> postgres=3D# select val from random()::integer as val; > >> The same statement rewritten with CAST AS works as expected: >> >> postgres=3D# select val from CAST(random() as integer) as val; > >> The documentation says these casts are equivalent, so either that's >> wrong, or this is a bug. > > Please point out where you think the documentation says that. =A0The > way I read it, this is the correct syntax: > > test=3D# select val from (select random()::integer) as x(val); > =A0val > ----- > =A0 1 > (1 row) > > Not only are you missing required parentheses and the SELECT keyword, > you're returning a record rather than a scalar value. yeah, that's the correct way, but why does this work? select val from random() as val; That's illegal IMO, and walls you off from syntax (like::) you normally should be able to use. merlin
Merlin Moncure <mmoncure@gmail.com> wrote: > yeah, that's the correct way, but why does this work? > select val from random() as val; If you look at the PostgreSQL reference docs for the SELECT statement, a from_item can be a SELECT statement in parentheses or a function call (among other things). It cannot be an arbitrary expression containing operators (like ::). -Kevin
Alexey Klyukin <alexk@commandprompt.com> wrote: > Function calls can appear in the FROM clause. (This is especially > useful for functions that return result sets, but any function can > be used.) This acts as though its output were created as a > temporary table for the duration of this single SELECT command. It doesn't say that operators which provide equivalent functionality to functions can also be used. -Kevin
On Aug 29, 2011, at 5:02 PM, Kevin Grittner wrote: > Alexey Klyukin <alexk@commandprompt.com> wrote: >=20 >> Function calls can appear in the FROM clause. (This is especially >> useful for functions that return result sets, but any function can >> be used.) This acts as though its output were created as a >> temporary table for the duration of this single SELECT command. >=20 > It doesn't say that operators which provide equivalent functionality > to functions can also be used. I agree, but why is it possible to use the type casting with CAST there? Do= esn't this break the promise of equivalency between the 'CAST .. ' and '::'? select val from CAST(random() as integer) as val; val=20 ----- 1 (1 row) -- Alexey Klyukin http://www.commandprompt.com The PostgreSQL Company =96 Command Prompt, Inc.
Alexey Klyukin <alexk@commandprompt.com> wrote:
> On Aug 29, 2011, at 5:02 PM, Kevin Grittner wrote:
>
>> Alexey Klyukin <alexk@commandprompt.com> wrote:
>>
>>> Function calls can appear in the FROM clause. (This is
>>> especially useful for functions that return result sets, but any
>>> function can be used.) This acts as though its output were
>>> created as a temporary table for the duration of this single
>>> SELECT command.
>>
>> It doesn't say that operators which provide equivalent
>> functionality to functions can also be used.
>
> I agree, but why is it possible to use the type casting with CAST
> there?
Because the syntax is that of a function, which is allowed.
> Doesn't this break the promise of equivalency between the
> 'CAST .. ' and '::'?
No. Equivalent functionality doesn't imply that the different
syntax forms can be used in the same places; just that they do the
same thing when used. This is hardly unique to casting.
Comparison of two text values is done through the texteq function.
test=# select val from texteq('a', 'a') as val;
val
-----
t
(1 row)
test=# select val from 'a' = 'a' as val;
ERROR: syntax error at or near "'a'"
LINE 1: select val from 'a' = 'a' as val;
^
test=# select val from (select 'a' = 'a') as val;
val
-----
(t)
(1 row)
> select val from CAST(random() as integer) as val;
> val
> -----
> 1
> (1 row)
Right. A function is allowed as a from_item. Arbitrary expressions
using operators which happen to provide equivalent services are not.
-Kevin
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> wrote: > test=# select val from (select 'a' = 'a') as val; > val > ----- > (t) > (1 row) Also note the difference between a record and a scalar here. I forgot to write it to return val as a scalar, which seems to be what you're after. It should have been: test=# select val from (select 'a' = 'a') as x(val); val ----- t (1 row) -Kevin
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Merlin Moncure <mmoncure@gmail.com> wrote:
>> yeah, that's the correct way, but why does this work?
>> select val from random() as val;
> If you look at the PostgreSQL reference docs for the SELECT
> statement, a from_item can be a SELECT statement in parentheses or a
> function call (among other things). It cannot be an arbitrary
> expression containing operators (like ::).
Right. We also accept things that look syntactically like function
calls, so as to avoid debates with newbies about whether, say,
COALESCE(...) is a function or not. CAST() falls into that category,
while :: doesn't.
There is actually a practical reason for this policy beyond the question
of whether CAST is a function call or not: the structure name(...) has
a well-defined syntactic extent, so there are no issues of operator
precedence to worry about when it's embedded in a larger construct.
IIRC, we ran into exactly that problem with the CREATE INDEX syntax,
which is why an expression index column has to be parenthesized unless
it looks like a function call.
So IMO there is no syntax bug here. There is a dump/reload bug though
:-( ... if you were to do
create view vv as select val from CAST(random() as integer) as val;
you will find that the system prints it out with the :: syntax,
which won't work.
regards, tom lane
On Aug 29, 2011, at 5:47 PM, Tom Lane wrote: > "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: >> Merlin Moncure <mmoncure@gmail.com> wrote: >>> yeah, that's the correct way, but why does this work? >>> select val from random() as val; >=20 >> If you look at the PostgreSQL reference docs for the SELECT >> statement, a from_item can be a SELECT statement in parentheses or a >> function call (among other things). It cannot be an arbitrary >> expression containing operators (like ::). >=20 > Right. We also accept things that look syntactically like function > calls, so as to avoid debates with newbies about whether, say, > COALESCE(...) is a function or not. CAST() falls into that category, > while :: doesn't. I was wondering exactly why CAST() is permitted, while it's not a function = (in Kevin's example, texteq is a function) and the explanation above answer= s my question.=20 >=20 > There is actually a practical reason for this policy beyond the question > of whether CAST is a function call or not: the structure name(...) has > a well-defined syntactic extent, so there are no issues of operator > precedence to worry about when it's embedded in a larger construct. > IIRC, we ran into exactly that problem with the CREATE INDEX syntax, > which is why an expression index column has to be parenthesized unless > it looks like a function call. >=20 > So IMO there is no syntax bug here.=20=20 I agree, thank you and Kevin for the great explanation! > There is a dump/reload bug though :-( ... if you were to do >=20 > create view vv as select val from CAST(random() as integer) as val;=20 >=20 > you will find that the system prints it out with the :: syntax, > which won't work. Would it be acceptable/sufficient to output CAST(...) instead of '::' for= all casts in pg_dump to fix this problem, assuming that CAST can be used a= nywhere where '::' is accepted? -- Alexey Klyukin http://www.commandprompt.com The PostgreSQL Company =96 Command Prompt, Inc.
Alexey Klyukin <alexk@commandprompt.com> writes:
> On Aug 29, 2011, at 5:47 PM, Tom Lane wrote:
>> There is a dump/reload bug though :-( ... if you were to do
>>
>> create view vv as select val from CAST(random() as integer) as val;
>>
>> you will find that the system prints it out with the :: syntax,
>> which won't work.
> Would it be acceptable/sufficient to output CAST(...) instead of '::' for all casts in pg_dump to fix this problem,
assumingthat CAST can be used anywhere where '::' is accepted?
I'm not really excited about that; CAST is more verbose and not
particularly more readable (at least IMO). What I was wondering about
was altering the internal representation to remember which format had
been used, and reverse-listing in that same format. That would both fix
this issue, and please users who have a stylistic preference for one or
the other format.
regards, tom lane
On Mon, Aug 29, 2011 at 9:00 AM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: > Merlin Moncure <mmoncure@gmail.com> wrote: > >> yeah, that's the correct way, but why does this work? >> select val from random() as val; > > If you look at the PostgreSQL reference docs for the SELECT > statement, a from_item can be a SELECT statement in parentheses or a > function call (among other things). =A0It cannot be an arbitrary > expression containing operators (like ::). right -- duh. I knew that...the canonical case for this is the definition of pg_locks view. I've just never seen it used for a regular scalar function. The whole thing is pretty peculiar IMO (but useful occasionally). merlin