Re: Tool to check why queries miss results?

Comments in-line

Andreas Koch wrote:
> DA Morgan wrote:
>

>>> The tool doesn't have to "expect" anything - thats what
>>> the user input is for.
>>
>>
>> So the user would go to the tool and input all of the things
>> that are supposed to result from the query before the query
>> is run? Sounds like you'd need a query to know that.

I wasn't. If I was flaming you'd have been singed.

> Forget the missing constraint. That was just an example
> and i HOPED that would make things easier to understand.
> Seems it only confused people more.
>
> Given :
> An application that is of course much much more complex
> than the example i've given. Lets say 1.000.000 lines
> of SQL functions and views, written by many developers
> over many years.
>
> All tables have perfect constraints and all data
> is perfectly consistant.
>
> One query is expected to return certain values, based
> on its creators understanding of the tables and views
> used in it.

How does the tool know that? And even if it does ... how does it know what all the possible values are except by running a proper query of all possible values that meet the criteria?

> That person may have misunderstood some tables or views,
> or some tables or views may have changed later without
> correctly checking for every possible side effect this
> may have on all statements.

In a production environment the later is impossible. If it happens the DBA should be summarily shown to the door. So lets deal with the first item you bring up.

This is why there are tools like Business Objects, Crystal Info. and Oracle Discoverer. They exist because there is an End User Layer created by persons who are subject-matter experts to make this impossible assuming a decently trained person is sitting in front of the keyboard.

> So, the query returns some results but not all results
> THE USER OF THAT STATEMENT WOULD EXPECT.
The tool CAN NOT know what the user expects. If the user is that incompetent, and I am being serious here, then it is the organization's obligation to either train or replace the user.

> So, he tells the magic tool:
>
> A) This is the Query i executed

[Quoted] Good so far.

> B) This is what results i got (the tool can get that
> itself of course)

Good so far.

> C) This is a value i would have expected to get, but
> didn't.

[Quoted] How can you possibly know what you should have expected. I [Quoted] know you think this is possible but it isn't. Here's an example. I have a table that looks like this:

SEATTLE January
LONDON June
LONDON June
SEATTLE February
LONDON July

Here is my first query:

SELECT city_name
FROM mytable
WHERE month like 'J%';

Here is my second query:

SELECT city_name
FROM mytable
WHERE month like 'Ju%'

Do I have a right to complain that the tool should have known I really wanted January? No. If I wrote the second query I am am unqualified and need to be trained or replaced.

> I don't look for such a tool because i couldn't debug
> such a problem by hand. I look for such a tool because
> i'd prefer to debug such a problem in 5 seconds instead
> of 5 hours. And if there is no such tool out there,
> i'll probably go and write one myself, sooner or later.

[Quoted] The tool you want does not exist and the technology to create the tool you want will not exist in my lifetime. And I am planning to live a very long time.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)