3:14 a.m.
Jan Stastny created TEIID-5036:
----------------------------------
Summary: 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
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}
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)