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
