[dna-issues] [JBoss JIRA] Commented: (DNA-552) Support BETWEEN and NOT BETWEEN in WHERE clause of SQL language and abstract query model
[
https://jira.jboss.org/jira/browse/DNA-552?page=com.atlassian.jira.plugin...
]
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