[
https://issues.apache.org/jira/browse/CALCITE-7079?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
zhaowang updated CALCITE-7079:
------------------------------
Description:
Mongodb adapter translate more than one $ne condition error.
run sql:
{code:java}
select city, state from zips where city <> 'ABERDEEN' and city <> 'AIKEN'{code}
Query plan:
{code:java}
{
"$match": {
"city": {
"$ne": "AIKEN"
}
}
}, {$project: {CITY: '$city', STATE: '$state'}}{code}
The condition (city <> 'ABERDEEN') lost，actually, more than one $ne condition
conpose of AND have to translate to $nin operation
Expected plan:
{code:java}
{
"$match": {
"city": {
"$nin": [
"AIKEN",
"ABERDEEN"
]
}
}
}, {$project: {CITY: '$city', STATE: '$state'}} {code}
More test case:
Case 1:
{code:java}
select city, state from zips where city <> 'ABERDEEN' and city <> 'AIKEN' and
state <> 'a' and state <> 'b' {code}
Query plan:
{code:java}
{
"$match": {
"city": {
"$ne": "AIKEN"
},
"state": {
"$ne": "b"
}
}
}, {$project: {CITY: '$city', STATE: '$state'}} {code}
Expected plan:
{code:java}
{
"$match": {
"city": {
"$nin": [
"ABERDEEN",
"AIKEN"
]
},
"state": {
"$nin": [
"a",
"b"
]
}
}
}, {$project: {CITY: '$city', STATE: '$state'}} {code}
Case 2:
{code:java}
select city, state from zips where city <> 'ABERDEEN' and city <> 'AIKEN' or
(state <> 'a' and state <> 'b') {code}
Query plan:
{code:java}
{
"$match": {
"$or": [
{
"city": {
"$ne": "AIKEN"
}
},
{
"state": {
"$ne": "b"
}
}
]
}
}, {$project: {CITY: '$city', STATE: '$state'}} {code}
Expected plan:
{code:java}
{
"$match": {
"$or": [
{
"city": {
"$nin": [
"ABERDEEN",
"AIKEN"
]
}
},
{
"state": {
"$nin": [
"a",
"b"
]
}
}
]
}
}, {$project: {CITY: '$city', STATE: '$state'}} {code}
was:
Mongodb adapter translate more than one $ne condition error.
run sql:
{code:java}
select city, state from zips where city <> 'ABERDEEN' and city <> 'AIKEN'{code}
query plan:
{code:java}
{
"$match": {
"city": {
"$ne": "AIKEN"
}
}
}, {$project: {CITY: '$city', STATE: '$state'}}{code}
The condition (city <> 'ABERDEEN') lost，actually, more than one $ne condition
conpose of AND have to translate to $nin operation, expected plan:
{code:java}
{
"$match": {
"city": {
"$nin": [
"AIKEN",
"ABERDEEN"
]
}
}
}, {$project: {CITY: '$city', STATE: '$state'}} {code}
More test case:
Case 1:
{code:java}
select city, state from zips where city <> 'ABERDEEN' and city <> 'AIKEN' and
state <> 'a' and state <> 'b' {code}
Query plan:
Expected plan:
{code:java}
{
"$match": {
"city": {
"$nin": [
"ABERDEEN",
"AIKEN"
]
},
"state": {
"$nin": [
"a",
"b"
]
}
}
}, {$project: {CITY: '$city', STATE: '$state'}} {code}
Case 2:
{code:java}
select city, state from zips where city <> 'ABERDEEN' and city <> 'AIKEN' or
(state <> 'a' and state <> 'b') {code}
Query plan:
Expected plan:
{code:java}
{
"$match": {
"$or": [
{
"city": {
"$nin": [
"ABERDEEN",
"AIKEN"
]
}
},
{
"state": {
"$nin": [
"a",
"b"
]
}
}
]
}
}, {$project: {CITY: '$city', STATE: '$state'}} {code}
> Mongodb Adapter unable to translate multiple $ne condition conposed of "AND"
> ----------------------------------------------------------------------------
>
> Key: CALCITE-7079
> URL: https://issues.apache.org/jira/browse/CALCITE-7079
> Project: Calcite
> Issue Type: Bug
> Reporter: zhaowang
> Assignee: zhaowang
> Priority: Major
>
> Mongodb adapter translate more than one $ne condition error.
> run sql:
> {code:java}
> select city, state from zips where city <> 'ABERDEEN' and city <>
> 'AIKEN'{code}
> Query plan:
> {code:java}
> {
> "$match": {
> "city": {
> "$ne": "AIKEN"
> }
> }
> }, {$project: {CITY: '$city', STATE: '$state'}}{code}
> The condition (city <> 'ABERDEEN') lost，actually, more than one $ne condition
> conpose of AND have to translate to $nin operation
> Expected plan:
> {code:java}
> {
> "$match": {
> "city": {
> "$nin": [
> "AIKEN",
> "ABERDEEN"
> ]
> }
> }
> }, {$project: {CITY: '$city', STATE: '$state'}} {code}
>
> More test case:
> Case 1:
> {code:java}
> select city, state from zips where city <> 'ABERDEEN' and city <> 'AIKEN' and
> state <> 'a' and state <> 'b' {code}
> Query plan:
> {code:java}
> {
> "$match": {
> "city": {
> "$ne": "AIKEN"
> },
> "state": {
> "$ne": "b"
> }
> }
> }, {$project: {CITY: '$city', STATE: '$state'}} {code}
> Expected plan:
> {code:java}
> {
> "$match": {
> "city": {
> "$nin": [
> "ABERDEEN",
> "AIKEN"
> ]
> },
> "state": {
> "$nin": [
> "a",
> "b"
> ]
> }
> }
> }, {$project: {CITY: '$city', STATE: '$state'}} {code}
>
> Case 2:
> {code:java}
> select city, state from zips where city <> 'ABERDEEN' and city <> 'AIKEN' or
> (state <> 'a' and state <> 'b') {code}
> Query plan:
> {code:java}
> {
> "$match": {
> "$or": [
> {
> "city": {
> "$ne": "AIKEN"
> }
> },
> {
> "state": {
> "$ne": "b"
> }
> }
> ]
> }
> }, {$project: {CITY: '$city', STATE: '$state'}} {code}
> Expected plan:
> {code:java}
> {
> "$match": {
> "$or": [
> {
> "city": {
> "$nin": [
> "ABERDEEN",
> "AIKEN"
> ]
> }
> },
> {
> "state": {
> "$nin": [
> "a",
> "b"
> ]
> }
> }
> ]
> }
> }, {$project: {CITY: '$city', STATE: '$state'}} {code}
>
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
