auto type casting bug

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

auto type casting bug

Matthew Manuel
Hello, I found a strange error which occurs when I run a query from PHP4, where if there is a column which has strings arbitrarily defined text for all rows of a sub-select, and you attempt to sort by that column, it cannot determine the type for that column in order to do the sort. 

- If you UNION more than one of these rows, the UNION seems to determine the data type for the column, so the sort works. 
- If the values are integers, the error does not occur
- if each arbitrary text value is explicitly cast, the error does not occur. I have not tested other data types.
- This error does NOT occur when the query is run in the psql command line.

If you have any8 questions, please feel free to contact me.

Thanks,
    -Matthew

Here is an example PHP page I wrote to demonstrate:
query1: 
SELECT a.col1, a.col2 
FROM (
	SELECT 'test row' AS col1, 1 AS col2
) AS a
ORDER BY a.col1
Running...


Warning:  pg_query(): Query failed: ERROR:  failed to find conversion function from "unknown" to text in /home3/manuel.ca/test/pgtest.php on line 14

done

 query2: 
SELECT a.col1, a.col2 
FROM (
	SELECT 'test row' AS col1, 1 AS col2
) AS a
ORDER BY a.col2
Running...
done

 query3: 
SELECT a.col1, a.col2 
FROM (
	SELECT 'test row' AS col1, 1 AS col2
	UNION
	SELECT 'next row' AS col1, 2 AS col2
) AS a
ORDER BY a.col1
Running...
done

 query4: 
SELECT a.col1, a.col2 
FROM (
	SELECT 'test row'::text AS col1, 1 AS col2
) AS a
ORDER BY a.col1
Running...
done

Reply | Threaded
Open this post in threaded view
|

Re: auto type casting bug

Richard Huxton
Matthew Manuel wrote:
> Hello, I found a strange error which occurs when I run a query from
> PHP4, where if there is a column which has strings arbitrarily defined
> text for all rows of a sub-select, and you attempt to sort by that
> column, it cannot determine the type for that column in order to do the
> sort.

I think it's actually a little more subtle than that, because of what
you're saying about psql.

> - If you UNION more than one of these rows, the UNION seems to determine
> the data type for the column, so the sort works. - If the values are
> integers, the error does not occur

Yep - the UNION will coerce the "unknown" type, or give an error.

> - if each arbitrary text value is explicitly cast, the error does not
> occur. I have not tested other data types.

That's correct behaviour.

> - This error does NOT occur when the query is run in the psql command line.

Now that puzzles me, since I get the error on the version I'm currently
logged into here (7.4.x). Could you just test it again?

> query1: SELECT a.col1, a.col2 FROM (
>     SELECT 'test row' AS col1, 1 AS col2
> ) AS a
> ORDER BY a.col1
> Running...
>
>
> *Warning*:  pg_query(): Query failed: ERROR:  failed to find conversion
> function from "unknown" to text in */home3/manuel.ca/test/pgtest.php* on
> line *14*

I don't suppose you could be running a locale of "C" in psql and
something else via php? I don't see how that could make the error go
away, but it's the only thing I can think of.

The heart of the problem is that the type is actually "unknown" and not
text. For example, if I had values '3 Jan 2005',' 3 Oct 2004' how should
they be sorted? Well, it depends on whether they are text or dates. How
does PG know which I want? It doesn't.

Actually, if we decide they are text then it depends on locale too,
since "C" locale will do a char-by-char sort whereas others will ignore
the leading space on the second example.

You can get similar problems with numeric literals if you want
floating-point or int8 instead of int4. PostgreSQL is flexible about its
types, but that does mean you need to be more precise in defining what
you mean sometimes.

HTH
--
   Richard Huxton
   Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq