Promoting database view fails to produce desired result

Hi,

In mySQL I have defined the following view, basically pulling together data from different batches of the same compound/structure, in order to be able to summarize the data automatically:

CREATE 

    ALGORITHM = UNDEFINED 

    DEFINER = `evehom`@`%` 

    SQL SECURITY DEFINER

VIEW `MTH1_Summary` AS

    SELECT 

        `B`.`Structure_ID` AS `Structure_ID`,

        `B`.`Compound_ID` AS `Compound_ID`,

        `B`.`Batch_ID` AS `Batch_ID`,

        `E`.`Target_ID` AS `Target_ID`,

        `E`.`IC50` AS `IC50`

    FROM

        (`BATCHES` `B`

        LEFT JOIN `ENZYME_DOSE_RESPONSE` `E` ON ((`E`.`Batch_ID` = `B`.`Batch_ID`)))

 

In mySQL Workbench this gives the desired result:

Structure_ID	Compound_ID	Batch_ID	Target_ID	IC50
TH000588	TH000588	H05001	NULL	NULL
TH000588	TH000588C	BK039101C	hMTH1	2.93E-09
TH000588	TH000588C	BK039001C	NULL	NULL
TH000588	TH000588C	BI036401	NULL	NULL
TH000588	TH000588C	J11101C	NULL	NULL
TH000588	TH000588C	BK230006	NULL	NULL
TH000588	TH000588T	F19502T	NULL	NULL

In IJC 15.5.4 the View shows the following mySQL code:

select `B`.`Structure_ID` AS `Structure_ID`,`B`.`Compound_ID` AS `Compound_ID`,`B`.`Batch_ID` AS `Batch_ID`,`E`.`Target_ID` AS `Target_ID`,`E`.`IC50` AS `IC50` from (`prototype`.`BATCHES` `B` left join `prototype`.`ENZYME_DOSE_RESPONSE` `E` on((`E`.`Batch_ID` = `B`.`Batch_ID`)))

This gives the following Grid View:

Structure_ID	Compound ID	Batch ID	Target ID	IC50
TH000588	TH000588T	F19502T
TH000588	TH000588T	F19502T
TH000588	TH000588T	F19502T
TH000588	TH000588T	F19502T
TH000588	TH000588T	F19502T
TH000588	TH000588T	F19502T
TH000588	TH000588T	F19502T

Can you see what is wrong, given that this works correctly on the mySQL server side?

Thanks,

Evert

Dear Evert,

I tried to recreate the issue and I think there is a bug. I will investigate further with our developers. I would like to ask you however, when you promoted the view to entity, what column in your example was used as the ID column? The Batches_ID?

Thank you for pointing this out.

Wishing all the best

David

Hi David,

Maybe it is not a bug: if I use the Batch_ID column as ID column when promoting the view I get the desired result, identical to the table as shown in mySQL Workbench.

A basic mistake on my part...still learning every day...

Thanks for helping out anyway, since this is a major step forward!

Best,

Evert

Dear Evert,

I am glad that it is resolved for you, but still during my testing, I did not like the behaviour of displaying of the null values there. Thank you for pointing me to that direction. Maybe a bug, or my poor prediction of left join behaviour.

I consider then this call as resolved for you and I will carry on inspecting the behaviour.

Wishing all the best

David