Incorrect SQL generated on Update

Posts   
 
    
Benjamin23
User
Posts: 26
Joined: 15-Nov-2017
# Posted on: 16-Jan-2018 14:55:16   

Hi,

I'm having problems with the query generated on update call.

Here are my specs:

LLBLGen Pro 4.2 Final Runtime version is 4.2.16.725 Database is PostgreSQL 9.4 and I'm using npgsql 3.2.5

The table structure in the case is the following: EventEntry table has a relation to Event table(EventID).

Here is the code I'm trying to run:


RelationPredicateBucket bucket = new RelationPredicateBucket();

bucket.PredicateExpression.Add(EventEntryFields.Id == entity.Id);

PredicateExpression pe = new PredicateExpression(); 
                bucket.Relations.Add(EventEntity.Relations.EventEntryEntityUsingEventId);
pe.AddWithOr(EventFields.Abrv == "Some abrv here");
bucket.PredicateExpression.AddWithAnd(pe);

uow.AddUpdateEntitiesDirectlyCall(entity, bucket);
uow.CommitAsync(adapter, true);

Here is the SQL that get's generated:


UPDATE "public"."EventEntry" LLBL_UT__0
SET "Title" = :p1,
       "Description" = :p2,
       "AuthorId" = :p3,
       "EventId" = :p4,
       "StartTime" = :p5,
       "EndTime" = :p6,
       "IsAllDay" = :p7,
       "IsRecurring" = :p8,
       "Json" = :p9,
       "DateCreated" = :p10,
       "DateUpdated" = :p11,
       "TimeStamp" = :p12
WHERE  EXISTS
       (SELECT *
        FROM   ( "public"."Event"
                 INNER JOIN "public"."EventEntry"
                     ON "public"."Event"."Id" = "public"."EventEntry"."EventId")
        WHERE  LLBL_UT__0."EventId" = "public"."Event"."Id"
           AND ((("public"."EventEntry"."Id" = :p13
                  AND ("public"."Event"."Abrv" = :p14))))) 

The problem is that this code will update all entites that are referencing the Event, and I need to update only the one based on the p13 value.

The script is working fine if the p13 comparison changes from "public"."EventEntry"."Id" to LLBL_UT__0."Id" so it reads LLBL_UT__0."Id" = p13

Can you please advise on how I can avoid this issue and only get the one item updated. I've tried adding aliases but they didn't work.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 16-Jan-2018 16:24:51   

Would it make a difference if you walk the relation the other way around?

i.e. bucket.Relations.Add(EventEntryEntity.Relations.EventEntity...);

Benjamin23
User
Posts: 26
Joined: 15-Nov-2017
# Posted on: 17-Jan-2018 08:00:06   

Walaa wrote:

Would it make a difference if you walk the relation the other way around?

i.e. bucket.Relations.Add(EventEntryEntity.Relations.EventEntity...);

I've tried both ways and the query is just slightly different but the same issue is present.

In the example above, I've actually made a little mistake it should be


bucket.Relations.Add(EventEntryEntity.Relations.EventEntityUsingEventId);

instead of


bucket.Relations.Add(EventEntity.Relations.EventEntryEntityUsingEventId);

But I've tested both cases and the end result is the same.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 17-Jan-2018 11:39:02   

I can't reproduce it.


[Test]
public void UpdateEntityDirectlyTest()
{
    var toUpdate = new OrdersEntity();
    toUpdate.Orderid = 10254;
    toUpdate.Employeeid = 4;
    var bucket = new RelationPredicateBucket();
    bucket.Relations.Add(OrdersEntity.Relations.CustomersEntityUsingCustomerid);
    bucket.PredicateExpression.Add(OrdersFields.Orderid == toUpdate.Orderid);
    var pe = new PredicateExpression(CustomersFields.Country=="Germany");
    bucket.PredicateExpression.AddWithAnd(pe);
    using(var adapter = new DataAccessAdapter())
    {
        try
        {
            adapter.StartTransaction(IsolationLevel.ReadCommitted, "Trans");
            Assert.AreEqual(1, adapter.UpdateEntitiesDirectly(toUpdate, bucket));
        }
        finally
        {
            adapter.Rollback();
        }
    }
}

2 order rows in the table, all customers of northwind (91 rows, 9 from Germany) and this succeeds:


UPDATE "public"."orders" LLBL_UT__0
SET "employeeid" = 4 /* :p1 */
WHERE  EXISTS
       (SELECT *
        FROM   ("public"."customers"
                INNER JOIN "public"."orders"
                    ON "public"."customers"."customerid" = "public"."orders"."customerid")
        WHERE  LLBL_UT__0."customerid" = "public"."customers"."customerid"
           AND ((("public"."orders"."orderid" = 10254 /* :p2 */
                  AND ("public"."customers"."country" = 'Germany' /* :p3 */)))))

The SQL is identical (in structure) to yours, or am I overlooking something?

The query works because the PK filter is on the orders table which is joined again and as this is a PK filter, it filters the inner join. It's a bit convoluted but gets the job done. The main question I have is: how can my query affect just 1 row and yours all rows? simple_smile

If you just run the inner select (in teh postgresql query tool) (and comment out the 'where LLBL_UT'), it should still result in just 1 row, does that select result in 1 row?

Frans Bouma | Lead developer LLBLGen Pro
Benjamin23
User
Posts: 26
Joined: 15-Nov-2017
# Posted on: 17-Jan-2018 12:43:46   

I've tried modifying your code to the following format


SELECT * from "public"."orders" LLBL_UT__0
WHERE EXISTS
     (SELECT *
        FROM ("public"."customers"
                INNER JOIN "public"."orders"
                    ON "public"."customers"."customerid" = "public"."orders"."customerid")
        WHERE LLBL_UT__0."customerid" = "public"."customers"."customerid"
         AND ((("public"."orders"."orderid" = 10254 /* :p2 */
                 AND ("public"."customers"."country" = 'Germany' /* :p3 */)))))

And it's returning all items and not just the ones where the parent country is Germany and OrderId 10254 in the above example.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 17-Jan-2018 16:01:30   

Ok, this is really strange. I run this query,

SELECT * FROM "public"."orders" LLBL_UT__0
WHERE EXISTS
     (SELECT *
        FROM ("public"."customers"
                INNER JOIN "public"."orders"
                    ON "public"."customers"."customerid" = "public"."orders"."customerid")
        WHERE LLBL_UT__0."customerid" = "public"."customers"."customerid"
         AND ((("public"."orders"."orderid" = 10254 /* :p2 */
                 AND ("public"."customers"."country" = 'Germany' /* :p3 */)))))

and get 1 row. 10254;"DRACD";2;"";"";"";;;"";"";"";"";"";""

I run

SELECT * FROM "public"."orders" LLBL_UT__0

I get 2 rows. 10254;"DRACD";2;"";"";"";;;"";"";"";"";"";"" 10255;"CHOPS";3;"";"";"";;;"";"";"";"";"";""

It's IMHO logical it will return just 1 row (due to the PK filter on public.orders.orderid). Do you know anything about what the difference is caused by? I can't see how it would return multiple rows to be honest, unless the event id isn't a pk in your situation.

Frans Bouma | Lead developer LLBLGen Pro
Benjamin23
User
Posts: 26
Joined: 15-Nov-2017
# Posted on: 17-Jan-2018 16:10:00   

EventId is not the PK on the EventEntry table in my case if that's what you are meaning.

The scenario is the following: EventEntry table has a PK Id and an FK EventId that points to Event table. Event table has a PK Id.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 17-Jan-2018 20:48:06   
"public"."EventEntry"."Id" = :p13

this is a PK predicate, correct? If so, it's impossible to get more than 1 row. In any case, we can't reproduce it so we can't look into what's wrong.

Frans Bouma | Lead developer LLBLGen Pro
Benjamin23
User
Posts: 26
Joined: 15-Nov-2017
# Posted on: 18-Jan-2018 07:35:25   

That is correct.

From what I've gathered, if I update my code from line


 AND ((("public"."EventEntry"."Id" = :p13

to


 AND (((LLBL_UT__0."Id" = :p13

Everything works.

Now, my question is, can I get the alias "LLBL_UT__0" somehow, so I can set it on the predicate that checks the Id ?

Benjamin23
User
Posts: 26
Joined: 15-Nov-2017
# Posted on: 18-Jan-2018 09:22:58   

I've tested the generated query on the adventureworks DB and I get the save results

For query :


  SELECT * from  "sales"."customer" LLBL_UT__0
  WHERE EXISTS
       (SELECT *
          FROM ( "sales"."store"  
                 INNER JOIN "sales"."customer"
                      ON  "sales"."store"."businessentityid" = "sales"."customer"."storeid")
          WHERE LLBL_UT__0."storeid" =  "sales"."store"."businessentityid"
           AND ((("sales"."customer"."customerid" = 509
                   AND ( "sales"."store"."salespersonid" =  281)))))


I get the following response

509;;316;4;"78ed169f-c38f-44f9-9abc-2a65d87c7f7d";"2014-09-12 11:15:07.263" 29496;315;316;4;"44e51ec2-9d0b-40cc-b725-1a3e81df9519";"2014-09-12 11:15:07.263"

The first collumn is customerid, so it should have returned only the first row where the customerid is 509, insted it returned all customers from that store

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 18-Jan-2018 11:09:00   

Benjamin23 wrote:

That is correct.

From what I've gathered, if I update my code from line


 AND ((("public"."EventEntry"."Id" = :p13

to


 AND (((LLBL_UT__0."Id" = :p13

Everything works.

Now, my question is, can I get the alias "LLBL_UT__0" somehow, so I can set it on the predicate that checks the Id ?

No, that's the problem, otherwise I'd have suggested that by now. If you specify the alias hard-coded it will be surrounded by "" and it mismatches the case of the alias on the main table.

I can reproduce it with the adventureworks tables, and I now see why it happens indeed. We won't fix any bugs anymore in v4 tho, so any workaround has to be done with manual coding.

One thing you could do is create your own fieldcomparevalue predicate class (copy the one from the runtime sourcecode), and allow it to get a hard-coded alias or at least strip off the "" around the alias if you do: .

bucket.PredicateExpression.Add(EventEntryFields.Id.SetSourceAlias("LLBL_UT__0") == entity.Id);

It's hacky but it's what I can give you. Altering the alias in the PK predicate is difficult as it doesn't know where the pk predicate is (it can be anywhere in the predicate expression).

We'll correct this in v5.2 and v5.3

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 18-Jan-2018 12:16:43   

Fixed in v5.2 and v5.3:

UPDATE "public"."orders" "LLBL_UT__0"
SET "employeeid" = 4 /* :p1 */
WHERE  EXISTS
       (SELECT *
        FROM   ("public"."customers"
                INNER JOIN "public"."orders"
                    ON "public"."customers"."customerid" = "public"."orders"."customerid")
        WHERE  "LLBL_UT__0"."customerid" = "public"."customers"."customerid"
           AND ((("LLBL_UT__0"."orderid" = 10254 /* :p2 */
                  AND ("public"."customers"."country" = 'Germany' /* :p3 */)))))

If you want to apply this patch to v4.2 (haven't checked if it's 1:1 applicable)

The PredicateFinder in GraphTraversers should look like:


////////////////////////////////////////////////////////////////////////////////////////////////////////
// LLBLGen Pro is (c) 2002-2016 Solutions Design. All rights reserved.
// http://www.llblgen.com
// The sourcecode for the ORM Support classes has been made available to LLBLGen Pro licensees
// so they can modify, update and/or extend it. Distribution of this sourcecode in textual, non-compiled, 
// non-binary form to non-licensees is prohibited. Distribution of binary compiled versions of this 
// sourcecode to non-licensees has been granted under the following license.
////////////////////////////////////////////////////////////////////////////////////////////////////////
// COPYRIGHTS:
// Copyright (c)2002-2011 Solutions Design. All rights reserved.
// http://www.llblgen.com
// 
// The ORM Support classes library sourcecode is released to LLBLGen Pro licensees under the 
// following license:
// --------------------------------------------------------------------------------------------
// 
// Redistribution and use of the sourcecode in compiled, binary forms, with or without modification, 
// are permitted provided that the following conditions are met: 
//
// 1) Redistributions must reproduce the above copyright notice, this list of 
//  conditions and the following disclaimer in the documentation and/or other materials 
//  provided with the distribution. 
// 2) Redistribution of the sourcecode in textual, non-binary, non-compiled form is prohibited.
// 
// THIS SOFTWARE IS PROVIDED BY SOLUTIONS DESIGN ``AS IS'' AND ANY EXPRESS OR IMPLIED WARRANTIES, 
// INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A 
// PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL SOLUTIONS DESIGN OR CONTRIBUTORS BE LIABLE FOR 
// ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT 
// NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR 
// BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, 
// STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE 
// USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. 
//
// The views and conclusions contained in the software and documentation are those of the authors 
// and should not be interpreted as representing official policies, either expressed or implied, 
// of Solutions Design. 
//////////////////////////////////////////////////////////////////////
// Contributers to the code:
//      - Frans Bouma [FB]
//////////////////////////////////////////////////////////////////////
using System;
using System.Collections.Generic;

namespace SD.LLBLGen.Pro.ORMSupportClasses
{
    /// <summary>
    /// Class which crawls over a predicateexpression and finds all predicates of the given type. 
    /// </summary>
    internal class PredicateFinder : QueryApiObjectTraverser
    {
        #region Class Member Declarations
        private List<IPredicate> _foundPredicates;
        private PredicateType _predicateTypeToFind;
        #endregion

        /// <summary>
        /// Initializes a new instance of the <see cref="PredicateFinder"/> class.
        /// </summary>
        /// <param name="traverseSetPredicateInnerSetElements">the flag to traverse the inner elements of a FieldCompareSetPredicate. (default is false). 
        /// Keep this value to false if you use the findings of this crawler to adjust an outer query, as the inner elements of a fieldcompareset are a 
        /// different scope.</param>
        /// <remarks>this ctor finds all predicates, regardless of type.</remarks>
        internal PredicateFinder(bool traverseSetPredicateInnerSetElements = false) : this(PredicateType.Undefined, traverseSetPredicateInnerSetElements)
        {
        }
    
        
        /// <summary>
        /// Initializes a new instance of the <see cref="PredicateFinder"/> class.
        /// </summary>
        /// <param name="predicateTypeToFind">The predicate type to find.</param>
        /// <param name="traverseSetPredicateInnerSetElements">the flag to traverse the inner elements of a FieldCompareSetPredicate. (default is false). 
        /// Keep this value to false if you use the findings of this crawler to adjust an outer query, as the inner elements of a fieldcompareset are a 
        /// different scope.</param>
        internal PredicateFinder(PredicateType predicateTypeToFind, bool traverseSetPredicateInnerSetElements = false)
            : base()
        {
            this.TraverseSetPredicateInnerSetElements = traverseSetPredicateInnerSetElements;
            _foundPredicates = new List<IPredicate>();
            _predicateTypeToFind = predicateTypeToFind;
        }

        
        /// <summary>
        /// Traverses the specified predicate and enclosed objects.
        /// </summary>
        /// <param name="objectToTraverse">The object to traverse.</param>
        public override void Traverse(IPredicate objectToTraverse)
        {
            if(objectToTraverse == null)
            {
                return;
            }
            if((_predicateTypeToFind==PredicateType.Undefined && objectToTraverse.InstanceType != (int)PredicateType.PredicateExpression) ||
                objectToTraverse.InstanceType == (int)_predicateTypeToFind)
            {
                _foundPredicates.Add(objectToTraverse);
            }
            base.Traverse(objectToTraverse);
        }


        #region Class Property Declarations
        /// <summary>
        /// Gets the found predicates.
        /// </summary>
        internal List<IPredicate> FoundPredicates
        {
            get { return _foundPredicates; }
        }
        #endregion
    }
}

Then in DynamicQueryEngineBase, CreateSingleTargetUpdateDQUsing, change the updatedTableAlias creation to:

string updatedTableAlias = this.Creator.CreateValidAlias("LLBL_UT__0");

Then, the first line in if(updateFilter!=null), should look like:

UpdateAliasInUpdateTablePredicates(fieldsToUpdate[0].ContainingObjectName, updateFilter, updatedTableAlias);

And you should add the following method below it in the same class:

        /// <summary>
        /// Updates the SourceAlias in the predicates in updateFilter to the alias updatedTableAlias if the predicate targets the entity name specified. 
        /// </summary>
        /// <param name="entityName"></param>
        /// <param name="updateFilter"></param>
        /// <param name="updatedTableAlias"></param>
        protected virtual void UpdateAliasInUpdateTablePredicates(string entityName, IPredicate updateFilter, string updatedTableAlias)
        {
            var finder = new PredicateFinder(traverseSetPredicateInnerSetElements:true);
            finder.Traverse(updateFilter);
            foreach(var predicate in finder.FoundPredicates)
            {
                var elements = predicate.GetFrameworkElementsInPredicate().OfType<IEntityFieldCore>();
                foreach(var element in elements)
                {
                    if(element.ContainingObjectName==entityName)
                    {
                        predicate.ObjectAlias = updatedTableAlias;
                        break;
                    }
                }
            }
        }

That aliases the target table properly with quotes, and adds an alias to all predicates which target the target table.

Frans Bouma | Lead developer LLBLGen Pro
Benjamin23
User
Posts: 26
Joined: 15-Nov-2017
# Posted on: 18-Jan-2018 12:56:53   

Thanks for the reply,

Can you let me know if the fix is released in the 5.3 versions already?

We are considering upgrading our project to 5.3 if the fix is already implemented there.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 18-Jan-2018 13:36:53   

Benjamin23 wrote:

Thanks for the reply,

Can you let me know if the fix is released in the 5.3 versions already?

We are considering upgrading our project to 5.3 if the fix is already implemented there.

The hotfix for the runtimes will be pushed to nuget later today after we've added some other changes as well.

Frans Bouma | Lead developer LLBLGen Pro