Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • The weird part is that the join is a 1-m, right? That is, conceptbindings.conceptid is not UNIQUE. Thus, c.rtype should not be admissible in the SELECT clause. A proper DBMS would complain about the fact that you need an aggregate function (like COUNT, or MIN).

    MySQL does not complain, right?

    • Really, c.rtype should not be admissible there even if conceptbindings.conceptid was UNIQUE (that is, it was a 1-1 relation), since there would be anyway no guarantee that c.rtype had a unique value for each concept group (value).
      Simply, the SQL standards for GROUP BY require that the columns in the select list must be in the GROUP BY expression or they must be arguments of aggregate functions (PostgreSQL would indeed complain, and so does even MS Access :-)

      Cosimo, you're right about SQL, this behaviou
      • Simply, the SQL standards for GROUP BY require that the columns in the select list must be in the GROUP BY expression or they must be arguments of aggregate functions

        That's not entirely true. The standards after 1992 state that the column must either be in the GROUP BY, part of an aggregate function, OR a "functional dependent" of something in the GROUP BY.

        I'm not sure if we can demonstrate a presence or lack of functional dependence based solely on the original SQL posted, but the spec is a little m

        • That's not entirely true. The standards after 1992 state that the column must either be in the GROUP BY, part of an aggregate function, OR a "functional dependent" of something in the GROUP BY.

          As far as I can see this was introduced in SQL:1999, while it's quite common to quote as SQL standards just SQL-92 (or even just SQL-89), since some think that later revisions are not to be considered real standards anymore:
          http://www.tdan.com/view-articles/4923/ [tdan.com]
          http://articles.techrepublic.com.com/5100-10878_11-1046268.html [com.com]

          Anyway you're formally correct ;-)

          I'm not sure if we can demonstrate a presence or lack of functional dependence based solely on the original SQL posted, but the spec is a little more nuanced than your quote suggests.

          Good point.

          I implied that if we had had a way to be sure that c.rtype had a unique value for each concept value (which is what functional dependence mean), there would have been no ambiguity. But I haven't probably been clear enough.
          (BTW, I see no way to demonstrate that c.rtype is functionally dependent on concept, given solely the original SQL posted, as I said in my post).

          However for the record, even if we had concept, c.rtype, concepts.id and c.conceptid all UNIQUE, PostgreSQL (v. 8.3.1) would anyway complain with:

          ERROR: column "c.rtype" must appear in the GROUP BY clause or be used in an aggregate function

          I gleaned this information from the following URL:

          http://dev.mysql.com/tech-resources/articles/debunking-group-by-myths.html [mysql.com]

          It also says:

          In MySQL, one can write GROUP BY queries that reference non-aggregated columns in the SELECT list that are not included in the GROUP BY clause, even if these columns are not functionally dependent upon the GROUP BY clause. This behaviour conforms to none of the SQL standard's versions.
          which is probably the answer Cosimo was looking for.

          Thanks for sharing this resource.

          Ciao!