COUNT(*) to find records which have a certain number of dependencies ?

T E Schmitz <mailreg@numerixtechnology.de> writes:

I want to select only those BRAND/MODEL combinations, where the MODEL has more
than one TYPE, but only where one of those has TYPE_NAME='xyz'.
I am not interested in MODELs with multiple TYPEs where none of them are called
'xyz'.

There are lots of approaches to this with various pros and cons.

The simplest one off the top of my head:

select *
from brand
join model on (brand_pk = brand_fk)
where exists (select 1 from type where model_fk = model_pk and type_name = 'xyz')
and (select count(*) from type where model_fk = model_pk) > 1

You could try to be clever about avoiding the redundant access to the type table:

select *
from brand
join model on (brand_pk = brand_fk)
where (select count(*)
from type
where model_fk = model_pk
having sum(case when type = 'xyz' then 1 else 0 end) >= 1
) > 1

I'm haven't tested that, it might need some tweaking. In any case I don't
think it's worth the added complexity, assuming you have indexes on type. I'm
not even sure it would run faster.

You could try to be really clever about it by turning the whole thing into a
join:

select *
from brand
join model on (brand_pk = brand_fk)
join (select model_fk
from type
group by model_fk
having sum(case when type = 'xyz' then 1 else 0 end) >= 1
and count(*) > 1
) on (model_fk = model_pk)

This would let the planner have a more plans to choose from and might be a big
win if there are lots of brands and models but few that satisfy the criteria
you're looking for.

--
greg

Hello Greg,
You have given me plenty of food for thought. Thank you for taking the
time.
Currently, the tables have such few records (350, 900, 1000) that
performance does not come into it, particularly seeing as this was only
needed for a one-shot report.
However, I have stached your examples away for future reference.

I was feeling a bit guilty about posting such a trivial question. I can
cobble together some straightforward SQL but I could really do with a
source of more complex SQL examples.
If you know of any links - that would great and save the list from more
such questions ;-)

I am correcting a couple of typos below in case someone tries these
examples out.

Greg Stark wrote:

select *
from brand
join model on (brand_pk = brand_fk)
where (select count(*)
from type
where model_fk = model_pk
having sum(case when type = 'xyz' then 1 else 0 end) >= 1
) > 1

... having sum(case when type_name = 'xyz' ...

select *
from brand
join model on (brand_pk = brand_fk)
join (select model_fk
from type
group by model_fk
having sum(case when type = 'xyz' then 1 else 0 end) >= 1
and count(*) > 1
) on (model_fk = model_pk)

) as somealias on (model_fk = model_pk)

(subquery in FROM must have an alias)

--

Regards/Gru�,

Tarlika

T E Schmitz <mailreg@numerixtechnology.de> writes:

) as somealias on (model_fk = model_pk)

(subquery in FROM must have an alias)

ARGH! This is one of the most annoying things about postgres! It bites me all
the time. Obviously it's totally insignificant since it's easy for my to just
throw an "AS x" on the end of it. But damn.

I see there's a comment foreseeing some annoyance value for this in the
source:

/*
* The SQL spec does not permit a subselect
* (<derived_table>) without an alias clause,
* so we don't either. This avoids the problem
* of needing to invent a unique refname for it.
* That could be surmounted if there's sufficient
* popular demand, but for now let's just implement
* the spec and see if anyone complains.
* However, it does seem like a good idea to emit
* an error message that's better than "syntax error".
*/

So where can I officially register my complaint? :)

--
greg

On Mon, 20 Sep 2004, T E Schmitz wrote:

I was feeling a bit guilty about posting such a trivial question. I can
cobble together some straightforward SQL but I could really do with a
source of more complex SQL examples.
If you know of any links - that would great and save the list from more
such questions ;-)

SQL for Smarties has some more complicated examples and topics for
"advanced" type queries. I can't say it's exhaustive, but I found it
a good bridge by hinting at what is really possible. I also found that
a good way to improve is to try to write every complicated query by
using all the different ways I can think of, like:

- UNION (ALL)
- SUB-SELECT
- LEFT OUTER JOINS
- HAVING
etc.

Here is the link for SQL for Smarties:

http://www.amazon.com/exec/obidos/tg/detail/-/1558605762/002-2222957-7220055?v=glance

The bad thing about the book is that it is sort of SQL agnostic, so
some of the examples would be sub-optimal on postgresql, or may not
even work.

I would like to hear about other sources too.

T E Schmitz <mailreg@numerixtechnology.de> writes:

There's a German saying "Go and find a parking-meter", i.e. suggesting to pop a
coin in the parking-meter and talk to it as nobody else wants to listen. ;-)

Yes well I anticipated such a response. So I tried my hand at it myself.

Well I finally found a problem tractable enough for me to get all the way from
start to end in a single sitting. Here's a simple solution to my complaint.

This patch allows subqueries without aliases. This is SQL-non-spec-compliant
syntax that Oracle supports and many users expect to work. It's also just
damned convenient, especially for simple ad-hoc queries.

There was a comment saying an alias name would have to be constructed so I
took that approach. It seems like it would have been cleaner to just ensure
that the code doesn't fail when no alias is present. But I have no idea how
much work would be involved in that, so I just took advice from the anonymous
author of the comment.

Incidentally, It seems weird to me that the counter doesn't reset for every
query. Perhaps I should change that?

Index: src/backend/parser/gram.y
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/parser/gram.y,v
retrieving revision 2.475
diff -u -p -c -r2.475 gram.y
cvs diff: conflicting specifications of output style
*** src/backend/parser/gram.y	29 Aug 2004 04:12:35 -0000	2.475
--- src/backend/parser/gram.y	20 Sep 2004 21:34:13 -0000
*************** table_ref:	relation_expr
*** 5158,5177 ****
  				{
  					/*
  					 * The SQL spec does not permit a subselect
! 					 * (<derived_table>) without an alias clause,
! 					 * so we don't either.  This avoids the problem
! 					 * of needing to invent a unique refname for it.
! 					 * That could be surmounted if there's sufficient
! 					 * popular demand, but for now let's just implement
! 					 * the spec and see if anyone complains.
! 					 * However, it does seem like a good idea to emit
! 					 * an error message that's better than "syntax error".
  					 */
! 					ereport(ERROR,
! 							(errcode(ERRCODE_SYNTAX_ERROR),
! 							 errmsg("subquery in FROM must have an alias"),
! 							 errhint("For example, FROM (SELECT ...) [AS] foo.")));
! 					$$ = NULL;
  				}
  			| select_with_parens alias_clause
  				{
--- 5158,5172 ----
  				{
  					/*
  					 * The SQL spec does not permit a subselect
! 					 * (<derived_table>) without an alias clause, We surmount
! 					 * this because of popular demand by gining up a fake name
! 					 * in transformRangeSubselect
  					 */
! 
! 					RangeSubselect *n = makeNode(RangeSubselect);
! 					n->subquery = $1;
! 					n->alias = NULL;
! 					$$ = (Node *) n;
  				}
  			| select_with_parens alias_clause
  				{
Index: src/backend/parser/parse_clause.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/parser/parse_clause.c,v
retrieving revision 1.136
diff -u -p -c -r1.136 parse_clause.c
cvs diff: conflicting specifications of output style
*** src/backend/parser/parse_clause.c	29 Aug 2004 05:06:44 -0000	1.136
--- src/backend/parser/parse_clause.c	20 Sep 2004 21:34:14 -0000
*************** transformRangeSubselect(ParseState *psta
*** 418,426 ****
  	 * an unlabeled subselect.
  	 */
  	if (r->alias == NULL)
! 		ereport(ERROR,
! 				(errcode(ERRCODE_SYNTAX_ERROR),
! 				 errmsg("subquery in FROM must have an alias")));

  	/*
  	 * Analyze and transform the subquery.
--- 418,434 ----
  	 * an unlabeled subselect.
  	 */
  	if (r->alias == NULL)
! 	{
! 		static int subquery_counter = 1;
! 		static char buf[30];
! 
! 		sprintf(buf, "*SUBQUERY*%d*", subquery_counter++);
! 
! 		r->alias = makeNode(Alias);
! 		r->alias->aliasname = pstrdup(buf);
! 		r->alias->colnames = NULL;
! 	}
! 	

/*
* Analyze and transform the subquery.

Greg Stark <gsstark@mit.edu> writes:

This patch allows subqueries without aliases. This is SQL-non-spec-compliant
syntax that Oracle supports and many users expect to work.

AFAIR you're the first to propose that we ignore the SQL spec here.
When I wrote "see if anyone complains", I had in mind waiting for
quite a few complaints before contemplating changing this...

regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:

Greg Stark <gsstark@mit.edu> writes:

This patch allows subqueries without aliases. This is SQL-non-spec-compliant
syntax that Oracle supports and many users expect to work.

AFAIR you're the first to propose that we ignore the SQL spec here.
When I wrote "see if anyone complains", I had in mind waiting for
quite a few complaints before contemplating changing this...

I understand. But I expect there are lots of users this annoys. It's just such
an easy thing to work around that it would be a petty thing to complain about.
That's the only reason I never complained about it all this time it was
annoying me. Not until I felt ready to poke around and fix it myself.

And I'm not suggesting ignoring the spec, just allowing the user to ignore it,
and not having postgres go out of its way to enforce the spec on users.

I doubt the spec says that the implementation cannot allow the syntax.

...Ok, well I wouldn't put it past the spec to do so. But it does so about
lots of things that Postgres allows. The general attitude postgres has seems
to be to extend the spec whenever it's nice for the user as long as doing so
doesn't interfere with actually supporting any spec compliant code. It's not
like postgres is a good platform for testing spec compliance of SQL code
otherwise.

It just seems excessively obnoxious to refuse queries because they don't match
a grammar precisely when the missing piece is entirely not needed by the
database. It doesn't cause any ambiguity or other problems with the SQL. It
doesn't cost a single cycle in the normal case. The code doesn't impact
anything else in the system. It's the database being intentionally nosy and
picky about something just because it can.

--
greg

Greg Stark wrote:

T E Schmitz <mailreg@numerixtechnology.de> writes:

) as somealias on (model_fk = model_pk)
(subquery in FROM must have an alias)

ARGH! This is one of the most annoying things about postgres! It bites me all
the time. Obviously it's totally insignificant since it's easy for my to just
throw an "AS x" on the end of it. But damn.

So where can I officially register my complaint? :)

Hope you don't mind an opinion from someone who looks at this from the
underside ...

Trying to give the parser a better chance of confusing you?

Having the tag only totally insignificant if you want to have a bunch of
special validation cases, where if there is only ONE anonymous
pseudotable, and no ambiguity is possible.

If all it does is give you an annoying but understandable error message,
might you care to consider the cryptic error messages you get from
systems that try to 'do what you mean, not what you say' for such
special cases ... and thereby turn a typo into an error twenty lines
further down the page.

BTW, the "as" is optional, but I always suggest that people use it
explicitly. Why? Because without it, you get another silly error message
or even a runtime error when what you did was omit a comma. For example

select salary name from Employee

returns one column (a dollar figure called "name").
(Yes, I know it's harder to cook up an example when the comma is missing
between tables in the FROM list; just wanted it to be obvious)

Okay, apologies for what may sound like a rant.
I've just been wrangling with an interpreter that tries WAY too hard to
make something executable out of what you tell it ... even if that's
really nothing like your intent.

Mischa Sandberg <ischamay.andbergsay@activestateway.com> writes:

Hope you don't mind an opinion from someone who looks at this from the
underside ...

Trying to give the parser a better chance of confusing you?

Ok, I understand the basic idea that a parser muddles along too long before
reporting an error makes it harder to track down the original error. However
this isn't such a case. I can't think of any way I could accidentally
introduce an extra comma that would lead to a valid looking alias and an error
much further along. You're talking about something like:

select * from a, as x

which would still produce a syntax error directly after the erroneous comma.
Which is right where you want the error to happen.

Having the tag only totally insignificant if you want to have a bunch of
special validation cases, where if there is only ONE anonymous pseudotable,
and no ambiguity is possible.

There's no additional ambiguity. There can't be since all the user would do to
make the parser happy is go and specify aliases, not necessarily use them. If
the user didn't feel the need to put aliases in initially presumably it was
because he wasn't using them because he didn't need them.

Can you really say queries like this are dangerous:

select a_id,b_id
from (select a_id from a where xyz=?),
(select b_id from b where xyz=?)

or queries like

select a_id,b_id
from (select a_id from a where xyz=?) as x,
(select b_id from b where xyz=?) as x

are any clearer or less ambiguous?

If all it does is give you an annoying but understandable error message

The reason it's annoying is because the database is saying, "I know perfectly
well what you're doing: I parsed the subquery fine, but I'm going to refuse to
run it because you didn't say the magic word." But when I add in "as x" after
every subquery even though it's utterly meaningless to the rest of the query,
then postgres is perfectly happy to cooperate.

BTW, the "as" is optional, but I always suggest that people use it explicitly.
Why? Because without it, you get another silly error message or even a runtime
error when what you did was omit a comma. For example

select salary name from Employee

I understand what you mean but I don't understand how always using the "as"
helps you here. You'll still get this error even if you were always using AS
when you intended. There's no option to make AS mandatory.

Incidentally, I also always use AS in both column and table aliases (when
specifying them) but for a different reason. I could never keep straight which
of Oracle and MSSQL required AS for columns and which required it for tables.
IIRC they're exactly reversed. However as best as I recall they both allowed
subqueries without any aliases at all.

--
greg