Reopening - the fix for mysql breaks Oracle again. And adding Eloy - CAST() to CHAR cross-db is the devil!
Note: this is important wrt Oracle support in 1.9 because the new accesslib uses similar code to this in many functions in the hot path – which is to say, we need this to work fast and reliably on the DB side.
- Oracle barfs on implicit cast to char as in
SELECT id || 'foo' FROM m_config;
it wants an explicit cast, like CAST(id AS CHAR(10)) or as VARCHAR(length). Oracle requires the length there – and will pad to that length. If we provide the length, we end up with
SELECT CAST(id AS CHAR(10) || 'foo' FROM m_config; // problem - will be space-padded on Pg and Oracle (not on mysql!)
SELECT CAST(id AS VARCHAR(10) || 'foo' FROM m_config; // problem - will error out on MySQL
and in either case, we may exceed the 10 chars
SELECT CAST(id AS CHAR) || 'foo' FROM m_config; // works in MySQL & Pg, barfs on Oracle
So far, the only cross-db solution I managed to get is
SELECT TRIM(CAST(id AS CHAR(10))) || 'foo' FROM m_config; // works in MySQL & Pg & Oracle - sems to have a minor perf impact on Pg
Still untested on MSSQL – which I don't have.
So for 1.9 I think we need a sql_castaschar($sqlexpr). Was going to add a $pad variable but mysql does not support that at all. See bug http://bugs.mysql.com/bug.php?id=24424 and have a good laugh.