discrepancy on result based on limit option

bwgjoseph commented 3 years ago

Hi,

I was testing out the limit option and notice a interesting difference in term of result returned between ottoman and query

// ottoman find
const find = await Airplane.find({},
    {
        limit: 0,
        consistency: SearchConsistency.LOCAL
    },
);

// ottoman query
const query = `
    SELECT * FROM \`testBucket\` LIMIT 0
    `;
const y = await getDefaultInstance().cluster.query(query, { scanConsistency: 'request_plus' });

The result is as such

// find result
{
  meta: {
    requestId: 'c14c4a85-5a94-49d7-9f18-fd77d6f803c6',
    clientContextId: 'fac1ec83e0f10080',
    status: 'success',
    signature: {
      callsign: 'json',
      capacity: 'json',
      destination: 'json',
      email: 'json',
      extension: 'json',
      id: 'json',
      info: 'json',
      location: 'json',
      model: 'json',
      name: 'json',
      operational: 'json',
      scheduledAt: 'json',
      size: 'json',
      type: 'json'
    },
    profile: undefined,
    metrics: {
      elapsedTime: 11.316,
      executionTime: 11.2777,
      sortCount: undefined,
      resultCount: 3,
      resultSize: 1280,
      mutationCount: undefined,
      errorCount: undefined,
      warningCount: undefined
    }
  },
  rows: [
    _Model {
      callsign: 'Vulture',
      capacity: 325,
      destination: [Array],
      email: 'vulture@gmail.com',
      extension: [Array],
      id: '36f47555-4127-47c7-a38f-d270fd64e0d0',
      info: [Object],
      location: [Object],
      model: '737 NG',
      name: 'NE Airlines',
      operational: false,
      scheduledAt: 2021-05-01T06:10:00.000Z,
      size: 'M',
      type: 'PRIVATE'
    },
    _Model {
      callsign: 'Eagle',
      capacity: 500,
      destination: [Array],
      email: 'eagle@gmail.com',
      extension: 123,
      id: '5ad73af2-3031-48de-a27c-78d7e53361d1',
      info: [Object],
      location: [Object],
      model: '767-300F',
      name: 'Couchbase Airlines',
      operational: true,
      scheduledAt: 2020-12-19T16:30:00.000Z,
      size: 'L',
      type: 'FIRST CLASS'
    },
    _Model {
      callsign: 'Hawk',
      capacity: 250,
      destination: [Array],
      email: 'hawk@gmail.com',
      extension: 'abc',
      id: '784ed119-cdb5-4bf9-840c-33e452b473ab',
      info: [Object],
      location: [Object],
      model: 'A380',
      name: 'Couchbase Airlines',
      operational: true,
      scheduledAt: 2020-11-20T03:30:00.000Z,
      size: 'S',
      type: 'ECONOMY'
    }
  ]
}

// query result
{
  "meta": {
    "requestId": "49808118-1eca-468d-98e6-23b01382f905",
    "clientContextId": "8d20821579cd78cc",
    "status": "success",
    "signature": {
      "*": "*"
    },
    "metrics": {
      "elapsedTime": 160.5018,
      "executionTime": 160.4402,
      "resultCount": 0,
      "resultSize": 0
    }
  },
  "rows": []
}

Notice that if using find, all results will be returned when limit: 0 but if the same is passed via the query, then no result will be returned. Wondering if this is intended, or a bug?

Thanks

AV25242 commented 3 years ago

Looks like not passing in a limit (i.e not setting a value in findOptions) and passing in a 0 is basically doing the same thing. I have opened an Investigation ticket (this is mainly for the team to investigate and let us know why it is what it is).

https://github.com/couchbaselabs/node-ottoman/issues/465 - Investigation Ticket

bwgjoseph commented 3 years ago

Agreed, I would like to know why first too. Not sure if there's any history behind it.

AV25242 commented 3 years ago

0 was treated as false not a value, we are going to fix this.

bwgjoseph commented 3 years ago

I'm not sure if it's the way different databases handles LIMIT 0 differently, or generally, between SQL and noSQL databases.

Seem like SQL (or SQL-like) handles LIMIT 0 with no result, and noSQL handles LIMIT 0 with all results like mongodb

I'm fine if ottoman is fixing to follow n1ql where it does not return any result if limit is 0

AV25242 commented 3 years ago

https://github.com/couchbaselabs/node-ottoman/issues/465 issue created

AV25242 commented 3 years ago

Available with next release

httpJunkie commented 3 years ago

Fixed w/ alpha 29

bwgjoseph / mongoose-vs-ottoman

discrepancy on result based on limit option #80