[teiid-issues] [JBoss JIRA] (TEIID-5036) Couchbase EXCEPT returns more values than expected

Couchbase EXCEPT returns more values than expected -------------------------------------------------- Key: TEIID-5036 URL: https://issues.jboss.org/browse/TEIID-5036 Project: Teiid Issue Type: Bug Components: Misc. Connectors Affects Versions: 8.12.x-6.4 Reporter: Jan Stastny Assignee: Steven Hawkins Fix For: 10.0, 8.12.x-6.4, 9.3.3 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. {code:sql|title=query_small_a} SELECT IntKey FROM BQT1.SmallA {code} || 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 || {code:sql|title=query_small_b} SELECT IntNum FROM BQT1.SmallB {code} || 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 || {code:sql|title=query_smalla_except_smallb} SELECT IntKey FROM BQT1.SmallA EXCEPT SELECT IntNum FROM BQT1.SmallB {code} || 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 || {code:title=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 {code}