Matt Sergeant wrote:
>I added it because of another bug report that was incorrectly sorting
>integer columns based on text sort order. [...]
>To be honest I'm not entirely sure what the correct fix is - maybe
>ignore the above bug and tell the requestor he has to: SELECT * FROM t
>ORDER BY int(Column2)
My copy of sqlite3 doesn't like "int(...)" in that context.
However, if I patch my copy of DBD::SQLite to removes the
looks_like_number() check and then run:
sqlite3 test 'create table t ( k text unique, v int);'
perl -e 'use DBI; $db = DBI->connect( "dbi:SQLite:dbname=test" );
$db->do( qq[REPLACE INTO t VALUES(?,?);], undef, "k1", "9");
$db->do( qq[REPLACE INTO t VALUES(?,?);], undef, "k2", "8");
$db->do( qq[REPLACE INTO t VALUES(?,?);], undef, "k3", "11");
$db->do( qq[REPLACE INTO t VALUES(?,?);], undef, "k4", "10");
'
sqlite3 test 'select * from t;'
sqlite3 test 'select * from t order by v;'
rm test
sqlite3 test 'create table t ( k text unique, v int);'
perl -e 'use DBI; $db = DBI->connect( "dbi:SQLite:dbname=test" );
$db->do( qq[REPLACE INTO t VALUES(?,?);], undef, "k1", "9");
$db->do( qq[REPLACE INTO t VALUES(?,?);], undef, "k2", "8");
$db->do( qq[REPLACE INTO t VALUES(?,?);], undef, "k3", "11");
$db->do( qq[REPLACE INTO t VALUES(?,?);], undef, "k4", "10");
'
sqlite3 test 'select * from t;'
sqlite3 test 'select * from t order by v;'
then I get:
k1|9
k2|8
k3|11
k4|10
k4|10
k3|11
k2|8
k1|9
k1|9
k2|8
k3|11
k4|10
k2|8
k1|9
k4|10
k3|11
So perhaps the check no longer performs a useful function now that
sqlite allows one to specify the data type of the column?
-jonathan
--
Jonathan H N Chin, 2 dan | deputy computer | Newton Institute, Cambridge, UK
<[EMAIL PROTECTED]> | systems mangler | tel/fax: +44 1223 767091/330508
"respondeo etsi mutabor" --Rosenstock-Huessy
