Mathematical operations in where

[anvog]

SELECT * FROM temptable where value1 = ((value2 * 5) - value3)

The statement above will throw the error.

Syntax error found near Binary Expression

Brackets should be fine. Any other way to structure the equations?

[nwronski]

It looks like the grammar isn't happy with the parenthesis on the left side ((value2 * 5)) of the expression. Your example is definitely valid. I will look into a fix for this one.

Looking at it closer, there appears to also be a problem when trying to switch the two sides of the inner expression:

SELECT * FROM temptable where value1 = (value3 * -1 + (value2 * 5))

Currently, the parser is treating this as if it is the expression: value3 * -(1 + (value2 * 5))

[nwronski]

Based on further investigation, the expression parsing works for simple expressions, but is not obeying order of operations or correctly composing complex expressions. For example:

-- parsed as -(1 * (2+3)) instead of (-1) * (2 + 3)
SELECT * FROM t where -1 * (2 + 3)

-- parsed as 3 + (4 * (5 > 20)) instead of (3 + (4 * 5)) > 20
SELECT * FROM t where 3 + 4 * 5 > 20

I will need to rewrite the expression parsing grammar for the next release to:

• [x] Obey order of operations: * is higher than +, etc...
• [x] Compose in the correct order: unary expressions, then binary expressions, then logical and/or (AND, OR), etc...
• [x] Allow for nesting parenthesis on either side of a binary expression

Expect these changes in release v0.15.0.

[nwronski]

@anvog I just released a beta version of the parser that contains the fix for the issue you were experiencing 0.15.0-beta.

You can install it via npm using (for now):

npm i sqlite-parser@beta

or, you can also try it out online here.

Let me know if you encounter any more problems, thanks!

[nwronski]

Whoops, didn't mean to close this one just yet. Will close after 0.15.0 is out of beta.

[nwronski]

Closing this one, but still need beta flag to install at the moment. (Latest: v1.0.0-beta)

npm i sqlite-parser@beta