SELECT CAST(123 AS char) -> 1
For sake of interoperability (and using an API that requires String-type
hashtable keys), I'm trying to find a single CAST (int -> var/char)
syntax that works between the most databases. Only char seems to be a
candidate, but in 8.3 casting from an integer outputs only the first char...
Is this a bug, or would someone like to horrify me by stating something
like "spec says this is correct". :-)
I noticed this is also occurring on date/time types though that's not my
need/concern.
Ken Johanson <pg-user@kensystem.com> writes:
For sake of interoperability (and using an API that requires String-type
hashtable keys), I'm trying to find a single CAST (int -> var/char)
syntax that works between the most databases. Only char seems to be a
candidate, but in 8.3 casting from an integer outputs only the first char...
Is this a bug, or would someone like to horrify me by stating something
like "spec says this is correct". :-)
Okay: the spec says this is correct.
SQL92 section 6.1 <data type> quoth
<character string type> ::=
CHARACTER [ <left paren> <length> <right paren> ]
| CHAR [ <left paren> <length> <right paren> ]
...
4) If <length> is omitted, then a <length> of 1 is implicit.
Therefore, writing just "char" is defined as equivalent to "char(1)".
Also, section 6.10 <cast specification> defines an explicit cast to
a fixed-length string type as truncating or padding to the target
length (LTD):
Case:
i) If the length in characters of SV is equal to LTD, then TV
is SV.
ii) If the length in characters of SV is larger than LTD, then
TV is the first LTD characters of SV. If any of the re-
maining characters of SV are non-<space> characters, then a
completion condition is raised: warning-string data, right
truncation.
iii) If the length in characters M of SV is smaller than LTD,
then TV is SV extended on the right by LTD-M <space>s.
We don't report a "completion condition" for lack of any infrastructure
for that, but the result of the expression is per spec.
Possibly you could get what you want by casting to char(10) or so.
regards, tom lane
Tom Lane wrote:
SQL92 section 6.1 <data type> quoth
<character string type> ::=
CHARACTER [ <left paren> <length> <right paren> ]
| CHAR [ <left paren> <length> <right paren> ]...
4) If <length> is omitted, then a <length> of 1 is implicit.
Therefore, writing just "char" is defined as equivalent to "char(1)".
However when length is not defined I think it will generally be safe(r)
to auto-size. In the grand scheme auto-size creates much more sensible
output than a 1-char wide one (even if right-padded to max char-length
of the type).
Also, section 6.10 <cast specification> defines an explicit cast to
a fixed-length string type as truncating or padding to the target
length (LTD):
And PG does this, perfectly. It even right-pads, the other databases
(tried My and Ms) do not...
Possibly you could get what you want by casting to char(10) or so.
Alas the behavior is different. The right padding exists (in PG). So I
cannot get uniform behavior (the other DB's fault I agree for not
supporting cast as varchar).
Unless PG can start throwing an exception in this version when it
truncates to implicit-1, I think it should be forgiving and auto-size..
Is it possible to override this built-in cast function with a create-cast?
On Mon, Feb 11, 2008 at 10:36:49PM -0700, Ken Johanson wrote:
For sake of interoperability (and using an API that requires String-type
hashtable keys), I'm trying to find a single CAST (int -> var/char)
syntax that works between the most databases. Only char seems to be a
candidate, but in 8.3 casting from an integer outputs only the first char...
Does it have to be a cast? I would've thought to_char() might work more
reliably across databases.
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Show quoted text
Those who make peaceful revolution impossible will make violent revolution inevitable.
-- John F Kennedy
"Ken Johanson" <pg-user@kensystem.com> writes:
Tom Lane wrote:
SQL92 section 6.1 <data type> quoth
<character string type> ::=
CHARACTER [ <left paren> <length> <right paren> ]
| CHAR [ <left paren> <length> <right paren> ]...
4) If <length> is omitted, then a <length> of 1 is implicit.
Therefore, writing just "char" is defined as equivalent to "char(1)".
However when length is not defined I think it will generally be safe(r) to
auto-size. In the grand scheme auto-size creates much more sensible output than
a 1-char wide one (even if right-padded to max char-length of the type).
Sure, but you're a prime candidate for understanding the value of following
the spec if you're trying to write software that works with multiple
databases.
It's a bit crazy to be using CHAR and then complaining about padding... That's
what CHAR is for. If the other database doesn't support varchar it's so far
from the SQL spec that writing something portable between it and something
else is probably hopeless.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's On-Demand Production Tuning
Gregory Stark wrote:
"Ken Johanson" <pg-user@kensystem.com> writes:
Tom Lane wrote:
SQL92 section 6.1 <data type> quoth
<character string type> ::=
CHARACTER [ <left paren> <length> <right paren> ]
| CHAR [ <left paren> <length> <right paren> ]...
4) If <length> is omitted, then a <length> of 1 is implicit.
Therefore, writing just "char" is defined as equivalent to "char(1)".
However when length is not defined I think it will generally be safe(r) to
auto-size. In the grand scheme auto-size creates much more sensible output than
a 1-char wide one (even if right-padded to max char-length of the type).Sure, but you're a prime candidate for understanding the value of following
the spec if you're trying to write software that works with multiple
databases.
The spec has diminished in this (CAST without length) context:
a) following it produces an output which has no usefulness whatsoever
(123 != 1)
b) all the other databases chose to not follow the spec in the context
of cast and char with implicit length.
When the length is unqualified, a cast to char should one of:
1) failfast
2) auto-size to char-count (de facto)
3) pad to the max-length
It's a bit crazy to be using CHAR and then complaining about padding...
I did say earlier that I could at least accept padding to the max-char
length, even though in my use-case it wont work.
On 2008-02-12 07:30, Ken Johanson wrote:
Sure, but you're a prime candidate for understanding the value of
following the spec if you're trying to write software that works with
multiple databases.The spec has diminished in this (CAST without length) context:
a) following it produces an output which has no usefulness whatsoever
(123 != 1)
I *OFTEN* use a cast of CHAR to get just the first character.
b) all the other databases chose to not follow the spec in the context
of cast and char with implicit length.
I doubt that:
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db2.doc.sqlref/castsp.htm
http://msdn2.microsoft.com/en-us/library/aa258242(SQL.80).aspx
Your specific example is covered here:
http://vista.intersystems.com/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL_cast
and here:
http://answers.yahoo.com/question/index?qid=20071017084134AA4mCJC
When the length is unqualified, a cast to char should one of:
1) failfast
2) auto-size to char-count (de facto)
3) pad to the max-length
What is wrong with using VARCHAR for your
purpose???????????????????????????? If you want the string auto-sized,
that is what VARCHAR is for.
CHAR is, BY DEFINITION, a DECLARED fixed length.
Dean Gibson (DB Administrator) wrote:
On 2008-02-12 07:30, Ken Johanson wrote:
Sure, but you're a prime candidate for understanding the value of
following the spec if you're trying to write software that works with
multiple databases.The spec has diminished in this (CAST without length) context:
a) following it produces an output which has no usefulness whatsoever
(123 != 1)I *OFTEN* use a cast of CHAR to get just the first character.
I'm guessing you declare an explicit length of 1 (for portability), or
do you "CAST (x as char)"? And one might ask in what context we'd need
CHAR(1) on a numeric type, or else if substr/ing or left() make the code
more readable for other data types..
b) all the other databases chose to not follow the spec in the context
of cast and char with implicit length.I doubt that:
http://msdn2.microsoft.com/en-us/library/aa258242(SQL.80).aspx
The actual behavior is to autosize on MS and My. I do not have DB2 but
would be curious to know how it behaves.
When the length is unqualified, a cast to char should one of:
1) failfast
2) auto-size to char-count (de facto)
3) pad to the max-lengthWhat is wrong with using VARCHAR for your
purpose????????????????????????????
Simply that a commonly used database (my) does not support it. I HAVE to
support that one (too widely in use), better/worse, its not my choice.
Even at the expense of its standards deviation, and for doubting the
vendor(s) will the change behavior (and break the app).
Ken Johanson <pg-user@kensystem.com> writes:
What is wrong with using VARCHAR for your
purpose????????????????????????????
Simply that a commonly used database (my) does not support it.
They do support char(n) in this context, which would have the advantage
of being standards compliant as well as de-facto portable.
Even at the expense of its standards deviation, and for doubting the
vendor(s) will the change behavior (and break the app).
You say that with a straight face while arguing for us to diverge from
the spec? It seems unlikely to me that other DBs would change their
historical behavior except to make it comply to spec.
regards, tom lane
Tom Lane wrote:
Simply that a commonly used database (my) does not support it.
They do support char(n) in this context, which would have the advantage
of being standards compliant as well as de-facto portable.
Hmm, interesting. Mysql actual returns:
select cast(123 AS char(10)) -> '123' (agreed wrong since length is
explicit)
And PG (Ms also) gives:
select cast(123 AS char(10)) -> '123 ' (tested via jdbc driver since
hard to visualize the space in psql)
and PG:
select '='||cast(123 AS char(10))||'=' -> '=123=' (test via psql)
On 2008-02-12 16:17, Ken Johanson wrote:
Dean Gibson (DB Administrator) wrote:
...I'm guessing you declare an explicit length of 1 (for portability), or
do you "CAST (x as char)"? And one might ask in what context we'd need
CHAR(1) on a numeric type, or else if substr/ing or left() make the
code more readable for other data types..
Actually, I just write "CHAR" for a length of 1.
What is wrong with using VARCHAR for your
purpose????????????????????????????
Simply that a commonly used database (my) does not support it.
By "my", do you mean "MySQL", or "MyDatabase"? If the latter, then
while it's your business decision (and/or that of your customers), the
availability of decent, free databases should make a compelling case for
anyone using anything else, to migrate (and never look back) to
something full-featured.
It's like requiring portable C code to use the old, pre-ANSI style of
function declarations, because some old C compilers might not support
the ANSI style. I think Richard Stallman of the FSF takes that
position, but I don't know of anyone else (although I'm sure there are
exceptions).
--
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.
Dean Gibson (DB Administrator) wrote:
On 2008-02-12 16:17, Ken Johanson wrote:
Dean Gibson (DB Administrator) wrote:
...I'm guessing you declare an explicit length of 1 (for portability), or
do you "CAST (x as char)"? And one might ask in what context we'd need
CHAR(1) on a numeric type, or else if substr/ing or left() make the
code more readable for other data types..Actually, I just write "CHAR" for a length of 1.
On a numeric type?.. That's the quintessential part to me...
What is wrong with using VARCHAR for your
purpose????????????????????????????
Simply that a commonly used database (my) does not support it.
By "my", do you mean "MySQL", or "MyDatabase"? If the latter, then
while it's your business decision (and/or that of your customers), the
availability of decent, free databases should make a compelling case for
anyone using anything else, to migrate (and never look back) to
something full-featured.
Yes, Mysql, and yes, it's customer driven.
It's like requiring portable C code to use the old, pre-ANSI style of
function declarations, because some old C compilers might not support
the ANSI style. I think Richard Stallman of the FSF takes that
position, but I don't know of anyone else (although I'm sure there are
exceptions).
Point taken. This is really just a rock and hard place because I'm stuck
between 3rd party products (customer API and database x^n). I'm trying
to convey here that changing the behavior to a (numb AS varchar) one,
practically speaking, is more benign/useful (vs now), even if that is
only a non-spec workaround, and "everyone else does it" is an invalid
arg. I'm much more concerned about the AS in column labels issue and
some driver todos. The pre standard_conforming_strings behavior used to
be the full show stopper for PG, and now I only hear smaller
compatibility and ease of migration concerns (whether spec or defacto).
Things are improving.
On 2008-02-12 19:39, Ken Johanson wrote:
Dean Gibson (DB Administrator) wrote:
On 2008-02-12 16:17, Ken Johanson wrote:
Dean Gibson (DB Administrator) wrote:
...I'm guessing you declare an explicit length of 1 (for portability),
or do you "CAST (x as char)"? And one might ask in what context we'd
need CHAR(1) on a numeric type, or else if substr/ing or left() make
the code more readable for other data types..Actually, I just write "CHAR" for a length of 1.
On a numeric type?.. That's the quintessential part to me...
No, not on a numeric type. The database stores a single byte code from
a gov't DB. In a VIEW, I do a table lookup on the code and suffix an
English explanation of the code. However, some of the users of the VIEW
(eg, php) would like to do a SELECT based on the original value, and I
use CAST( ... AS CHAR ) to get just the original code back. I use the
CAST as a shorthand for SUBSTRING. I don't know if that is easier for
the planner to flatten than a function call, but it's easier (for me) to
read (especially if I use the PostgreSQL "::" cast extension).
What is wrong with using VARCHAR for your
purpose????????????????????????????
Simply that a commonly used database (my) does not support it.
By "my", do you mean "MySQL", or "MyDatabase"? If the latter, then
while it's your business decision (and/or that of your customers),
the availability of decent, free databases should make a compelling
case for anyone using anything else, to migrate (and never look back)
to something full-featured.Yes, Mysql, and yes, it's customer driven.
Then I don't understand. While I've never used MySQL, the MySQL web
pages apparently indicate that VARCHAR has been supported since version
3.2: http://dev.mysql.com/doc/refman/4.1/en/char.html
--
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.
Dean Gibson (DB Administrator) wrote:
Then I don't understand. While I've never used MySQL, the MySQL web
pages apparently indicate that VARCHAR has been supported since version
3.2: http://dev.mysql.com/doc/refman/4.1/en/char.html
Only in DDL and not the cast function, apparently. Mysql 5.1 says:
select cast(123 AS varchar)
or
select cast(123 AS varchar(10))
You have an error in your SQL syntax; check the manual that corresponds
to your MySQL server version for the right syntax to use near 'varchar)'
at line 1.
On Tue, Feb 12, 2008 at 08:39:05PM -0700, Ken Johanson wrote:
between 3rd party products (customer API and database x^n). I'm trying
to convey here that changing the behavior to a (numb AS varchar) one,
practically speaking, is more benign/useful (vs now), even if that is
only a non-spec workaround, and "everyone else does it" is an invalid
arg.
No, you're trying to convey that it is more benign/useful _to you_. Others
are arguing that they want to write conformant code, and don't much care
what MyOccasionallyReadTheSpec does. It's a pity that SQL conformance is
not better across systems, but surely the way to improve that is to reduce
the number of products that are being careless, not to increase them?
A
Andrew Sullivan wrote:
No, you're trying to convey that it is more benign/useful _to you_. Others
are arguing that they want to write conformant code, and don't much care
what MyOccasionallyReadTheSpec does. It's a pity that SQL conformance is
not better across systems, but surely the way to improve that is to reduce
the number of products that are being careless, not to increase them?
Let's leave it as-is then. We'll conform to the spec, and this good
since someone may want to extract the leftmost char from a base-10
number representation.
Henceforth SELECT CAST(123 AS char) will and should undisputedly return '1'.
Ken Johanson wrote:
Henceforth SELECT CAST(123 AS char) will and should undisputedly return '1'.
If you are arguing that the spec's definition of the CHARACTER type is
not really very useful, I think you are going to find a lot of
supporters. You can send your complaints to the SQL committee; but
then, it is unlikely that this is going to change anytime soon because
of the fear of breaking backwards compatibility.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
Alvaro Herrera wrote:
If you are arguing that the spec's definition of the CHARACTER type is
not really very useful, I think you are going to find a lot of
supporters. You can send your complaints to the SQL committee; but
then, it is unlikely that this is going to change anytime soon because
of the fear of breaking backwards compatibility.
Agreed. There may be allot of users who in the case of CAST, need to
extract the leftmost digit, and for those database and their users that
do this by default, it would cause harm.
Ken Johanson wrote:
Alvaro Herrera wrote:
If you are arguing that the spec's definition of the CHARACTER type is
not really very useful, I think you are going to find a lot of
supporters. You can send your complaints to the SQL committee; but
then, it is unlikely that this is going to change anytime soon because
of the fear of breaking backwards compatibility.Agreed. There may be allot of users who in the case of CAST, need to
extract the leftmost digit, and for those database and their users that
do this by default, it would cause harm.
Right. And those that don't need that are certainly using a cast to
CHARACTER VARYING.
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera wrote:
Ken Johanson wrote:
Alvaro Herrera wrote:
If you are arguing that the spec's definition of the CHARACTER type is
not really very useful, I think you are going to find a lot of
supporters. You can send your complaints to the SQL committee; but
then, it is unlikely that this is going to change anytime soon because
of the fear of breaking backwards compatibility.Agreed. There may be allot of users who in the case of CAST, need to
extract the leftmost digit, and for those database and their users that
do this by default, it would cause harm.Right. And those that don't need that are certainly using a cast to
CHARACTER VARYING.
What I couldn't figure out was what type MySQL was using. I mean, what
type is this?
mysql> SELECT cast(a as char) as achar FROM tt;
+-------+
| achar |
+-------+
| 1 |
| 10 |
+-------+
2 rows in set (0.00 sec)
Is it char(2)?
mysql> CREATE TEMPORARY TABLE ttchar0 AS SELECT cast(a as char) as achar
FROM tt;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> describe ttchar0;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| achar | varchar(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+TA DAH! It looks like you are casting to varchar anyway Ken, it's just
that MySQL isn't letting you spell it properly. So does an unconstrained
"char" just map to varchar with MySQL then?
mysql> CREATE TEMPORARY TABLE ttx (c char);
Query OK, 0 rows affected (0.00 sec)
mysql> describe ttx;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c | char(1) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)mysql> INSERT INTO ttx VALUES (123);
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> SELECT * FROM ttx;
+------+
| c |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
Hmm - looks like a cast to char doesn't produce values that fit into a
column defined as char. I'll say this for MySQL - always something new
to learn!
Looks like you have to choose between convenience and sanity Ken.
--
Richard Huxton
Archonet Ltd
