Skip to main content

I recently was part of a discussion regarding the use of parenthesis with a SELECT statement.

My understanding was that parenthesis were not evaluated with respect to AND and OR logic.

Is that correct?

Looking in the online documentation, the only mention (that I could find) regarding parenthesis use was in relation to OPTIONS.

Specifically the the open parenthesis indicated the beginning of an OPTIONS clause and that a close parenthesis could be used to end the clause and that the OPTIONS clause should always be at the end of the statement.

If that is the only allowed use for parenthesis then:

Why are parenthesis allowed to be used at all in a SELECT statement as follows:
SELECT FILE WITH 1 EQ "ABC" AND ( WITH 2 EQ "123" OR WITH 3 EQ "XYZ" )
This command executes without an error?

Using a file with the following records:

TEST..... A1....... A2...

100        A         1
200        A         2
300        A         3
400        B         1
500        B         2

This statement (1):

SELECT TEST WITH 1 EQ "A" AND WITH 2 EQ "2" OR WITH 2 EQ "3"

correctly produces this result with two records:

    200   A          2
    300   A          3   

Record 100 is correctly omitted.

This statement (2):

SELECT TEST WITH 1 EQ "A" AND ( WITH 2 EQ "2" OR WITH 2 EQ "3" )

I would expect to get the same result but it incorrectly produces this result with three records:

    200   A          2
    300   A          3
    500   B          2

It seems to be evaluated as: SELECT TEST WITH 1 EQ "A" OR WITH 2 EQ "2" OR WITH 2 EQ "3"

This statement (3):

SELECT LANCE WITH 1 EQ "A" AND (WITH 2 EQ "2" OR WITH 2 EQ "3")

returns a single value of "3"

It seems that the open parenthesis in this statement seems to be interpreted as the beginning of an OPTIONS clause.

How it then parses the rest of the statement is a complete mystery.

Based on this information, maybe some changes need to be made to the AQL parsing routine.

  1. A single open parenthesis followed by a space should either be ignored an error should be raised.
  2. A single close parenthesis proceeded by a space should either be ignored or an error should be raised.
  3. When an OPTION clause is specified and there are additional items after the clause an error should be raised (at least based on what the documentations states).

Finally, is there a way to issue a SELECT statement with multiple AND/OR sets where specific grouping are evaluated a true or false like statement (2) above?

Thanks,

Lance

I recently was part of a discussion regarding the use of parenthesis with a SELECT statement.

My understanding was that parenthesis were not evaluated with respect to AND and OR logic.

Is that correct?

Looking in the online documentation, the only mention (that I could find) regarding parenthesis use was in relation to OPTIONS.

Specifically the the open parenthesis indicated the beginning of an OPTIONS clause and that a close parenthesis could be used to end the clause and that the OPTIONS clause should always be at the end of the statement.

If that is the only allowed use for parenthesis then:

Why are parenthesis allowed to be used at all in a SELECT statement as follows:
SELECT FILE WITH 1 EQ "ABC" AND ( WITH 2 EQ "123" OR WITH 3 EQ "XYZ" )
This command executes without an error?

Using a file with the following records:

TEST..... A1....... A2...

100        A         1
200        A         2
300        A         3
400        B         1
500        B         2

This statement (1):

SELECT TEST WITH 1 EQ "A" AND WITH 2 EQ "2" OR WITH 2 EQ "3"

correctly produces this result with two records:

    200   A          2
    300   A          3   

Record 100 is correctly omitted.

This statement (2):

SELECT TEST WITH 1 EQ "A" AND ( WITH 2 EQ "2" OR WITH 2 EQ "3" )

I would expect to get the same result but it incorrectly produces this result with three records:

    200   A          2
    300   A          3
    500   B          2

It seems to be evaluated as: SELECT TEST WITH 1 EQ "A" OR WITH 2 EQ "2" OR WITH 2 EQ "3"

This statement (3):

SELECT LANCE WITH 1 EQ "A" AND (WITH 2 EQ "2" OR WITH 2 EQ "3")

returns a single value of "3"

It seems that the open parenthesis in this statement seems to be interpreted as the beginning of an OPTIONS clause.

How it then parses the rest of the statement is a complete mystery.

Based on this information, maybe some changes need to be made to the AQL parsing routine.

  1. A single open parenthesis followed by a space should either be ignored an error should be raised.
  2. A single close parenthesis proceeded by a space should either be ignored or an error should be raised.
  3. When an OPTION clause is specified and there are additional items after the clause an error should be raised (at least based on what the documentations states).

Finally, is there a way to issue a SELECT statement with multiple AND/OR sets where specific grouping are evaluated a true or false like statement (2) above?

Thanks,

Lance

I'm of the understanding that parentheses are ignored except for the options, and I'm not sure if the AND/OR respects hierarchy or sequence. Now you have me curious and I'm gonna take a look.

I recently was part of a discussion regarding the use of parenthesis with a SELECT statement.

My understanding was that parenthesis were not evaluated with respect to AND and OR logic.

Is that correct?

Looking in the online documentation, the only mention (that I could find) regarding parenthesis use was in relation to OPTIONS.

Specifically the the open parenthesis indicated the beginning of an OPTIONS clause and that a close parenthesis could be used to end the clause and that the OPTIONS clause should always be at the end of the statement.

If that is the only allowed use for parenthesis then:

Why are parenthesis allowed to be used at all in a SELECT statement as follows:
SELECT FILE WITH 1 EQ "ABC" AND ( WITH 2 EQ "123" OR WITH 3 EQ "XYZ" )
This command executes without an error?

Using a file with the following records:

TEST..... A1....... A2...

100        A         1
200        A         2
300        A         3
400        B         1
500        B         2

This statement (1):

SELECT TEST WITH 1 EQ "A" AND WITH 2 EQ "2" OR WITH 2 EQ "3"

correctly produces this result with two records:

    200   A          2
    300   A          3   

Record 100 is correctly omitted.

This statement (2):

SELECT TEST WITH 1 EQ "A" AND ( WITH 2 EQ "2" OR WITH 2 EQ "3" )

I would expect to get the same result but it incorrectly produces this result with three records:

    200   A          2
    300   A          3
    500   B          2

It seems to be evaluated as: SELECT TEST WITH 1 EQ "A" OR WITH 2 EQ "2" OR WITH 2 EQ "3"

This statement (3):

SELECT LANCE WITH 1 EQ "A" AND (WITH 2 EQ "2" OR WITH 2 EQ "3")

returns a single value of "3"

It seems that the open parenthesis in this statement seems to be interpreted as the beginning of an OPTIONS clause.

How it then parses the rest of the statement is a complete mystery.

Based on this information, maybe some changes need to be made to the AQL parsing routine.

  1. A single open parenthesis followed by a space should either be ignored an error should be raised.
  2. A single close parenthesis proceeded by a space should either be ignored or an error should be raised.
  3. When an OPTION clause is specified and there are additional items after the clause an error should be raised (at least based on what the documentations states).

Finally, is there a way to issue a SELECT statement with multiple AND/OR sets where specific grouping are evaluated a true or false like statement (2) above?

Thanks,

Lance

Well, I ran a test myself. Looks like the parens are honored as a prioritization as long as they are preceded and followed by spaces. If not followed by spaces, the select hung, apparently processing some text as an option with which I'm not familiar. I'm checking with others on this. My test:

:list seltst a1 a2
Page   1     seltst                                                12:42:18 09 Jun 2025

seltst.... a1................................... a2...................................

1          a                                     1
2          a                                     2
3          a                                     2
4          b                                     2
5          b                                     1

[405] 5 items listed out of 5 items.
:list seltst a1 a2 with a1 "a" and with a2 "1" or with a1 = "b"
Page   1     seltst                                                12:43:36 09 Jun 2025

seltst.... a1................................... a2...................................

1          a                                     1
4          b                                     2
5          b                                     1

[405] 3 items listed out of 5 items.
:list seltst a1 a2 with a1 "b" or with a2 = "1" and with a1 = "a"
Page   1     seltst                                                12:44:29 09 Jun 2025

seltst.... a1................................... a2...................................

1          a                                     1
4          b                                     2
5          b                                     1

[405] 3 items listed out of 5 items.
:list seltst a1 a2 with a1 "b" or ( with a2 = "1" and with a1 = "a" )
Page   1     seltst                                                12:44:57 09 Jun 2025

seltst.... a1................................... a2...................................

1          a                                     1
4          b                                     2
5          b                                     1

[405] 3 items listed out of 5 items.
:list seltst a1 a2 with a2 = "1" and ( with a1 = "a" or with a1 = "b" )
Page   1     seltst                                                12:54:00 09 Jun 2025

seltst.... a1................................... a2...................................

1          a                                     1
2          a                                     2
3          a                                     2
4          b                                     2
5          b                                     1

[405] 5 items listed out of 5 items.
:list seltst a1 a2 with a2 = "1" and with a1 = "a" or with a1 = "b"
Page   1     seltst                                                12:54:18 09 Jun 2025

seltst.... a1................................... a2...................................

1          a                                     1
4          b                                     2
5          b                                     1

[405] 3 items listed out of 5 items.
:list seltst a1 a2 with a2 = "1" and (with a1 = "a" or with a1 = "b")
Processing...
:::end

Terms & ConditionsAccessibility statement