pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Old question - failed to find conversion function from "unknown"
8+ messages / 3 participants
[nested] [flat]

* Old question - failed to find conversion function from "unknown"
@ 2005-07-19 09:50  Ilja Golshtein <ilejn@yandex.ru>
  0 siblings, 1 reply; 8+ messages in thread

From: Ilja Golshtein @ 2005-07-19 09:50 UTC (permalink / raw)
  To: pgsql-general@postgresql.org

Hi!

I've already asked this question a month ago -
it remains unanswered.
Dare to repeat since this issue is rather important for me.

So.

When I invoke

select 1 where 5 in (null)

everything is Ok,
though when I try

select 1 where 5 in (select null)

I get 

ERROR:  failed to find conversion function from "unknown" to integer.

Is it desired behavior or subject to change 
in future versions of PG?

I realize my simplified example looks radiculous while it came from reality.
Sometimes my engine generates SQL queries does not know correct type
and unable to cast, while PostgreSQL should have all information required.

Thanks.

-- 
Best regards
Ilja Golshtein



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Old question - failed to find conversion function from
@ 2005-07-19 11:03  Richard Huxton <dev@archonet.com>
  parent: Ilja Golshtein <ilejn@yandex.ru>
  0 siblings, 1 reply; 8+ messages in thread

From: Richard Huxton @ 2005-07-19 11:03 UTC (permalink / raw)
  To: ilejn@yandex.ru; +Cc: pgsql-general@postgresql.org

Ilja Golshtein wrote:
> 
> When I invoke
> 
> select 1 where 5 in (null)
> 
> everything is Ok,
> though when I try
> 
> select 1 where 5 in (select null)
> 
> I get 
> 
> ERROR:  failed to find conversion function from "unknown" to integer.
> 
> Is it desired behavior or subject to change 
> in future versions of PG?

Well, it would obviously be better if PG could figure out it was safe, 
but I'm not sure there's a general case where it is. You can see it's OK 
because you know there's only one row in your SELECT result-set. 
However, what if you had:
  ... (select null UNION ALL select '2005-01-01')
Now is this a set of "unknown" or "text" or "date"? Should PG change 
it's decision based on what the rest of the query wants?

So - although this may change in future versions of PG, I wouldn't count 
  on it never being a problem.

> I realize my simplified example looks radiculous while it came from reality.
> Sometimes my engine generates SQL queries does not know correct type
> and unable to cast, while PostgreSQL should have all information required.

How are you generating the SQL if you don't know the types involved? Are 
you just blindly quoting all values? What do you do with arrays/points 
etc, or don't you handle those?

--
   Richard Huxton
   Archonet Ltd



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Old question - failed to find conversion function from "unknown"
@ 2005-07-19 12:19  Ilja Golshtein <ilejn@yandex.ru>
  parent: Richard Huxton <dev@archonet.com>
  0 siblings, 2 replies; 8+ messages in thread

From: Ilja Golshtein @ 2005-07-19 12:19 UTC (permalink / raw)
  To: dev@archonet.com; +Cc: pgsql-general@postgresql.org

>Well, it would obviously be better if PG could figure out it was safe, 
>but I'm not sure there's a general case where it is. You can see it's OK 
>because you know there's only one row in your SELECT result-set. 

I think, it's OK because NULL can be compared with anything 
with predictable result and no additional information about 
types is necessary.
Is it correct vision?

I agree it's hard to proceed your query with UNION 
and some sort of error is reasonable here.

-- 
Best regards
Ilja Golshtein



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Old question - failed to find conversion function from "unknown"
@ 2005-07-19 14:17  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Ilja Golshtein <ilejn@yandex.ru>
  1 sibling, 1 reply; 8+ messages in thread

From: Tom Lane @ 2005-07-19 14:17 UTC (permalink / raw)
  To: ilejn@yandex.ru; +Cc: dev@archonet.com; pgsql-general@postgresql.org

"Ilja Golshtein" <ilejn@yandex.ru> writes:
>> Well, it would obviously be better if PG could figure out it was safe, 
>> but I'm not sure there's a general case where it is. You can see it's OK 
>> because you know there's only one row in your SELECT result-set. 

> I think, it's OK because NULL can be compared with anything 
> with predictable result and no additional information about 
> types is necessary.
> Is it correct vision?

The backend doesn't really distinguish NULL from 'foo' (or untyped
string literals in general) when making datatype decisions.  If we
were to change the behavior of 
	select 1 where 5 in (select null)
at all, it would undoubtedly be to treat it as
	select 1 where 5 in (select null::text)
because TEXT is the default resolution for UNKNOWN in every other
case where we force a choice to be made.  But this is not what you
want for your example, and in general it would probably break as
many cases as it fixed.  So I'm inclined to leave it as-is ---
an error message is probably better than a surprising silent choice.

My recommendation is to cast the NULL to the right type explicitly.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Old question - failed to find conversion function from
@ 2005-07-19 14:18  Richard Huxton <dev@archonet.com>
  parent: Ilja Golshtein <ilejn@yandex.ru>
  1 sibling, 1 reply; 8+ messages in thread

From: Richard Huxton @ 2005-07-19 14:18 UTC (permalink / raw)
  To: ilejn@yandex.ru; +Cc: pgsql-general@postgresql.org

Ilja Golshtein wrote:
>>Well, it would obviously be better if PG could figure out it was safe, 
>>but I'm not sure there's a general case where it is. You can see it's OK 
>>because you know there's only one row in your SELECT result-set. 
> 
> 
> I think, it's OK because NULL can be compared with anything 
> with predictable result and no additional information about 
> types is necessary.
> Is it correct vision?

Yes*, but you've not got a single NULL in your examples, you've got a 
set of rows containing one unnamed column with an unspecified type. That 
set happens to have only one row and that contains a NULL.

> I agree it's hard to proceed your query with UNION 
> and some sort of error is reasonable here.

But from outside the brackets, they look the same.

What would happen ideally, is that PG would notice we have a single row 
and column here and collapse this down to a single scalar value. 
However, I'm not sure under what circumstances it can do so (or does), 
or whether it is cost-effective.

[* Actually, I think NULLs are typed in SQL, which means you should be 
able to get type violations. ]

--
   Richard Huxton
   Archonet Ltd



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Old question - failed to find conversion function from "unknown"
@ 2005-07-19 15:01  Ilja Golshtein <ilejn@yandex.ru>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 8+ messages in thread

From: Ilja Golshtein @ 2005-07-19 15:01 UTC (permalink / raw)
  To: tgl@sss.pgh.pa.us; +Cc: pgsql-general@postgresql.org

Hello!

>>> Well, it would obviously be better if PG could figure out it was safe, 
>>> but I'm not sure there's a general case where it is. You can see it's OK 
>>> because you know there's only one row in your SELECT result-set. 
>
>> I think, it's OK because NULL can be compared with anything 
>> with predictable result and no additional information about 
>> types is necessary.
>> Is it correct vision?
>
>The backend doesn't really distinguish NULL from 'foo' (or untyped
>string literals in general) when making datatype decisions. 

I think when PG is about to compare object of known type and known
value (it is 5 in my example) with object with unknown type
but known value or known null flag (it is null in my example) it is
high time to return 'false' instead of producing error.

As far as I understand, it is not about comparision only, 
but about any operation.

I really believe NULLs are special here.

Of course I am a complete stranger and unaware of PostgreSQL internals
so what I am saying may contradict with some basical concepts. Looks like
it does since according to Tom, PostgreSQL does not treat null literals
in special way :(

Thanks.

-- 
Best regards
Ilja Golshtein



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Old question - failed to find conversion function from
@ 2005-07-19 15:19  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Richard Huxton <dev@archonet.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Tom Lane @ 2005-07-19 15:19 UTC (permalink / raw)
  To: Richard Huxton <dev@archonet.com>; +Cc: ilejn@yandex.ru; pgsql-general@postgresql.org

Richard Huxton <dev@archonet.com> writes:
> [* Actually, I think NULLs are typed in SQL, which means you should be 
> able to get type violations. ]

I'm pretty sure the entire construct is illegal per a strict reading of
the SQL spec --- the spec only allows NULL to appear in contexts where a
datatype can be assigned to it immediately.  Per spec you'd have to
write this as
	select 1 where 5 in (select cast(null as integer));

In the spec, NULL is not a general <expression>, it's a <contextually
typed value expression>, and those are only allowed as the immediate
argument of a CAST(), the immediate column value of an INSERT or UPDATE,
and one or two other very circumscribed cases.  SQL99 section 6.4 is
very clear about what they intend:

         2) The declared type DT of a <null specification> NS is determined
            by the context in which NS appears. NS is effectively replaced
            by CAST ( NS AS DT ).

            NOTE 70 - In every such context, NS is uniquely associated with
            some expression or site of declared type DT, which thereby
            becomes the declared type of NS.

PG's ability to infer a type for a NULL constant goes well beyond what
the spec allows --- but it does have limits.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Old question - failed to find conversion function from
@ 2005-07-19 17:24  Ilja Golshtein <ilejn@yandex.ru>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 8+ messages in thread

From: Ilja Golshtein @ 2005-07-19 17:24 UTC (permalink / raw)
  To: tgl@sss.pgh.pa.us; +Cc: dev@archonet.com; pgsql-general@postgresql.org

>Richard Huxton <dev@archonet.com> writes:
>> [* Actually, I think NULLs are typed in SQL, which means you should be 
>> able to get type violations. ]
>
>I'm pretty sure the entire construct is illegal per a strict reading of
>the SQL spec --- the spec only allows NULL to appear in contexts where a
>datatype can be assigned to it immediately. 

Honestly I cannot say this info changes too much for me (other DBMSs I use as backend handle this untyped NULLs), though the situation is clarified.

Thanks a lot.

-- 
Best regards
Ilja Golshtein



^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2005-07-19 17:24 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-07-19 09:50 Old question - failed to find conversion function from "unknown" Ilja Golshtein <ilejn@yandex.ru>
2005-07-19 11:03 ` Re: Old question - failed to find conversion function from Richard Huxton <dev@archonet.com>
2005-07-19 12:19   ` Ilja Golshtein <ilejn@yandex.ru>
2005-07-19 14:17     ` Tom Lane <tgl@sss.pgh.pa.us>
2005-07-19 15:01       ` Ilja Golshtein <ilejn@yandex.ru>
2005-07-19 14:18     ` Re: Old question - failed to find conversion function from Richard Huxton <dev@archonet.com>
2005-07-19 15:19       ` Re: Old question - failed to find conversion function from Tom Lane <tgl@sss.pgh.pa.us>
2005-07-19 17:24         ` Re: Old question - failed to find conversion function from Ilja Golshtein <ilejn@yandex.ru>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox