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

Incomplete subchunk list causing query to fail.

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Done
    • Resolution: Done
    • Fix Version/s: None
    • Component/s: sphgeom
    • Labels:
      None
    • Team:
      Data Access and Database

      Description

      It is possible to make a query that covers a small region of the sky using qserv_areaspec_box that returns several rows. Increasing the area cover by the qserv_areaspec_box call (or eliminating the qserv_arespec_box call entirely) results in an empty set.

      Working Query, 77 rows in result set:
      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, LSST.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 Id1, Id2;
      

      A slight change in the query results in the empty set
       
      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, LSST.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;
      

      The first query has this list of chunks and subchunks on the czar:

      [ChunkSpec(chunkId=9630, subChunks=[770]), 
      ChunkSpec(chunkId=9631, subChunks=[759]), 
      ChunkSpec(chunkId=9797, subChunks=[11])]
      

      The second query has a much longer list of chunks and subchunks (which will be added in a comment below), but the list does not include chunkId=9630, subChunks=[770].

      It does have chunkId=9631, subChunk= 759 and chunkId=9797, subChunk=11

      chunkId=9631 subChunks=[0, 69, 138, 207, 276, 345, 414, 483, 552, 621, 690, 759]
      chunkId=9797, subChunks=[0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11]
      

      The query was made to test that the ObjectFullOverlap table was being used when spacially joining 2 databases sharded separately, specifically to find items nearby each other at the corner of chunk 9630, so missing subchunk 770 results in the empty set.

        Attachments

          Issue Links

            Activity

            Hide
            jgates John Gates added a comment -

            Complete list of subchunks for 9630 for the second query above (returns empty set). subchunk 770 is not in the list.

            chunkId=9630, subChunks=[46368, 46369, 46370, 46371, 46372, 46373, 46374, 46375, 46376, 46377, 46378, 46379, 46437, 46438, 46439, 46440, 46441, 46442, 46443, 46444, 46445, 46446, 46447, 46448, 46506, 46507, 46508, 46509, 46510, 46511, 46512, 46513, 46514, 46515, 46516, 46517, 46575, 46576, 46577, 46578, 46579, 46580, 46581, 46582, 46583, 46584, 46585, 46586, 46644, 46645, 46646, 46647, 46648, 46649, 46650, 46651, 46652, 46653, 46654, 46655, 46713, 46714, 46715, 46716, 46717, 46718, 46719, 46720, 46721, 46722, 46723, 46724, 46782, 46783, 46784, 46785, 46786, 46787, 46788, 46789, 46790, 46791, 46792, 46793, 46851, 46852, 46853, 46854, 46855, 46856, 46857, 46858, 46859, 46860, 46861, 46862, 46920, 46921, 46922, 46923, 46924, 46925, 46926, 46927, 46928, 46929, 46930, 46931, 46989, 46990, 46991, 46992, 46993, 46994, 46995, 46996, 46997, 46998, 46999, 47000, 47058, 47059, 47060, 47061, 47062, 47063, 47064, 47065, 47066, 47067, 47068, 47069, 47127, 47128, 47129, 47130, 47131, 47132, 47133, 47134, 47135, 47136, 47137, 47138])
            

            Show
            jgates John Gates added a comment - Complete list of subchunks for 9630 for the second query above (returns empty set). subchunk 770 is not in the list. chunkId=9630, subChunks=[46368, 46369, 46370, 46371, 46372, 46373, 46374, 46375, 46376, 46377, 46378, 46379, 46437, 46438, 46439, 46440, 46441, 46442, 46443, 46444, 46445, 46446, 46447, 46448, 46506, 46507, 46508, 46509, 46510, 46511, 46512, 46513, 46514, 46515, 46516, 46517, 46575, 46576, 46577, 46578, 46579, 46580, 46581, 46582, 46583, 46584, 46585, 46586, 46644, 46645, 46646, 46647, 46648, 46649, 46650, 46651, 46652, 46653, 46654, 46655, 46713, 46714, 46715, 46716, 46717, 46718, 46719, 46720, 46721, 46722, 46723, 46724, 46782, 46783, 46784, 46785, 46786, 46787, 46788, 46789, 46790, 46791, 46792, 46793, 46851, 46852, 46853, 46854, 46855, 46856, 46857, 46858, 46859, 46860, 46861, 46862, 46920, 46921, 46922, 46923, 46924, 46925, 46926, 46927, 46928, 46929, 46930, 46931, 46989, 46990, 46991, 46992, 46993, 46994, 46995, 46996, 46997, 46998, 46999, 47000, 47058, 47059, 47060, 47061, 47062, 47063, 47064, 47065, 47066, 47067, 47068, 47069, 47127, 47128, 47129, 47130, 47131, 47132, 47133, 47134, 47135, 47136, 47137, 47138])
            Hide
            jgates John Gates added a comment -

            Examining the existing subchunks for chunkId=9630 on ccqserv143 showed that the largest subChunkId was 770. It looks like sphgeom Chunker::getSubChunksIntersecting is not calculating the subchunks correctly in some cases.

            Show
            jgates John Gates added a comment - Examining the existing subchunks for chunkId=9630 on ccqserv143 showed that the largest subChunkId was 770. It looks like sphgeom Chunker::getSubChunksIntersecting is not calculating the subchunks correctly in some cases.
            Hide
            tjenness Tim Jenness added a comment -

            Fritz Mueller is this still relevant?

            Show
            tjenness Tim Jenness added a comment - Fritz Mueller is this still relevant?
            Hide
            fritzm Fritz Mueller added a comment -

            This may very well be an sphgeom bug (in the Chunker class, though, which as far as I know only Qserv uses).

            I would like to try and code up and attach a Python repro case using sphgeom directly, with Qserv out of the picture.  In order to do this, though, I need to know the partitioning parameters used by John Gates for the data set being queried in the bug report.  John, any idea which data set this was or what its partitioning parameters were?

            Show
            fritzm Fritz Mueller added a comment - This may very well be an sphgeom bug (in the Chunker class, though, which as far as I know only Qserv uses). I would like to try and code up and attach a Python repro case using sphgeom directly, with Qserv out of the picture.  In order to do this, though, I need to know the partitioning parameters used by John Gates  for the data set being queried in the bug report.  John, any idea which data set this was or what its partitioning parameters were?
            Hide
            fritzm Fritz Mueller added a comment -

            John pointed out that the fact that this ticket is open is apparently just a bookkeeping error – the sphgeom bug was found, fixed, and merged by Serge back in 2017 (https://github.com/lsst/sphgeom/commit/97efe999b5a18fc3b61f14f57d98d9395bda6bb3)

            Show
            fritzm Fritz Mueller added a comment - John pointed out that the fact that this ticket is open is apparently just a bookkeeping error – the sphgeom bug was found, fixed, and merged by Serge back in 2017 ( https://github.com/lsst/sphgeom/commit/97efe999b5a18fc3b61f14f57d98d9395bda6bb3 )

              People

              Assignee:
              jgates John Gates
              Reporter:
              jgates John Gates
              Watchers:
              Fritz Mueller, John Gates, Tim Jenness
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Jenkins

                  No builds found.