Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-5036

Couchbase EXCEPT returns more values than expected

    Details

      Description

      Except doesn't work as expected, it returns all the results from the first table, it does not remove entries which are present in the second table. Might be connected to TEIID-5035.

      query_small_a

      SELECT IntKey FROM BQT1.SmallA
      

      IntKey
      0
      1
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      2
      20
      21
      22
      23
      24
      25
      26
      27
      28
      29
      3
      30
      31
      32
      33
      34
      35
      36
      37
      38
      39
      4
      40
      41
      42
      43
      44
      45
      46
      47
      48
      49
      5
      6
      7
      8
      9

      query_small_b

      SELECT IntNum FROM BQT1.SmallB
      

      IntNum
      <null>
      -23
      -14
      -13
      -12
      -11
      -10
      -9
      <null>
      -7
      -6
      -5
      -22
      -4
      -3
      -2
      -1
      0
      1
      2
      3
      4
      5
      -21
      6
      7
      <null>
      9
      10
      11
      12
      13
      14
      15
      -20
      16
      17
      18
      19
      20
      21
      22
      23
      <null>
      25
      -19
      -18
      -17
      -16
      -15

      query_smalla_except_smallb

      SELECT IntKey FROM BQT1.SmallA EXCEPT SELECT IntNum FROM BQT1.SmallB
      

      IntKey
      13
      32
      30
      17
      8
      36
      11
      34
      4
      15
      38
      0
      19
      6
      2
      47
      45
      43
      41
      49
      26
      28
      22
      24
      20
      31
      10
      12
      35
      14
      33
      16
      9
      39
      18
      7
      37
      5
      3
      1
      44
      40
      46
      42
      48
      29
      25
      27
      21
      23

      teiid-command.log

      10:08:57,788 INFO  [org.teiid.COMMAND_LOG] (New I/O worker #2) QE2ZpDa/Btt5     START USER COMMAND: startTime=2017-08-25 10:08:57.788       requestID=QE2ZpDa/Btt5.153    txID=null sessionID=QE2ZpDa/Btt5        applicationName=JDBC          principal=user@teiid-security vdbName=couchbase   vdbVersion=1        sql=SELECT IntKey FROM BQT1.SmallA EXCEPT SELECT IntNum FROM BQT1.SmallB
      10:08:57,795 DEBUG [org.teiid.COMMAND_LOG] (Worker52_QueryProcessorQueue466) QE2ZpDa/Btt5           START DATA SRC COMMAND:       startTime=2017-08-25 10:08:57.795       requestID=QE2ZpDa/Btt5.153    sourceCommandID=0   executionID=113     txID=null modelName=Couchbase_small     translatorName=couchbase      sessionID=QE2ZpDa/Btt5        principal=user@teiid-security sql=SELECT Couchbase_small.SmallA.IntKey AS c_0 FROM Couchbase_small.SmallA EXCEPT SELECT Couchbase_small.SmallB.IntNum AS c_0 FROM Couchbase_small.SmallB
      10:08:57,795 DEBUG [org.teiid.COMMAND_LOG] (Worker52_QueryProcessorQueue466) QE2ZpDa/Btt5           SOURCE SRC COMMAND: endTime=2017-08-25 10:08:57.795         requestID=QE2ZpDa/Btt5.153    sourceCommandID=0   executionID=113     txID=null modelName=Couchbase_small     translatorName=couchbase      sessionID=QE2ZpDa/Btt5        principal=user@teiid-security sourceCommand=[SELECT `$cb_c1_IntKey` FROM `dvqe_small` `$cb_t1` LET `$cb_c1_IntKey` = `$cb_t1`.`IntKey` WHERE `$cb_t1`.`type` = 'SmallA' EXCEPT SELECT `$cb_c2_IntNum` FROM `dvqe_small` `$cb_t2` LET `$cb_c1_IntKey` = `$cb_t2`.`IntKey`, `$cb_c2_IntNum` = `$cb_t2`.`IntNum` WHERE `$cb_t2`.`type` = 'SmallA']
      10:08:58,061 DEBUG [org.teiid.COMMAND_LOG] (Worker51_QueryProcessorQueue467) QE2ZpDa/Btt5           END SRC COMMAND:    endTime=2017-08-25 10:08:58.061         requestID=QE2ZpDa/Btt5.153    sourceCommandID=0   executionID=113     txID=null modelName=Couchbase_small     translatorName=couchbase      sessionID=QE2ZpDa/Btt5        principal=user@teiid-security finalRowCount=50    cpuTime(ns)=1088787
      10:08:58,069 INFO  [org.teiid.COMMAND_LOG] (Worker51_QueryProcessorQueue468) QE2ZpDa/Btt5           END USER COMMAND:   endTime=2017-08-25 10:08:58.069         requestID=QE2ZpDa/Btt5.153    txID=null sessionID=QE2ZpDa/Btt5        principal=user@teiid-security vdbName=couchbase   vdbVersion=1        finalRowCount=50
      

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                shawkins Steven Hawkins
                Reporter:
                jstastny Jan Stastny
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: