[freeside-devel] mysql and complex queries

Dave Burgess burgess at neonramp.com
Mon Nov 26 14:59:30 PST 2001


> On Tue, Nov 20, 2001 at 12:29:50PM -0600, Dave Burgess wrote:
> > OK.
> >
> > I've been through the BNF for the SELECT statement in MySQL, and Version
> > 3 doesn't like this particular syntax at all.
>
> Do you mean to say it works in version 4?

I can't verify it one way or the other.  All I know for sure is that Version 3

chokes on it bad.

>
>
> > - The parentheses might be a problem.  I wasn't able to use them for
> > grouping.
>
> Hmm, I thought I only used them where required by the JOIN syntax or for
> sub-queries.  Is MySQL missing support for subqueries?

According to the documentation, parentheses can be used to force
interpretation .  Of course, I could be misinterpreting the error messages.

I took a look at the BNF for MySQL, Oracle 7, and PostgreSQL.  Of the three,
the only one that allows embedded sub-queries in the form you used is
PostgreSQL.

>
>
> > - Even without those, cascading OUTER JOINS need to have a specific
> > syntax:
> >
> >   SELECT * FROM table LEFT OUTER JOIN table_reference USING (field_list)
> > LEFT OUTER JOIN table_reference ON conditional ;
> >
> > USING and ON clauses can be interchanged.
>
> This looks to be the same as the PostgreSQL syntax for joins:
> http://www.postgresql.org/users-lounge/docs/7.1/postgres/sql-select.html
>

OK.  I was thinking that the syntax should be fairly straightforward.  The
following reference points to the same syntactic element in MySQL:

http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#Data_Manipulation

>
> > So, the query below is OK.
> >
> > SELECT * FROM cust_main left outer join cust_pkg using (custnum) left
> > outer join part_pkg using (pkgpart);
> >
> > This one fails:
> >
> > SELECT * FROM cust_main left outer join cust_pkg using  (custnum)
> >     left outer join part_pkg using (pkgpart)
> >       left outer join cust_svc
> >
> > It fails on the final "LEFT OUTER JOIN", apparently because there's no
> > obvious way to connect these tables....
>
> Hmm, I'm pretty sure I used an "ON" or "USING" qualifier on all the joins
> in the query below.
>

You did, it's just that the ordering gets confusing.

>
> > If you don't mind me asking,
>
> I certainly do not mind.
>
> > is this just a thought exercise, or are we
> > actually trying to accomplish something in particular.
>
> This is not a thought exercise.  This is the actual SQL used by the
> customer search page in CVS (httempalte/search/cust_main.cgi).  It's
> significantly faster to use one large query rather than thousands of small
> queries, and sped up the customer search for large data sets
> significantly.
>

Cool.  I've just finished pulling the schema into Dezign For Databases (an ER
Diagramming tool).  This should (theoretically) make it easier for me to
visualize the inter-relationships of the data.  I ended up buying the full
version to support the number of database relations.  If there's interest, I
can put the schema/database project file up on my website for everyone to
use.  I'm not done validating the relationships, and there are a couple of
relationships that the tools doesn't handle well, but it's more or less ready
to go.

Would it be possible to describe the desired resultant dataset?  While I don't
doubt that the query you wrote is excellent, I'm thinking there should be
another way to get the same information.

>
> >  For example, I
> > don't see how we can connect part_pkg and cust_svc like this last join
> > is trying.
>
> Hmm, I'm not joining part_pkg and cust_svc, I'm joining cust_pkg to
> part_pkg, and also cust_pkg to cust_svc.
>

I think I figured that out at some point.  I played around with the select for
about an hour before I had to start earning my pay again.

>
> > If I modify the select so that it tries to connect the cust_svc.pkgnum
> > key with the one in the cust_pkg (like this):
> >
> > SELECT * FROM cust_main left outer join cust_pkg using  (custnum)
> >     left outer join part_pkg using (pkgpart)
> >       left outer join cust_svc using (pkgnum)
> >
> > It fails again (ERROR 1054: Unknown column 'part_pkg.pkgnum' in 'on
> > clause')
> >
> > If I replace the using(pkgnum) with "on cust_svc.pkgnum =
> > cust_pkg.pkgnum" the SELECT works again.
> >
> > SELECT * FROM cust_main left outer join cust_pkg using  (custnum)
> >     left outer join part_pkg using (pkgpart)
> >       left outer join cust_svc on cust_svc.pkgnum = cust_pkg.pkgnum
> >
> > So, complex LEFT JOINS are possible, it might just take a little more
> > study to get the query to work in MySQL.
>
> I pretty much only develop with PostgreSQL now, so if MySQL support is
> important to the Freeside-using community, the community needs to help
> keep the MySQL support current.  Let me know, guys.  As it looks now,
> 1.4.0pre6 will not include MySQL support unless someone makes it happen.
>

I hate PostgreSQL.  I've struggled with PostgreSQL on a couple of projects
now, and I just can't get my head around it.  Now that MySQL supports
Transactions, the one compelling reason for using it is gone.

I'll be glad to help with MySQL support.  I'm still trying to get our entire
customer database built, but once I'm finished with that, I'll be getting
started on various 'extensions' to the system, like making SSAN optional/gone
through a conf file setting.

>
> > BTW:  MySQL Version 4 supports UNION now, so we might be able to use
> > that to accomplish this.
>
> If it's easy to support MySQL v4 but difficult to support MySQL v3, I
> don't mind dropping support for MySQL 3.
>

Me neither.  From everything I've seen, the existing tools work from v3 to v4,
and give better support for some more of these complex queries.  If I was a
brave sort, I'd try an upgrade.  I'm just too chicken today.

>




More information about the freeside-devel mailing list