We dont need no stinkin tables, we got sys.dual



  • Found as a view used extensively in custom code written for an enterprisey tool we use:

    [code]CREATE OR REPLACE VIEW ITIL_SERVICE_V1 AS
    SELECT 'SS1' SERVICIO, 'Low' IMPACTO TIME, 'Low' URGENCIA TIME, 48 RESPONSE TIME, 96 RESOLUTION TIME, 4 ESCALATES TIME FROM DUAL -- Priority Low
    UNION ALL
    SELECT 'SS1', 'Low', 'Medium', 48,96,4 FROM DUAL -- Priority low
    UNION ALL
    SELECT 'SS1', 'Medium', 'Low', 48,96,4 FROM DUAL -- Priority low
    UNION ALL
    SELECT 'SS1', 'High', 'Low', 1.5,24,1 FROM DUAL -- Priority Medium
    UNION ALL
    SELECT 'SS1', 'Medium', 'Medium', 3,36,2 FROM DUAL -- Priority normal
    UNION ALL
    SELECT 'SS1', 'Low', 'High', 48,96,4 FROM DUAL -- Priority low
    UNION ALL
    SELECT 'SS1', 'Medium', 'High', 1.5,24,1 FROM DUAL -- Priority medium
    UNION ALL
    SELECT 'SS1', 'High', 'Medium', 0.30,16,0.30 FROM DUAL -- Priority High
    UNION ALL
    SELECT 'SS1', 'High', 'High', 0.15,8,0.15 FROM DUAL -- Priority Urgent
    UNION ALL
    --Hardware Support (HS2)
    SELECT 'HS2', 'Low', 'Low', 48,96,4 FROM DUAL -- Priority Low
    UNION ALL
    SELECT 'HS2', 'Low', 'Medium', 48,96,4 FROM DUAL -- Priority Low
    UNION ALL
    SELECT 'HS2', 'Medium', 'Low', 48,96,4 FROM DUAL -- Priority low
    UNION ALL
    SELECT 'HS2', 'High', 'Low', 1.5,24,1 FROM DUAL -- Priority Medium
    UNION ALL
    SELECT 'HS2', 'Medium', 'Medium', 3,36,2 FROM DUAL -- Priority Medium
    UNION ALL
    SELECT 'HS2', 'Low', 'High', 48,96,4 FROM DUAL -- Priority low
    UNION ALL
    SELECT 'HS2', 'Medium', 'High', 1.5,24,1 FROM DUAL -- Priority medium
    UNION ALL
    SELECT 'HS2', 'High', 'Medium', 0.30,16,0.30 FROM DUAL -- Priority High
    UNION ALL
    SELECT 'HS2', 'High', 'High', 0.15,8,0.15 FROM DUAL -- Priority Urgent
    UNION ALL
    --Asset Managemetn  (AM3)
    SELECT 'AM3', 'Low', 'Low', 48,96,4 FROM DUAL -- Priority Low
    UNION ALL
    SELECT 'AM3', 'Low', 'Medium', 48,96,4 FROM DUAL -- Priority Low

    ...snip 800 lines...

    UNION ALL
    SELECT 'ACC45', 'Medium', 'High', 1.5,24,1 FROM DUAL -- Priority medium
    UNION ALL
    SELECT 'ACC45', 'High', 'Medium', 0.30,16,0.30 FROM DUAL -- Priority High
    UNION ALL
    SELECT 'ACC45', 'High', 'High', 0.15,8,0.15 FROM DUAL -- Priority Urgent;

    [/code]

    I dont even know man.



  • @Aspirist said:

    URGENCIA TIME

    Why would they translate one word and not the other? Or am I missing something?



  • Magic numbers fill me with disappointment.



  • This already looks sufficiently scary without background knowledge, but just out of morbid curiosity, could you explain what sys.dual is?



  • @PSWorx said:

    This already looks sufficiently scary without background knowledge, but just out of morbid curiosity, could you explain what sys.dual is?

    sys.dual is a dummy table that doesn't actually exist. It's just there because Oracle barfs if you write a select statement without a from.



  • So, that query creates all those 'rows' with magic numbers, magic strings, and magic bullshit, instead of that shit being kept in a table?

    But why use a database at all, and not just hardcode that shit into the consuming application?



  • @Buttembly Coder said:

    @Aspirist said:
    URGENCIA TIME
    Why would they translate one word and not the other? Or am I missing something?

    They gave up in the middle of translating? I see that alot in our code, were based in Mexico and 90% of our clients are international so everyone is bilingual, so like I said, I dont even know.

     

    @eViLegion said:

    So, that query creates all those 'rows' with magic numbers, magic strings, and magic bullshit, instead of that shit being kept in a table? But why use a database at all, and not just hardcode that shit into the consuming application?

     Its a 3rd party app , this is used on the reporting side of things where you can feed querys to the app to get all kind of bullshit you want to show to the suits higher up. All the magic numbers there are custom SLA's times.


  • Trolleybus Mechanic

    @eViLegion said:

    So, that query creates all those 'rows' with magic numbers, magic strings, and magic bullshit, instead of that shit being kept in a table?

    But why use a database at all, and not just hardcode that shit into the consuming application?

     

    1) Something further up the chain needs that data

    2) The something can use any object, but the developer only "knows" data sets

    3) The developer is completely unaware of the DataTable object

    4) ???

    5) Select from DUAL

    Now, if I were to give the benefit of the doubt, I say that since Ass Peer's company is enterprisey, they have DBAs. And those DBAs feel that databases are for DBAs and DBAs only. Once some developer cleverly decided on #1-#3 above, they were denied access to the database. Maybe someone didn't like their schema change. Maybe they didn't fill out the paperwork in triplicate.

    So they went to #5 and got their dataset after all.

    That being said, I've been guilty of doing this, but only when I'm trying to hack together some proof of concept thing and I don't have the time (or a fuck to give) about creating the actual schema.  Usually it's when I'm trying to change one of the (many many many) hard-coded menus in various applicaitons into something database driven. If it ever makes it past POC, I'll make a proper datatable.



  • @eViLegion said:

    So, that query creates all those 'rows' with magic numbers, magic strings, and magic bullshit, instead of that shit being kept in a table?

    But why use a database at all, and not just hardcode that shit into the consuming application?

    I remember a front page WTF from a while back where someone was writing an application for some other company that didn't need a database at all. The guy's boss, who I think was non-techie business guy, forced him to buy and use oracle just because he read in some business magazine that it was the best database for businesses or something. So he did, but since there wasn't anything reasonable to use it for he made some kind of small, static lookup table and used oracle solely for that.

    Something like that could have happened here. At least I hope so.



  • @powerlord said:

    sys.dual is a dummy table that doesn't actually exist. It's just there because Oracle barfs if you write a select statement without a from.

    Wwhahahhaahahhhahaahah...

    WHAT!

    That is mind-blowing. The FROM clause has always been optional in SQL. And I thought the empty string == null shit was bad... Jesus.

    Even considering why this system "table" exists, why is it called "dual"? I mean... WTF? Dual? Why not, say, "DummyTable"?



  • @blakeyrat said:

    And I thought the empty string == null shit was bad... Jesus.
     

    nvarchar2



  • @blakeyrat said:

    @powerlord said:
    sys.dual is a dummy table that doesn't actually exist. It's just there because Oracle barfs if you write a select statement without a from.

    The FROM clause has always been optional in SQL.

    In oracle land, it isnt. So they give us this magic table with 1 row and 1 col which does magical things when used.

    on why its named dual, well...

    @Charles Weiss of Oracle said:

    I created the DUAL table as an underlying object in the Oracle Data Dictionary. It was never meant to be seen itself, but instead used inside a view that was expected to be queried. The idea was that you could do a JOIN to the DUAL table and create two rows in the result for every one row in your table. Then, by using GROUP BY, the resulting join could be summarized to show the amount of storage for the DATA extent and for the INDEX extent(s). The name, DUAL, seemed apt for the process of creating a pair of rows from just one.



  • @Aspirist said:

    CREATE OR REPLACE VIEW ITIL_SERVICE_V1 AS


    <Snip>

    I see the WTF here. The developer should have declared a cursor and populated it with those SELECTS.



  • @Buttembly Coder said:

    @Aspirist said:
    URGENCIA TIME

    Why would they translate one word and not the other? Or am I missing something?

    Yes, you are. "URGENCIA" is the column name (and has no meaning to the SQL system); "TIME" is the type (and is a keyword, so not translateable).



  • @blakeyrat said:

    WHAT!

    The answer to "WHAT!" is usually "Oracle".



  • @blakeyrat said:

    The FROM clause has always been optional in SQL.
     

    I wasn't aware it was. Most RDBMS I've used required it[1].

    @blakeyrat said:

    And I thought the empty string == null shit was bad...

    It ain't improved.

    @blakeyrat said:

    Why not, say, "DummyTable"?

    Because Oracle. Larry Ellison is clearly on a different set of drugs to the rest of us, hence he sees not a single but a dual dummy table.

    That's only the tip of the iceberg when it comes to Oracle WTFs. Descending into Oracle-bashing is like cattle-prodding disabled kids in a swimming pool.

    [1] apart from MySQL, but I learned that much later. I hadn't had much call to use it SELECT without FROM.


  • Discourse touched me in a no-no place

    @Cassidy said:

    Descending into Oracle-bashing is like cattle-prodding disabled kids in a swimming pool.
    A worthy activity indeed…


  • Discourse touched me in a no-no place

    @eViLegion said:

    So, that query creates all those 'rows' with magic numbers, magic strings, and magic bullshit, instead of that shit being kept in a table?
    Even a session-scoped temporary table would have reduced the WTF level, assuming it is being reused a good amount. (Transaction-scoped… not so much.)



  • @Charles Weiss of Oracle said:

    I created the DUAL table as an underlying object in the Oracle Data Dictionary. It was never meant to be seen itself, but instead used inside a view that was expected to be queried. The idea was that you could do a JOIN to the DUAL table and create two rows in the result for every one row in your table. Then, by using GROUP BY, the resulting join could be summarized to show the amount of storage for the DATA extent and for the INDEX extent(s). The name, DUAL, seemed apt for the process of creating a pair of rows from just one.

    I really don't understand why if you're going to do that you don't just create an arbitrarily sized tally table. It's strictly better.



  • @Cassidy said:

    I wasn't aware it was. Most RDBMS I've used required it[1].

    Which ones have you used? Because it's always been optional in all the ones I've used. (Although I don't recall if I tried in Postgres...)



  • @blakeyrat said:

    Which ones have you used?
     

    Ingres, MySQL, Oracle, sqlite3.

    I've done a smattering of SQL Server, but that was largely writing a few queries and hitting F5. Most of the SQL I wrote in there was basic DB-agnostic stuff so I wasn't exposed to more advanced concepts - and thus never encountered this "FROM is optional" bit. I'm guessing I've never been in a position where it wasn't used (other than Oracle, and they provided DUAL because FROM is mandatory)



  • @Cassidy said:

    Most of the SQL I wrote in there was basic DB-agnostic stuff so I wasn't exposed to more advanced concepts - and thus never encountered this "FROM is optional" bit.

    "FROM is optional" isn't an "advanced concept", it's a fundamental of the language. Or look at it logically, why should from be required? What if I write a query like this:

    SELECT 'hello, I am a SELECT clause'

    Why in God's green hell should I be required to bullshit some kind of fake-o FROM clause to make it work, when this query is perfectly valid:

    SELECT 'hello, I am a SELECT clause'
    FROM literallyAnyTableAvailableInAnyDatabaseAvailableEver
    LIMIT 1

    I mean it's basic logic that FROM wouldn't be required, right? Going by the design of the rest of the language, it's a "duh". Oracle et al are just making you type more for literally NO PURPOSE. No practical advantage to it. No conceptual advantage to it. It's just designed by a dumb shit who didn't think through the design longer than 0.5 microseconds.



  • @blakeyrat said:

    "FROM is optional" isn't an "advanced concept", it's a fundamental of the language.
     

    *sigh*. Yes, it may be.

    I never said that "FROM is optional" is an advanced concept. I was querying tables and my statements pretty much were SELECT/FROM/JOIN/WHERE/GROUP/HAVING - I never did anything (much different | more advanced | out of the ordinary) than that and in my (limted) experience JOIN and WHERE onwards were optional.  

    Hence I was never in a situation where any of my queries actually had anything like "SELECT 1 + 1" so to me SELECT was always about specifying a target to interrogate.

    @blakeyrat said:

    Why in...

    ...microseconds.

    Explanation not required (well, not required for me, anyway). And I don't disagree with your observations on why it's stupid. As I mentioned previously, Oracle is a veritable clusterfuck of WTFery that this ain't the only fly in the ointment.



  • SELECT 'hello, I am a SELECT clause' 

     Works for me in sqlite3 - and Postgresql.

     



  •  @Watson said:

    SELECT 'hello, I am a SELECT clause' 

     Works for me in sqlite3 - and Postgresql.

     

    Of course it does - makes total sense (well, except apparently in Oracle - haven't used it enough to come across THAT wtf). SELECT just gets data - it might as well be a literal. As in "SELECT NOW()" or "SELECT md5('mynormalpassword')" (to check if some hash matches). Or "SELECT ROUND(subquery1 / subquery2)". Or "UPDATE foo SET bar = (SELECT my_stored_procedure(somecolumn))".

    Come to think of it, I simply cannot believe the Oracle database - which might well be a clusterfuck of WTFs, but is still widely used among enterprises - is THAT stupid. It almost MUST be a misunderstanding on the OPs case. (Then again, SQLServer didn't support LIMIT/OFFSET syntax last time I checked, so who knows.)

     


  • Discourse touched me in a no-no place

    @Monomelodies said:

    I simply cannot believe the Oracle database is THAT stupid.
    Ho ho ho! You are an innocent idealist, you know?



  • @Monomelodies said:

    I simply cannot believe those that designed and wrote the Oracle database are THAT stupid.
     

    FTFY. There are many things I like about using their RDBMS and many things I find stupid.

    Probably their biggest WTF is this tendency to refrain from fixing broken things on the grounds that it's been broken for so long it's now established as expected behaviour.



  • @Aspirist said:

    @blakeyrat said:
    @powerlord said:
    sys.dual is a dummy table that doesn't actually exist. It's just there because Oracle barfs if you write a select statement without a from.
    The FROM clause has always been optional in SQL.
    In oracle land, it isnt. So they give us this magic table with 1 row and 1 col which does magical things when used.
    Which means that a way to test if an Oracle (JDBC) connection is still valid is by performing a "SELECT 1 FROM DUAL" query on it. This is, in fact, the default query we use to test our connections.

    However, when we performed that exact query on a MySQL cluster, it took down the cluster hard. Such fun...

     



  • @Monomelodies said:

     @Watson said:

    SELECT 'hello, I am a SELECT clause' 

     Works for me in sqlite3 - and Postgresql.

     

    Of course it does - makes total sense (well, except apparently in Oracle - haven't used it enough to come across THAT wtf). SELECT just gets data - it might as well be a literal. As in "SELECT NOW()" or "SELECT md5('mynormalpassword')" (to check if some hash matches). Or "SELECT ROUND(subquery1 / subquery2)". Or "UPDATE foo SET bar = (SELECT my_stored_procedure(somecolumn))".

    Come to think of it, I simply cannot believe the Oracle database - which might well be a clusterfuck of WTFs, but is still widely used among enterprises - is THAT stupid. It almost MUST be a misunderstanding on the OPs case. (Then again, SQLServer didn't support LIMIT/OFFSET syntax last time I checked, so who knows.)

     

    SQL Server has LIMIT, it just calls it TOP and it's in the wrong place (at least from what I know about LIMIT, there might be more than that). But yeah the real WTF is that Oracle requires a FROM clause even if you don't need to select data from an actual table.



  • @ObiWayneKenobi said:

    SQL Server has LIMIT, it just calls it TOP

    Yes, I know that, that's why I said it doesn't support the SYNTAX. Also, no offsets - I have nasty memories of writing portable code that had to page through query results in SQL server. Ouch. IIRC it involved doing the query twice and calling TOP on the TOP'ed results of the inner one.

     


  • Considered Harmful

    Not a beautiful solution, but you could try, eg:

    select
      foo,
      bar
    from
      (
        select
          foo,
          bar,
          rownumber() over( order by foo asc ) as row
        from baz
      ) [ ]
    where row between 10 and 20
    order by foo asc
    

    (I have no idea if this will run.)


  • Trolleybus Mechanic

    @joe.edwards said:

    Not a beautiful solution, but you could try, eg:

    select
      foo,
      bar
    from
      (
        select
          foo,
          bar,
          rownumber() over( order by foo asc ) as row
        from baz
      ) [ ]
    where row between 10 and 20
    order by foo asc
    

     

    In all fairness, MSSQL only started supporting row_number() in 2005. 

     



  • @joe.edwards said:

    Not a beautiful solution, but you could try, eg:

    select
      foo,
      bar
    from
      (
        select
          foo,
          bar,
          rownumber() over( order by foo asc ) as row
        from baz
      ) [ ]
    where row between 10 and 20
    order by foo asc
    

    (I have no idea if this will run.)

     

    It's been quite a while, but I think the trick was to do a TOP(x) with ORDER BY [whatever] ASC and then use that as a subquery to the same but only ordered by DESC, and then reverse the resultset before outputting. Or something. Anyway, it was ugly.

     



  • @Monomelodies said:

    @joe.edwards said:

    Not a beautiful solution, but you could try, eg:

    select
    foo,
    bar
    from
    (
    select
    foo,
    bar,
    rownumber() over( order by foo asc ) as row
    from baz
    ) [ ]
    where row between 10 and 20
    order by foo asc

    (I have no idea if this will run.)

     

    It's been quite a while, but I think the trick was to do a TOP(x) with ORDER BY [whatever] ASC and then use that as a subquery to the same but only ordered by DESC, and then reverse the resultset before outputting. Or something. Anyway, it was ugly.

     

    My solution would have been to delete system32 and get a better database server with a better database.



  • @Monomelodies said:

    Yes, I know that, that's why I said it doesn't support the SYNTAX. Also, no offsets

    Ohh, another pod person. although it seems by reading some ofl the other responses that you are not alone.
    @Monomelodies said:
    I have nasty memories of writing portable code that had to page through query results in SQL server.

    Fuck, people, it is not that hard
    @Monomelodies said:
    IIRC it involved doing the query twice and calling TOP on the TOP'ed results of the inner one

    So now we know that your pod closed before SQL Server 2005

    For the record people this is the syntax in sql server 2012

    SELECT whatever FROM whatever ORDER BY something OFFSET number FETCH NEXT number

    I'll grant you that it is not as flexible as it could be as OFFSET needs ORDER BY and FETCH but, hey who cares about small details when bashing MS is so much fun!

    Interesting read if somebody wants to know the different ways to do this depending on the version of SQL Server installed

    http://blog.sqlauthority.com/2013/04/14/sql-server-tricks-for-row-offset-and-paging-in-various-versions-of-sql-server/


  • IIRC the FROM clause is also mandatory in IBM DB2. Their standard filler table is called SYSIBM.SYSDUMMY1.



  • @serguey123 said:

    For the record people this is the syntax in sql server 2012

     

    SELECT whatever FROM whatever ORDER BY something OFFSET number FETCH NEXT number

    That is a nice clean syntax. I approve.

    Oracle still uses ROWNUM (and it's a convoluted way in which it's used also).

     


Log in to reply