Sails: Certain or/and combinations failing in MySQL query builder

[github1337]

Sails version: 1.0.2
Node version: 8.11.2
NPM version: 5.6.0
DB adapter name: sails-mysql
DB adapter version: 1.0.0
Operating system: OSX

---

A Query like this fails for every Model with MySQL. The Error happens after Stage 3; the statement compiler creates weird columns for this query:
Model.find().where({a:1, or: [{b:2, c:3}]})
-> Error: Unexpected error from database adapter: ER_BAD_FIELD_ERROR: Unknown column '0' in 'where clause'

Other queries:

• works Model.find().where({or: [{b:2, c:3}]})
• works Model.find().where({a:1, or: [{b:2, c:3}, {b:4, c:5}]})
• doesn't work: Model.find().where({a:1, or: [{b:2, c:3}]})
• works Model.find().where({and: [{b:2, c:3}]})
• works Model.find().where({a:1, and: [{b:2, c:3}, {b:4, c:5}]})
• doesn't work: Model.find().where({a:1, and: [{b:2, c:3}]})

So there seems to be an error if there is a field and an or/and with only one element

[sailsbot]

Hi @github1337! It looks like you may have removed some required elements from the initial comment template, without which I can't verify that this post meets our contribution guidelines. To re-open this issue, please copy the template from here, paste it at the beginning of your initial comment, and follow the instructions in the text. Then post a new comment (e.g. "ok, fixed!") so that I know to go back and check.

Sorry to be a hassle, but following these instructions ensures that we can help you in the best way possible and keep the Sails project running smoothly.

*If you feel this message is in error, or you want to debate the merits of my existence (sniffle), please contact [email protected]

[github1337]

ok, fixed!

[sailsbot]

@github1337 Thanks for posting, we'll take a look as soon as possible.

---

For help with questions about Sails, click here. If you’re interested in hiring @sailsbot and her minions in Austin, click here.

[mikermcneil]

@github1337 I think next step is to bust this open and make sure the stage 2 query is correct. If you have a sec to dig that up, that'd be awesome-- if not, no worries.

But just to make sure, what version of sails-hook-orm are you running?

[github1337]

@mikermcneil sails-hook-orm version is 2.0.1

For this: Model.find().where({a:1, or: [{b:2, c:3}]})
Pre Stage-2:

{
  "method": "find",
  "using": "model",
  "criteria": {
    "where": {
      "a": 1,
      "or": [
        {
          "b": 2,
          "c": 3
        }
      ]
    }
  }
}

Stage 2

{
  "method": "find",
  "using": "model",
  "criteria": {
    "where": {
      "and": [
        {
          "a": 1
        },
        {
          "or": [
            {
              "and": [
                {
                  "b": "2"
                },
                {
                  "c": "3"
                }
              ]
            }
          ]
        }
      ]
    },
    "limit": 9007199254740991,
    "skip": 0,
    "sort": [],
    "select": [
      "*"
    ],
    "omit": []
  },
  "populates": {}
}

[github1337]

@mikermcneil any updates on this?

[streleck]

@github1337 What was the result of the stage-2 query you have here? It looks to be formed more like what you want for waterline, where nested ands and ors are always within an and/or key at the top level of the object.

[github1337]

@streleck Stage 2 looks fine, I agree with you. I only posted it because Mike asked for it. The error still happens though. As said on top, the error probably happens after stage 3 and it says: Error: Unexpected error from database adapter: ER_BAD_FIELD_ERROR: Unknown column '0' in 'where clause'. Probably the query synthesizer or something does not work with this criteria.

[streleck]

I did some tests to verify @github1337 's findings above and they worked the same for me. It seems that there is a problem having a single item in the array for or/and, when that conditional is passed in as part of an array in the first level of the query.

[github1337]

Any updates on this?

[johnabrams7]

@github1337 sails-hook-orm has been updated to 2.1.1 and sails-mysql adapter has been updated to v1.0.1 - how does this issue respond to these new versions?