Re: Another angle on this....

You wrote: "No db that I am aware of returns rows from null comparisons"

And I would say you are incorrect.

TTBOMK the following code will return results in SQL Server and Sybase:

SELECT 'x'
FROM mytable
WHERE null = null;

It will not in Oracle.

Daniel Morgan

Heinz Kiosk wrote:

> "damorgan" <dan.morgan_at_ci.seattle.wa.us> wrote in message
> news:3C72A014.C8DCFDDC_at_ci.seattle.wa.us...
> > 1. Defending the indefensible? Bug? Let me quote you from page 38 of Tom
> Kyte's book expert one-on-one
> > Oracle.
> >
> > "The developer doing the conversion (from TransactSQL to Oracle) was
> complaining that the SQL queries in
> > Oracle returned the 'wrong' answer. The goal here was to find al of the
> rows in T where X was Null if
> > some condition was not met or where x equaled a specific value if some
> condition was met.
> >
> > The complaint was that, in Oracle, this query would return no data hen
> L_SOME_VARIABLE was not set to a
> > specific value (when it was left as Null). In Sybase and SQL Server, this
> was not the case - the query
> > would find the rows where X was set to a Null value. I see this on almost
> every conversion from Sybase
> > or SQL Server to Oracle. SQL is supposed to operate under tri-valued logic
> and Oracle implements Null
> > values the way ANSI SQL requires them to be implemented. Under those
> rules, comparing x to Null is
> > neither True or False - it is, in fact, unknown."
> >
> > So if complying with ANSI SQL is an indefensible bug I'll stand in for
> Larry Ellison. >>Fire when ready!
> Straw man again.... You do like them. I hope you didn't pay much for that
> book.<G>
> I'm not complaining about Oracle returning no rows from comparisons with
> Null value fields. Nor am I complaining that I can't have Nulls in unique
> keys. No db that I am aware of returns rows from null comparisons and the
> reference above to SQL Server doing it is mistaken. It definitely doesn't.
> Reading the snippet Tom Kyte has missed the point (deliberately?). He didn't
> notice that all other db platforms actually distinguish between blank and
> null (correctly). Presumably he is some Oracle high-priest and I'll now be
> burnt at the stake for heresy.
>
> To repeat; I am complaining that Oracle turns blank strings into Nulls. That
> is the indefensible behaviour. All of the rest is collateral damage
> resulting from the fact that Oracle doesn't appear to understand that "" is
> a valid value for a string field distinct from NULL. Lets put it another
> way, would you still defend Oracle if integer zeroes were considered as
> nulls and failed to match any query predicate other than "is null" and
> integer zeroes couldn't be in a key and appeared last in sorted lists etc.
> Come on.
>
> You and Tom Kyte are in a hole thats so deep I can't see either of you any
> more, just the frenzied earth flying out the top. Stop Digging.
> > ----------------------------------------------
> > 2. BFD = Big ..... Deal
> Thank you, I'll remember that.
> However, its a big deal for me when porting a large app from another
> platform. Did the developer who implemented quoted identifiers really think
> that users would want to distinguish between columns named "Comment" and
> "COMMENT" and "cOMMENT" and "CoMMENT"......? Not useful
> > ----------------------------------------------
> > 3. I've been using identity columns for years.
> > What difference can't be implemented in 29 keystrokes?
> > Be specific.
> See my boring and pointlessly detailed rebuttal in another thread.
> > ----------------------------------------------
> > 4. Creating an index is a pain? Really? No doubt you'll never touch C or
> Java.
> My main development is done entirely in C++. Its only a minor pain.
> > ----------------------------------------------
> > 5. Had you given an example I'd gladly comment. But, like CASE, I suspect
> > you are expressing more your lack of knowledge of Oracle rather than any
> > inability of Oracle to perform some function.
> Try decode (fieldvalue>4,0,otherfieldvalue,0) As it happens, most of my
> gripe about this goes away as I can use CASE when fieldvalue>4 then 0 else
> otherfieldvalue END
> > ----------------------------------------------
> > 6. Didn't appear in 9i. I use it in 8i.
> > You stand doubly corrected.
> Another Oracle user has told me that their documentation for 8i does not
> mention it either. Out of interest, could you definitely create views using
> CASE in 8i using PL/SQL? Thats what I wanted to do and failed for some
> reason. Probably incompetence.
> > ----------------------------------------------
> > 8. No one uses Oracle "out of the box" any more than one builds a house
> with 2x4s.
> > Nails and carpentry are required. The advantages come with a cost. The
> cost of
> > having to do real work and reading the manuals.
> I've been pampered, the other db manufacturers built my house for me, its a
> bummer to have to build my own. <G>
> So, optimised ODBC routines only managing to return 2.5% of the performance
> of 4 other platforms (out of the box performance) is acceptable performance
> for Oracle out of the box, cos "no-one uses it that way". As a clueless
> whining stranger to the real work that you are familiar with any guesses at
> how long it'll take me to solve this one? I need to know how long I'll be
> doing real work and when I can revert to my normal slothful condition. <G>
> However there is a serious point here. The performance of multi-record
> retrieval is dreadful (despite terrific performance in returning the *first*
> row even of complex queries). I accept that its my responsibility to sort it
> out, and Oracle is very tunable etc etc etc , but I have no idea how much
> work I'll have to do, and how much knowledge I'll have to acquire to fix it.
> The commitment is completely open ended as no-one has even given the vaguest
> hint as to what the nature of the problem might be and where I should start
> looking other than to recommend that I start reading some weighty tomes.
> > ----------------------------------------------
> > Finally: I'm not upset. It takes a lot more than the usenet to upset me.
> Then why do so many of your posts in these groups come across as upset
> put-downs?
> >
> > But you don't see Oracle developers in the SQL Server usenet groups
> complaining about the lack of
> > multiversioning and the fact that row level locks are a finite resource do
> you?
> Of course you don't, they're all too busy doing "real work" trying to get
> usable performance out of the shipped version of Oracle <G>. Meanwhile all
> the SQL Server/DB2/Sybase bunnies whose apps are just singing along without
> tuning so they've got no work to do come in here and post specifically to
> wind you up. Its that or yet another game of Hearts. ;)
> Seriously, because Oracle *is* a higher pitched and more advanced product
> than SQL Server there probably aren't as many people trying to do the port
> in that direction. You can bet your life that if there were, and they needed
> those features, they'd let the NG know about it.
> >
> > I am just tired of what I perceive to be the kind of whining I would
> expect from a toddler coming from
> > people that think that whatever they know is better than what they don't
> know. That are reluctant to
> > learn new things. That think change is bad. And post in the Oracle usenet
> groups, day after day, week
> > after week, the same nonsense. Software is just a tool not a religion. At
> Boeing they don't put
> > airplanes together with hammers. And my house was not built with a rivet
> gun.
> Another straw man. I've ported my app to 5 different db platforms. I love
> learning new things. I approached Oracle with excitement. However, it is a
> fact that despite Oracle's many nice features, most of which are
> unfortunately of little use to me because I develop cross-platform, the
> Oracle port was considerably more painful than all of the others put
> together, by a long margin.
>
> Its not me who has a religious attitude to one particular technology. Pots
> and Black Kettles I think.....
>
> > ----------------------------------------------
> > Daniel Morgan
> >
> >
> >
> > Tom McClelland wrote:
> >
> > > damorgan <dan.morgan_at_ci.seattle.wa.us> wrote in message
> news:<3C7144EB.7CA04F3A_at_ci.seattle.wa.us>...
> > > > My comments, barbs, interspersed below.
> > > >
> > > > Daniel Morgan
> > > >
> > > >
> > > >
> > > > Tom McClelland wrote:
> > > >
> > > > > Your points are valid, I don't contest them. However as someone
> coming
> > > > > from SQL Server to Oracle I have faced the following significant (to
> > > > > me) irritations:
> > > > >
> > > > > 1. Empty strings turned into nulls. Empty strings per se are not
> > > > > supported
> > > >
> > > > What's your point? Those going from Oracle to SQL Server can complain
> > > > about the exact opposite.
> > > No they can't, those going from SQL Server to Oracle acquire the
> > > remarkable ability to distinguish between blank values and unknown
> > > values. They aren't required to use it. To me there is a big
> > > difference between someone not having a middle name and someone's
> > > middle name being unknown.
> > > > So they are different ... so what? Did you think
> > > This is not merely a difference. It is a bug in the way Oracle treats
> > > blank strings. I don't understand how you can defend it. I can't
> > > include a blank field in a primary key, ever, in Oracle. I can't get
> > > sensible results from select * from table where field>"". You are
> > > defending the indefensible in your zeal to consider Oracle equal or
> > > superior in every way.
> > > > Oracle was SQL Server with another company name on the CD? Do you
> think
> > > > that everything Microsoft does is the "standard" to which everyone
> else
> > > > must adhere?
> > > No, there are lots of things that I like better about Oracle, and lots
> > > of inadequacies in SQL Server.
> > > > Do you think that because you learned SQL Server first
> > > > somehow that makes it better?
> > > No. You are wrong. I didn't learn SQL Server first (though I did learn
> > > it before Oracle), and I don't think that it is better in every
> > > situation.
> > > > Good grief. If I followed that logic I'd be
> > > > whining about things being different than they were in Fortran IV.
> > > You are fighting a straw man with this one. You've set me up as
> > > someone who thinks a particular way, and then you are knocking that
> > > way of thinking over. Nothing to do with me.
> > >
> > > > >
> > > > > 2. Quoted identifiers are case sensitive, just what you need...
> > > >
> > > > BFD
> > > You'll have to explain that one to me.
> > > >
> > > > >
> > > > > 3. No support for identity columns, instead requiring programmers to
> > > > > create sequences and triggers to achieve effects that are trivial in
> > > > > other databases
> > > >
> > > > Implementing a seqence takes 29 keystrokes. Count them ... 29 to
> create
> > > > the sequence and add it to an insert statement. This is a lot of
> whining
> > > > over 29 keystrokes.
> > > It takes a lot more than that to duplicate the full functionality of
> > > an identity column. You need to study the difference more closely.
> > >
> > > > But go the other way ... I'd like to see you make your
> > > > identity column count backwards by every third number and reset itself
> > > > after every 300 inserts.
> > > But I've never needed to do anything like that in 20 years. I just
> > > want column identities. Its a common requirement, sensibly supplied by
> > > all other platforms that I've used.
> > >
> > > >
> > > > >
> > > > > 4. No option to ignore case on indexes or character-string
> comparison
> > > >
> > > > BFD. It is that bloody painful to type UPPER()?
> > > Of course not, but for it to be efficient you also have to create the
> > > upper case functional index too. These little details add to the pain
> > > of porting generic code. I need to go through all my code looking for
> > > places where this may be necessary.
> > > >
> > > > >
> > > > > 5. No support for comparison operators in arithmetic expressions
> > > >
> > > > I'm not clear on what you mean by comparison operators. But I strongly
> > > > suspect you are otally incorrect.
> > > How do you suspect that I'm incorrect when you don't know what I'm
> > > talking about? What do you think it can do? Look at the definition of
> > > expressions in the BN diagrams, and you'll see the difference between
> > > the expressions permitted for comparisons and the expressions
> > > permitted for arithmetic operations. (unless of course like CASE this
> > > has been added, in which case I apologise)
> > >
> > > >
> > > > >
> > > > > 6. No support for "Case" notation
> > > >
> > > > Wrong! I use CASE WHEN in much of what I write. Try RTFM.
> > > I stand corrected. I didn't notice that this had appeared in 9i, due
> > > to my stupid inability to find the 9i manual pages on Oracle's website
> > > (thank you DStevens for the URL). I am also told that it works in at
> > > least some versions of 8i despite not apparently appearing in the
> > > documentation. Curious.
> > > >
> > > > >
> > > > > 7. Bugs in the ODBC driver which (in my case) make CLOBS unusable,
> so
> > > > > I have to rely on varchar2(4000)
> > > >
> > > > Try an ODBC driver from another company.
> > > Which one do you recommend? It's Oracle's ODBC driver that I am using.
> > > I've got 2 CLOB bugs.
> > > >
> > > > > 8. Out-of-the-box retrieval of multi-record datasets at 1/40 the
> speed
> > > > > of SQL Server in my (highly optimised) ODBC usage (SQLBindCol,
> > > > > multi-record Chunked retrievals, etc)
> > > >
> > > > Then you don't have a clue what you are doing.
> > > That told me, I'm an idiot. So sorry for troubling you.
> > >
> > > I said, "Out of the box". My routines retrieve records at around
> > > 4000/second on 4 other platforms as they install out of the box
> > > (MSSQL,Sybase,MSAccess(!), and DB2), and around 100/second on Oracle.
> > > I am sure I am doing something very wrong, but despite posting
> > > detailed expositions of this problem in these forums no-one has
> > > managed to suggest what it might be beyond suggesting that I read
> > > books on tuning Oracle, in order to achieve the same performance that
> > > I get for free in 4 rival technologies.
> > > >
> > > > > I know that there are work-arounds for some of these "features". And
> I
> > > > > understand that Oracle is a great tool for those wanting to store
> vast
> > > > > quantities of data. A competitor to SQL Server for those just
> wanting
> > > > > to store a few million records and have a thousands of transactions
> > > > > per day, with less than 50 permanent users, it ain't, IMHO.
> > > > >
> > > > > Ducks back into large concrete bunker and dons flameproof suit <G>
> > > > >
> > > > > Regards
> > > >
> > > > What your list clearly indicates to me is that you are totally
> clueless as
> > > > to what Oracle is.
> > > Its not a database then? Damn.
> > >
> > > > Let me give you the reverse complaint list. One for
> > > > which there is no work-around in SQL Server.
> > > >
> > > > 1. No multiversioning.
> > > Sadly in my ignorance I don't know what this is. In my other platforms
> > > I am presumably suffering because of its absence. You didn't name lots
> > > of other great features of Oracle, that I would love if it were the
> > > *only* platform that I were trying to develop against, eg materialised
> > > views, the ability to use a sub query where a table-name goes in a
> > > query without building a view first, function-based indexes, and many
> > > others. Lots of great features don't make a product perfect however,
> > > and my experience of porting to Oracle has been around 100 times more
> > > painful than the other ports of my product that I've done.
> > > >
> > > > Didn't need any others. That one is more than enough. When you figure
> out
> > > > how to code around that in TransactSQL let Bill Gates know.
> > > Why should I, I don't like Microsoft particularly above Oracle.
> > > >
> > > > BTW: When the developer of the Jet engine left Microsoft and started
> his
> > > > own company two or three years back his database platform of choice
> was
> > > > Oracle on Linux. I guess he didn't know as much about it as you.
> > > >
> > > > Daniel Morgan
> > >
> > > Daniel, what's your problem? You seem very upset and defensive that I
> > > don't love Oracle as much as you do. Do you not think that at least
> > > some of my complaints could be legitimate criticisms?
> >
Received on Wed Feb 20 2002 - 17:47:39 CST