[dna-issues] [JBoss JIRA] Commented: (DNA-552) Support BETWEEN and NOT BETWEEN in WHERE clause of SQL language and abstract query model

[Randall Hauch (JIRA)]

    [ https://jira.jboss.org/jira/browse/DNA-552?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12495070#action_12495070 ] 

Randall Hauch commented on DNA-552:
-----------------------------------

The abstract query model now has a new Constraint subclass called Between that has a DynamicOperand, a StaticOperand lower boundary, a StaticOperand upper boundary, and a flag for each boundary that specifies whether the boundary is included or excluded in the range.  This very capably handles all kinds of continuous ranges.

The dna-searc project was enhanced to support this new Constraint type for numeric values (long, double, decimal, and date).

The SQL language parser now handles the '<dynamicOperand> BETWEEN <staticOperandLowerBound> [EXCLUSIVE] AND <staticOperandUpperBound> [EXCLUSIVE]'.

Additionally, a query optimizer rule was created to look for pairs of Comparison constraints that can be merged or rewritten:
- two range constraints are replaced with a single Between constraint (e.g., 'table.column' >=4 and table.column <=10' replaced with 'table.column BETWEEN 4 AND 10', with the exclusive flags set properly)
- two range constraints that specify a range of a single value are replaced with a single equality constraint (e.g., 'table.column >=4 AND table.column <= 4' replaced with 'table.column = 4')
- unnecessary Comparison constraints are removed (e.g., given 'table.column < 4 AND table.column < 10' the latter constraint is removed)
- conflicting constraints that will never be satisfied (e.g., 'table.column < 4 AND table.column > 10), marking the ACCESS node as having no results (this is handled in the query processor)

The QueryProcessor was also changed to look for an ACCESS node with the (new) ACCESS_NO_RESULTS property, and to create a NoResultsComponent rather than a real access component.  This will work in that an access query will never be made when we know the range constraints are invalid, but it does not look for other always-false constraints nor does it optimize the plan.  (A RemoveEmptyAccessNodes optimizer plan was created, though it basically is a no-op at the moment.)


> Support BETWEEN and NOT BETWEEN in WHERE clause of SQL language and abstract query model
> ----------------------------------------------------------------------------------------
>
>                 Key: DNA-552
>                 URL: https://jira.jboss.org/jira/browse/DNA-552
>             Project: DNA
>          Issue Type: Feature Request
>          Components: Graph, Query, Search
>    Affects Versions: 0.6
>            Reporter: Randall Hauch
>            Assignee: Randall Hauch
>             Fix For: 0.7
>
>
> Our search/query implementation will perform much better when it can detect a range of values in a criteria.  Currently, ranges can only be specified using the form "WHERE ... <dynamicOperand> <operator1> <staticOperand1> ... AND <dynamicOperand> <operator2> <staticOperand2> ..."  For example: 
>     WHERE ... DEPTH(myType) >= 3 AND DEPTH(myType) <=6
> SQL already has a BETWEEN operator (and corresponding NOT BETWEEN operator) that makes this not only more concise but also more easily understandable and obvious about the intent:
>     WHERE ... DEPTH(myType) BETWEEN 3 and 6
> This concept would also have to be added to the abstract query model, and in particular as a subclass of Constraint that has a DynamicOperand and two StaticOperand values.  And by adding this specialization of Constraint, we can add an optimizer rule that looks for ANDed constraints that represent a range on a DynamicOperand, and rewrite the constraints as a BETWEEN (or NOT BETWEEN).  
> Note that a NOT BETWEEN would simply wrap a BetweenCriteria with a Not.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: https://jira.jboss.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira