[DM-10283] Add ability to join different databases. - Jira

XML

Word

Printable

Details

Type: Story
Status: Done
Resolution: Done
Fix Version/s: None
Component/s: None
Labels:
None

Sprint:
DB_S17_5
Team:
Data Access and Database

Description

Joins between two databases are not working. On the PDAC, stripe82 cannot be joined with WISE, despite using identical partitioning methods. This is probably a result of an expectation that qserv would onlt access one database at time.

An example of this would be an attempt to figure out all entries from the ForcedSource table of WISE catalog (database wise_00) which correspond to the rejected objects of the catalog (those objects live in a separate database wise_ext_00):

SELECT COUNT(ForcedSource.source_id_mf)

  FROM

    wise_ext_00.Reject Reject,

    wise_00.ForcedSource ForcedSource

  WHERE

    Reject.source_id LIKE '0000m016_ac51-000002'

  AND ForcedSource.source_id_mf = Reject.source_id;

ERROR 4110 (Proxy): Query processing error: QI=?: Failed to instantiate query: AnalysisError:Query involves

partitioned table joins that Qserv does not know how to evaluate using only partition-local data

The only relevant line in the CZAR's log file is:

[2017-04-21T16:01:43.412-0500] [LWP:346] ERROR ccontrol.UserQueryFactory (core/modules/ccontrol/UserQueryFactory.cc:114)

- Invalid query: AnalysisError:Query involves partitioned table joins that Qserv does not know how to evaluate

using only partition-local data

Attachments

Issue Links

relates to

DM-9372 Load WISE catalog data in PDAC

Done

DM-12910 Summary of WISE catalogs loaded into PDAC in 2017

Done

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

John Gates added a comment - 24/Apr/17 10:32 AM

The query is failing to pass qana::RelationGraph::_validate().

Show

John Gates added a comment - 24/Apr/17 10:32 AM The query is failing to pass qana::RelationGraph::_validate().

Hide

Permalink

John Gates added a comment - 27/Apr/17 1:33 PM - edited

Tried the query on the pdac

[2017-04-27T17:46:55.602-0500] [LWP:346] DEBUG qana.RelationGraph (core/modules/qana/RelationGraph.cc:483) - &&& _addWhereEqEdges first=(,ForcedSource,source_id_mf) second=(,Reject,source_id)

[2017-04-27T17:46:55.602-0500] [LWP:346] DEBUG qana.RelationGraph (core/modules/qana/RelationGraph.cc:248) - &&& addEqEdge a=CTI(TI(wise_00.ForcedSource kind=1) fk=source_id_mf director=(DTI(TI(wise_00.Object kind=0) pk=source_id lon=ra lat=decl partId=18))) b=DTI(TI(wise_ext_00.Reject kind=0) pk=source_id lon=ra lat=decl partId=20)

[2017-04-27T17:46:55.602-0500] [LWP:346] DEBUG qana.TableInfo (core/modules/qana/TableInfo.cc:211) - &&& DirTableInfo::isEqPredAdmissible b res=0 r1=0 r2=1 r3=1

[2017-04-27T17:46:55.602-0500] [LWP:346] DEBUG qana.RelationGraph (core/modules/qana/RelationGraph.cc:256) - &&& addEqEdge false for (source_id_mf,source_id)

[2017-04-27T17:46:55.603-0500] [LWP:346] ERROR ccontrol.UserQueryFactory (core/modules/ccontrol/UserQueryFactory.cc:116) - Invalid query: AnalysisError:Query involves partitioned table joins that Qserv does not know how to evaluate using only partition-local data

What that means is that it is comparing a child table to a director table. In this case, the child's director is not the same as the parent (represented by r1=0) but the director primary key is correct and the child's foreign key is correct.

So either the child's director table is incorrect, or a link needs to be made between the director tables.

Show

John Gates added a comment - 27/Apr/17 1:33 PM - edited Tried the query on the pdac [2017-04-27T17:46:55.602-0500] [LWP:346] DEBUG qana.RelationGraph (core/modules/qana/RelationGraph.cc:483) - &&& _addWhereEqEdges first=(,ForcedSource,source_id_mf) second=(,Reject,source_id) [2017-04-27T17:46:55.602-0500] [LWP:346] DEBUG qana.RelationGraph (core/modules/qana/RelationGraph.cc:248) - &&& addEqEdge a=CTI(TI(wise_00.ForcedSource kind=1) fk=source_id_mf director=(DTI(TI(wise_00.Object kind=0) pk=source_id lon=ra lat=decl partId=18))) b=DTI(TI(wise_ext_00.Reject kind=0) pk=source_id lon=ra lat=decl partId=20) [2017-04-27T17:46:55.602-0500] [LWP:346] DEBUG qana.TableInfo (core/modules/qana/TableInfo.cc:211) - &&& DirTableInfo::isEqPredAdmissible b res=0 r1=0 r2=1 r3=1 [2017-04-27T17:46:55.602-0500] [LWP:346] DEBUG qana.RelationGraph (core/modules/qana/RelationGraph.cc:256) - &&& addEqEdge false for (source_id_mf,source_id) [2017-04-27T17:46:55.603-0500] [LWP:346] ERROR ccontrol.UserQueryFactory (core/modules/ccontrol/UserQueryFactory.cc:116) - Invalid query: AnalysisError:Query involves partitioned table joins that Qserv does not know how to evaluate using only partition-local data What that means is that it is comparing a child table to a director table. In this case, the child's director is not the same as the parent (represented by r1=0) but the director primary key is correct and the child's foreign key is correct. So either the child's director table is incorrect, or a link needs to be made between the director tables.

Hide

Permalink

John Gates added a comment - 27/Apr/17 4:02 PM

I tried a slightly different version of the query that joins accross wise_00.Object. It also fails as it only allows self joins on director tables, and Reject and Object are director tables.

SELECT COUNT(ForcedSource.source_id_mf)

FROM wise_ext_00.Reject Reject, wise_00.ForcedSource ForcedSource, wise_00.Object Object

WHERE Reject.source_id LIKE '0000m016_ac51-000002'

    AND ForcedSource.source_id_mf = Object.source_id

    AND Reject.source_id = Object.source_id;

[2017-04-27T17:52:30.506-0500] [LWP:346] DEBUG qana.RelationGraph (core/modules/qana/RelationGraph.cc:483) - &&& _addWhereEqEdges first=(,ForcedSource,source_id_mf) second=(,Object,source_id)

[2017-04-27T17:52:30.506-0500] [LWP:346] DEBUG qana.RelationGraph (core/modules/qana/RelationGraph.cc:248) - &&& addEqEdge a=CTI(TI(wise_00.ForcedSource kind=1) fk=source_id_mf director=(DTI(TI(wise_00.Object kind=0) pk=source_id lon=ra lat=decl partId=18))) b=DTI(TI(wise_00.Object kind=0) pk=source_id lon=ra lat=decl partId=18)

[2017-04-27T17:52:30.506-0500] [LWP:346] DEBUG qana.TableInfo (core/modules/qana/TableInfo.cc:211) - &&& DirTableInfo::isEqPredAdmissible b res=1 r1=1 r2=1 r3=1

[2017-04-27T17:52:30.506-0500] [LWP:346] DEBUG qana.RelationGraph (core/modules/qana/RelationGraph.cc:251) - &&& addEqEdge true for (source_id_mf,source_id)

[2017-04-27T17:52:30.506-0500] [LWP:346] DEBUG qana.RelationGraph (core/modules/qana/RelationGraph.cc:483) - &&& _addWhereEqEdges first=(,Reject,source_id) second=(,Object,source_id)

[2017-04-27T17:52:30.507-0500] [LWP:346] DEBUG qana.RelationGraph (core/modules/qana/RelationGraph.cc:248) - &&& addEqEdge a=DTI(TI(wise_ext_00.Reject kind=0) pk=source_id lon=ra lat=decl partId=20) b=DTI(TI(wise_00.Object kind=0) pk=source_id lon=ra lat=decl partId=18)

[2017-04-27T17:52:30.507-0500] [LWP:346] DEBUG qana.TableInfo (core/modules/qana/TableInfo.cc:194) - &&& DirTableInfo::isEqPredAdmissible a res=0 r1=0 r2=1 r3=1

[2017-04-27T17:52:30.507-0500] [LWP:346] DEBUG qana.RelationGraph (core/modules/qana/RelationGraph.cc:256) - &&& addEqEdge false for (source_id,source_id)

Show

John Gates added a comment - 27/Apr/17 4:02 PM I tried a slightly different version of the query that joins accross wise_00.Object. It also fails as it only allows self joins on director tables, and Reject and Object are director tables. SELECT COUNT(ForcedSource.source_id_mf) FROM wise_ext_00.Reject Reject, wise_00.ForcedSource ForcedSource, wise_00.Object Object WHERE Reject.source_id LIKE '0000m016_ac51-000002' AND ForcedSource.source_id_mf = Object.source_id AND Reject.source_id = Object.source_id; [2017-04-27T17:52:30.506-0500] [LWP:346] DEBUG qana.RelationGraph (core/modules/qana/RelationGraph.cc:483) - &&& _addWhereEqEdges first=(,ForcedSource,source_id_mf) second=(,Object,source_id) [2017-04-27T17:52:30.506-0500] [LWP:346] DEBUG qana.RelationGraph (core/modules/qana/RelationGraph.cc:248) - &&& addEqEdge a=CTI(TI(wise_00.ForcedSource kind=1) fk=source_id_mf director=(DTI(TI(wise_00.Object kind=0) pk=source_id lon=ra lat=decl partId=18))) b=DTI(TI(wise_00.Object kind=0) pk=source_id lon=ra lat=decl partId=18) [2017-04-27T17:52:30.506-0500] [LWP:346] DEBUG qana.TableInfo (core/modules/qana/TableInfo.cc:211) - &&& DirTableInfo::isEqPredAdmissible b res=1 r1=1 r2=1 r3=1 [2017-04-27T17:52:30.506-0500] [LWP:346] DEBUG qana.RelationGraph (core/modules/qana/RelationGraph.cc:251) - &&& addEqEdge true for (source_id_mf,source_id) [2017-04-27T17:52:30.506-0500] [LWP:346] DEBUG qana.RelationGraph (core/modules/qana/RelationGraph.cc:483) - &&& _addWhereEqEdges first=(,Reject,source_id) second=(,Object,source_id) [2017-04-27T17:52:30.507-0500] [LWP:346] DEBUG qana.RelationGraph (core/modules/qana/RelationGraph.cc:248) - &&& addEqEdge a=DTI(TI(wise_ext_00.Reject kind=0) pk=source_id lon=ra lat=decl partId=20) b=DTI(TI(wise_00.Object kind=0) pk=source_id lon=ra lat=decl partId=18) [2017-04-27T17:52:30.507-0500] [LWP:346] DEBUG qana.TableInfo (core/modules/qana/TableInfo.cc:194) - &&& DirTableInfo::isEqPredAdmissible a res=0 r1=0 r2=1 r3=1 [2017-04-27T17:52:30.507-0500] [LWP:346] DEBUG qana.RelationGraph (core/modules/qana/RelationGraph.cc:256) - &&& addEqEdge false for (source_id,source_id)

Hide

Permalink

John Gates added a comment - 02/May/17 12:02 PM

The query that triggered this issue isn't valid. The foreign keys in ForcedSource aren't being constrained properly to have matches in wise_00.Object. If they were, a match table could be made. Since, nothing in Reject.Object will match with anything in wise_00.Object (they are mutually exclusive), those entries in wise_00.ForcedSource that are for Reject should be removed and put in wise_ext_00.ForcedSource.

There is still case for joining across databases, the joins need to have a spacial relation as the data is sharded spacially.

This will require the parser and qana to be able to identify scisql_angSep() between two different director tables.

Show

John Gates added a comment - 02/May/17 12:02 PM The query that triggered this issue isn't valid. The foreign keys in ForcedSource aren't being constrained properly to have matches in wise_00.Object. If they were, a match table could be made. Since, nothing in Reject.Object will match with anything in wise_00.Object (they are mutually exclusive), those entries in wise_00.ForcedSource that are for Reject should be removed and put in wise_ext_00.ForcedSource. There is still case for joining across databases, the joins need to have a spacial relation as the data is sharded spacially. This will require the parser and qana to be able to identify scisql_angSep() between two different director tables.

Hide

Permalink

John Gates added a comment - 04/May/17 4:05 PM - edited

The following fragment (and all similar fragments) generates an error where "Subchunks_LSST20_6634.ObjectFullOverlap_6634_765" does not exist. The table isn't being made the message from the czar does not include information for making the table. This information needs to be added to the message on the czar and the worker needs code to make the temporary tables from the extra information. see qproc::TaskMsgFactory::_makeMsg on the czar and wdb::ChunkResourceRequest::getResourceFragment on the worker.

Some care should be given to make sure this provides the correct answer.

SELECT count(*) AS QS1_COUNT FROM Subchunks_LSST_6634.Object_6634_765 AS o1,Subchunks_LSST20_6634.ObjectFullOverlap_6634_765 AS o2 WHERE scisql_s2PtInBox(o1.ra,o1.decl,9.299197,-6.468216,9.762526,-5.412851)=1 AND scisql_s2PtInBox(o2.ra,o2.decl,9.299197,-6.468216,9.762526,-5.412851)=1 AND scisql_angSep(o1.ra,o1.decl,o2.ra,o2.decl)<0.015

Show

John Gates added a comment - 04/May/17 4:05 PM - edited The following fragment (and all similar fragments) generates an error where "Subchunks_LSST20_6634.ObjectFullOverlap_6634_765" does not exist. The table isn't being made the message from the czar does not include information for making the table. This information needs to be added to the message on the czar and the worker needs code to make the temporary tables from the extra information. see qproc::TaskMsgFactory::_makeMsg on the czar and wdb::ChunkResourceRequest::getResourceFragment on the worker. Some care should be given to make sure this provides the correct answer. SELECT count(*) AS QS1_COUNT FROM Subchunks_LSST_6634.Object_6634_765 AS o1,Subchunks_LSST20_6634.ObjectFullOverlap_6634_765 AS o2 WHERE scisql_s2PtInBox(o1.ra,o1.decl,9.299197,-6.468216,9.762526,-5.412851)=1 AND scisql_s2PtInBox(o2.ra,o2.decl,9.299197,-6.468216,9.762526,-5.412851)=1 AND scisql_angSep(o1.ra,o1.decl,o2.ra,o2.decl)<0.015

Hide

Permalink

John Gates added a comment - 10/May/17 11:48 AM

MariaDB [LSST]> select o1.deepSourceId as Id1, o2.deepSourceId as Id2, o1.ra as RA1, o2.ra as RA2, o1.decl as Dec1, o2.decl as Dec2, POWER((o1.ra - o2.ra), 2) + POWER((o1.decl - o2.decl), 2) as distSq  from Object o1, LSST20.Object o2  where qserv_areaspec_box(9.755, -6.468216, 9.762526, -6.412851) and scisql_angSep(o1.ra, o1.decl, o2.ra, o2.decl) < 0.001 GROUP BY Id1;

+------------------+------------------+-------------------+-------------------+---------------------+---------------------+-------------------------------+

| Id1              | Id2              | RA1               | RA2               | Dec1                | Dec2                | distSq                        |

+------------------+------------------+-------------------+-------------------+---------------------+---------------------+-------------------------------+

| 2269091352019821 | 9076375737074808 | 9.758343652925024 | 9.758343652925024 |  -6.414147396612178 |  -6.414147396612178 |                             0 |

| 2269091352019822 | 9076375737074806 | 9.761260170917357 | 9.761260170917357 |  -6.413964159342002 |  -6.413964159342002 |                             0 |

| 2269091352019824 | 9076375737074756 | 9.759579570169107 | 9.759579570169107 |  -6.414707886704982 |  -6.414707886704982 |                             0 |

| 2269091352022946 | 9076375737074808 | 9.758338261730625 | 9.758343652925024 |   -6.41414986424196 |  -6.414147396612178 |  0.00000000003515417379402991 |

| 2269091352022947 | 9076375737074806 | 9.761334515340284 | 9.761260170917357 | -6.4139588270687415 |  -6.413964159342002 |    0.000000005555526358500184 |

| 2269091352022960 | 9076375737074756 |  9.75957268218951 | 9.759579570169107 |  -6.414708858653203 |  -6.414707886704982 | 0.000000000048388946264870295 |

| 2269091352023015 | 9076375737075420 | 9.761424222272204 | 9.761424222272204 |  -6.418296204952816 |  -6.418296204952816 |                             0 |

| 2269091352023018 | 9076375737075443 | 9.757939576915705 | 9.757939576915705 |  -6.418293826842805 |  -6.418293826842805 |                             0 |

| 2269091352023062 | 9076375737075438 | 9.757681515347498 | 9.757681515347498 |  -6.421112648821524 |  -6.421112648821524 |                             0 |

| 2269091352023069 | 9076375737075434 | 9.756493823203469 | 9.756493823203469 |  -6.421473281422651 |  -6.421473281422651 |                             0 |

| 2269091352023134 | 9076375737075638 | 9.762226243763228 | 9.762226243763228 |  -6.425578541031368 |  -6.425578541031368 |                             0 |

| 2269091352023138 | 9076375737074354 | 9.760651408565437 | 9.760472483451856 |  -6.426049678390307 |  -6.425200472612312 |      0.0000007531646496501782 |

| 2269091352023156 | 9076375737074935 | 9.756524918189491 | 9.756524918189491 |  -6.427136471014436 |  -6.427136471014436 |                             0 |

| 2269091352023203 | 9076375737075666 | 9.760579596045822 | 9.760579596045822 |  -6.430218016721356 |  -6.430218016721356 |                             0 |

| 2269091352023262 | 9076375737074944 | 9.760343019717345 | 9.760343019717345 |  -6.434304289114439 |  -6.434304289114439 |                             0 |

| 2269091352023267 | 9076375737074944 | 9.761125611726566 | 9.760343019717345 |  -6.434474837003689 |  -6.434304289114439 |      0.0000006415368354246672 |

| 2269099941954111 | 9076405801845881 | 9.761959324589162 | 9.761959324589162 |  -6.437157113695933 |  -6.437157113695933 |                             0 |

| 2269099941954112 | 9076405801845879 | 9.761447758214926 | 9.761447758214926 |  -6.450093999345987 |  -6.450093999345987 |                             0 |

| 2269099941954113 | 9076405801845878 | 9.761149019449281 | 9.761149019449281 |  -6.465115236098208 |  -6.465115236098208 |                             0 |

| 2269099941954114 | 9076405801845877 | 9.760221776334772 | 9.760221776334772 |   -6.44963201233946 |   -6.44963201233946 |                             0 |

| 2269099941954116 | 9076405801845873 | 9.759944977827725 | 9.759944977827725 |  -6.453641953170964 |  -6.453641953170964 |                             0 |

| 2269099941954117 | 9076405801845923 | 9.759310889109647 | 9.759033662593415 |  -6.443894521862392 | -6.4430454016115855 |      0.0000007978597416315295 |

| 2269099941954118 | 9076405801845923 | 9.759033662593415 | 9.759033662593415 | -6.4430454016115855 | -6.4430454016115855 |                             0 |

| 2269099941954119 | 9076405801845868 |  9.75888638074658 |  9.75888638074658 |  -6.446598152694162 |  -6.446598152694162 |                             0 |

| 2269099941954120 | 9076405801845874 | 9.756793124244272 | 9.756793124244272 |  -6.439186137213939 |  -6.439186137213939 |                             0 |

| 2269099941954121 | 9076405801845899 | 9.756898810100177 | 9.756898810100177 |  -6.451573197001368 |  -6.451573197001368 |                             0 |

| 2269099941954122 | 9076405801845897 | 9.756335315120118 | 9.756335315120118 |  -6.446350979333114 |  -6.446350979333114 |                             0 |

| 2269099941954123 | 9076405801845895 | 9.755147704778684 | 9.755147704778684 |  -6.443765086471689 |  -6.443765086471689 |                             0 |

| 2269099941954124 | 9076405801845894 |  9.75526394199197 |  9.75526394199197 |  -6.462003630066952 |  -6.462003630066952 |                             0 |

+------------------+------------------+-------------------+-------------------+---------------------+---------------------+-------------------------------+

29 rows in set (0.40 sec)

Show

John Gates added a comment - 10/May/17 11:48 AM MariaDB [LSST]> select o1.deepSourceId as Id1, o2.deepSourceId as Id2, o1.ra as RA1, o2.ra as RA2, o1.decl as Dec1, o2.decl as Dec2, POWER((o1.ra - o2.ra), 2) + POWER((o1.decl - o2.decl), 2) as distSq from Object o1, LSST20.Object o2 where qserv_areaspec_box(9.755, -6.468216, 9.762526, -6.412851) and scisql_angSep(o1.ra, o1.decl, o2.ra, o2.decl) < 0.001 GROUP BY Id1; +------------------+------------------+-------------------+-------------------+---------------------+---------------------+-------------------------------+ | Id1 | Id2 | RA1 | RA2 | Dec1 | Dec2 | distSq | +------------------+------------------+-------------------+-------------------+---------------------+---------------------+-------------------------------+ | 2269091352019821 | 9076375737074808 | 9.758343652925024 | 9.758343652925024 | -6.414147396612178 | -6.414147396612178 | 0 | | 2269091352019822 | 9076375737074806 | 9.761260170917357 | 9.761260170917357 | -6.413964159342002 | -6.413964159342002 | 0 | | 2269091352019824 | 9076375737074756 | 9.759579570169107 | 9.759579570169107 | -6.414707886704982 | -6.414707886704982 | 0 | | 2269091352022946 | 9076375737074808 | 9.758338261730625 | 9.758343652925024 | -6.41414986424196 | -6.414147396612178 | 0.00000000003515417379402991 | | 2269091352022947 | 9076375737074806 | 9.761334515340284 | 9.761260170917357 | -6.4139588270687415 | -6.413964159342002 | 0.000000005555526358500184 | | 2269091352022960 | 9076375737074756 | 9.75957268218951 | 9.759579570169107 | -6.414708858653203 | -6.414707886704982 | 0.000000000048388946264870295 | | 2269091352023015 | 9076375737075420 | 9.761424222272204 | 9.761424222272204 | -6.418296204952816 | -6.418296204952816 | 0 | | 2269091352023018 | 9076375737075443 | 9.757939576915705 | 9.757939576915705 | -6.418293826842805 | -6.418293826842805 | 0 | | 2269091352023062 | 9076375737075438 | 9.757681515347498 | 9.757681515347498 | -6.421112648821524 | -6.421112648821524 | 0 | | 2269091352023069 | 9076375737075434 | 9.756493823203469 | 9.756493823203469 | -6.421473281422651 | -6.421473281422651 | 0 | | 2269091352023134 | 9076375737075638 | 9.762226243763228 | 9.762226243763228 | -6.425578541031368 | -6.425578541031368 | 0 | | 2269091352023138 | 9076375737074354 | 9.760651408565437 | 9.760472483451856 | -6.426049678390307 | -6.425200472612312 | 0.0000007531646496501782 | | 2269091352023156 | 9076375737074935 | 9.756524918189491 | 9.756524918189491 | -6.427136471014436 | -6.427136471014436 | 0 | | 2269091352023203 | 9076375737075666 | 9.760579596045822 | 9.760579596045822 | -6.430218016721356 | -6.430218016721356 | 0 | | 2269091352023262 | 9076375737074944 | 9.760343019717345 | 9.760343019717345 | -6.434304289114439 | -6.434304289114439 | 0 | | 2269091352023267 | 9076375737074944 | 9.761125611726566 | 9.760343019717345 | -6.434474837003689 | -6.434304289114439 | 0.0000006415368354246672 | | 2269099941954111 | 9076405801845881 | 9.761959324589162 | 9.761959324589162 | -6.437157113695933 | -6.437157113695933 | 0 | | 2269099941954112 | 9076405801845879 | 9.761447758214926 | 9.761447758214926 | -6.450093999345987 | -6.450093999345987 | 0 | | 2269099941954113 | 9076405801845878 | 9.761149019449281 | 9.761149019449281 | -6.465115236098208 | -6.465115236098208 | 0 | | 2269099941954114 | 9076405801845877 | 9.760221776334772 | 9.760221776334772 | -6.44963201233946 | -6.44963201233946 | 0 | | 2269099941954116 | 9076405801845873 | 9.759944977827725 | 9.759944977827725 | -6.453641953170964 | -6.453641953170964 | 0 | | 2269099941954117 | 9076405801845923 | 9.759310889109647 | 9.759033662593415 | -6.443894521862392 | -6.4430454016115855 | 0.0000007978597416315295 | | 2269099941954118 | 9076405801845923 | 9.759033662593415 | 9.759033662593415 | -6.4430454016115855 | -6.4430454016115855 | 0 | | 2269099941954119 | 9076405801845868 | 9.75888638074658 | 9.75888638074658 | -6.446598152694162 | -6.446598152694162 | 0 | | 2269099941954120 | 9076405801845874 | 9.756793124244272 | 9.756793124244272 | -6.439186137213939 | -6.439186137213939 | 0 | | 2269099941954121 | 9076405801845899 | 9.756898810100177 | 9.756898810100177 | -6.451573197001368 | -6.451573197001368 | 0 | | 2269099941954122 | 9076405801845897 | 9.756335315120118 | 9.756335315120118 | -6.446350979333114 | -6.446350979333114 | 0 | | 2269099941954123 | 9076405801845895 | 9.755147704778684 | 9.755147704778684 | -6.443765086471689 | -6.443765086471689 | 0 | | 2269099941954124 | 9076405801845894 | 9.75526394199197 | 9.75526394199197 | -6.462003630066952 | -6.462003630066952 | 0 | +------------------+------------------+-------------------+-------------------+---------------------+---------------------+-------------------------------+ 29 rows in set (0.40 sec)

Hide

Permalink

John Gates added a comment - 10/May/17 3:51 PM

The above shows a successful query that joins some elements of the Object tables from the LSST and LSST20 databases on the upper half of the in2p3 cluster. It doesn't prove that the ObjectFullOverlap table is being used. To do that, I gather some statistics about a specific chunk's Object and ObjectFullOverlap tables, basically min and max RA and Dec. I used chunk 9630 because I knew the node where it is found, ccqserv143.

MariaDB [LSST20]> select MIN(ra), MIN(decl), MAX(ra), MAX(decl) from LSST.Object_9630;

+-------------------+-------------------+------------------+-------------------+

| MIN(ra)           | MIN(decl)         | MAX(ra)          | MAX(decl)         |

+-------------------+-------------------+------------------+-------------------+

| 271.2328939250148 | 28.58823593332637 | 273.698629816619 | 30.70587788324296 |

+-------------------+-------------------+------------------+-------------------+

1 row in set (1.03 sec)

MariaDB [LSST20]> select MIN(ra), MIN(decl), MAX(ra), MAX(decl) from Object_9630;

+--------------------+-------------------+------------------+-------------------+

| MIN(ra)            | MIN(decl)         | MAX(ra)          | MAX(decl)         |

+--------------------+-------------------+------------------+-------------------+

| 271.23287799367233 | 28.58823593332637 | 273.698629816619 | 30.70587788324296 |

+--------------------+-------------------+------------------+-------------------+

1 row in set (1.29 sec)

MariaDB [LSST20]> select MIN(ra), MIN(decl), MAX(ra), MAX(decl) from ObjectFullOverlap_9630;

+-------------------+--------------------+-------------------+--------------------+

| MIN(ra)           | MIN(decl)          | MAX(ra)           | MAX(decl)          |

+-------------------+--------------------+-------------------+--------------------+

| 271.2135250525471 | 28.571568701395268 | 273.7180105379097 | 30.722546655347717 |

+-------------------+--------------------+-------------------+--------------------+

1 row in set (0.15 sec)

I then constructed the following query. It allows answer to come from one corner of chunk 9630 with a scisql_angSep large enough to include the ObjectFullOverlap table. The RA and Dec of the LSST values must be within chunk 9630 and the values from LSST20 must be outside chunk 9630, indicating that it joined using the ObjectFullOverlap table. qserv_areaspec_box appears to remove any result that is located outside the box, so it must include the overlap table.

SELECT o1.deepSourceId AS Id1, o2.deepSourceId AS Id2,

               o1.ra AS RA1, o2.ra AS RA2,

               o1.decl AS Dec1, o2.decl AS Dec2,

               POWER((o1.ra - o2.ra), 2) + POWER((o1.decl - o2.decl), 2) AS distSq

FROM LSST.Object o1, LSST20.Object o2

WHERE qserv_areaspec_box(273.6, 30.7, 273.7180105379097, 30.722546655347717)

     AND scisql_angSep(o1.ra, o1.decl, o2.ra, o2.decl) < 0.015

     AND o1.ra < 273.698629816619 AND o1.decl < 30.70587788324296

     AND o2.ra > 273.698629816619 AND o2.decl > 30.70587788324296

ORDER BY RA2;

The results indicate that the ObjectFullOverlap table was used.

| 3560897255572001 | 14243591590510780 |   273.697084265946 |  273.7126696357443 | 30.704851480872293 |  30.70664517500156 |  0.00024612109037963886 |

| 3560897255572013 | 14243591590510780 | 273.69590208497834 |  273.7126696357443 | 30.703573971921628 |  30.70664517500156 |  0.00029058304704731875 |

| 3560897255572001 | 14243591590510807 |   273.697084265946 | 273.71289687064206 | 30.704851480872293 | 30.708879919693462 |  0.00026626678660995586 |

| 3560897255572030 | 14243591590510807 | 273.69802002807006 | 273.71289687064206 | 30.702745251269743 | 30.708879919693462 |  0.00025895460158086096 |

+------------------+-------------------+--------------------+--------------------+--------------------+--------------------+-------------------------+

132 rows in set (0.43 sec)

Show

John Gates added a comment - 10/May/17 3:51 PM The above shows a successful query that joins some elements of the Object tables from the LSST and LSST20 databases on the upper half of the in2p3 cluster. It doesn't prove that the ObjectFullOverlap table is being used. To do that, I gather some statistics about a specific chunk's Object and ObjectFullOverlap tables, basically min and max RA and Dec. I used chunk 9630 because I knew the node where it is found, ccqserv143. MariaDB [LSST20]> select MIN(ra), MIN(decl), MAX(ra), MAX(decl) from LSST.Object_9630; +-------------------+-------------------+------------------+-------------------+ | MIN(ra) | MIN(decl) | MAX(ra) | MAX(decl) | +-------------------+-------------------+------------------+-------------------+ | 271.2328939250148 | 28.58823593332637 | 273.698629816619 | 30.70587788324296 | +-------------------+-------------------+------------------+-------------------+ 1 row in set (1.03 sec) MariaDB [LSST20]> select MIN(ra), MIN(decl), MAX(ra), MAX(decl) from Object_9630; +--------------------+-------------------+------------------+-------------------+ | MIN(ra) | MIN(decl) | MAX(ra) | MAX(decl) | +--------------------+-------------------+------------------+-------------------+ | 271.23287799367233 | 28.58823593332637 | 273.698629816619 | 30.70587788324296 | +--------------------+-------------------+------------------+-------------------+ 1 row in set (1.29 sec) MariaDB [LSST20]> select MIN(ra), MIN(decl), MAX(ra), MAX(decl) from ObjectFullOverlap_9630; +-------------------+--------------------+-------------------+--------------------+ | MIN(ra) | MIN(decl) | MAX(ra) | MAX(decl) | +-------------------+--------------------+-------------------+--------------------+ | 271.2135250525471 | 28.571568701395268 | 273.7180105379097 | 30.722546655347717 | +-------------------+--------------------+-------------------+--------------------+ 1 row in set (0.15 sec) I then constructed the following query. It allows answer to come from one corner of chunk 9630 with a scisql_angSep large enough to include the ObjectFullOverlap table. The RA and Dec of the LSST values must be within chunk 9630 and the values from LSST20 must be outside chunk 9630, indicating that it joined using the ObjectFullOverlap table. qserv_areaspec_box appears to remove any result that is located outside the box, so it must include the overlap table. SELECT o1.deepSourceId AS Id1, o2.deepSourceId AS Id2, o1.ra AS RA1, o2.ra AS RA2, o1.decl AS Dec1, o2.decl AS Dec2, POWER((o1.ra - o2.ra), 2) + POWER((o1.decl - o2.decl), 2) AS distSq FROM LSST.Object o1, LSST20.Object o2 WHERE qserv_areaspec_box(273.6, 30.7, 273.7180105379097, 30.722546655347717) AND scisql_angSep(o1.ra, o1.decl, o2.ra, o2.decl) < 0.015 AND o1.ra < 273.698629816619 AND o1.decl < 30.70587788324296 AND o2.ra > 273.698629816619 AND o2.decl > 30.70587788324296 ORDER BY RA2; The results indicate that the ObjectFullOverlap table was used. | 3560897255572001 | 14243591590510780 | 273.697084265946 | 273.7126696357443 | 30.704851480872293 | 30.70664517500156 | 0.00024612109037963886 | | 3560897255572013 | 14243591590510780 | 273.69590208497834 | 273.7126696357443 | 30.703573971921628 | 30.70664517500156 | 0.00029058304704731875 | | 3560897255572001 | 14243591590510807 | 273.697084265946 | 273.71289687064206 | 30.704851480872293 | 30.708879919693462 | 0.00026626678660995586 | | 3560897255572030 | 14243591590510807 | 273.69802002807006 | 273.71289687064206 | 30.702745251269743 | 30.708879919693462 | 0.00025895460158086096 | +------------------+-------------------+--------------------+--------------------+--------------------+--------------------+-------------------------+ 132 rows in set (0.43 sec)

Hide

Permalink

John Gates added a comment - 11/May/17 1:41 PM

For a test, I tried the above query without the qserv_areaspec_box condition, and it returned no rows (I expected at least 132 rows). I then tried a slightly larger box, and it too returned no rows. This is the query.

SELECT o1.deepSourceId AS Id1, o2.deepSourceId AS Id2, o1.ra AS RA1, o2.ra AS RA2, o1.decl AS Dec1, o2.decl AS Dec2,

    POWER((o1.ra - o2.ra), 2) + POWER((o1.decl - o2.decl), 2) AS distSq

FROM LSST.Object o1, LSST20.Object o2

WHERE qserv_areaspec_box(250.6, 24.7, 273.7180105379097, 30.722546655347717)

    AND scisql_angSep(o1.ra, o1.decl, o2.ra, o2.decl) < 0.015 AND o1.ra < 273.698629816619 AND o1.decl < 30.70587788324296

    AND o2.ra > 273.698629816619 AND o2.decl > 30.70587788324296  ORDER BY Id1, Id2;

Looking at the log, the successful query used subchunk 770 and the unsuccessful query used several chunks, but did not use 770.

success

getResourceFragment b db=LSST:9630 dbTableSet[LSST.Object, LSST20.Object] subChunks=[770]

vs.

failure

getResourceFragment b db=LSST:9630 dbTableSet[LSST.Object, LSST20.Object] subChunks=[46368, 46369, 46370, 46371, 46372, 46373, 46374, 46375, 46376, 46377, 46378, 46379, 46437, 46438, 46439, 46440, 46441, 46442, 46443, 46444]

Show

John Gates added a comment - 11/May/17 1:41 PM For a test, I tried the above query without the qserv_areaspec_box condition, and it returned no rows (I expected at least 132 rows). I then tried a slightly larger box, and it too returned no rows. This is the query. SELECT o1.deepSourceId AS Id1, o2.deepSourceId AS Id2, o1.ra AS RA1, o2.ra AS RA2, o1.decl AS Dec1, o2.decl AS Dec2, POWER((o1.ra - o2.ra), 2) + POWER((o1.decl - o2.decl), 2) AS distSq FROM LSST.Object o1, LSST20.Object o2 WHERE qserv_areaspec_box(250.6, 24.7, 273.7180105379097, 30.722546655347717) AND scisql_angSep(o1.ra, o1.decl, o2.ra, o2.decl) < 0.015 AND o1.ra < 273.698629816619 AND o1.decl < 30.70587788324296 AND o2.ra > 273.698629816619 AND o2.decl > 30.70587788324296 ORDER BY Id1, Id2; Looking at the log, the successful query used subchunk 770 and the unsuccessful query used several chunks, but did not use 770. success getResourceFragment b db=LSST:9630 dbTableSet[LSST.Object, LSST20.Object] subChunks=[770] vs. failure getResourceFragment b db=LSST:9630 dbTableSet[LSST.Object, LSST20.Object] subChunks=[46368, 46369, 46370, 46371, 46372, 46373, 46374, 46375, 46376, 46377, 46378, 46379, 46437, 46438, 46439, 46440, 46441, 46442, 46443, 46444]

Hide

Permalink

John Gates added a comment - 12/May/17 11:10 AM

Running with a significantly older docker image (tickets_~~DM-9321~~), the problem exists. A query above (on a single database) on a very small region works correctly, while a larger region returns an empty set.

Show

John Gates added a comment - 12/May/17 11:10 AM Running with a significantly older docker image (tickets_ DM-9321 ), the problem exists. A query above (on a single database) on a very small region works correctly, while a larger region returns an empty set.

Hide

Permalink

John Gates added a comment - 17/May/17 9:51 AM

The primary problem was that temporary tables were not being made for the tables of all databases. The czar now sends database name with each table name and the worker constructs the temporary tables, so the query works. I did find a problem that isn't new and created ~~DM-10535~~ to look into it.

Show

John Gates added a comment - 17/May/17 9:51 AM The primary problem was that temporary tables were not being made for the tables of all databases. The czar now sends database name with each table name and the worker constructs the temporary tables, so the query works. I did find a problem that isn't new and created DM-10535 to look into it.

People

Assignee:: John Gates

Reporter:: John Gates

Reviewers:: Andy Salnikov, Igor Gaponenko

Watchers:: Andy Salnikov, Igor Gaponenko, John Gates, Serge Monkewitz

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 19/Apr/17 2:16 PM

Updated:: 21/Jun/18 9:34 AM

Resolved:: 18/May/17 11:57 AM

Jenkins

No builds found.

Data Management

Details

Description

Attachments

Issue Links

Activity

People

Dates

Jenkins