FWD » Core Development » Database

Consider the following statement:

FOR EACH Customer WHERE CAN-FIND(
        FIRST Order WHERE Order.CustNum = Customer.CustNum 
                    AND CAN-FIND(
            FIRST OrderLine WHERE OrderLine.OrderNum = Order.OrderNum
                              AND CAN-FIND(
                FIRST Item WHERE Item.ItemNum = OrderLine.ItemNum
            )
        )
    ):
    MESSAGE Customer.CustNum.
END. 

The query is converted to (for MariaDB, formatted):

select customer.recid from Customer__Impl__ as customer 
where (
   exists(
      from Order___Impl__ as order__1 
      where checkError_BB(initError_B(false),  (order__1.custNum = customer.custNum or order__1.custNum is null and customer.custNum is null) 
                 and exists(
                        from OrderLine__Impl__ as orderLine_1 
                        where checkError_BB(initError_B(false), orderLine_1.orderNum is null 
                                   and exists(
                                       from Item__Impl__ as item_1 where item_1.itemNum is null
                                    )
                              )
            )
        )
   )
)  
order by customer.custNum asc

In addition at runtime we get an exception:

Caused by: java.util.NoSuchElementException
        at java.util.ArrayDeque.removeFirst(ArrayDeque.java:285)
        at java.util.ArrayDeque.pop(ArrayDeque.java:522)
        at com.goldencode.p2j.persist.orm.FqlToSqlConverter$2.ascent(FqlToSqlConverter.java:2195)
        at com.goldencode.ast.AnnotatedAst$1.notifyListenerLevelChanged(AnnotatedAst.java:2734)
        at com.goldencode.ast.AnnotatedAst$1.hasNext(AnnotatedAst.java:2643)
        at com.goldencode.p2j.persist.orm.FqlToSqlConverter.generateExpression(FqlToSqlConverter.java:2535)
        at com.goldencode.p2j.persist.orm.FqlToSqlConverter.generateWhere(FqlToSqlConverter.java:2088)
        at com.goldencode.p2j.persist.orm.FqlToSqlConverter.processSelect(FqlToSqlConverter.java:1418)
        at com.goldencode.p2j.persist.orm.FqlToSqlConverter.processStatement(FqlToSqlConverter.java:912)
        at com.goldencode.p2j.persist.orm.FqlToSqlConverter.generateExpression(FqlToSqlConverter.java:2525)
        at com.goldencode.p2j.persist.orm.FqlToSqlConverter.generateWhere(FqlToSqlConverter.java:2088)
        at com.goldencode.p2j.persist.orm.FqlToSqlConverter.processSelect(FqlToSqlConverter.java:1418)
        at com.goldencode.p2j.persist.orm.FqlToSqlConverter.processStatement(FqlToSqlConverter.java:912)
        at com.goldencode.p2j.persist.orm.FqlToSqlConverter.toSQL(FqlToSqlConverter.java:599)
        at com.goldencode.p2j.persist.orm.Query.lambda$createSqlQuery$0(Query.java:355)
        at com.goldencode.p2j.jmx.NanoTimer.timer(NanoTimer.java:129)
        at com.goldencode.p2j.persist.orm.Query.createSqlQuery(Query.java:370)
        at com.goldencode.p2j.persist.orm.Query.list(Query.java:298)
        at com.goldencode.p2j.persist.Persistence.list(Persistence.java:1512)
        at com.goldencode.p2j.persist.ProgressiveResults.getResults(ProgressiveResults.java:1112)
        at com.goldencode.p2j.persist.ProgressiveResults.moveTo(ProgressiveResults.java:943)
        at com.goldencode.p2j.persist.ProgressiveResults.moveTo(ProgressiveResults.java:801)
        at com.goldencode.p2j.persist.ProgressiveResults.next(ProgressiveResults.java:415)
        at com.goldencode.p2j.persist.ResultsAdapter.next(ResultsAdapter.java:161)
        at com.goldencode.p2j.persist.AdaptiveQuery.next(AdaptiveQuery.java:1676)
        at com.goldencode.p2j.persist.PreselectQuery.next(PreselectQuery.java:2621)

Tested with 6129c/14808.

The problem is at the program conversion (not only runtime).
The query from the previous noye is converted to

            query0.initialize(customer, "exists(from Order_ as order_ where order_.custNum = customer.custNum and exists(from OrderLine as orderLine where orderLine.orderNum = ? and exists(from Item as item where item.itemNum = ?)))", null, "customer.custNum asc", new Object[]
            {
               (P2JQuery.Parameter) () -> order_.getOrderNum(),
               (P2JQuery.Parameter) () -> orderLine.getItemNum()
            });
            query0.addExternalBuffers(item, orderLine, order_);

Constantin Asofiei wrote:

What's the problem with the query? What would be the correct variant? If you change the FQL manually to the correct version, does this solve the runtime issue?

I understand that tested exists would not contain parameters - like the first one.I have not tryied to change it manually since I was working on a different task.
Should I start analyzing this issue?
Thank you.

Igor Skornyakov wrote:

Constantin Asofiei wrote:

What's the problem with the query? What would be the correct variant? If you change the FQL manually to the correct version, does this solve the runtime issue?

I understand that tested exists would not contain parameters - like the first one.I have not tryied to change it manually since I was working on a different task.
Should I start analyzing this issue?
Thank you.

So instead of ? it should be Order.OrderNum and OrderLine.ItemNum, respectively? If yes, just do a quick test just to check if the issue is with conversion only.

Constantin Asofiei wrote:

So instead of ? it should be Order.OrderNum and OrderLine.ItemNum, respectively? If yes, just do a quick test just to check if the issue is with conversion only.

After manual change the query to

query0.initialize(customer, "exists(from Order_ as order_ where order_.custNum = customer.custNum and exists(from OrderLine as orderLine where orderLine.orderNum = order_.orderNum and exists(from Item as item where item.itemNum = orderLine.itemNum)))", null, "customer.custNum asc");

select customer.recid from Customer__Impl__ as customer 
where (exists(
    from Order___Impl__ as order__1 
    where checkError_BB(initError_B(false), (order__1.custNum = customer.custNum or order__1.custNum is null and customer.custNum is null)
     and exists(
         from OrderLine__Impl__ as orderLine_1 
         where checkError_BB(initError_B(false), (orderLine_1.orderNum = order__1.orderNum or orderLine_1.orderNum is null and order__1.orderNum is null) 
           and exists(
               from Item__Impl__ as item_1 
               where (item_1.itemNum = orderLine_1.itemNum or item_1.itemNum is null and orderLine_1.itemNum is null)))))))  
order by customer.custNum asc

Caused by: java.util.NoSuchElementException
        at java.util.ArrayDeque.removeFirst(ArrayDeque.java:285)
        at java.util.ArrayDeque.pop(ArrayDeque.java:522)
        at com.goldencode.p2j.persist.orm.FqlToSqlConverter$2.ascent(FqlToSqlConverter.java:2196)
        at com.goldencode.ast.AnnotatedAst$1.notifyListenerLevelChanged(AnnotatedAst.java:2734)
        at com.goldencode.ast.AnnotatedAst$1.hasNext(AnnotatedAst.java:2643)
        at com.goldencode.p2j.persist.orm.FqlToSqlConverter.generateExpression(FqlToSqlConverter.java:2536)
        at com.goldencode.p2j.persist.orm.FqlToSqlConverter.generateWhere(FqlToSqlConverter.java:2089)
        at com.goldencode.p2j.persist.orm.FqlToSqlConverter.processSelect(FqlToSqlConverter.java:1419)
        at com.goldencode.p2j.persist.orm.FqlToSqlConverter.processStatement(FqlToSqlConverter.java:913)
        at com.goldencode.p2j.persist.orm.FqlToSqlConverter.generateExpression(FqlToSqlConverter.java:2526)
        at com.goldencode.p2j.persist.orm.FqlToSqlConverter.generateWhere(FqlToSqlConverter.java:2089)
        at com.goldencode.p2j.persist.orm.FqlToSqlConverter.processSelect(FqlToSqlConverter.java:1419)
        at com.goldencode.p2j.persist.orm.FqlToSqlConverter.processStatement(FqlToSqlConverter.java:913)
        at com.goldencode.p2j.persist.orm.FqlToSqlConverter.toSQL(FqlToSqlConverter.java:600)

Should I start working on this?
Thank you.

One more thing: is this a regression in 6129c compared to trunk?

Constantin Asofiei wrote:

One more thing: is this a regression in 6129c compared to trunk?

No, I get the same exception with the trunk

Igor Skornyakov wrote:

Constantin Asofiei wrote:

One more thing: is this a regression in 6129c compared to trunk?

No, I get the same exception with the trunk

The conversion is also broken with the trunk.

Thank you for checking, you can continue with your normal tasks.

The nested CAN-FIND is an old issue. There were some fixes in time (see #3040, #3465, #3771, and others) and the issue seemed fixed lately.

The (manual) fix in #7113-6 is correct. This is what the conversion should have generated.

The problem which causes the NoSuchElementException is an unbalanced access to aliases stack. In processSelect there is a pair: aliases.push(topLevelAliasMap); and then down if (aliases.pop() != topLevelAliasMap) which should do that and also validate if the process was correct. However, there is another aliases.pop(); in generateExpression(), as the stack shows. I think that is not correct (any more).

After removing it the query seems to be almost correct. There are some extra RPARENS ()) added at the end of the WHERE predicate. It seems to be that they come from the "emulate the ASCENT" block: some nodes are re-walked multiple times causing these tokens to be added to sb output.

Ovidiu Maxiniuc wrote:

The nested CAN-FIND is an old issue. There were some fixes in time (see #3040, #3465, #3771, and others) and the issue seemed fixed lately.

The (manual) fix in #7113-6 is correct. This is what the conversion should have generated.

The problem which causes the NoSuchElementException is an unbalanced access to aliases stack. In processSelect there is a pair: aliases.push(topLevelAliasMap); and then down if (aliases.pop() != topLevelAliasMap) which should do that and also validate if the process was correct. However, there is another aliases.pop(); in generateExpression(), as the stack shows. I think that is not correct (any more).

After removing it the query seems to be almost correct. There are some extra RPARENS ()) added at the end of the WHERE predicate. It seems to be that they come from the "emulate the ASCENT" block: some nodes are re-walked multiple times causing these tokens to be added to sb output.

Thank you, Ovidiu!

Igor Skornyakov wrote:

Fixed nested CAN-FIND conversion (see attached .diff).
Please review.
Thank you.

Working on runtime issue (#7113-6).

Sorry, the converted code is still not correct.

Created task branch 7113a.

Committed fix for the nested CAN-FIND to 7113a/14485.

The only remaining issue is that generated SQL statement contains extra closing brackets ) at the end of the WHERE clause.
Working on it.

Ovidiu Maxiniuc wrote:

Igor,
I have fixed the extra closing parenthesis as part of another task. Please do not put additional effort in that.

I had a look at 7113a/14896, but I did not do a full review. What I did instead was I put the testcase from note-1 to test, without your patch. It converted as you described and, with my runtime patch, it executed without errors. I do not see anything wrong with the conversion.

I recommend to put the status of this task to "duplicate".

Ovidiu,
Sorry, but I do not understand. The conversion w/o my patch was incorrect and, AFAIR, you agreed with this. The fact that runtime changes makes it run w/o errors doesn't make it logically correct. Please double check your fix.

I stopped working on this two months ago waiting for a code review.

BTW: in what branch have you made your changes?
Thank you.

Sorry, but I do not understand. The conversion w/o my patch was incorrect and, AFAIR, you agreed with this. The fact that runtime changes makes it run w/o errors doesn't make it logically correct. Please double check your fix.

I am sorry. I read the task in FFD mode, being focused on the runtime issue and skipped the lines about conversion. Indeed, the current conversion is incorrect (I can confirm with my local test) and must be fixed. In my test I analysed the work done by fql2sql converter. The FQL expression it received was syntactically correct although - as you rightfully said - not correct from the POV of business logic.

I stopped working on this two months ago waiting for a code review.

OK, I will do the review for it as I have finish my other urgent tasks.

BTW: in what branch have you made your changes?

The branch is 7259a.

Eric Faulhaber wrote:

Ovidiu, 7259a was merged to trunk some time ago. Did it fix this issue?

7259a contained changes in FQL processor, which happen at runtime, in a later stage of query processing. I remember it fixed that issue.

If not, are the changes in 7113a needed?

I looked at 7113a now (for the first time, apparently). Beside the runtime issue I notified Igor to avoid work duplication in FqlToSqlConverter because I fixed in 7259a, there are some changes at conversion (where_clause_prep.rules) and FqlPreprocessor level (about UDFs ?). I will analyse the testcase from first note with current FWD to understand what Igor was looking at. There is a short discussion with Constantin about it in notes 2-6.