[BUGS] auto type casting bug

Thu, 25 Aug 2005 20:48:50 -0700

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 via email to