Uploaded image for project: 'Couchbase Server'
  1. Couchbase Server
  2. MB-55059

multi level JOIN on both sides gives wrong results/hangs

    XMLWordPrintable

Details

    • Untriaged
    • 0
    • Unknown

    Description

       

      CREATE PRIMARY INDEX on default;
      insert into default (KEY k, VALUE v) SELECT "k"||TO_STR(d) AS k , {"c1":d, "c2":"doc"} AS v FROM ARRAY_RANGE(0,3) AS d;
      

       

      SELECT t3.*
      FROM  ( SELECT t2.*
                     FROM ( SELECT d.*
                            FROM default d) AS t1
                     JOIN ( SELECT d0.*
                            FROM default d0) AS t2
                     ON t1.c1 = t2.c1) AS t3
      JOIN  ( SELECT t4.*
                     FROM ( SELECT d2.*
                            FROM default d2) AS t4
                     JOIN ( SELECT d3.*
                            FROM default d3) AS t5
                     ON t4.c1 = t5.c1) AS t6
      ON t3.c1 = t6.c1 and t3.c2 = t6.c2;

      6.6.2 gives 1 row which is wrong

      7.1.3 hangs

       

      In 7.1.3 t3<==>t6  using NESTED LOOP even forced HASH that may contributing hang

      SELECT t3.*
      FROM  ( SELECT t2.*
                     FROM ( SELECT d.*
                            FROM default d) AS t1
                     JOIN ( SELECT d0.*
                            FROM default d0) AS t2 USE HASH (BUILD)
                     ON t1.c1 = t2.c1) AS t3
      JOIN  ( SELECT t4.*
                     FROM ( SELECT d2.*
                            FROM default d2) AS t4
                     JOIN ( SELECT d3.*
                            FROM default d3) AS t5 USE HASH (BUILD)
                     ON t4.c1 = t5.c1) AS t6 USE HASH (BUILD)
      ON t3.c1 = t6.c1 and t3.c2 = t6.c2;

       

      Workaround: If right side of JOIN is subquery use that subquery as CTE if possible

      Attachments

        Issue Links

          No reviews matched the request. Check your Options in the drop-down menu of this sections header.

          Activity

            People

              pierre.regazzoni Pierre Regazzoni
              Sitaram.Vemulapalli Sitaram Vemulapalli
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Gerrit Reviews

                  There are no open Gerrit changes

                  PagerDuty