SQL Parser in 2.1+ causes a parsing error with a SQL in version 1.7.8

37 views
Skip to first unread message

Hung Tran

unread,
Jan 11, 2016, 1:49:58 PM1/11/16
to orient-...@googlegroups.com
Hi,

I am migrating from version 1.7.8 to version 2.1.8, the SQL below does not working any more

SELECT IFNULL(@rid, '') AS Id,
    IFNULL(Number, '') AS Number,
    IFNULL(IssuedDate, '') AS IssuedDate,
    IFNULL(Title, '') AS Title,
    eval('SubTotal - DiscountAmount') AS Taxable,
    IFNULL(GrandTotal, '') AS GrandTotal,
    IFNULL(Status, '') AS Status,
    IFNULL(1, '') AS __metadata,
    IFNULL($v0, '') AS __xf8,
    IFNULL($v1, '') AS __xf9,
    IFNULL($v2, '') AS __xf10,
    IFNULL($v3, '') AS __xf11,
    IFNULL($v4, '') AS __xf12,
    IFNULL($v5, '') AS __xf13,
    IFNULL($v6, '') AS __xf14
FROM Invoice 
 LET $v0 = $__currentProjector.@this.CurrencyCode,
     $v1 = $__currentProjector.@this.IssuedTo,
     $v2 = $__currentProjector.@this.IsInvalidated,
     $v3 = $__currentProjector.@this.IssuedTo.Username,
     $v4 = $__currentProjector.@this.ToAccount.Username,
     $v5 = $__currentProjector.@this.InvalidatedInvoice.Number, $v6 = $__currentProjector.@this.OriginalInvoice.Number,
     $__currentProjector = (SELECT FROM $parent.$current)
WHERE((IssuedTo = #20:0 OR ToAccount= #20:0) AND (NOT (Number LIKE '__temp_' + '%'))) AND EntityInfo.State= 0 
ORDER BY IssuedDate DESC LIMIT 10

An error is

com.orientechnologies.orient.core.sql.OCommandSQLParsingException: Error on parsing command at position #0: Error parsing query: SELECT IFNULL(@rid,'') AS Id,IFNULL(Number,'') AS Number,IFNULL(IssuedDate,'') AS IssuedDate,IFNULL(Title,'') AS Title,eval('SubTotal - DiscountAmount') AS Taxable,IFNULL(GrandTotal,'') AS GrandTotal,IFNULL(Status,'') AS Status,IFNULL(1,'') AS __metadata,IFNULL($v0,'') AS __xf8,IFNULL($v1,'') AS __xf9,IFNULL($v2,'') AS __xf10,IFNULL($v3,'') AS __xf11,IFNULL($v4,'') AS __xf12,IFNULL($v5,'') AS __xf13,IFNULL($v6,'') AS __xf14 FROM Invoice LET $v0 = $__currentProjector.@this.CurrencyCode,$v1 = $__currentProjector.@this.IssuedTo,$v2 = $__currentProjector.@this.IsInvalidated,$v3 = $__currentProjector.@this.IssuedTo.Username,$v4 = $__currentProjector.@this.ToAccount.Username,$v5 = $__currentProjector.@this.InvalidatedInvoice.Number,$v6 = $__currentProjector.@this.OriginalInvoice.Number,$__currentProjector = (select from $parent.$current) WHERE ((IssuedTo=#20:0 OR ToAccount=#20:0) AND (NOT (Number LIKE '__temp_' + '%'))) AND EntityInfo.State=0 ORDER BY IssuedDate DESC LIMIT 10 Encountered " "." ". "" at line 1, column 471.
 Was expecting one of:
 <EOF> 
 <WHERE> ...
 <ORDER> ...
 <GROUP> ...
 <LIMIT> ...
 <SKIP2> ...
 <OFFSET> ...
 <TIMEOUT> ...
 <FETCHPLAN> ...
 <LOCK> ...
 <NOCACHE> ...
 <PARALLEL> ...
 <UNWIND> ...
 ";" ...
 "," ...
 "," ...
 <WHERE> ...
 <GROUP> ...
 <ORDER> ...
 <UNWIND> ...
 <SKIP2> ...
 <OFFSET> ...
 <LIMIT> ...
 <FETCHPLAN> ...
 <TIMEOUT> ...
 <LOCK> ...
 <PARALLEL> ...
 <NOCACHE> ...
 <NOCACHE> ...

What is the correct syntax in version 2.1+ for this case?

I have tried to keep it in simple version

SELECT FROM Invoice LET $__current = (SELECT FROM $parent.$current), $v1 = __current.@this.CurrencyCode

but it is still getting a parsing error.

Any help will be very appreciated!

My Best,
Hung Tran


acsandeep

unread,
Jan 12, 2016, 4:05:08 AM1/12/16
to OrientDB
Hi,

See this link

ALTER DATABASE custom strictSql=false

You might have to rewrite some of your syntax.

Regards
S

Luigi Dell'Aquila

unread,
Jan 12, 2016, 4:06:55 AM1/12/16
to orient-...@googlegroups.com
Hi,

the problem seems to be in the ".@this", but does it actually make sense? What's the reason to have that in the query?

Thanks

Luigi


Hi,

Any help will be very appreciated!

My Best,
Hung Tran


--

---
You received this message because you are subscribed to the Google Groups "OrientDB" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orient-databa...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Reply all
Reply to author
Forward
0 new messages