Uploaded image for project: 'Phoenix'
  1. Phoenix
  2. PHOENIX-5884

Join query return empty result when filters for both the tables are present

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 4.14.0
    • Fix Version/s: 5.1.0, 4.16.0
    • Component/s: None
    • Labels:
      None

      Description

      Let's assume DDL to be same for both the tables involved in a join

      CREATE TABLE LeftTable (    id1 CHAR(6) NOT NULL,    id2 VARCHAR(22) NOT NULL,    id3 VARCHAR(12) NOT NULL,    id4 CHAR(2) NOT NULL,    id5 CHAR(6) NOT NULL,     id6 VARCHAR(200) NOT NULL,    id7 VARCHAR(50) NOT NULL,    ts TIMESTAMP ,    CONSTRAINT PK_JOIN_AND_INTERSECTION_TABLE PRIMARY KEY(id1,id2,id3,id4,id5,id6,id7))
      

      Following query return right results

      SELECT m.*,r.* FROM LEFT_TABLE m join RIGHT_TABLE r  on m.id3 = r.id3 and m.id2 = r.id2  and m.id4 = r.id4  and m.id5 = r.id5  and m.id1 = r.id1  and m.ts = r.ts  where  r.id1 IN ('201904','201905')  and r.id2 = 'ID2_VAL'  and r.id3 IN ('ID3_VAL','ID3_VAL2') 
      

      but When to optimize the query, filters for the left table are also added , query returned empty result . Though the filters are based on join condition so semantically above query and below query should be same.

      SELECT m.*,r.* FROM LEFT_TABLE m join RIGHT_TABLE r  on m.id3 = r.id3  and m.id2 = r.id2  and m.id4 = r.id4 and m.id5 = r.id5  and m.id1 = r.id1 and m.ts = r.ts  where m.id1 IN ('201904','201905')  and r.id1 IN ('201904','201905') and r.id2 = 'ID2_VAL'             and m.id3 IN ('ID3_VAL','ID3_VAL2')  and r.id3 IN ('ID3_VAL','ID3_VAL2') 
      

        Attachments

        1. PHOENIX-5884_v1.patch
          5 kB
          Ankit Singhal
        2. PHOENIX-5884.master.v2.patch
          6 kB
          Istvan Toth
        3. PHOENIX-5884.master.v3.patch
          8 kB
          Ankit Singhal
        4. PHOENIX-5884.master.v4.patch
          9 kB
          Istvan Toth
        5. PHOENIX-5884.master.v5.patch
          9 kB
          Istvan Toth

          Activity

            People

            • Assignee:
              ankit@apache.org Ankit Singhal
              Reporter:
              ankit Ankit Singhal
            • Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: