Re: [Modeling-users] Double Result

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

  To all: what Yannick revealed with this is a bug that impacts ALL
  ------  adaptors: Postgresql, MySQL, SQLite and Oracle.

          Summary: ec.fetch() can return duplicates in its result set.

          At the end of the message there's a patch that fixes the
          problem, until a new release is made.


Yannick Gingras <yan...@sa...> writes:
> > NB: it seems to me that these requests corresponds to/are variant of
> >     your original query returning duplicates, which was:
> >
> >   SELECT t0.activity_id, t0.code, t0.enterprise_id, t0.is_active
> >   FROM ABCI18N t1 INNER JOIN ABC t0 ON ( t0.activity_id=3Dt1.activity_i=
d )
> >   WHERE (t0.code LIKE '%%' AND t0.is_active <> -255 AND t1.title LIKE
> > '%ABC%')
>=20
> What may be different is that I have 2 records in ABCI18N that in fact al=
l=20
> matcch "%ABC%" :=20
> mysql> select * from ABCI18N where title like "%ABC%";
> +----+-------------+---------+-------------------------------------------=
+-----------------------------------+
> | id | activity_id | lang_id | description                               =
|=20
> title                             |
> +----+-------------+---------+-------------------------------------------=
+-----------------------------------+
> |  3 |           2 |       1 | Description de l'activit=C3=A9 num=C3=A9ro=
 ABC123 |=20
> Titre de l'activit=C3=A9 ABC123       |
> |  4 |           2 |       2 | Description of the activity code ABC123   =
|=20
> Title of the activity code ABC123 |
> +----+-------------+---------+-------------------------------------------=
+-----------------------------------+
>=20
> So this may explain why the select match twice here and not on you databa=
se.



        Thanks *a lot* for reporting this and giving the details.
        It reveals a real bug in the framework.


In fact I was not careful enough when writing my test queries. The
following query is the equivalent one, this time:

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

and it returns duplicates as well, for the data inserted when
initializing the test db (no insert needed):

 id | first_name | last_name | age |      birthday       | fk_writer_id=20
----+------------+-----------+-----+---------------------+--------------
  2 | Francois   | Rabelais  | 508 | 1484-07-02 18:16:12 |=20=20=20=20=20=
=20=20=20=20=20=20=20=20
  3 | Frederic   | Dard      |  81 | 1921-06-29 04:56:34 |            2
  3 | Frederic   | Dard      |  81 | 1921-06-29 04:56:34 |            2
  3 | Frederic   | Dard      |  81 | 1921-06-29 04:56:34 |            2

  So J=E9r=F4me was right and what we have here is not a mysql bug at all
--it's the way SQL behaves: all JOIN results in a cartesian product so
we can get duplicates. I don't know how many times I've forgotten that,
but I bet it's not the last one :/   For details, see for example:
http://developer.postgresql.org/docs/postgres/sql-select.html

=3D> the bug is in the SQL generated by the framework itself.  It makes it
   possible to get duplicates in the result set returned by
   EditingContext.fetch().

  You'll find attached a simple patch solving the problem. I won't have
  the time to make a new release this evening, and probably not before
  the week-end. I plan to do it not latter than sunday evening.

        Regards,

-- S=E9bastien.


------------------------------------------------------------------------
--- SQLExpression.py    24 Jul 2003 12:07:25 -0000      1.19
+++ SQLExpression.py    30 Jul 2003 20:36:34 -0000
@@ -491,7 +491,7 @@
                                             lockClause):
     """
     """
-    statement=3DselectString or 'SELECT '
+    statement=3DselectString or 'SELECT DISTINCT '
     statement+=3DcolumnList
     statement+=3D' FROM '+tableList
     if lockClause:
------------------------------------------------------------------------