Thread: No error when column doesn't exist
I've just spent a couple of hours tracking down a bug which turned out to be a typo in my code. What surprises me is that the SQL in question didn't immediately produce an error. Here's a simplified example: CREATE TABLE foo(a int, b int); INSERT INTO foo VALUES(1,2); SELECT foo.text FROM foo; I expected that to generate an error: column foo.text does not exist. Instead it treats "foo.text" as "foo.*::text AS text": SELECT foo.text FROM foo; text ------- (1,2) (1 row) If foo actually does have a column called text, this works as expected, selecting just that column. Is this a feature or a bug? Dean _________________________________________________________________ Make a mini you and download it into Windows Live Messenger http://clk.atdmt.com/UKM/go/111354029/direct/01/
Dean Rasheed <dean_rasheed@hotmail.com> writes:
> CREATE TABLE foo(a int, b int);
> INSERT INTO foo VALUES(1,2);
> SELECT foo.text FROM foo;
> I expected that to generate an error: column foo.text does not exist.
> Instead it treats "foo.text" as "foo.*::text AS text":
> Is this a feature or a bug?
Hmm. It's a feature, but maybe a dangerous one. The expression is
being treated as text(foo), which is intentional in order to allow
use of functions as if they were virtual columns. However, then it
decides that what you've got there is a cast request. There wasn't
any ability to cast composite types to text before 8.3, so this fails
in the expected way in 8.2 and before; but in 8.3 the cast
interpretation succeeds, and away we go.
foo.char and foo.varchar have similarly unexpected behavior; I think
that's probably the end of it, though, since those are the only types
that CoerceViaIO will take as targets.
Maybe we could/should restrict things so that the syntax continues to
fail, but I can't think of any restrictions that don't seem like warts.
What's worse, they might break stuff that used to work.
regards, tom lane
>> I expected that to generate an error: column foo.text does not exist. >> Instead it treats "foo.text" as "foo.*::text AS text": >> Is this a feature or a bug? > > Hmm. It's a feature, but maybe a dangerous one. The expression is > being treated as text(foo), which is intentional in order to allow > use of functions as if they were virtual columns. However, then it > decides that what you've got there is a cast request. There wasn't > any ability to cast composite types to text before 8.3, so this fails > in the expected way in 8.2 and before; but in 8.3 the cast > interpretation succeeds, and away we go. > > foo.char and foo.varchar have similarly unexpected behavior; I think > that's probably the end of it, though, since those are the only types > that CoerceViaIO will take as targets. > > Maybe we could/should restrict things so that the syntax continues to > fail, but I can't think of any restrictions that don't seem like warts. > What's worse, they might break stuff that used to work. I like that functionality and think the behavior is as it should be. Best practice is to not name columns with reserved words. So maybe we could update the manual with another reason not to use SQL reserved words as column names.
> Hmm. It's a feature, but maybe a dangerous one. The expression is > being treated as text(foo), which is intentional in order to allow > use of functions as if they were virtual columns. However, then it > decides that what you've got there is a cast request. There wasn't > any ability to cast composite types to text before 8.3, so this fails > in the expected way in 8.2 and before; but in 8.3 the cast > interpretation succeeds, and away we go. > Thanks for the explanation. I see what's going on now. > foo.char and foo.varchar have similarly unexpected behavior; I think > that's probably the end of it, though, since those are the only types > that CoerceViaIO will take as targets. > ... and also any user defined domains based on those, which is what I actually had. I was unlucky enough that the row text matched the regexp on my domain, so my typo went unnoticed for a while ;-( > Maybe we could/should restrict things so that the syntax continues to > fail, but I can't think of any restrictions that don't seem like warts. > What's worse, they might break stuff that used to work. > > regards, tom lane OK, I can live with that. At least I know what to look out for now! Cheers, Dean _________________________________________________________________ Win New York holidays with Kellogg’s & Live Search http://clk.atdmt.com/UKM/go/111354033/direct/01/
Dean Rasheed <dean_rasheed@hotmail.com> writes:
>> foo.char and foo.varchar have similarly unexpected behavior; I think
>> that's probably the end of it, though, since those are the only types
>> that CoerceViaIO will take as targets.
> ... and also any user defined domains based on those, which is
> what I actually had.
Ouch. That makes the scope for unexpected behavior wider than I thought.
Maybe we do need some restriction here?
The ideas I had involved not considering the cast interpretation when
the actual syntax is table.column and some-set-of-other-conditions.
While this is certainly possible to implement, any variant of it will
break the existing 100% equivalence of foo.bar and bar(foo); which
seems to me to be a nice principle, though I grant you won't find it
anywhere in the SQL standard.
The other-conditions are a bit up for grabs. The narrowest restriction
that would serve the purpose is "table variable is of composite type
and the cast would be a CoerceViaIO cast", but that definitely seems
like a wart. However, cleaner-seeming restrictions like "no casts on
composites at all" could potentially break applications that worked
okay before 8.3.
Comments anyone? Should we try to change this, or leave well enough
alone?
regards, tom lane
Tom Lane wrote: > The ideas I had involved not considering the cast interpretation when > the actual syntax is table.column and some-set-of-other-conditions. > While this is certainly possible to implement, any variant of it will > break the existing 100% equivalence of foo.bar and bar(foo); which > seems to me to be a nice principle, though I grant you won't find it > anywhere in the SQL standard. I think if we say that functions can be used as table attributes, and types can be used as (cast) functions, and tables are types, then we are simply stuck with the current behavior. Individually, these all make sense, so you can't break that chain without some really complicated warts.