Oracle's ridiculously narrow concept of number



  • (This really belongs both in the IHOC and in Coding Help -- so if someone has a solution, that'd be appreciated!)

    So, I have a database column that's the address of a bit of kit out in the field, as imported from a file describing the configurations of that kit.

    Thing is, some of these addresses are in hex and others are in binary, as such:


    address

    0x5
    111000
    000011
    0x42

    How do I convert these to decimal for reporting purposes, or am I stuck having the DB spit out this garbage and then cleaning it up afterwards? TO_NUMBER doesn't handle binary at all, nor does it handle the 0x prefix on hex...



  • When getting data in two different formats, I'd normally have a raw_address column for the data that you have, then either one or two columns, depending on whether you need both formats, for a standardized value.

    That's assuming that the source is giving it to you in different formats.



  • Maybe rework some of these functions as appropriate?



  • @chubertdev said:

    When getting data in two different formats, I'd normally have a raw_address column for the data that you have, then either one or two columns, depending on whether you need both formats, for a standardized value.

    That's assuming that the source is giving it to you in different formats.

    The source is giving it to me in different formats, in the same "column" (really, a name=value token in a text file, that being one of the major WTFs I deal with daily).

    @MathNerdCNU said:

    Maybe rework some of these functions as appropriate?

    Looked at that -- doesn't help me with the mix of bases in one column. Also -- a stored function would require DBA intervention, which is possible, but not ideal.



  • @tarunik said:

    How do I convert these to decimal for reporting purposes

    What is putting the data into the database? Just about any language will have a conversion function you can run it through. The database isn't a great place for code.



  • So I understand:

    You have mixed data coming in; ie, pure binary and 0xhex, but you can't get a DBA to do their fucking job create SPROCs that will convert the mixed data to a reportable format? You are 11 kinds of screwed if I ever saw it.



  • @MathNerdCNU said:

    So I understand:

    You have mixed data coming in; ie, pure binary and 0xhex, but you can't get a DBA to [s]do their fucking job[/s] create SPROCs that will convert the mixed data to a reportable format? You are 11 kinds of screwed if I ever saw it.

    I have the mixed data brought in already. I can get a stored function created, but it'd add days to the process of creating a single, near-one-off report -- this report is meant for use in troubleshooting a very specific class of data issue.

    @Jaime said:

    What is putting the data into the database? Just about any language will have a conversion function you can run it through. The database isn't a great place for code.

    Like I said -- the data's already in the database. I do have enough control over the process to add import-time translation, but I also need to be able to reverse the translation as the table it is in is not dedicated to reporting...


  • :belt_onion:

    Step 1: Shoot the motherfucker that stuck that trash in 1 column in your database.



  • @tarunik said:

    The source is giving it to me in different formats, in the same "column" (really, a name=value token in a text file, that being one of the major WTFs I deal with daily).

    If you put that in a raw column and can easily determine the format, then my solution above should help.


  • Discourse touched me in a no-no place

    @tarunik said:

    but it'd add days to the process of creating a single, near-one-off report

    SELECT *, convert_from_hex(col_raw_number) AS col_decimal FROM tbl_table WHERE col_raw_number LIKE '0x%'
    UNION
    SELECT *, convert_from_bin(col_raw_number) AS col_decimal FROM tbl_table WHERE col_raw_number NOT LIKE '0x%';
    

    Stick that in a set of parens and make it the target of the SELECT that deals with your report, only use col_decimal and ignore col_raw_number.

    Or replace the * with all the fields except col_raw_number and alias the the convert_from_*() results as col_raw_number.

    Implementing convert_from_*() bits are left as an exercise for the student...



  • @PJH said:

    Implementing convert_from_*() bits are left as an exercise for the student...

    That's where the DBA gets involved -- TRWTF here is that I don't have the rights to CREATE FUNCTION in dev :P

    Otherwise, good idea...


  • Discourse touched me in a no-no place

    >=8.1.5 to_number(SUBSTRING(col_raw_number, 2?, 'xxxxxxxxxxor however many are needed')) as col_decimal looks as if it should work for the former.

    getting creative with SUBSTRING(col_raw_number, 2^n-pos,1)*2^n repeated and summated a few times could get you binary...



  • The binary problem was solved by BIN_TO_NUM combined with SUBSTR abuse ;)

    Also -- you can use scalar subqueries FROM DUAL to do things to the columns coming back from your query -- which came in handy in this case!

    P.S. your solution needed an extra branch or two -- it didn't account for the mixed endianness that was going on, and the presence of decimal values as well...

    @darkmatter said:

    Step 1: Shoot the motherfucker that stuck that trash in 1 column in your database.

    Well, I suspect that that was an artifact of good intentions from a long time ago, back when Oracle was a twinkling in Ellison's eye...


  • Discourse touched me in a no-no place

    @tarunik said:

    P.S. your solution needed an extra branch or two

    Wait.. wot? You went for that??



  • @PJH said:

    Wait.. wot? You went for that??

    The overall structure, yeah. Works a treat when combined with the BIT_TO_NUM hack...


  • Discourse touched me in a no-no place

    @darkmatter said:

    Shoot the motherfucker that stuck that trash in 1 column in your database.

    One of our projects involves dealing with a database where the encoding of text field contents varies by row, and even occasionally by column within a row. The data is in many encodings (it's the names of various people from around the world) the encodings aren't consistent (it depends on who put the data in and where the data originally came from) the database is owned by a third-party, and users feel massively constrained to work with it (it's the leading database for that data in the whole world). Oh, and the REST web service they've wrapped around it tells lies and just claims that everything is UTF-8. :facepalm:

    I don't know if it is an Oracle DB, but I do know that the poor database isn't the villain of the piece. (There's other problems, such as confusion about whether the locations are of where the animal was found or where the museum with the stuffed corpse is 😃 but they're minor by comparison.)


  • Java Dev

    I know if you give oracle some data and tell it it's in its internal character set, it won't argue until it needs to transcode (at which point it'll blow up).



  • Another serious Oracle WTF:

    [code]
    SQL> select count(*) from some_table where some_pretend_boolean_flag not in ('T', 'F');

    count(*)

         0
    

    SQL> select id, name, some_pretend_boolean_flag from some_table where name in ('name1', 'name2');
    id name some_pretend_boolean_flag


    0XAAAAAA name1
    0XAAABBB name2
    [/code]

    WTF ORACLE?!! :wtf: :wtf: :wtf:

    [spoiler]
    For those who don't get it -- Oracle apparently thinks the empty string (aka NULL) is IN the set ('T','F').
    [/spoiler]


  • :belt_onion:

    That's no WTF, the flag is null (which is neither in nor not in ('T', 'F'), unfortunately).

    Okay, there is a WTF there, in that Oracle doesn't allow empty strings. An oracle empty string IS null (not EQUAL TO null, IS null)



  • @darkmatter said:

    the flag is null (which is neither in nor not in ('T', 'F'), unfortunately).

    Well, not Oracle's fault specifically...but make up your Belgium‍ing mind, Oracle! There is no mathematical reason that Oracle should throw up its hands and decide that NULL is neither in nor not in the given set!


  • :belt_onion:

    @tarunik said:

    Oracle apparently thinks the empty string (aka NULL) is IN the set ('T','F').

    It's not in any set, and it's not not in any set. It's not even IN (NULL). Which is what makes it a real pisser that you can't have an actual empty string, because it means every comparison of strings where the empty string is possible must include an IS NULL / IS NOT NULL check.


  • Discourse touched me in a no-no place

    Unless you use NVL(this_might_be_null_but_gets_returned_if_its_not,return_this_if_it_is) in the comparison.



  • It's in the set, but counts as zero. We already covered this elsewhere.

    Not bothering with Discosearch™.


  • :belt_onion:

    @chubertdev said:

    It's in the set, but counts as zero.

    nein. he did count(*) not count(field_name), though it is correct that count(field_name) of a null field would return 0 even if you properly did an IS NULL comparison.

    @loopback0 said:

    Unless you use NVL(this_might_be_null_but_gets_returned_if_its_not,return_this_if_it_is) in the comparison

    Except now you have to do what, use magic strings that aren't supposed to exist in the data? Sure,

    NVL(field_with_nulls, 'NULL IS NOT TRUE OR FALSE') not in ('T','F')

    works, but are you going to do something like

    NVL(field_with_nulls, 'I PROMISE THIS WONT EXIST ANYWHERE ELSE IN THE DATA') not in (select NVL(some_other_field_with_nulls,'I PROMISE THIS WONT EXIST ANYWHERE ELSE IN THE DATA') from another_table)

    to compare when your comparison set isn't a predetermined set of strings keyed by hand?



  • @darkmatter said:

    nein. he did count(*) not count(field_name), though it is correct that count(field_name) of a null field would return 0 even if you properly did an IS NULL comparison.

    Oh wait, that is messed up.


  • Discourse touched me in a no-no place

    @darkmatter said:

    Except now you have to do what, use magic strings that aren't supposed to exist in the data?

    Depends what you're trying to do, but yes, you might.

    Comparisons against null (outside of is null or is not null) are unknown, once you know that, it's really not a problem.


  • :belt_onion:

    @loopback0 said:

    once you know that, it's really not a problem.

    Knowing it doesn't make it less annoying when you have a legitimate reason to need to store both '' AND a null in the same column. I have no middle name. If I fill in personal info, my middle name is not unknown, it is '' and should therefore not be null, it should be ''. Had I not filled in the name section, then it would be unknown and null would be appropriate.

    Oracle's empty string null is about as sensible as their lack of bit/boolean field types. It's like they crave people making up their own booleans and rolling with 'T', 'F', 'Y', 'N', 'MAYBE', or 'FILE_NOT_FOUND'.

    It makes me joyous that we're moving all our databases out of Oracle.



  • So a value that is known to not exist (A middle name) is equivalent to a value that does exist (Empty string)?
    How is that any different to what oracle did?



  • Oracle: NULL vs '' -> JS undefined vs null

    New look, same old :wtf:



  • @Salamander said:

    So a value that is known to not exist (A middle name) is equivalent to a value that does exist (Empty string)?How is that any different to what oracle did?

    No, @darkmatter's middle name is empty. This is different from someone who didn't specify their middle name, which is also different from a case where someone's name record was missing in an OUTER JOIN.

    Oracle (with help from the SQL standard) makes this into a :wtf: by lumping this all into NULL.

    Of course, the needlessly sketchy comparison semantics of NULLs in SQL proceed to magnify this :wtf: into a near language breaker...

    (Seriously, if anyone who's familiar with the SQL committee or the innards of databases is around, why didn't SQL adopt comparison semantics for NULL that keep NULLs from leaking past comparison operators and into your logic, where they promptly proceed to cause havoc?)



  • @tarunik said:

    No, @darkmatter's middle name is empty. This is different from someone who didn't specify their middle name, which is also different from a case where someone's name record was missing in an OUTER JOIN.

    No, he does not have an "empty" middle name, because there is no such thing as "a" middle name.
    The number of middle names that a person can have is variable, and two or more is not an unrealistic possibility. His middle name is not blank in the same sense that his other 24 middle names are not blank.
    Not having any middle names is different to not knowing any middle names, which is different again to having any blank middle names.


  • Java Dev

    Also note the conditional NULL IN ('T','F') is not false. It is null. Otherwise NOT(NULL IN ('T','F')) would be true, but in fact that is also NULL. This means booleans in SQL (not just oracle, AFAIK) are actually tristate.

    Semantics for NULL in boolean logic are as 'unknown', so NULL AND FALSE is false, NULL OR TRUE is true, and all other operations I can think of at the moment return NULL.



  • @tarunik said:

    SQL adopt comparison semantics for NULL that keep NULLs from leaking past comparison operators and into your logic, where they promptly proceed to cause havoc

    The semantics for NULL are consistent and sane. NULL is treated as "could be anything". NULL = NULL could be true, or maybe false, depending on what each NULL turns out to be. NULL IN (5, 6) is true if the value is 5 and false if the value is 7. So, it could be anything - which is expressed as NULL.



  • @tarunik said:

    Oracle (with help from the SQL standard) makes this into a by lumping this all into NULL.

    It's been awhile since I read it, but I'm pretty sure the SQL standard (which nobody follows anyway) specifies NULL and empty string as different.



  • @blakeyrat said:

    It's been awhile since I read it, but I'm pretty sure the SQL standard (which nobody follows anyway) specifies NULL and empty string as different.

    It indeed does -- Oracle is the :wtf: when it lumps them together. I was referring to that and the still-overloaded meaning of the standard SQL NULL (it can mean "element doesn't exist because it was because the table row wasn't around in an outer join", "element doesn't exist because the data source cannot provide such an element in general", "element doesn't exist because the data source provided nothing for that specific row", or "element doesn't exist because the table's denormalized and that element doesn't apply to that type of value", among other things...) in one fell swoop, though.

    Say -- I wonder if tagged NULLs would be a worthwhile addition to a RDBMS?



  • @Jaime said:

    The semantics for NULL are consistent and sane. NULL is treated as "could be anything". NULL = NULL could be true, or maybe false, depending on what each NULL turns out to be. NULL IN (5, 6) is true if the value is 5 and false if the value is 7. So, it could be anything - which is expressed as NULL.

    They are consistent. They are NOT SANE!!! The entire theory of relational algebra was founded on binary (Boolean) logic -- the use of tri-valued logic which treats NULL as an UNKNOWN leaves you wondering just what to do when that UNKNOWN gets to the top level...

    Compare this with the IEEE 754 NaN, which cannot propagate beyond comparisons and does not generally have a deleterious effect on Boolean logic...



  • @tarunik said:

    Compare this with the IEEE 754 NaN, which cannot propagate beyond comparisons and does not generally have a deleterious effect on Boolean logic...

    NULL is not the same semantic thing as NaN. NaN means that you performed a mathematical operation and the result cannot be represented in this numbering system. NULL means you have no idea what the value is. NULL is semantically valid in circumstances where NaN is nonsensical - like for non numeric fields.

    In expression contexts, NULL is "unknown". In data contexts NULL is "no information". Since you are talking about nulls NULLS through comparisons, the former is the definition that applys. Whenever you switch from data context to expression context, it's very important to determine if NULLs are possible and how they should be treated. NVL, ISNULL, and friends were invented to handle the case where you wanted NULL to be treated as zero, empty string, or something else. You don't always want NULLs to have this treatment - especially for things like averages.

    SELECT AVG(LateFee) FROM LibraryBookCheckouts
    SELECT AVG(NVL(LateFee, 0)) FROM LibraryBookCheckouts
    

    Both of these mean something different and both are useful. The first gives you the average of all non-NULL late fees. This could be used to figure out if your late fee policy is sufficient to pay the salaries of the people that handle doing collections. The first gives you the average late fee divided across all checkouts. This number bakes in the rate of lateness and would be useful for determining the effect of in influx of new borrowers to a library (perhaps due to a closure of a nearby library).



  • @Jaime said:

    applys.

    Ftfy
    Applies.



  • @Jaime said:

    NULL is not the same semantic thing as NaN. NaN means that you performed a mathematical operation and the result cannot be represented in this numbering system. NULL means you have no idea what the value is. NULL is semantically valid in circumstances where NaN is nonsensical - like for non numeric fields.

    Agreed that NULL is not the same thing as NaN -- however, I was simply using NaN as an example of an exceptional value that doesn't propagate through comparisons.

    What I'm arguing is that NULL's should propagate through value operations (i.e. behave in a data context) as you describe (the only operation where this is annoying is concatenation), but behave sanely when compared (returning FALSE for all comparisons involving NULL is what I'd do), instead of returning these UNKNOWNs that proceed to leave relational algebraists scratching their heads.



  • @smallshellscript said:

    Oracle: NULL vs '' -> JS undefined vs null

    No, in the Oracle case, they're making two distinct values equivalent. In the JavaScript case, they're making two equivalent values distinct.



  • @ben_lubar said:

    No, in the Oracle case, they're making two distinct values equivalent. In the JavaScript case, they're making two equivalent values distinct.

    Not a heavy JS users so I may be talking out of my ass here but my impression was that JS null represents an empty object (sort of a more general case of the empty string) while undefined represents a non-existent object. It's like it's a naming convention problem - JS undefined should be called null and null should be something else entirely.


  • Discourse touched me in a no-no place

    @smallshellscript said:

    JS undefined should be called null and null should be something else entirely.

    Rename null to PiersMorgan. Annoying and empty? It must match!


  • Discourse touched me in a no-no place

    @dkf said:

    Rename null to PiersMorgan. Annoying and empty? It must match!

    I would've expected a crack like that from @Boomzilla, but have a like anyway, because it's funny because it's true.


  • Discourse touched me in a no-no place

    @FrostCat said:

    I would've expected a crack like that from @Boomzilla, but have a like anyway, because it's funny because it's true.

    I think I speak for the whole of the UK when I say that when… that man… went to the US, it was great! Because the bastard was no longer here. Please keep him. Please.


  • Discourse touched me in a no-no place

    Please take him back? Please? Jeremy Clarkson can punch him as much as he wants!

    Maybe Canada will take him.

    Mercifully, since he got booted from MSNBC or whichever low-rated network he was on, I haven't heard much out of him.


  • Discourse touched me in a no-no place

    I thought the Americans sacked him and he came back?


  • Discourse touched me in a no-no place

    @loopback0 said:

    I thought the Americans sacked him and he came back?

    Even though he's working for the Daily Mail, we haven't managed to dislodge him from our shores yet.

    Y'all owe us for not shipping him home post-haste.


  • ♿ (Parody)

    @FrostCat said:

    Y'all owe us for not shipping him home post-haste.

    Give me your fired, your boor,
    Your befuddled asses yearning to speak dumb,
    The wretched refuse of your truthy news.
    Send these, the clueless, tasteless-git to me,
    I lift my lamp beside the golden door!



  • @FrostCat said:

    Maybe Canada will take him.

    Nope. You touched him last, he's all yours. (Kinda like Bieber).



  • @Jon Zamboni said:

    You know you love me, I know you care
    But the end is coming, please be aware
    You are my love, you are my heart
    But apocalyptic floods will make us part.
    Are we an item? Girl, quit playin'
    All of my calendars are Mayan
    Say there's another and look right in my eyes
    Yeah well December 21st you'll be surprised.


Log in to reply