[Info-Ingres] not quit what I meant by min()

#1

Hi All,

I've just been shown the following usage of min and its results have got
me puzzelled. Here is a test case...have a play and see what you think.

Martin Bowes

sql bowtest << CREATE_TABLES

\r

set autocommit on

\p\g

create table k2_mailing (pt_id integer, list_id integer, form_id
integer, date_replied date, date_logged date);

\p\g

create table form_reminder(form_id integer, original_form integer);

\p\g

insert into form_reminder values(8016510, 8219188);

\p\g

insert into k2_mailing values(1471945, 12675, 8219188, '13/02/2008
16:32:03', null);

\p\g

insert into k2_mailing values(1471945, 13220, 8016510, '13/02/2008
16:32:03', '13/02/2008 16:32:03');

\p\g

\q

CREATE_TABLES

And then...

sql bowtest << WEIRD

-- returns no rows! WHY?

select o.form_id,

o.date_logged,

o.date_logged as fubar,

min(r.date_replied) as date_replied_r,

min(r.date_logged) as date_logged_r

from k2_mailing o

join form_reminder f on f.original_form=o.form_id

join k2_mailing r on r.form_id=f.form_id

group by 1,2,3;

\p\g

-- returns 1 row, as expected.

select o.form_id,

o.date_logged,

o.date_replied as fubar,

min(r.date_replied) as date_replied_r,

min(r.date_logged) as date_logged_r

from k2_mailing o

join form_reminder f on f.original_form=o.form_id

join k2_mailing r on r.form_id=f.form_id

group by 1,2,3;

\p\g

-- This is like the first query, but with onle less min() statement.

-- And it seems to work, from Ingres2006, but in II2.6 it is still
returning

-- no rows.

select o.form_id,

o.date_logged,

o.date_logged as fubar,

min(r.date_replied) as date_replied_r

from k2_mailing o

join form_reminder f on f.original_form=o.form_id

join k2_mailing r on r.form_id=f.form_id

group by 1,2,3;

\p\g

-- This is like the first query, but with onle less min() statement.

select o.form_id,

o.date_logged,

o.date_replied as fubar,

min(r.date_replied) as date_replied_r

from k2_mailing o

join form_reminder f on f.original_form=o.form_id

join k2_mailing r on r.form_id=f.form_id

group by 1,2,3;

\p\g

\q

WEIRD

#2

On Apr 6, 2009, at 10:34 AM, Martin Bowes wrote:

Quote:

-- returns no rows! WHY?

select o.form_id,

o.date_logged,

o.date_logged as fubar,

min(r.date_replied) as date_replied_r,

min(r.date_logged) as date_logged_r

from k2_mailing o

join form_reminder f on f.original_form=o.form_id

join k2_mailing r on r.form_id=f.form_id

group by 1,2,3;

\p\g

Interesting. The OP150 shows a qp_qual (post-aggregation
qualification) that is
screwing things up. There is at least one bug here, and maybe two.

If you write the select, but omit the o.date_logged as fubar result
column,
BUT still write group by 1,2,3, it returns a result row. That is
wrong, it
should get a syntax error.

If you write the select as originally (with fubar), but group by 1,2
it gets
the correct result row. (which looks funky because there are THREE
non-agg
result columns, but two are the same, so group by 1,2 is actually OK.)

If you write the select with "group by o.form_id,o.date_logged" it
works.
If you write it with "group by o.form_id,o.date_logged,o.date_logged"
you
get the spurious qp_qual and no rows. Which would be a bug.

Karl

#3

Hi Karl,

Thanks for that response. I've raised this with the Corp as a bug.

Marty

-----Original Message-----
From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of Karl
& Betty Schendel
Sent: 06 April 2009 15:57
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] not quit what I meant by min()

On Apr 6, 2009, at 10:34 AM, Martin Bowes wrote:

Quote:

-- returns no rows! WHY?

select o.form_id,

o.date_logged,

o.date_logged as fubar,

min(r.date_replied) as date_replied_r,

min(r.date_logged) as date_logged_r

from k2_mailing o

join form_reminder f on f.original_form=o.form_id

join k2_mailing r on r.form_id=f.form_id

group by 1,2,3;

\p\g

Interesting. The OP150 shows a qp_qual (post-aggregation
qualification) that is
screwing things up. There is at least one bug here, and maybe two.

If you write the select, but omit the o.date_logged as fubar result
column,
BUT still write group by 1,2,3, it returns a result row. That is
wrong, it
should get a syntax error.

If you write the select as originally (with fubar), but group by 1,2
it gets
the correct result row. (which looks funky because there are THREE
non-agg
result columns, but two are the same, so group by 1,2 is actually OK.)

If you write the select with "group by o.form_id,o.date_logged" it
works.
If you write it with "group by o.form_id,o.date_logged,o.date_logged"
you
get the spurious qp_qual and no rows. Which would be a bug.

Karl

_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://www.kettleriverconsulting.com...fo/info-ingres

#4

Quote:

I've just been shown the following usage of min() and its results have got me puzzled.

Why id you write so much needless dialect? Tables with all NULL-able
columns. No keys? No ISO-8601 DATE as required by Standards? Then why
did you do a GROUP BY with ordinal position numbers??!! Did your
auditor really let you put logging dates in the table being
audited!!??

First, let's clean up this mess.

CREATE TABLE K2_Mailings -- more than one?
(pt_id INTEGER NOT NULL PRIMARY KEY, -- guess?
list_id INTEGER NOT NULL,
form_id INTEGER NOT NULL
REFERENCES Form_Reminders (form_id) , -- guess?
reply_date DATE NOT NULL,
log_date DATE); -- illegal design!

CREATE TABLE Form_Reminders -- more than one?
(form_id INTEGER NOT NULL PRIMARY KEY,
original_form INTEGER NOT NULL);

INSERT INTO Form_Reminders
VALUES(8016510, 8219188);

INSERT INTO K2_Mailings
VALUES(1471945, 12675, 8219188, '2008-13-02 16:32:03', NULL),
(1471945, 13220, 8016510, '2009-13-02 16:32:03', '2008-13-02
16:32:03');

Think about what it would mean to group on a redundant duplicate of a
column. Waste of time and a violation of first normal form, etc.

SELECT O.form_id, O.log_date,
-- why have the same column twice? Does 1NF mean anything??
MIN (R.reply_date) AS min_reply_date,
MIN (R.log_date) AS min_log_date
FROM K2_Mailings AS O,
Form_Reminders AS F,
K2_Mailings AS R
WHERE R.form_id = F.form_id
AND F.original_form = O.form_id
GROUP BY O.form_id, O.log_date; -- no stinking ordinals in RDBMS!,

The SELECT is done last so alias names are not known to the GROUP BY
in real SQL. GROUP BY is done only on columns, not computation.
Think about it for a second.

Most of your posting should have failed and given an error message
rather than just no rows. This is all wrong!!

#5

On Apr 8, 2009, at 9:26 PM, --CELKO-- wrote:

Quote:

I've just been shown the following usage of min() and its results
have got me puzzled.

Why id you write so much needless dialect? Tables with all NULL-able
columns. No keys? No ISO-8601 DATE as required by Standards? Then why
did you do a GROUP BY with ordinal position numbers??!!

I suspect that the only reason info-ingres didn't see these ragings
from myself or Roy already, is that we know Marty and Joe doesn't.
If Marty wrote that stuff himself ab initio, I trust that he has enough
sense to keep quiet about it. :-) But I'm guessing he didn't, and
there is no point in savaging the messenger.

Nulls are evil, positional group by's are a tile on the
slippery slope to the dark place, etc etc. I applaud
all of it, but at the same time, Ingres is supposed to
give the right friggin answer no matter how disturbed,
evil, wrong, and broken the query is...

Karl

#6

--CELKO-- wrote:

Quote:

SELECT O.form_id, O.log_date,
-- why have the same column twice? Does 1NF mean anything??
MIN (R.reply_date) AS min_reply_date,
MIN (R.log_date) AS min_log_date
FROM K2_Mailings AS O,
Form_Reminders AS F,
K2_Mailings AS R
WHERE R.form_id = F.form_id
AND F.original_form = O.form_id
GROUP BY O.form_id, O.log_date; -- no stinking ordinals in RDBMS!,

While I agree with your points (although they're made a bit stridently,
no?) why abandon SQL-92-style joins? I haven't written a SQL-89 join in
over 10 years.

Quote:

from k2_mailing o
join form_reminder f on f.original_form=o.form_id
join k2_mailing r on r.form_id=f.form_id

is at least as clear, although "as" wouldn't hurt. .

Quote:

The SELECT is done last so alias names are not known to the GROUP BY
in real SQL.

That took me awhile to understand. You mean that tuples are formed,
logically, as a cartesian product of the tables, the WHERE clause filters
them, the GROUP BY consolidates them, and the SELECT statement chooses
columns. And because things are -- theoretically -- done thus, the GROUP
BY can't know the aliases established "later" by SELECT. Except, who
applies the aggregation functions? Doesn't GROUP BY have to peek?

Not that using ordinals in a GROUP BY has any merit, just that I don't see
how the "real SQL" pot shot does, either. :-)

--jkl

#7

--CELKO-- wrote:

Quote:

I've just been shown the following usage of min() and its results have got me puzzled.

Why id you write so much needless dialect? Tables with all NULL-able
columns. No keys? No ISO-8601 DATE as required by Standards? Then why
did you do a GROUP BY with ordinal position numbers??!! Did your
auditor really let you put logging dates in the table being
audited!!??

Whoa. Down, boy.

You are ranting enthusiastically about a pathological example that was
presented as such.

Re ISO-8601 dates, I suspect the DBMS has been installed with the native
Ingres date type as the default DATE. I agree the Ingres DATE type is
excessively liberal (sloppy, even) and a cause of numerous postings here
over the years. In principle I would prefer to use the ANSI/ISO DATE
type too, but the Ingres DATE type offers a number of date functions
that are extremely tempting.

--
Roy

UK Ingres User Association Conference 2009 will be on Tuesday June 9, 2009
Go to http://www.iua.org.uk/join to get on the mailing list.

#8

Hi Jim,

Thanks for the reply, you are of course quite right about this, but let
me retort...because its kinda fun...

Quote:

Why id you write so much needless dialect?
I didn't, it was shown to me by the programmers. They are somewhat

forced into it by the designers who love to have one table hold as much
data on as many subjects as physically possible. And boy does it create
some chunky queries which are completely indecipherable gibberish even
to experienced programmers! Have you heard this before..."A week after
writing it I had no idea why it works, or what its trying to do".

My rantings to them that 'Hey Ingres does support more than 16 tables in
a database' were greeted with astonishment and then politely ignored.
Ditto sensible names for columns...'you've got 32 characters, use 'em'.
Also plurals..."please choose one, I don't care which, just be
faithful!".

Last time, I got all choked up, there were tears.

Sadly (or not), the design has been in place for some time and has the
benefit of inertia. We havent had the 'Big Bang' which could make this
change. Nor given the workload would a comprehensive reworking be
possible.

Quote:

Tables with all NULL-able columns. No keys?
That was my trivial example of the problem as much as anything else. I

try to make test cases which can be easily cut and pasted with as little
extra stuff in there as possible.

If their too long ... who will read them?

You don't need a structure to demonstrate the problem, you don't need
constraints to demonstrate the problem.

Quote:

No ISO-8601 DATE as required by Standards?
We just use ingresdate, it seems OK for what we need.

Quote:

Then why did you do a GROUP BY with ordinal position numbers??!!
Slackness. I tell the programmers to use column names, but its their

problem if they don't.

Quote:

Did your auditor really let you put logging dates in the table being
audited!!??
That's the way, uh-huh, uh-huh, they like it.

You think that's bad, we actually have a case where the audit record
*HAS* to be written before the record that forces the audit record. I
gave up trying to understand why, but they seemed very adamant about it.

It ain't broken, don't fix it.

Quote:

Most of your posting should have failed and given an error message
rather than just no rows. This is all wrong!!
Granted, but the query *MUST* return the correct data. If ingres got all

hissy about crap code and table structures then most of us would be
working with Oracle or Access by now.

The system has to be as tolerant of sub-sub-optimal code as reasonably
possible.

Marty

_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://www.kettleriverconsulting.com...fo/info-ingres

#9

Hi Karl,

Quote:

If Marty wrote that stuff himself ab initio, I trust that he has
enough
sense to keep quiet about it. :-)
I'm not that smart!

Quote:

I applaud all of it, but at the same time, Ingres is supposed to
give the right friggin answer no matter how disturbed, evil, wrong,
and broken the query is...
Damn right!

I defend our right to write garbage. Its in the constitution!

Granted ponographers use the same defence....

Marty

#10

Quote:

That took me awhile to understand. *You mean that tuples are formed, logically, as a Cartesian product of the tables, the WHERE clause filters them, the GROUP BY consolidates them, and the SELECT statement chooses columns. *And because things are -- theoretically -- done thus, the GROUP BY can't know the aliases established "later" by SELECT. *Except, who appliesthe aggregation functions? *Doesn't GROUP BY have to peek? *

Nope. The grouping is done on the columns, then the groups are
reduced to single rows (constants, aggregates, grouping columns and
expressions built on those things). You cannot GROUP BY an
expression.