Re: [Modeling-users] Double Result

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

Jer...@fi... wrote:
[About SELECT DISTINCT]
> I'm thinking that you really need to check this carefully since sometimes
> 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

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).

  Deletes, inserts or updates are not affected by this bug, because the
  framework never joins tables for these operations.

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.


Now back on fetchCount(): I've found these two forms:

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 '%');

Form #2
-------
SELECT  count(*) from (
SELECT DISTINCT t0.ID,t0.FIRST_NAME,t0.LAST_NAME,t0.AGE,t0.BIRTHDAY,t0.FK_W=
RITER_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 '%'))

And here is how db-server accepts them:

   |Postgres| MySQL | SQLite | O9i | O8i |
---+--------+-------+--------+-----+-----|
#1 |   Y    |   Y   |   N    |  Y  |  Y  |
---+--------+-------+--------+-----+-----|
#2 |   Y[1] |   N   |   Y    |  Y  |  Y  |
---+--------+-------+--------+-----+-----+

[1] for postgresql, 'as foo' should be appended at the end of the SQL
    statement

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.

  I think I'll probably make #2 the default in SQLExpression, and use #1
  for MySQL.

-- S=E9bastien.