Details
-
Bug
-
Resolution: Done
-
Major
-
8.12.11.6_4
-
None
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
