Uploaded image for project: 'Data Management'
  1. Data Management
  2. DM-10283

Add ability to join different databases.

    XMLWordPrintable

    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

            Activity

            Hide
            jgates John Gates added a comment -

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

            Show
            jgates John Gates added a comment - The query is failing to pass qana::RelationGraph::_validate().
            Hide
            jgates John Gates added a comment - - 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
            jgates John Gates added a comment - - 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
            jgates John Gates added a comment -

            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
            jgates John Gates added a comment - 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
            jgates John Gates added a comment -

            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
            jgates John Gates added a comment - 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
            jgates John Gates added a comment - - 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
            jgates John Gates added a comment - - 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
            jgates John Gates added a comment -

            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
            jgates John Gates added a comment - 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
            jgates John Gates added a comment -

            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
            jgates John Gates added a comment - 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
            jgates John Gates added a comment -

            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
            jgates John Gates added a comment - 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
            jgates John Gates added a comment -

            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
            jgates John Gates added a comment - 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
            jgates John Gates added a comment -

            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
            jgates John Gates added a comment - 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:
              jgates John Gates
              Reporter:
              jgates 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:
                Updated:
                Resolved: