MS SQL Asterisk WTF



  • I just executed the following query against an MS SQL 2000 database:


    select cast(1112222 as varchar(6))



    Yes, I know it's a WTF that a seven digit number is being cast to a six character string.

    What really made me go WTF! is the result this query returns:


    ------
    *

    (1 row(s) affected)


    I could understand SQL converting the number to '111222', NULL, or throwing an error. But it changed a number into an asterisk????




  • Two other data points.


    Oracle 10g:
       select cast(1112222 as varchar(6)) from dual;
       => ORA-25137: Data value out of range

    PostgreSQL 8.1:
        select cast(1112222 as varchar(6));
        => 111222



  •  

    WTF!  Works as documented?

     

    When converting character or binary expressions (char, nchar, nvarchar, varchar, binary, or varbinary) to an expression of a different data type, data can be truncated, only partially displayed, or an error is returned because the result is too short to display. Conversions to char, varchar, nchar, nvarchar, binary, and varbinary are truncated, except for the conversions shown in this table.

    From data type To data type Result
    int, smallint, or tinyint char *
      varchar *
      nchar E
      nvarchar E
    money, smallmoney, numeric, decimal, float, or real char E
      varchar E
      nchar E
      nvarchar E



  • @delta407 said:

    Two other data points.

    Oracle 10g:
    select cast(1112222 as varchar(6)) from dual;
    => ORA-25137: Data value out of range

    PostgreSQL 8.1:
    select cast(1112222 as varchar(6));
    => 111222

    One more item to add to my list of reasons to use Oracle (and be weirded out by SQL Server). 



  • from dual

    One more item already in my list of reasons to stick with things besides Oracle.



  • Eh, the Dual table isn't any where near the top of Oracle WTFs, it's just a side affect of Oracle's reliance on FROM clauses. In MySQL and Postgres you can SELECT 1; but not in Oracle. So the Oracle install creates this Dual table with one row, one column.

    Now image what happens to a system when someone adds another row to the Dual table...
     



  • A fun follow-up question:

    How does a table with a single column, and which contains a single record, which itself consists of only a single character end up with the name dual?


  • Discourse touched me in a no-no place

    @db2 said:

    A fun follow-up question:

    How does a table with a single column, and which contains a single record, which itself consists of only a single character end up with the name dual?

    http://web.archive.org/web/20041205061859/http://www.oracle.com/technology/oramag/oracle/02-jan/o12sendmail.html

    Now if there's anyone out there willing to translate that into something I can understand... 



  • @db2 said:

    from dual

    One more item already in my list of reasons to stick with things besides Oracle.

    If something that small will keep you from Oracle, that's awesome.  Good luck with all those CAST(NULL AS VARCHAR)-type WTF's from IBM.



  • @nerdydeeds said:

    @db2 said:
    from dual

    One more item already in my list of reasons to stick with things besides Oracle.

    If something that small will keep you from Oracle, that's awesome.  Good luck with all those CAST(NULL AS VARCHAR)-type WTF's from IBM.

    All database products suck, just in different ways.   



  • @lpope187 said:

    @nerdydeeds said:
    @db2 said:
    from dual

    One more item already in my list of reasons to stick with things besides Oracle.

    If something that small will keep you from Oracle, that's awesome.  Good luck with all those CAST(NULL AS VARCHAR)-type WTF's from IBM.

    All database products suck, just in different ways.   

    All software (that includes programming languages) sucks, just in different ways.  But correctness matters; flexibility matters; downtime matters; security matters.  It would be nice if there were something better than Oracle, but right now there's nothing close.



  • Yes, you have to love the Oracle fanboys who endlessly say that all you have to do to figure out Oracle is read the documentation or search the internet and you will find the answer to your problem but aren't able to look in the SQL Server documentation (BOL - which is provided with the product) to see whether they really found an issue or it works as documented.  As they are quick to point out over at the Oracle Haters Forum - just because it doesn't behave how YOU expect it to doesn't make it wrong.  I guess all that heavy lifting working with the big iron means they don't have to read the documentation for the little pc database, or just gasbag hypocrites.

     

    @gshouse said:

     

    WTF!  Works as documented?

     

    When converting character or binary expressions (char, nchar, nvarchar, varchar, binary, or varbinary) to an expression of a different data type, data can be truncated, only partially displayed, or an error is returned because the result is too short to display. Conversions to char, varchar, nchar, nvarchar, binary, and varbinary are truncated, except for the conversions shown in this table.

     

    From data type To data type Result
    int, smallint, or tinyint char *
      varchar *
      nchar E
      nvarchar E
    money, smallmoney, numeric, decimal, float, or real char E
      varchar E
      nchar E
      nvarchar E


  • @pasqldba said:

    Yes, you have to love the Oracle fanboys who endlessly say that all you have to do to figure out Oracle is read the documentation or search the internet and you will find the answer to your problem but aren't able to look in the SQL Server documentation (BOL - which is provided with the product) to see whether they really found an issue or it works as documented.  As they are quick to point out over at the Oracle Haters Forum - just because it doesn't behave how YOU expect it to doesn't make it wrong.  I guess all that heavy lifting working with the big iron means they don't have to read the documentation for the little pc database, or just gasbag hypocrites.

    The real WTF is that SQL Server doesn't tell you there's a problem. It just stores garbage and moves right along.  It doesn't truncate the data (like Postgres) so you can track down where the problem occurred, and it doesn't give an error message (like Oracle), it just spews garbage.  I don't mind looking up error messages or searching documentation (SQL Server's docs are much better than DB2's) but if you're going to defend product allows errors to pass through unnoticed or prevents you from dealing with certain errors (SQL Sever is very guilty of both) how can you sit and complain about fanboys?



  • @nerdydeeds said:

    @pasqldba said:

    Yes, you have to love the Oracle fanboys who endlessly say that all you have to do to figure out Oracle is read the documentation or search the internet and you will find the answer to your problem but aren't able to look in the SQL Server documentation (BOL - which is provided with the product) to see whether they really found an issue or it works as documented.  As they are quick to point out over at the Oracle Haters Forum - just because it doesn't behave how YOU expect it to doesn't make it wrong.  I guess all that heavy lifting working with the big iron means they don't have to read the documentation for the little pc database, or just gasbag hypocrites.

    The real WTF is that SQL Server doesn't tell you there's a problem. It just stores garbage and moves right along.  It doesn't truncate the data (like Postgres) so you can track down where the problem occurred, and it doesn't give an error message (like Oracle), it just spews garbage.  I don't mind looking up error messages or searching documentation (SQL Server's docs are much better than DB2's) but if you're going to defend product allows errors to pass through unnoticed or prevents you from dealing with certain errors (SQL Sever is very guilty of both) how can you sit and complain about fanboys?

    Do you really think the asterisk will that much harder to track down than the truncated version that Postgres keeps?  Either way you don't get what you are expecting.  The point that I am trying to make about fanboys is the frequent refrain of "check the documentation".  In my mind that is simply what we are dealing with here.  If a SQL Server user points out on the Oracle board here or many internet message boards something they find bizarre they are told to check the documentation, it is explained there.  That is what I am saying in this case.  Your expectation is one thing, you find the * bizarre.  My point is that this behavior is documented in BOL in at least three versions of SQL Server - 2000, 2005 & 2008.  Yes I checked all three, under the topic Cast and Convert.  The Oracle Fanboy is quick to point out that what one expects is irrelevant if the behavior is documented.  This behavior is documented.  It would be bizarre if it is documented the way it is but performed like Postgres or Oracle.
     



  • @pasqldba said:

    Do you really think the asterisk will that much harder to track down than the truncated version that Postgres keeps?  Either way you don't get what you are expecting.  The point that I am trying to make about fanboys is the frequent refrain of "check the documentation".  In my mind that is simply what we are dealing with here.  If a SQL Server user points out on the Oracle board here or many internet message boards something they find bizarre they are told to check the documentation, it is explained there.  That is what I am saying in this case.  Your expectation is one thing, you find the * bizarre.  My point is that this behavior is documented in BOL in at least three versions of SQL Server - 2000, 2005 & 2008.  Yes I checked all three, under the topic Cast and Convert.  The Oracle Fanboy is quick to point out that what one expects is irrelevant if the behavior is documented.  This behavior is documented.  It would be bizarre if it is documented the way it is but performed like Postgres or Oracle.

    Having loaded 60 million+ rows in a single batch, yes: having something to go on is very helpful.  Documented or not, it's a WTF (and not even consistent at that).  But seriously, who actually said "check the documentation" for this?  Someone posted the documentation, nobody berated the OP.  If it were something simple or just odd, I could understand someone shouting RTFM, but this is a real-life WTF that will apparently never be patched.



  • @nerdydeeds said:

    The real WTF is that SQL Server doesn't tell you there's a problem. It just stores garbage and moves right along.  It doesn't truncate the data (like Postgres) so you can track down where the problem occurred, and it doesn't give an error message (like Oracle), it just spews garbage.  I don't mind looking up error messages or searching documentation (SQL Server's docs are much better than DB2's) but if you're going to defend product allows errors to pass through unnoticed or prevents you from dealing with certain errors (SQL Sever is very guilty of both) how can you sit and complain about fanboys?

    Slap a constraint on the column such that it can't be equal to '*' or 'E'. Problem (mostly) solved. Better yet, cast to varchar(8000) or varchar(max) instead of something that's obviously too small. But honestly, how often do you need to cast numeric data types to character data types? If it's to store it in a table, the database design probably sucks. If it's for formatting, save it for the application layer.

    Interesting Dual table article, by the way. Though heck if I can figure out how joining against a table with one row in it should somehow double the size of your result set. Did Dual originally have two rows or something?



  • @db2 said:

    @nerdydeeds said:

    The real WTF is that SQL Server doesn't tell you there's a problem. It just stores garbage and moves right along.  It doesn't truncate the data (like Postgres) so you can track down where the problem occurred, and it doesn't give an error message (like Oracle), it just spews garbage.  I don't mind looking up error messages or searching documentation (SQL Server's docs are much better than DB2's) but if you're going to defend product allows errors to pass through unnoticed or prevents you from dealing with certain errors (SQL Sever is very guilty of both) how can you sit and complain about fanboys?

    Slap a constraint on the column such that it can't be equal to '*' or 'E'. Problem (mostly) solved. Better yet, cast to varchar(8000) or varchar(max) instead of something that's obviously too small. But honestly, how often do you need to cast numeric data types to character data types? If it's to store it in a table, the database design probably sucks. If it's for formatting, save it for the application layer.

    Interesting Dual table article, by the way. Though heck if I can figure out how joining against a table with one row in it should somehow double the size of your result set. Did Dual originally have two rows or something?

    Well, if I had more control over the data structures I work with, my life would be a lot simpler.  Yes, a constraint would solve the problem (so long as someone remembers to put them on every possibly affected column).  There are (too many) cases where I've had to import decimal numbers into character fields, and this kind of behavior really doesn't make sense to me. Microsoft could have done so much better (or maybe not?) if they hadn't borrowed from Sybase.

    In Oracle, you wouldn't normally do an explicit cast anyway, you would just supply a numeric instead of a character and Oracle would figure it out for you.  Or you would cast it using %TYPE, and not duplicate column definitions.  But varchar(6) is not obviously too small, except in the trivial example provided.  Many times I've been told it's impossible for the incoming data feed to exceed some limit, only to see it happen a few days or months later.  Always check your inputs, and use tools that report errors (or your hair might do like mine and start falling out).



  • In any case, it would be a good suggestion for MS. I could see the potential use of extra syntax like 'WITH ERROR_ON_TRUNCATION' for the CAST statement or an option like 'SET TRUNCATION_ABORT ON' to be extra careful, or for dealing with lousy database designs. I think you can submit stuff like that here: http://connect.microsoft.com/sql



  • @delta407 said:

    Two other data points.

    Oracle 10g:
       select cast(1112222 as varchar(6)) from dual;
       => ORA-25137: Data value out of range

    PostgreSQL 8.1:
        select cast(1112222 as varchar(6));
        => 111222


    MySQL truncates and has a warning:
    mysql> SELECT CAST(1112222 AS CHAR(6));
    +--------------------------+
    | CAST(1112222 AS CHAR(6)) |
    +--------------------------+
    | 111222                   |
    +--------------------------+
    1 row in set, 1 warning (0.07 sec)

    mysql> SHOW WARNINGS;
    +---------+------+----------------------------------------------+
    | Level   | Code | Message                                      |
    +---------+------+----------------------------------------------+
    | Warning | 1292 | Truncated incorrect CHAR(6) value: '1112222' |
    +---------+------+----------------------------------------------+
    1 row in set (0.00 sec)



  • @JamesKilton said:

    Now image what happens to a system when someone adds another row to the Dual table...

    It would be much more funny to replace the dual table with a view that returns one row most of the times; based on some random generator, it would sometimes return no row, sometimes two or more rows, sometimes cause an exception and sometimes do something so expensive that it never returns.
     



  • @nerdydeeds said:

    @pasqldba said:

    Do you really think the asterisk will that much harder to track down than the truncated version that Postgres keeps?  Either way you don't get what you are expecting.  The point that I am trying to make about fanboys is the frequent refrain of "check the documentation".  In my mind that is simply what we are dealing with here.  If a SQL Server user points out on the Oracle board here or many internet message boards something they find bizarre they are told to check the documentation, it is explained there.  That is what I am saying in this case.  Your expectation is one thing, you find the * bizarre.  My point is that this behavior is documented in BOL in at least three versions of SQL Server - 2000, 2005 & 2008.  Yes I checked all three, under the topic Cast and Convert.  The Oracle Fanboy is quick to point out that what one expects is irrelevant if the behavior is documented.  This behavior is documented.  It would be bizarre if it is documented the way it is but performed like Postgres or Oracle.

    Having loaded 60 million+ rows in a single batch, yes: having something to go on is very helpful.  Documented or not, it's a WTF (and not even consistent at that).  But seriously, who actually said "check the documentation" for this?  Someone posted the documentation, nobody berated the OP.  If it were something simple or just odd, I could understand someone shouting RTFM, but this is a real-life WTF that will apparently never be patched.

     It was never my intention to berate the OP.  I'm attempting to point out a double standard.  People have posted lots of weird Oracle behavior on the I Hate Oracle Club forum.  One of the frequently encountered replies on that forum is that the weird behavior is documented by Oracle, and it is implied that the weird behavior in Oracle ok because it is documented.  However, when someone points out a weird SQL Server behavior and someone else can point that this behavior is documented, then you see replies along the line of yours.  "This is a real-life WTF that will apparently never be patched."

    To get back to your data load example, which non-Oracle way of handling this issue do you think would be found sooner?  Neither Postgres nor SQL Server report an error.  Personally I think the Postgres way of handling this issue could go unnoticed much longer because you are at least seeing numbers in the character column.  Seeing the asterisk in SQL Server would cause one to immediately investigate WTF happened.  A little research in BOL points out that you are trying to stuff more data in the column than it can hold.  You can then make the necessary adjustments to fix the problem.  Even though I am not a fan of Oracle I think they are the only dbms to correctly handle this situation by providing an error.
     



  • @pasqldba said:

     It was never my intention to berate the OP.  I'm attempting to point out a double standard.  People have posted lots of weird Oracle behavior on the I Hate Oracle Club forum.  One of the frequently encountered replies on that forum is that the weird behavior is documented by Oracle, and it is implied that the weird behavior in Oracle ok because it is documented.  However, when someone points out a weird SQL Server behavior and someone else can point that this behavior is documented, then you see replies along the line of yours.  "This is a real-life WTF that will apparently never be patched."

    To get back to your data load example, which non-Oracle way of handling this issue do you think would be found sooner?  Neither Postgres nor SQL Server report an error.  Personally I think the Postgres way of handling this issue could go unnoticed much longer because you are at least seeing numbers in the character column.  Seeing the asterisk in SQL Server would cause one to immediately investigate WTF happened.  A little research in BOL points out that you are trying to stuff more data in the column than it can hold.  You can then make the necessary adjustments to fix the problem.  Even though I am not a fan of Oracle I think they are the only dbms to correctly handle this situation by providing an error.
     

    Errors like this are usually found during reviews by data analysts (if you're lucky enough to have (a good) one).  Otherwise, there aren't any great ways to deal with it.  I think I would be happier if SQL Server just returned one of * or E, instead of expecting you to know the data types.  Check constraints don't work if you're just passing information around in stored procedures or exporting to a text file, so there aren't a lot of good options.  I think the last time I saw this kind of thing I spent a couple days fixing data models, but that is not an option at some places I've worked.  I'm going to stop thinking of ways to solve this problem before I remember any more of the reams of error-checking code I had to write in T-SQL for this (and a lot of stuff like it).


Log in to reply