Promoting database view fails to produce desired result

User 8a7878ec6d

22-06-2015 19:08:06


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:



    DEFINER = `evehom`@`%` 


VIEW `MTH1_Summary` AS


        `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`


        (`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?



ChemAxon 26d92e5dcd

25-06-2015 14:02:58

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


User 8a7878ec6d

25-06-2015 15:06:30

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!



ChemAxon 26d92e5dcd

25-06-2015 15:14:02

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