IWETHEY v. 0.3.0 | TODO
1,095 registered users | 0 active users | 0 LpH | Statistics
Login | Create New User
IWETHEY Banner

Welcome to IWETHEY!

New 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".
Expand Edited by gdaustin Oct. 30, 2002, 11:06:21 AM EST
New 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.
New Why does it care about line-ends in the first place?!?
New 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.
Expand Edited by gdaustin Oct. 30, 2002, 11:51:17 AM EST
New 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..."
New 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."

New 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.
     SQL Server Database loses half of its records and indexes - (orion) - (27)
         SQL Database loses half of its records and indexes - (gdaustin) - (14)
             Recipe for doom - (tablizer) - (13)
                 Re: Recipe for doom - (jlalexander) - (12)
                     I'd disagree as well, but not so strongly. - (mmoffitt) - (11)
                         DBA's fault? - (tablizer) - (10)
                             Have an example handy? - (ChrisR) - (8)
                                 Actually there was a bug in Sql 7.0. - (mmoffitt) - (7)
                                     Seen lots of SQL generate "incorrect" results... - (gdaustin) - (6)
                                         Those dang outer joins - (wharris2)
                                         Why does it care about line-ends in the first place?!? -NT - (CRConrad) - (3)
                                             Re: Why does it care about line-ends in the first place?!? - (gdaustin) - (2)
                                                 We do the same thing here. - (admin) - (1)
                                                     We've done that, too. - (static)
                                         Speaking of incorrect results... - (Meerkat)
                             Possibly - (wharris2)
         What kind of replication are you doing? - (mmoffitt) - (11)
             Expect no response from Norm... -NT - (folkert) - (3)
                 Why's that? Is he no longer active here? -NT - (mmoffitt) - (2)
                     Claimed so on the mailing list- but, "Promises, promises..." -NT - (CRConrad) - (1)
                         Get over it - (orion)
             Update on Replication - (orion) - (6)
                 Try MSMQ. - (mmoffitt)
                 how will you resolve last write issues on the same record? - (boxley) - (3)
                     I've told him how to do it already. -NT - (CRConrad) - (2)
                         Thanks, but I lost that job. - (orion) - (1)
                             Yeah, sure - that must be why I did it. (new thread) - (CRConrad)
                 Since you're down to synchronization, not replication now - - (Arkadiy)

The honey tastes sweeter when you anger the bees.
114 ms