Re: [Modeling-users] Double Result

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

Jer...@fi... wrote:
> Hum i'm quite sure that it isn't a MySQL bug .. but a SQL one .=20
> This kind of 'too much result' usually occur while using a wrong
> 'LEFT OUTER JOIN' or 'GROUP' directive . Pay caution that=20
>=20
> select * from t1 left outer join t2 on t1.ID =3D=3D t2.FK_t1 is different=
=20
> from=20
> select * from t2 left outer join t1 on t1.ID =3D=3D t2.FK_t1.=20
> Even thought you will get the same kind of result.=20

Yes, I'm no sql guru, but I know such things can happen and that's why
I'd like to make sure. If I could reproduce it on my msql 4.0 I'll
conclude the same --but maybe I'm not trying the right thing.


    Yannick, could you please perform the following:

  - create, if it's not already done, the AUTHOR_BOOKS test database
    e.g.: [mysqladmin -h localhost -u root create "AUTHORBOOKS"]
=20=20
  - change the current directory to Modeling/tests and run python
    ./test_EditingContext_Global.py -d MySQL -r (you'll get errors for
    DROP TABLE --this is okay)
=20=20
  - at the mysql prompt for db AUTHOR_BOOKS copy-paste the following:

INSERT INTO PK_SEQ_WRITER VALUES (18);
INSERT INTO PK_SEQ_BOOK VALUES (20);
INSERT INTO BOOK VALUES (NULL,'t3',20,18);
INSERT INTO BOOK VALUES (NULL,'t2',19,18);
INSERT INTO BOOK VALUES (NULL,'t1',18,18);
INSERT INTO WRITER VALUES ('','w test1',NULL,0,NULL,18);

  Now, try these three select statements:

select t0.id, t0.title, t0.price
from WRITER t1
INNER JOIN BOOK t0 ON (t0.fk_writer_id=3Dt1.id)
where (t0.title like '%' AND t0.price <> 0 AND t1.last_name like '%');

  and:

select t0.id, t0.title, t0.price=20
from BOOK t0=20
INNER JOIN WRITER t1 ON (t0.fk_writer_id=3Dt1.id)
where (t0.title like '%' AND t0.id <> 0 AND t1.last_name like '%');

  and:

select t0.id, t0.title, t0.price=20
from BOOK t0=20
INNER JOIN WRITER t1 ON (t1.id=3Dt0.fk_writer_id)
where (t0.title like '%' AND t0.price <> 0 AND t1.last_name like '%');


  Do you see any duplicates??


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=20
  FROM ABCI18N t1 INNER JOIN ABC t0 ON ( t0.activity_id=3Dt1.activity_id )
  WHERE (t0.code LIKE '%%' AND t0.is_active <> -255 AND t1.title LIKE '%ABC=
%')


-- S=E9bastien.