Re: Another angle on this....

Please email me the SQL statement you tried.

Thanks,

Daniel Morgan

Heinz Kiosk wrote:

> "damorgan" <dan.morgan_at_ci.seattle.wa.us> wrote in message
> news:3C7435AD.A754FB7C_at_ci.seattle.wa.us...> 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:
>
> TBOYK is misinformed. Just tried it in SQL Server 7. No rows returned AIKA.
> Bad luck. Another straw man goes onto the bonfire. Can't be bothered to fire
> up sybase I'm afraid. I suspect some anti SS zealots are going around
> feeding you crap, that Kyte guy maybe?
>
> I can see how Oracle types might get confused about this. They'd write
> blanks (not nulls, don't get confused now ;) ) into every-other-db varchars,
> and do select * from table where column='' expecting no rows to be returned
> and be shocked and horrified when every-other-db gives them all the blank
> rows in the table. "UGH, it's giving me hits on nulls, what a load of
> rubbish, back to SQL compliant Oracle for me.", said Mr Kyte.
>
> >
> > 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 - 18:49:08 CST