Have an example handy?

Post #59,840 by ChrisR 10/28/02 9:37:21 AM Reply	Have an example handy? There was a problem with SQLServer 7.0 when both assigning a temp variable and using it as a constraint - it would cause a problem when the query was interrupted with a context switch. My opinion on that matter was that it was bad query design in the first place - though MS should have fixed the problem. Other than that one, can you give some examples of wrong results being returned?
Post #60,104 by mmoffitt 10/29/02 6:58:07 PM Reply	Actually there was a bug in Sql 7.0. Before SP1, if you had a query that had a where clause that looked something like: where (a.Foo = 1 or a.Foo = 2 or a.Foo = 3 or ... a.Foo = 40) and (a.Foo1 = 100 or a.Foo2 = 101 or a.Foo2 = 102 or ... a.Foo2 = 201) and a.Foo3 = 200 ... When the sql was compiled, only the first big OR was included and executed. AFAIK, that was the only one where a legit (although messy) query might have returned the wrong result. And it was fixed in SP1 for Sql 7. (Note: I think this is an example of the problem, I'm stretching my memory here, but there was a bug in how large OR's were handled). bcnu, Mikem
Post #60,199 by gdaustin 10/30/02 11:04:42 AM 10/30/02 11:06:21 AM Reply	Seen lots of SQL generate "incorrect" results... Not so much because the SQL parser was bad (although that does occur occasionally), but because the person didn't know SQL in general or that specific dialect of SQL very well. I actually had a bug in some of my DB2 SQL code some time ago, where I had a multiple line SQL statment with line continuation characters (so the parser saw the code as a single line ), and I put a -- comment at the end of one of my lines before the \\. The query ran fine, but I didn't know that the -- caused the parser to ignore everything else in the SQL statement. Then I started getting results I wasn't expecting, rows were included, that were supposed to be excluded. I started investigating. By the time I figured it out, I was cursing DB2 SQL big time. Now, I know better. It took me about 2-3 hours to figure it out. In SQL, -- comments cause everthing to be ignored to the end of the line. If you use line continuation characters, then everything on subsequent lines is ignored, too! These kinds of bugs are the ones that separate the people who have been in a CS class in college (where parser principles are taught), from everyone else. I've seen people who didn't understand parsing spend WEEKS trying to figure out these kinds of problems. They end up reading the manual or asking someone before they "get it". Edited by gdaustin Oct. 30, 2002, 11:06:21 AM EST Expand All History
Post #60,201 by wharris2 10/30/02 11:10:57 AM Reply	Those dang outer joins The rules for outer joins are pretty well defined but often far from intuitive. I've seen SQL using outer joins produce seemingly weird but (after meditating on them for a while) completely logical results.
Post #60,205 by CRConrad 10/30/02 11:15:37 AM Reply	Why does it care about line-ends in the first place?!?
Post #60,213 by gdaustin 10/30/02 11:50:03 AM 10/30/02 11:51:17 AM Reply	Re: Why does it care about line-ends in the first place?!? It may have just been the command line tool I was using. We write a lot of DB scripts and then "pipe them" into the DB2 command line tool. That way they can be automated into shell scripts quickly. We even archive our scripts in our source code database, something I've never seen done before, but now I'm a fan. I'm now a "zealot" about this, because I can't lose key DDL or queries, and I have back versions. All the commands we need to create tables, indexes, permissions, views, etc. are in our source code control system. In fact, I've even created a script which pretty much creates all the tables/indexes/permissions in the DB after the DB is created. Now that I've been to SysAdmin class for DB2 (and I'm certified), I could actually automate the whole thing ( DB creation, tablespace creation, tuning comands ) into a script or series of scripts. But maybe that's going too far. Then again, we're about to run a test on some hardware we may be buying, and that level of automation could make my life really sweet. Just walk in, run the the scripts, and we're all installed! But, alas, I'm also SysAdmin, system architect, and Java developer, too. Edited by gdaustin Oct. 30, 2002, 11:51:17 AM EST Expand All History
Post #60,214 by admin 10/30/02 11:52:45 AM Reply	We do the same thing here. All DDL, packages, even the client config data, is in the source code control system. You can run one command and set up the entire system, including databases, web servers, and process servers. Regards, -scott anderson "Welcome to Rivendell, Mr. Anderson..."
Post #60,393 by static 10/31/02 6:30:42 AM Reply	We've done that, too. Well, for the SQL, anyway. It was an illuminating experience trying to use it to create a new install. We discovered a number of tables with incorrect definitions. :-) Wade. "Ah. One of the difficult questions."
Post #60,693 by Meerkat 11/2/02 12:35:34 AM Reply	Speaking of incorrect results... At two places I've worked they've used a database that can't even count properly. RedBrick database, now owned by Informix now owned by IBM. Stores count results in some kind of pre-calc table thing, and doesn't update it anywhere near enough. Select count(*) from fred where 1=1 forced it to actually look at the table in question and give you an accurate result. In a word: shocking. John. Busy lad.

Welcome to IWETHEY!