Re: [Modeling-users] Double Result

[Sebastien B. <sbi...@us...>]

        Hi,

  A complete fix for bug #780495 (when ec.fetch() is joining two tables
  or more, the returned set of objects could have duplicates) has been
  submitted onto the main trunk. Corrected files are:

SQLExpression.py v1.21
DatabaseAdaptors/MySQLAdaptorLayer/MySQLSQLExpression.py v1.6
DatabaseAdaptors/PostgresqlAdaptorLayer/PostgresqlSQLExpression.py v1.6
test_SQLExpression.py v1.7

  Details: the 'DISTINCT' keywork is only added when needed, ie when two
           tables or more are joined. The corresponding SQL statement
           for counting rows / ec.fetchCount() is generated following
           the general rules exposed in the previous post (incl. below).

-- S=E9bastien.


Sebastien Bigaret <sbi...@us...> wrote:
> Jer...@fi... wrote:
> [About SELECT DISTINCT]
> > I'm thinking that you really need to check this carefully since sometim=
es
> > we don't want to use disctinct, no ? hum deleting or count may have the
> > same trouble no ?=20
> >=20
> > I haven't work w/ modeling since 5 months right now so I may say wrong=
=20
> > things .. pay caution
>=20
> No, you're right as far as ec.fetchCount() is concerned: it is also
> affected by this bug. Alas, I've no patch to propose for fetchCount()
> yet: I can't find a portable way of making this (see below).
>=20
>   Deletes, inserts or updates are not affected by this bug, because the
>   framework never joins tables for these operations.
>=20
> About fetch(): I can't think of any situations where you would not want
> the DISTINCT stuff. If you can think of such situations, I think it's
> time to speak ;)   However, I'm still not sure that the DISTINCT keyword
> will always be added to any select statements, it can be better to add
> it only when tables are joined.
>=20
>=20
> Now back on fetchCount(): I've found these two forms:
>=20
> Form #1
> -------
> SELECT  count(DISTINCT(t0.id)) [as count]
> FROM WRITER t0
> INNER JOIN BOOK t1 ON t0.ID=3Dt1.FK_WRITER_ID
> WHERE ((t0.AGE < 100 OR t0.AGE > 200) AND t1.title LIKE '%');
>=20
> Form #2
> -------
> SELECT  count(*) from (
> SELECT DISTINCT t0.ID,t0.FIRST_NAME,t0.LAST_NAME,t0.AGE,t0.BIRTHDAY,t0.FK=
_WRITER_ID
> FROM WRITER t0
> INNER JOIN BOOK t1 ON t0.ID=3Dt1.FK_WRITER_ID
> WHERE ((t0.AGE < 100 OR t0.AGE > 200) AND t1.title LIKE '%'))
>=20
> And here is how db-server accepts them:
>=20
>    |Postgres| MySQL | SQLite | O9i | O8i |
> ---+--------+-------+--------+-----+-----|
> #1 |   Y    |   Y   |   N    |  Y  |  Y  |
> ---+--------+-------+--------+-----+-----|
> #2 |   Y[1] |   N   |   Y    |  Y  |  Y  |
> ---+--------+-------+--------+-----+-----+
>=20
> [1] for postgresql, 'as foo' should be appended at the end of the SQL
>     statement
>=20
> Form #1 is clearly an extension of ANSI SQL-92, but I can't find whether
> form #2 is SQL-92 or not; I suspect it is, and that a from statement can
> be a sub-query (the so-called table subqueries), but I'd like to hear
> from you if you're sure about it.
>=20
>   I think I'll probably make #2 the default in SQLExpression, and use #1
>   for MySQL.