Subject: Re: CAP: Badly formed SQL

From: "John Stracke" <[email-masked]>
Date: Thu, 10 Jan 2002 14:56:42 -0500

>C: QUERY:SELECT DTSTART,DTEND,SUMMARY,UID,VALARM.*
>C:    FROM VEVENT,VTODO
>C:    WHERE VALARM.TRIGGER >= '19990310T080000Z'
>C:    AND VALARM.TRIGGER <= '19990310T190000Z'
>C:    AND METHOD IS BOOKED
>
>I'm also not sure about selecting from both VEVENT
>and VTODO in a single statement like this,

Got it! The above example should have written as a union rather than a 
join.  ("Run this WHERE on this table, and then on that table, and return 
all that match from either table" instead of "take the cross product of 
these two tables, and return all the combined rows that match.") This 
leads to further complications if we want to really look like SQL, because 
you can't really do a union on two incompatible tables; but SQL does allow 
you to specify a remapping to make it work, so we can declare that, in a 
case like this, CAP's SQL does that remapping implicitly.  So the above 
query would be written as:

QUERY:SELECT DTSTART,DTEND,SUMMARY,UID,VALARM.*
   FROM TABLE(VEVENT) UNION TABLE(VTODO)
   WHERE VALARM.TRIGGER >= '19990310T080000Z'
   AND VALARM.TRIGGER <= '19990310T190000Z'
   AND METHOD IS BOOKED

(The TABLE() stuff is a quirk in SQL's syntax--there's such a thing as a 
UNION JOIN, so VEVENT UNION VTODO could give a parser trouble, so you have 
to do TABLE() to disambiguate.)

Meanwhile, we should probably disallow joins in CAP, because they just 
don't make sense--what does it mean to combine a VEVENT and a VTODO into a 
single component? Because that's what the join means.

/===============================================================\
|John Stracke                   |Principal Engineer             |
[email-masked]  |Incentive Systems, Inc.        |
|http://www.incentivesystems.com|My opinions are my own.        |
|===============================================================|
|"I use emacs, which might be thought of as a thermonuclear word|
|processor." -- Neal Stephenson                                 |
\===============================================================/

---

From: Doug Royer <[email-masked]>
Date: Thu, 10 Jan 2002 16:24:54 -0700

John Stracke wrote:

> 
> Meanwhile, we should probably disallow joins in CAP, because
> they just don't make sense--what does it mean to combine a
> VEVENT and a VTODO into a single component? Because that's what
> the join means.

The replies have been lost or whatever in CAP. As the results
were NOT merged into a single component. As in

	SELECT * FROM VTODO,VEVENT

Produced:

	BEGIN:VTODO		#1
	...
	END:VTODO
	...
	BEGIN:VTODO		#n
	...
	END:VTODO
	...
	BEGIN:VEVENT		#1
	...
	END:VEVENT
	...
	BEGIN:VEVENT		#n
	...

It DID NOT produce one big blob inside of a single BEGIN/END.

I could be happy with - yes, no joins.