Re: [Modeling-users] Double Result

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

I found a possible source for that bug:

   http://www.bitmechanic.com/mail-archives/mysql/Jul1999/1321.html
   http://www.bitmechanic.com/mail-archives/mysql/current/0838.html
   http://www.bitmechanic.com/mail-archives/mysql/current/1005.html

point to release 3.22.26 and 3.23.2 where a similar bug is fixed:

    http://www.mysql.com/doc/en/News-3.22.26.html
    http://www.mysql.com/doc/en/News-3.23.2.html

<< Fixed range optimiser bug: SELECT * FROM table_name WHERE key_part1
   >=3D const AND (key_part2 =3D const OR key_part2 =3D const). The bug was
   that some rows could be duplicated in the result. >>

But both were released in august/sept. 1999, so unless you work w/ an
old server I don't know if this applies to you. Or maybe the bug has
leaked/reappeared to future versions? Note: while roaming around mysql
changes/bugfixes announcements I had the feeling that bug fixes in mysql
are not always clear so it's hard to tell at first sight, see for
example http://www.mysql.com/doc/en/News-3.22.26.html which only says:

<< Fixed range optimiser bug in SELECT * FROM table_name WHERE
   key_part1 >=3D const AND (key_part2 =3D const OR key_part2 =3D const) >>>

even if the msgs from the mysql archives at this time make it clear that
this is the same bug fixed in 3.22.26 as in 3.23.2.

-- S=E9bastien.


Sebastien Bigaret <sbi...@us...> wrote:
>         Hi,
>=20
>   A quick answer on this:
>=20
> Yannick Gingras <yan...@sa...> wrote:
> > Hi, I think that something is broken with the MySQL SQL.  I mean, I thi=
nk
> > that MySQL is broken but I have to use it and I wonder if the framework=
 can
> > help.
>=20
> > The fetch spec :=20
> >=20
> >  ((codelike**) AND (isActive!=3D-255) AND (i18n.titlelike*ABC*))
> >=20
> > gives me the my 2 rows twice :=20
> >=20
> > Evaluating: SELECT t0.activity_id, t0.code, t0.enterprise_id, t0.is_act=
ive=20
> > FROM ABCI18N t1 INNER JOIN ABC t0 ON ( t0.activity_id=3Dt1.activity_id =
)  WHERE=20
> > (t0.code LIKE '%%' AND t0.is_active <> -255 AND t1.title LIKE '%ABC%')
> > rowcount: 4 Returning: {'activityId': 1L, 'code': 'ABC009', 'enterprise=
Id':=20
> > 1L, 'isActive': 1L}
> > rowcount: 4 Returning: {'activityId': 1L, 'code': 'ABC009', 'enterprise=
Id':=20
> > 1L, 'isActive': 1L}
> > rowcount: 4 Returning: {'activityId': 2L, 'code': 'ABC123', 'enterprise=
Id':=20
> > 1L, 'isActive': 1L}
> > rowcount: 4 Returning: {'activityId': 2L, 'code': 'ABC123', 'enterprise=
Id':=20
> > 1L, 'isActive': 1L}
> >=20
> > MySQL returns 4 rows with the same select typed at the prompt.  Is
> > there anything that I can do to prevent the duplication ?
>=20
> Okay. Just to be sure: I assume you really have no duplicates in table
> ABC ;)
>=20
>  As a quick workaround, try typing the same at mysql prompt, but with
> SELECT DISTINCT: does it help?
>=20
> If it does, you can add the following method to
> DatabaseAdaptors.MySQLAdaptorLayer.MySQLSQLExpression:
>=20
> ------------------------------------------------------------------------
>   def assembleSelectStatementWithAttributes(self, attributes, lock, quali=
fier,
>                                             fetchOrder, selectString,
>                                             columnList, tableList, whereC=
lause,
>                                             joinClause, orderByClause,
>                                             lockClause):
>     """
>     """
>     selectString=3DselectString or 'SELECT DISTINCT'
>     super=3DSQLExpression.assembleSelectStatementWithAttributes
>     return super(self, attributes, lock, qualifier, fetchOrder, selectStr=
ing,
>                  columnList, tableList, whereClause, joinClause,=20
>                  orderByClause, lockClause)
> ------------------------------------------------------------------------
>=20
> Which version of mysql are you using? I cannot reproduce it here; for
> example and from the test database 'AUTHOR_BOOKS', the following
> statement produces the correct number of rows (with no duplicates):
>=20
> select t0.id, t0.title, t0.price=20
> from WRITER t1
> INNER JOIN BOOK t0
>   ON (t0.fk_writer_id=3Dt1.id)
> where (t0.title like '%%' AND t0.price is NULL AND
>        t1.last_name like '%%');
>=20
> Mine is: mysql --version
> mysql  Ver 12.20 Distrib 4.0.13, for pc-linux-gnu (i686)
>=20
>=20
>   That's intriguing, indeed, but I've no time to investigate by
>   now. Does anyone know anything about this behaviour?
>=20
> Last: could you try reversing the join (see below) at the mysql prompt
>       and report what it returns?
>=20
>  SELECT t0.activity_id, t0.code, t0.enterprise_id, t0.is_active=20
>  FROM ABC t0 INNER JOIN ABCI18N t1 ON ( t0.activity_id=3Dt1.activity_id )
>  WHERE (t0.code LIKE '%%' AND t0.is_active <> -255 AND t1.title LIKE '%AB=
C%')
>=20
>=20
> -- S=E9bastien.