• (disco) in reply to Quite

    Don't I wish this was what we were doing.

    The ERP solution our pointy-headed managers chose had such a brain dead UI (that was intentional - you see that allowed them to charge for 'training')...that it was simpler for our users to use the system as it was supposed to be used (with respect to business logic) if we wrapped a simple set of web forms around their business logic.

  • (disco) in reply to kt_
    kt_:
    John_Imrie:
    RIP David Bowie.

    Oh my... Cause spamming YT comments under fucking every Bowie song is not enough for you people, right?

    At what point did reading comments under YouTube videos become a good idea?

  • (disco) in reply to DCRoss
    DCRoss:
    kt_:
    John_Imrie:
    RIP David Bowie.

    Oh my... Cause spamming YT comments under fucking every Bowie song is not enough for you people, right?

    At what point did reading comments under YouTube videos become a good idea?

    Not sure, but how otherwise would I learn that suddenly everybody rips on Bowie?

  • (disco) in reply to DCRoss

    If they're my videos, you can see a lot of really annoyed people telling me I shouldn't try playing a video game without taking a 3-year-long study course on how to play it.

    Silly me. Thinking games should be fun to play right away.

  • (disco) in reply to anotherusername
    anotherusername:
    Table 2-20 seems to indicate so, unless Oracle has tri-state booleans: `TRUE`, `FALSE`, and `UNKNOWN`.

    It isn't strictly Oracle that has tri-state booleans, it's part of standard SQL.

    What isn't part of standard SQL is that in Oracle, '' IS NULL is true. As far as I'm concerned, that's the biggest :wtf: ever. I wonder how this system handles that.

  • (disco) in reply to boomzilla

    But UNKNOWN OR TRUE is true, and UNKNOWN AND FALSE is false (and hence, NOT (UNKNOWN AND FALSE) is true).

  • (disco) in reply to PleegWat

    SQL tri-state logic: fucking with people's minds since NULL

  • (disco) in reply to PleegWat
    PleegWat:
    But `UNKNOWN OR TRUE` is true, and `UNKNOWN AND FALSE` is false (and hence, `NOT (UNKNOWN AND FALSE)` is true).

    Which makes sense when you realize UNKNOWN is not some random third value, but a literally unknown boolean value.

    Which means that statements where the value depends on UNKNOWN will be UNKNOWN themselves, but those where it doesn't (i.e. the result is the same if you substitute TRUE or FALSE under the UNKNOWN) will have a known value.

    Oracle docs seem to have belgium‌ed that up, though...

  • (disco) in reply to Maciejasjmj
    Maciejasjmj:
    Which makes sense when you realize UNKNOWN is not some random third value, but a literally unknown boolean value.

    The logic working with such things is not standard boolean, but rather a kind of modal logic. Knowing that something is UNKNOWN OR FALSE means that it is known to be not definitely TRUE. Things get more complicated from thereon in.

    I'm really happy I don't work with that sort of thing any more. ;)

  • (disco) in reply to dkf
    dkf:
    Knowing that something is UNKNOWN OR FALSE means that it is known to be not definitely TRUE

    No, this is boolean logic:

    (TRUE || TRUE) == (FALSE || TRUE), so (??? || TRUE) simplifies to TRUE.

    (TRUE||FALSE) != (FALSE||FALSE), so (???||FALSE) simplifies to ???.

  • (disco) in reply to Yamikuronue
    Yamikuronue:
    No, this is boolean logic:

    Different interpretation of OR. ;)

  • (disco) in reply to dkf
    dkf:
    I'm really happy I don't work with that sort of thing any more.

    I do, constantly. (Well, when I have a job, anyway.) One of the "favorite" parts of the job is figuring out why some logic went unknown. Especially when it went unknown because, say, the carry bit of the embedded CPU went unknown because the data in some CPU register went unknown 100 instructions earlier, and the CPU just tried to do some operation with that data. At least those sorts of test failures are obvious, if far removed from the actual cause.

  • (disco)

    Hey guys slow down you've lost me.

    The problem is that WTF developer wants to know if the value changed, and with ANSI NULLS, NULL != NULL. OK so far. First code:

    IF NOT oldValue IS NULL AND oldValue != newValue THEN
  /* do some stuff */
END IF;

    The problem with that code is not that the intent is not clear, it's that if oldValue is NULL then the code does not execute regardless of whether newValue is different from oldValue.

    Replacement code:

    a_Changed := a_OldValue is null and a_NewValue is not null or
             a_NewValue is null and a_OldValue is not null or
             a_OldValue != a_NewValue;

    If you plug in two NULLs, you return NULL!=NULL, thus TRUE, thus you get "changed" even though it didn't change. Maybe other developers do this differently because different ways work...

    Either I've accidentally boolean logic, or TRWTF is the article itself!

  • (disco) in reply to Lawrence
    Lawrence:
    If you plug in two NULLs, you return NULL!=NULL, thus TRUE, thus you get "changed" even though it didn't change. Maybe other developers do this differently because different ways work...

    If things are too complicated, stop trying to use a single expression. Instead, use several with an outer conditional based on (say) whether the old value is NULL.

    IF a_OldValue IS NULL THEN
    a_Changed := a_NewValue IS NOT NULL;
ELSE
    a_Changed := a_OldValue != a_NewValue;
END IF;

    Whether this is the most efficient way is beside the point; you can look at the code and see that it is definitely right. (As a bonus, any optimiser worth the name will emit the same back-end code as you'd get in a single nasty-but-correct expression.)

  • (disco) in reply to Lawrence

    NULL == NULL does not return TRUE NULL != NULL does not return TRUE

    In SQL, if a condition does not return true, then, for all practical purposes, it will handle the case as if FALSE was returned

    So the code will work as intended

  • (disco) in reply to anotherusername

    In SQL, they're UNKNOWN. Three valued logic, rememeber?

    UNKNOWN is kind of like FALSE because records only are returned when the WHERE clause evaluates to TRUE; however, NOT UNKNOWN is still UNKNOWN. It's considered very poor practice to allow your whole WHERE clause to evaluate to UNKNOWN. You should always explicitly handle your null values. Its the only way to really be sure that your queries will return the results you want.

    There is an operator that allows this type of comparison in the SQL standard, but neither SQL Server nor Oracle implement it, although PostgreSQL does. This is the IS DISTINCT FROM operator. A IS DISTINCT FROM B is equivalent to A <> B OR (A IS NULL AND B IS NOT NULL) OR (A IS NOT NULL AND B IS NULL). Similarly A IS NOT DISTINCT FROM B is equivalent to A = B OR (A IS NULL AND B IS NULL).

    TRWTF, for those wondering, is using a function. A function here is not sargable, so your indexes on the compared fields will be useless. Additionally, the query engine will need to execute this function for every single row, which is a hell of a lot slower than just loading all the data into memory (which it has to do anyways) and doing a straight comparison. The author should just write the damn expression out. It's not hard to do, and the function just adds complexity.

  • (disco) in reply to BaconBits
    BaconBits:
    A function here is not sargable

    That depends on how the query optimiser handles the function; it's not required to treat it as a black box.

  • (disco)

    Specifically, from the world of sqlserver.

    I had to look it up, having worked with databases for 19 years and counting.

  • (disco) in reply to dkf

    I've not seen any query optimizer unroll a scalar function to allow it to use an index. Have you got an example where it does that?

  • (disco) in reply to BaconBits
    BaconBits:
    I've not seen any query optimizer unroll a scalar function to allow it to use an index. Have you got an example where it does that?

    No, but I'm writing a compiler for fun and modern compilers most definitely can do this sort of thing quite extensively. A query optimiser is just a specialist kind of compiler with a slightly unusual target language.

  • (disco)
    PJH:
    BaconBits:
    sargable

    TIL: another [neologism][1] from the world of databases.

    I know that English allows verbing of most words, but what is the action related to a sarge?

    Yes, I've seen https://en.wiktionary.org/wiki/sargable
    dkf:
    I have an odd definition of fun, I guess

    Anyway, it's just a bit of fun that has to be one. The least significant one too, to be precise.

  • (disco) in reply to PWolff
    PWolff:
    but what is the action related to a sarge?

    There was a link up-thread:

    The term is derived from a contraction of Search ARGument ABLE.

  • (disco) in reply to PWolff
    PWolff:
    but what is the action related to a sarge?

    I assume it involves a lot of shouting and gratuitous insults.

  • (disco) in reply to boomzilla

    https://www.youtube.com/watch?v=GTQAXX08A-s

  • (disco) in reply to dkf

    Mmm... kind of. It's more of a JIT compiler, except query plans are focused on what's deterministic and what isn't, because they're designed to enforce the relational model first. There's not really a good equivalent for gcc's --funroll-loops, for example. That's why cursors often perform so poorly. It's also one reason why functions are usually recommended to be avoided: you sacrifice performance for what's often a minor ease of query writing. The second reason, parameter sniffing, means your query engine might choose to use a poor cached query plan for the function (or sproc) because that was the best one available the first time it ran.

    SQL Server will treat a very narrow range of functions as unrollable. So-called "Inline Table-Valued Functions" (ITVFs) will be broken up into their components by the query planner, but Multi-Statement Table-Valued Functions and all Scalar Functions will not (with some exceptions for system functions that the SQL Server devs know how to optimize for). You have to write ITVFs in very specific ways, and they really only work well for a fairly narrow set of circumstances, since both a table and a view are the most preferred type of "function" in SQL and perform even better. And this is with SQL Server, which has one of the better query planners out there.

    This blog gives a really good overview of what's going on internally.

    In TFA, of course, there should be no function or sproc at all. People should just write the damned Boolean expression and learn to handle NULLs in their WHERE clauses.

  • (disco) in reply to BaconBits
    BaconBits:
    And this is with SQL Server, which has one of the better query planners out there.

    The point is they could go a lot further. Yes, the relational model needs to be enforced, but that's just a bunch of semantic constraints on what the legal transformations are, and once you've established the semantics according to the constraints, any operation that preserves the semantics is actually legal within the query engine. This is how C++ and C compilers work, and how the JIT engines for Java, C# and Javascript work; the result of compilation isn't required to be in a language that guarantees that the program is correct, but rather just that the program itself actually is correct.

    Function inlining is one of the more trivial transformations (with appropriate semantics set up elsewhere).

Leave a comment on “Ch-ch-ch-changes”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article