The Daily WTF: Curious Perversions in Information Technology

2013-06-06 Reply Admin

sagaciter:
Andrew:
When that checks out, you insert it all at once instead of executing N insert statements...
He didn't say N insert statements, he said one insert statement specifying N rows.

I know that, but since he didn't provide valid SQL, the alternative is to use N insert statements.

2013-06-06 Reply Admin

bobby:

Fixed?

create or replace view itil_service_v1 as
select *
from (
    values
        ('SS1', 'Low', 'Low', 48, 96, 4),
        ('SS1', 'Low', 'Medium', 48, 96, 4),
        ('SS1', 'Medium', 'Low', 48, 96, 4),
        ('SS1', 'High', 'Low', 1.5, 24, 1),
        ('SS1', 'Medium', 'Medium', 3, 36, 2),
        ('SS1', 'Low', 'High', 48, 96, 4),
        /* SNIP a few dozen lines for brevity */
        ('HS2', 'Low', 'High', 48, 96, 4),
        ('HS2', 'Medium', 'High', 1.5, 24, 1),
        ('HS2', 'High', 'Medium', 0.30, 16, 0.30),
        ('HS2', 'High', 'High', 0.15, 8, 0.15),
        --Asset Managemetn; (AM3)
        ('AM3', 'Low', 'Low', 48, 96, 4),
        ('AM3', 'Low', 'Medium', 48, 96, 4),
        /* SNIP 800 more lines of the same */
        ('ACC45', 'Medium', 'High', 1.5, 24, 1),
        ('ACC45', 'High', 'Medium',  0.30, 16, 0.30),
        ('ACC45', 'High', 'High', 0.15, 8, 0.15)
    ) as itil_service (servicio, impacto, urgencia, response, resolution, escalates)

Believe it or not, his way is faster... Way faster...

http://blog.sqlauthority.com/2007/06/08/sql-server-insert-multiple-records-using-one-insert-statement-use-of-union-all/

2013-06-06 Reply Admin

jay:
Andrew:
I actually did this all the time when I had to create insert scripts.
insert into TABLE ( COLUMN1, ... ) select COLUMN1, ... from ( select null COLUMN1, ... from DUAL where 1=0 union select value1, ... from dual union select value2, ... from dual union etc. )

Never seen it in prod code though.

Umm, why? Why not just
insert into table1(column1,...)
values (value1,...),
(value2,...),
(value3,...);

If you use UNION ALL (not UNION), it's faster. Plus I don't think Oracle has that syntax yet.

cmccormick · 2013-06-06 Reply Admin

I've done something not to far from this once or twice. I'm guessing they're writing PL/SQL for a company or (more likely) government agency where database changes take forever.

Sometimes it's not worth the battle to get a lookup table created.

2013-06-06 Reply Admin

Jayman:
olaf:
It is ITIL, that is how it is supposed to be! Look in the docs ;)

Yeah. Agreed. I'm ITIL V3 certified; throwing ITIL at incompetence just leads to more complex but well-documented - in theory, at least - incompetence.

Throwinf ITIL at anything.....never mind

2013-06-06 Reply Admin

cmccormick:
I've done something not to far from this once or twice. I'm guessing they're writing PL/SQL for a company or (more likely) government agency where database changes take forever.
Sometimes it's not worth the battle to get a lookup table created.

Or one of the devs mentioned that doing it as a table would let them add new entries or update the times if anything changed, and manglement freaked out: "NO! You CAN'T let the times change! They're defined in our contracts! No tables. At all. Do it some way where you can't change the times.". And the dev rolled his eyes, decided it was Friday afternoon and he just didn't want to have this fight, and lo and behold another WTF is born.

2013-06-06 Reply Admin

chubertdev:
sagaciter:
Also, mine doesn't generate an ORA-00933: SQL command not properly ended.
Oracle sucks.

No, that's ORA-22275.

I thought error numbers that high were typically user defined.

2013-06-06 Reply Admin

Why is everyone sayinmg that Oracle requirirng FROM is a WTF?

SQL = Structured Query Language.

Structured - so sometimes we require statements to be a particular way
Query - as in get something FROM somewhere/something

Why should you be able to select other stuff (like constants, or the current time, or the timezone in Beijing?). It might sometimes be CONVENIENT to, but usually whatever you're calling the SQL FROM has more convenient ways to do it....

If I want to add two numbers, I would do it in my calling code, not on the DB (unless it was related to a query on a table- in which aase I have my FROM clause eg "SELECT i.Create_time + t.TimeOffset FROM Invoice i, Timezone t WHERE i.timezone_id = t.timezone_id")

TRWTF is the people here who think we should use a databse to select stuff out of nowhere.

2013-06-06 Reply Admin

Not all of them - ORA-20000 to ORA-20999 are "supposed" to be free for users to use, although there are some Oracle-supplied packages which use error numbers in this range.

2013-06-07 Reply Admin

You're still querying something, just not a database. Just think of "select ..." as SQL for "Oh great machine, pray tell, what is ... ?"

2013-06-07 Reply Admin

Captain Oblivious:
Indeed, the table dual is dual to the empty table. In other words, it is the top of the Dedekind-Maclane completion of the lattice of relations defined by the schema.

I understood each word perfectly, but could make no sense of the whole

2013-06-07 Reply Admin

Andrew:
I know that, but since he didn't provide valid SQL, the alternative is to use N insert statements.

If you believe Wikipedia, what he wrote has been valid SQL since 1992. The fact that Oracle doesn't support it is another matter, namely the matter of "Oracle sucks".

2013-06-07 Reply Admin

PRMan:
If you use UNION ALL (not UNION), it's faster. Plus I don't think Oracle has that syntax yet.

If Oracle doesn't support it at all then it doesn't make sense to say that it's faster or slower.

2013-06-07 Reply Admin

I wish I could slap that code.

I've already slapped Oracle.

2013-06-07 Reply Admin

liberate tutemae ex Oracle...

chubertdev · 2013-06-07 Reply Admin

Jim:
Why is everyone sayinmg that Oracle requirirng FROM is a WTF?
SQL = Structured Query Language.

Structured - so sometimes we require statements to be a particular way

Query - as in get something FROM somewhere/something

Why should you be able to select other stuff (like constants, or the current time, or the timezone in Beijing?). It might sometimes be CONVENIENT to, but usually whatever you're calling the SQL FROM has more convenient ways to do it....

If I want to add two numbers, I would do it in my calling code, not on the DB (unless it was related to a query on a table- in which aase I have my FROM clause eg "SELECT i.Create_time + t.TimeOffset FROM Invoice i, Timezone t WHERE i.timezone_id = t.timezone_id")

TRWTF is the people here who think we should use a databse to select stuff out of nowhere.

A procedure where you want a consistent timestamp for your transactions that are in multiple insert/update statements.

SELECT @Timestamp = CURRENT_TIMESTAMP

Now please go into another field of work.

2013-06-07 Reply Admin

ugly, but I've seen much worse.

My favorite are the EAV tables for everything under the sun... which someone will create some nifty view around for a small result set, and yet it is near impossible to read... but had it been a well-defined table structure for the relation at hand, it would have been straightforward and sensible.

But hey, who has time for all those pesky little doo-dads? Let's just consolidate all to a single SUPER/MASTER/EVERYTHING table.. That way we only have a one-stop shop!!! <rolls eyes>

2013-06-07 Reply Admin

Perhaps it was meant to be "DUEL", as in I'll fight you to the death over this design!

2013-06-08 Reply Admin

[quote user="Andy"][quote user="Captain Oblivious"] Indeed, the table dual is dual to the empty table. In other words, it is the top of the Dedekind-Maclane completion of the lattice of relations defined by the schema. [/quote]

The relational algebra turns a schema into a lattice. But such a lattice might not be "complete". In other words, meets and joins for relations might not be defined by the schema. For example, if you have a key constraint on a relation, then the key constraint defines a join.

In these theoretical terms, the purpose of a RDBMS is to compute "arbitrary" meets and joins. Even those not explicitly defined by the schema. In other words, it is "contains" (in a mathematical/metaphysical sense) the Dedekind-Maclane completion of the lattice (the intersection of all the complete lattices which contain the lattice defined by the schema).

A complete lattice has a "bottom" element (the empty relation), and a "top" element (the relation which contains every field). The table called "dual" is the top element of the lattice. It is called "dual" because it is "dual" to the bottom element. It could also be called "top".

2013-06-09 Reply Admin

Dual is the dual of the wooden table.

2013-06-10 Reply Admin

SELECT without FROM is not standard SQL. The standard way of doing it is with a bare VALUES command: http://www.postgresql.org/docs/current/static/sql-values.html Works on PostgreSQL and DB2.

2013-06-10 Reply Admin

PRMan:
Believe it or not, his way is faster... Way faster...
http://blog.sqlauthority.com/2007/06/08/sql-server-insert-multiple-records-using-one-insert-statement-use-of-union-all/

That's decidedly not what the article says. Inserting values into a table is also not what's being discussed.

2013-06-12 Reply Admin

1/ No good way (in any DB platform) to keep lookup / config tables continuously synched with version controlled code[/quote]

Not built in sure, but maybe you should check out some decent source control tools like Red-Gate source control. It allows you to source control static data on any table with a primary key. We just source control all of our lookup or control tables check them in from dev, and to a get latest to prod.

2013-06-12 Reply Admin

jay:
Andrew:
I actually did this all the time when I had to create insert scripts.
insert into TABLE ( COLUMN1, ... ) select COLUMN1, ... from ( select null COLUMN1, ... from DUAL where 1=0 union select value1, ... from dual union select value2, ... from dual union etc. )

Never seen it in prod code though.

Umm, why? Why not just
insert into table1(column1,...)
values (value1,...),
(value2,...),
(value3,...);

Because Oracle in all of its perfect glory doesn't allow normal crap in their DB. Just try to work with dates and see if you don't lose your mind.

2013-06-13 Reply Admin

No one noticed the line /* SNIP a few dozen lines for brevity */

Instead: /* SKIP a few dozen lines for brevity */

All of you that tryied ti "Fix" it just copied it and didn't think at all.

Maybe not the WTF but Read and understand ALL the code also the remaks

BTW there is no DBMS that follows the standard completely and only. they all have their owd additions

2013-06-13 Reply Admin

Clearly the developer of the view did not have priviledge to create a table for the data, so we see the circumvention.

2013-06-13 Reply Admin

Vinny:
No one noticed the line /* SNIP a few dozen lines for brevity */
Instead: /* SKIP a few dozen lines for brevity */

All of you that tryied ti "Fix" it just copied it and didn't think at all.

Maybe not the WTF but Read and understand ALL the code also the remaks

What the fucking fuck are you talking about?

Vinny:
BTW there is no DBMS that follows the standard completely and only. they all have their owd additions

There's a difference between not perfectly complying with the standard and not bothering to support a very simple and useful feature.

Where We're Going, We Don't Need Tables

Leave a comment on “Where We're Going, We Don't Need Tables”