not function is not supported

[wouterv]

The not() function implementation does not exist in orientdb. There is a keyword NOT that can be used, but looks like that it isnt implemented.

When you try to use it using Clause.not(..) you get:

Caused by: com.orientechnologies.orient.core.sql.OCommandSQLParsingException: Error on parsing command at position #0: No function with name 'not', available names are : [outv,percentile,ifnull,both,count,last,set,traversedvertex,sum,bothv,date,shortestpath,mode,distinct,distance,document,if,gremlin,map,bothe,avg,dijkstra,min,coalesce,encode,max,traversedelement,eval,ine,list,variance,label,format,in,difference,median,decode,unionall,sysdate,intersect,inv,stddev,uuid,traversededge,first,oute,out]

[raymanrt]

Hi, I think it's a problem with the Orient query parsers, since this query works: SELECT FROM Person WHERE NOT( label = 'riccardo' OR label = 'wouterv')

But this one doesn't: SELECT FROM Person WHERE NOT( label = 'riccardo')

Probably orientqb should write: SELECT FROM Person WHERE NOT label = 'riccardo' Which seems to work.

[raymanrt]

Ok, I understood the query parser bug! This works: SELECT FROM Person WHERE NOT (label = 'riccardo') This doesn't work: SELECT FROM Person WHERE NOT(label = 'riccardo')

Please note the space between NOT and boolean expression.

Hopefully with the new Orient query parser this will be fixed, but I will adapt orientqb to write the "right" version of the query. This is one motivation of orientqb. Sometimes it helps to give query parser good queries.

[raymanrt]

Hi, this is fixed: could you please try the orientqb 0.1.1-SNAPSHOT?

You can build it from master branch of github or download the jars from maven.

[wouterv]

@raymanrt nice catch. It works now, thanks! Can you create a release with this?

And before you do, please take a look at my pull request ;-)

[raymanrt]

Sorry for the wait, but here's 0.1.2 stable version. Hope everything is ok, I've also merged your pull request.

The master goes on with 0.1.3-SNAPSHOT.

[ggeremy]

I have a similar problem with the OrientDB parser (2.1.4) I tried with OrientDB 2.2.3 and the probleme is not corrected.

I have a query which throws a ClassCastException: core.sql.filter.OSQLFilterItemField cannot be case to core.sql.filter.OSQLFilterCondition The query is:

SELECT FROM V1 WHERE attr1 = 'value' AND
( out('MyEdge') IN (SELECT FROM V2 WHERE KEY IN ['value1', 'value2'] OR out('MyEdge').size() = 0 )
AND NOT (attr2 IN ['v1', 'v2'])

If I put the last NOT clause at first position after the WHERE, the exception is not throwed

I tried different solution and found that put parenthesis around the NOT clause works I tried the NOT IN operator (I created an issue for this #12 ), it worked but only if nested in parenthesis too. If no parenthesis the parser throws an OCommandSQLParsingException: Invalid Keyword

Maybe a new NOT_IN operator is not a solution because it's the OrientDB parser which makes mistakes. But create a nested clause would do the trick ? As for nested Query with Target.nested(Query q) ?

Thanks you Geremy

Sorry for my bad english

[gibrit]

i am using version 0.1.9 ...

The query with select from model where modelYear=2007 and not name like '%4WD%' is not working ( which is build by query builder)

select from model where modelYear=2007 and not (name like '%4WD%')

the query is working

Simple not more usable?

    public static final Clause not(Clause clause) {

        return new CustomFormatClause(NOT, clause);
    }

[raymanrt]

@ggeremy is the problem solved with the introduction of NOT IN operator?

@gibrit I would open an issue to the orientdb team: I don't like the idea of writing less readable queries, but I could also add some kind of workaround such as: not(complexClause("name", Operator.EQ, "%4WD%")) Or probably better: notWithParenthesis(clause("name", Operator.EQ, "%4WD%")) What do you think about it?

[gibrit]

notWithParenthesis(clause("name", Operator.EQ, "%4WD%")) cluase is easier to remember than complexClause Maybe use a boolean for parenthesis not(complexClause("name", Operator.EQ, "%4WD%"), true) ; default value of boolean is

public static final Clause not(Clause clause) {

    return not(clause, false) ;
}

public static final Clause not(Clause clause, boolean withParanthesis) {

    return new CustomFormatClause(NOT, clause);
}

[raymanrt]

The suggestion of using a boolean is good, I've pushed the fix. Thanks!