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
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)
|
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)
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]
|
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]
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.
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. 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.
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. 
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)