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

Incomplete subchunk list causing query to fail.

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: In Progress
    • Resolution: Unresolved
    • 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

          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.

            People

            Assignee:
            jgates John Gates
            Reporter:
            jgates John Gates
            Watchers:
            John Gates
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated: