The Daily WTF: Curious Perversions in Information Technology

lofwyr · 2005-08-31 Reply Admin

ammoQ:
UPPERCASE or lowercase is just a matter of personal style in PL/SQL, since PL/SQL is not case sensitive. Anyway, some tools like TOAD have a tendency to force their opinion about "why UPPERCASE is so much better" to innocent programs.

... you know that you can change (format) styles in toad, right?

l.

ammoQ · 2005-08-31 Reply Admin

lofwyr:
ammoQ:
UPPERCASE or lowercase is just a matter of personal style in PL/SQL, since PL/SQL is not case sensitive. Anyway, some tools like TOAD have a tendency to force their opinion about "why UPPERCASE is so much better" to innocent programs.

... you know that you can change (format) styles in toad, right?

l.

That's not the problem, since I don't use TOAD. The real problem is that this feature makes it too easy for my uppercase-prefering co-workers to mess up the code ;-)

Richard Nixon · 2005-08-31 Reply Admin

foxyshadis:
Alex Papadimoulis:

to have misplaced my references. They were in SQL PROGRAMMING STYLE by Joe Celko. I used to do lower case with syntax hilighting, but he had good evidence from recent studies (1997 I think) which demonstrated that SQL code with the upper case keywords was easier to read, understand, and therefore less error prone.

Consider that, especially when it comes to SQL, syntax hilighting is less avaiable than other languages. SQL Strings in applications for example.

I can get behind that. It doesn't work for me, but if it's better on average... Personally, I find too many letters in a row with straight lines and the same metrics blends into line noise for me; lowercase offers more variety for the pattern-matching, even without color. I don't know eclipse highlights, haven't used sql in it yet, but I think phped did and I know textpad and editplus don't.

On the other hand the whole second half of that is uppercase, I guess he was never taught that sql variables aren't keywords. =p

I love your spelling of highlightning. I know it's becoming more common, but it always makes me think of h-dawg and his buddy's hilita fix. The onion used to be so cool. =D

http://www.editplus.com/javacpp.html

Syntax highlighting file for SQL in EditPlus can be had there.

John Smallberries · 2005-08-31 Reply Admin

ammoQ:
lofwyr:
ammoQ:
UPPERCASE or lowercase is just a matter of personal style in PL/SQL, since PL/SQL is not case sensitive. Anyway, some tools like TOAD have a tendency to force their opinion about "why UPPERCASE is so much better" to innocent programs.

... you know that you can change (format) styles in toad, right?

l.

That's not the problem, since I don't use TOAD. The real problem is that this feature makes it too easy for my uppercase-prefering co-workers to mess up the code ;-)

This is a very good point.
Everyone has "personal styles", but the whole point of case & naming conventions is to make it easy, efficient and umambiguous for development teams (and by extension, the community) to share code.

It's brutal to go into a shop that has wacky (or no) code style standards.

2005-08-31 Reply Admin

ammoQ:
The ''=NULL thingy has a "nice" side effect in Java: of course, the JDBC driver will give you a null pointer instead of an empty string for columns containting ''. A good oportunity to test how the system handles null pointer exceptins.

I used to think there was a bug in the Zope Database Adapter for Oracle, because f2w helpdesk was behaving exactly as though empty strings were NULLs, coming back into Python as None instead of "". I built in a nasty work-around and forgot about it. Today my worst fears are condfirmed.
I think the big giant WTF here is Oracle wilfully breaking string algebra for who-knows-what implementation reason. Did they want to save a few processor cycles checking the field length for zero instead of having a separate NULL bit? Pure genius.
But that function above... Wow, just ...wow.

stevekj · 2005-08-31 Reply Admin

I know! The problem is that they forgot to handle uppercase spaces, right?

ammoQ · 2005-08-31 Reply Admin

Anonymous:
ammoQ:
The ''=NULL thingy has a "nice" side effect in Java: of course, the JDBC driver will give you a null pointer instead of an empty string for columns containting ''. A good oportunity to test how the system handles null pointer exceptins.

I used to think there was a bug in the Zope Database Adapter for Oracle, because f2w helpdesk was behaving exactly as though empty strings were NULLs, coming back into Python as None instead of "". I built in a nasty work-around and forgot about it. Today my worst fears are condfirmed.
I think the big giant WTF here is Oracle wilfully breaking string algebra for who-knows-what implementation reason. Did they want to save a few processor cycles checking the field length for zero instead of having a separate NULL bit? Pure genius.
But that function above... Wow, just ...wow.

I guess they had a PHB dictating ''=NULL; then there were two teams, one defending string algebra (length('')=0) and another one defending NULL semantics (f(NULL)=NULL for f!=NVL). The NULL team one, the losing team eventually went away to build a database that doesn't suck. Unfortunately, they haven't been heard of, since.

ammoQ · 2005-08-31 Reply Admin

Correction: The NULL team won

ammoQ · 2005-08-31 Reply Admin

(the story continues)
but the victory of the NULL team was not complete, as one could assume; the string algebra team
somehow managed to force their behaviour of string concetination into the final concept:
NULL||someString = someString||NULL = someString (not NULL, as one could expect)

ammoQ · 2005-08-31 Reply Admin

correction: concatenation

2005-08-31 Reply Admin

Anonymous:
emurphy:
Anonymous:

So, let me see if I have this straight - if I call RPAD_SPACES(99) (assume I have a head injury or whatever), I have the satisfaction of knowing that it constructed the return values for RPAD_SPACES(3) through RPAD_SPACES(98) (using RPAD, 'natch) and promptly tossed them aside. Brillant!

Actually, no, you won't have that satisfaction.

Exercise for the student: Why not?

Because it will fall over on anything over 60 characters

PADVAR VARCHAR2 ( 60 );

Ah, forgot to look back at the declaration... but I think you get my point. Truly a brillant WTF nonetheless!

2005-08-31 Reply Admin

JoeS:
I've never worked with Oracle, and I spent about 15 seconds reading the link to the RPAD function. I'm also totally taking syntax based on the WTF code. But wouldn't this do the same thing? Unless RPAD doesn't treat NULL values as blanks in which case you just need a simple IF to eliminate that case.

/* Preparing First and Last Name */

v_first_nm := RPAD(v_first_nm,9);

v_last_nm := RPAD(v_last_nm,15);

v_first_nm := RPAD(COALESCE(v_first_nm,''), 9); v_last_nm := RPAD(COALESCE(v_last_nm,''), 15);

Look, ma, no IFs!

I'm not previewing this because the preview never even looks like my final submissions anyway.

ammoQ · 2005-08-31 Reply Admin

Anonymous:

v_first_nm := RPAD(COALESCE(v_first_nm,''), 9); v_last_nm := RPAD(COALESCE(v_last_nm,''), 15);
Look, ma, no IFs!

I'm not previewing this because the preview never even looks like my final submissions anyway.

I think you meant COALESCE(v_first_nm,' ')
(since '' is null) But this use of COALESCE is completely equivalent to NVL.

dubwai · 2005-08-31 Reply Admin

ammoQ:
Anonymous:

An empty string is just another way of representing null.

In Oracle, this is unfortunately true (at least in the versions I know) and these NULL strings can give you many happy debugging hours, because

length('') is not 0, but NULL
lpad('', 5) is not ' ' but NULL
rpad('', 5) is not ' ' but NULL

I love:

? '' = '' -> false

'test' <> '' -> false

2005-08-31 Reply Admin

i no speak orable. but it does look like too much work.

ammoQ · 2005-08-31 Reply Admin

dubwai:
I love:

? '' = '' -> false

'test' <> '' -> false

yes, and also
NOT ('' = '') -> false
NOT ('test' <> '') -> false

because it's actually not "false", but NULL (again!) and NOT(NULL) = NULL; only in the last instance, NULL is treated like FALSE. This trinary logic offer's lots of room for nice effects.
For example, should you ever stumble upon PL/SQL-Code like this:

IF someCondition THEN
  NULL; -- do nothing
ELSE
  doSomething;
END IF;

you should better resist the urge to change that to

IF NOT someCondition THEN
  doSomething;
END IF;

because after the change, the program would no longer call doSomething if someCondition is NULL.

2005-08-31 Reply Admin

I have noticed that when I set up a linked server in MS SQL that points to an Oracle (8i) database, queries written in MS Query analyser against the Oracle database require UPPERCASE for the table name/owner:

Select * from LINKEDSERVERNAME..TABLEOWNER.TABLE

I have always been curious about this. Is it a setting in Oracle, In MS SQL, or just a WTF from two technologies that don't like eachother. It works fine, just very bizarre that the caps matter...oh and it was fun trying to figure it out too! Anyone else seen this behaviour? Anyone know what the cause is?

Richard Nixon · 2005-08-31 Reply Admin

ammoQ:
correction: concatenation

Why so many posts?

ammoQ · 2005-08-31 Reply Admin

Richard Nixon:
ammoQ:
correction: concatenation

Why so many posts?

Because the WTF forum software won't let me edit my posts.

Anonymous:

I have noticed that when I set up a linked server in MS SQL that points to an Oracle (8i) database, queries written in MS Query analyser against the Oracle database require UPPERCASE for the table name/owner:

Select * from LINKEDSERVERNAME..TABLEOWNER.TABLE

I have always been curious about this. Is it a setting in Oracle, In MS SQL, or just a WTF from two technologies that don't like eachother. It works fine, just very bizarre that the caps matter...oh and it was fun trying to figure it out too! Anyone else seen this behaviour? Anyone know what the cause is?

Oracle is per se not case sensitive, but in it's internal representation, it's all uppercase. Exception: Identifiers in double quotes (like "my identifier with spaces") are case sensitive (and they can contain spaces and other special characters). Accidentally, identifiers without double quotes are identical to the same in uppercase and double quotes; e.g. myTable = "MYTABLE". (But because of that, as said before, myTable != "myTable")
I guess MS-SQL sends all identifiers in double quotes, so only upper case will normally do the trick. You can find out by creating a database Table with double-quoted identifiers:

<code>
create table wtf ( "myWtf" varchar2(5) );
insert into wtf values ( 'WTF?!' );
</code>

and then, from MS-SQL,

<code>
select myWtf from WTF
</code>

RhythmAddict · 2005-08-31 Reply Admin

ammoQ:
Anonymous:

An empty string is just another way of representing null.

In Oracle, this is unfortunately true (at least in the versions I know) and these NULL strings can give you many happy debugging hours, because

length('') is not 0, but NULL
lpad('', 5) is not ' ' but NULL
rpad('', 5) is not ' ' but NULL

NULL [at least in oracle 9i] is when a column has never had a value (or manually set to NULL). Whereas '' is just that.

ammoQ · 2005-08-31 Reply Admin

RhythmAddict:
NULL [at least in oracle 9i] is when a column has never had a value (or manually set to NULL). Whereas '' is just that.

Read the full story here:
http://asktom.oracle.com/pls/ask/f?p=4950:8:8519634631622021894::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:5984520277372

For people using varchar instead of char, the assumption ''=NULL holds.

2005-09-01 Reply Admin

quote user="Alex Papadimoulis"

It's actually the recommend way of doing it. Studies have shown that UPPERCASE keywords are much easier to read ...

<FONT face="Courier New">SELECT col1, col2
FROM sometable
WHERE col3 = 'asdf'
AND col4 IS NULL</FONT>

end quote

I'm sure studies have ACTUALLY shown that easily distinguished keywords are easier to read. If you have syntax highlighting (which surely even Oracle must have by now) you don't need to uppercase everything.

Uppercasing your SQL in anything but example code is terrible. If you can't read sql, don't.

Ulvhamne · 2005-09-01 Reply Admin

Anonymous:
If you can't read sql, don't.

How are you supposed to learn it then?

dhromed · 2005-09-01 Reply Admin

Ulvhamne:
Anonymous:
If you can't read sql, don't.

How are you supposed to learn it then?

Oh come on.

Everybody knows that in the nature/nurture debate, SQL fits under 'nature'. You're born with that knowledge!

Ulvhamne · 2005-09-01 Reply Admin

dhromed:
Ulvhamne:

Anonymous:
If you can't read sql, don't.

How are you supposed to learn it then?

Oh come on.

Everybody knows that in the nature/nurture debate, SQL fits under 'nature'. You're born with that knowledge!

Ah, I stand corrected.

2005-09-01 Reply Admin

Otto:
Here's a question for you, don't ponder it too hard...

How many drugs would you have to take, over what period of time, for this loop to actually make any sense?

        FOR LOOPVAR IN 1 ..SPACES - 2
        LOOP
            PADVAR := PAD || RPAD ( PAD
                                  , LOOPVAR + 1
                                  , ' ' );
        END LOOP;
        RETURN PADVAR;

Of course it makes sense! It keeps the CPU warm and the bits moving so the integer pipes won't freeze in a long, cold winter night. Damn, why do people always fail to see the obvious?

2005-09-01 Reply Admin

Anonymous:
I don't know about Oracle, but in MS SQL, NULL <> NULL pi

I don't know about MS SQL, but shouldn't (NULL <> NULL) IS NULL?

Maurits · 2005-09-01 Reply Admin

Anonymous:
Anonymous:
I don't know about Oracle, but in MS SQL, NULL <> NULL pi

I don't know about MS SQL, but shouldn't (NULL <> NULL) IS NULL?

I know about MS SQL. And I know about ANSI.

Comparisons (NULL <> NULL) are either true or false, they cannot themselves be null.

MS SQL supports ANSI behavior but you have to turn it on (I did.)

Here's the difference (T-SQL script) - I use the *'s to note LITERAL nulls rather than calculated or selected nulls. " ... = NULL" and "... != NULL" are semantic sugar for "... IS NULL" and "... IS NOT NULL" - this is WRONG, IMHO.

SET ANSI_NULLS OFF

SELECT
    'N = N' = CASE WHEN CAST(Null AS BIT) = CAST(Null AS BIT) THEN 'True' ELSE 'False' END,
    'N != N' = CASE WHEN CAST(Null AS BIT) != CAST(Null AS BIT) THEN 'True' ELSE 'False' END,
    'N = *N' = CASE WHEN CAST(Null AS BIT) = Null THEN 'True' ELSE 'False' END,
    'N != *N' = CASE WHEN CAST(Null AS BIT) != Null THEN 'True' ELSE 'False' END,
    '1 = N' = CASE WHEN CAST(1 AS BIT) = CAST(Null AS BIT) THEN 'True' ELSE 'False' END,
    '1 != N' = CASE WHEN CAST(1 AS BIT) != CAST(Null AS BIT) THEN 'True' ELSE 'False' END,
    '1 = *N' = CASE WHEN CAST(1 AS BIT) = Null THEN 'True' ELSE 'False' END,
    '1 != *N' = CASE WHEN CAST(1 AS BIT) != Null THEN 'True' ELSE 'False' END,
    '1 IS N' = CASE WHEN CAST(1 AS BIT) IS Null THEN 'True' ELSE 'False' END,
    '1 IS NOT N' = CASE WHEN CAST(1 AS BIT) IS NOT Null THEN 'True' ELSE 'False' END,
    'N IS N' = CASE WHEN CAST(Null AS BIT) IS Null THEN 'True' ELSE 'False' END,
    'N IS NOT N' = CASE WHEN CAST(Null AS BIT) IS NOT Null THEN 'True' ELSE 'False' END

N = N    FALSE
N != N    FALSE
N = *N    TRUE
N != *N    FALSE
1 = N    FALSE
1 != N    FALSE
1 = *N    FALSE
1 != *N    TRUE
1 IS N    FALSE
1 IS NOT N    TRUE
N IS N    TRUE
N IS NOT N    FALSE

SET ANSI_NULLS ON

SELECT
    'N = N' = CASE WHEN CAST(Null AS BIT) = CAST(Null AS BIT) THEN 'True' ELSE 'False' END,
    'N != N' = CASE WHEN CAST(Null AS BIT) != CAST(Null AS BIT) THEN 'True' ELSE 'False' END,
    'N = *N' = CASE WHEN CAST(Null AS BIT) = Null THEN 'True' ELSE 'False' END,
    'N != *N' = CASE WHEN CAST(Null AS BIT) != Null THEN 'True' ELSE 'False' END,
    '1 = N' = CASE WHEN CAST(1 AS BIT) = CAST(Null AS BIT) THEN 'True' ELSE 'False' END,
    '1 != N' = CASE WHEN CAST(1 AS BIT) != CAST(Null AS BIT) THEN 'True' ELSE 'False' END,
    '1 = *N' = CASE WHEN CAST(1 AS BIT) = Null THEN 'True' ELSE 'False' END,
    '1 != *N' = CASE WHEN CAST(1 AS BIT) != Null THEN 'True' ELSE 'False' END,
    '1 IS N' = CASE WHEN CAST(1 AS BIT) IS Null THEN 'True' ELSE 'False' END,
    '1 IS NOT N' = CASE WHEN CAST(1 AS BIT) IS NOT Null THEN 'True' ELSE 'False' END,
    'N IS N' = CASE WHEN CAST(Null AS BIT) IS Null THEN 'True' ELSE 'False' END,
    'N IS NOT N' = CASE WHEN CAST(Null AS BIT) IS NOT Null THEN 'True' ELSE 'False' END

N = N    FALSE
N != N    FALSE
N = *N    FALSE
N != *N    FALSE
1 = N    FALSE
1 != N    FALSE
1 = *N    FALSE
1 != *N    FALSE
1 IS N    FALSE
1 IS NOT N    TRUE
N IS N    TRUE
N IS NOT N    FALSE

foxyshadis · 2005-09-01 Reply Admin

Richard Nixon:
http://www.editplus.com/javacpp.html

Syntax highlighting file for SQL in EditPlus can be had there.

Not the same thing; any editor can highlight SQL but few can highlight it in strings inside other languages. SPs are still better in general but some databases don't or didn't until recently support them, and dynamic is useful in other cases anyway.

duck1123 · 2005-09-01 Reply Admin

I'm lost, should the SQL in my query string be upper, or lowercase?

William Robertson · 2005-09-02 Reply Admin

foxyshadis:
Upper-case SQL always seemed like a throwback to the COBOL and FORTRAN days to me; after all, they are contemporaries. Weird how that particular habit has survived the lower_casing and then CamelCasing of every other language, even if it is slowly dying.

I think this WTF is a corker, but fact that any Oracle code posted here is immediately followed by two pages of comments about how the case conventions give it an unfashionable look and how they hate Oracle because of cursors, is a WTF in itself.

SQL is not Java.

Oracle did not invent SQL.

PL/SQL is designed to be an extension of SQL and therefore would be crazy if it did not follow SQL's object naming rules.

For example, if procedure names followed the Java convention so that AddInvoiceItems was not the same as addInvoiceItems or ADDINVOICEITEMS, while tables and columns did not, the language might have a more contemporary feel in some eyes but it would surely be absolute hell to work with. Though of course CREATE PROCEDURE is an SQL command anyway, and procedures and tables reside in the same SQL namespace, even if the procedure is implemented in PL/SQL or Java.

The case conventions of Java make sense and work well for Java, where you can divide pretty much everything into classes (initial capital) and instances of classes (initial lowercase), and "myString" usefully means something different to "MyString". In SQL that makes no sense because it is not object oriented, so should that be InvoiceItems, invoiceItems or what? Especially when it will appear in data dictionary listings as INVOICEITEMS, where the idea of running the words together to save typing doesn't look so clever any more.

Richard Nixon · 2005-09-02 Reply Admin

ammoQ:
Richard Nixon:
ammoQ:
correction: concatenation

Why so many posts?

Because the WTF forum software won't let me edit my posts.

So stop making mistakes in your posts.

You're annoying.

devdas · 2005-09-03 Reply Admin

Quoth Sharon Smith: In most RDBMSs (Oracle, MS SQL, MySQL, PostgreSQL, Ingres) null <> null. Oracle is the only one I've seen where isnull('') = true. It's great (NOT) when you're using trim(firstname) = trim(:c-variable) in your where clause and wondering why your counts are incorrect.

MySQL treats '' as NULL.

If you think of NULL as an unknown value, the whole '' == NULL stops making sense, and is plain wrong. NULL <> NULL because both of them are unknowns.

2005-09-06 Reply Admin

I wonder how many recrusions you could do with this before the sqls stack overflows...

Great test!!

ammoQ · 2005-09-06 Reply Admin

Anonymous:
I wonder how many recrusions you could do with this before the sqls stack overflows...

Great test!!

Oracle does not have an arbitrary limit on recursion depth in PL/SQL, but if you run into an endless recursion, nasty things may happen to your server. (Disclaimer: Don't try this on a production server)

2005-10-11 Reply Admin

its crap my mum could write that common this is slippin

2005-10-31 Reply Admin

RE: It's cr@p like this that reminds me why I prefer to use DB/2 and Microsoft SQL Server.

Let me see if I understand correctly: Somebody writes incredibly dumb code and it's the fault of the vendor of the product that person happened to be using?

Yes, I work for Oracle. No, I am not an Oracle fanatic (having worked for most of the database companies over the years).

Jim

2005-10-31 Reply Admin

RE: I'm lost, should the SQL in my query string be upper, or lowercase?

Ahem. I am the editor for all parts of the ANSI and ISO SQL standard. That doesn't mean that I am the expert, but it does mean that I am reasonably familiar with the standard and the history thereof. I also happen to work for Oracle and know a little bit (not much, really) about why things are the way they are regarding zero-length strings and null values.

In the SQL standard, the keywords happen to be defined in uppercase: SELECT, FROM, WHERE, CAST, RECURSIVE, etc. However, SQL is not (generally...see next paragraph) a case-sensitive language, so you can write your keywords in any case you like: select, fRoM, WHErE, etc. They all mean the same thing.

In addition, the SQL standard states that all regular identifiers (including table names, column names, variable names, parameter names, etc.) are implicitly upcast when an SQL statement is parsed. (SQL's delimited identifiers, identified in an earlier post as identifiers in double-quotes, are case sensitive and are not changed by the parser in any way; by the way, such identifiers can contain arbitrary characters, including spaces.)

The SQL standard defines the semantics of comparison when the null value is involved by saying (paraphrasing) that the null value is never equal to any value, including the null value. Because one "expects" that a null value is only a placeholder for a value that is not known, not supplied, missing, not applicable, etc., it could be substituted by any value (of the appropriate data type, that is). Thus, to allow NULL = NULL to be true would be misleading, since the first might be substituted by 42 and the second by 10, which aren't equal (except in base 42).

Oracle was first released 'way back when lots of current programmers were still in short pants. At that time, the SQL standard did not even have the notion of variable/varying-length character strings; it had only the fixed-length character string data type (CHARACTER or CHAR). When, in SQL-92, variable-length strings were added (CHARACTER VARYING, or VARCHAR), a heady debate ensued over the meaning of "no characters in the string" (zero-length strings).

In very large part because the C language referred to zero-length strings as "the null string", a lot of database implementors (and not just at Oracle, either...check your history of SQL Server!) conflated the notion of SQL's null value with the notion of "strings with no characters". Without the SQL standard to guide them, Oracle's implementors used the C-inspired conflation and equated zero-length variable-length character strings with the null value.

Once the SQL standard defined VARCHAR and its semantics that differentiate between the null value and "no characters", one might have hoped that Oracle, SQL Server, et al, would be "fixed". But there's that nasty little detail of the installed user base, in which there were (arguably) millions of SQL statements already used in production applications that depended on the old semantics. Oracle made what is certainly the right business decision not to break all of their customers, but to violate a very small piece of the standard. I have heard rumors that Oracle may be on the way to fixing this problem, but I do not know any facts to support or eliminate that rumor.

Hope this helps,
Jim

Horrible Orable Code

Leave a comment on “Horrible Orable Code”