Join on concatenated string column doesn't get any result

ServiceStack.OrmLite PostgreSQL
1

Hi, I have a problem on executing the following SQL

var query = db.From<CustomerOrder>(db.TableAlias("order"))
                .Join<CustomerOrder>((c, a) => addOnPrefix + c.Name == a.Name, db.TableAlias("addon"))
var orders = await db.SelectAsync(query);

It returns always an emtpy list.
But when I convert the generated SQL toString and use SqlListAsync it returns the requested data.

async Task<List<CustomerOrder>> ExecuteQuery(ISqlExpression sqlExpression)
{
    var sql = sqlExpression.ToSelectStatement();
    for (var i = 0; i < sqlExpression.Params.Count; ++i)
    {
        sql = sql.Replace(":" + i, sqlExpression.Params[i].Value.ToString());
    }

    return await db.SqlListAsync<CustomerOrder>(sql);
}

could it be that there is internally an exception, which is silently catched ?

2

There are a number of ways to view the generated SQL in OrmLite’s Logging and Introspection docs, e.g. inside a test you can log generated SQL with:

OrmLiteUtils.PrintSql();

Can you inspect the generated SQL then try executing it in your RDBMS directly to see if it returns any results.

3

Hi @mythz,

I did some additional debugging, to visualize the problem a bit better:

image
image1061×360 61 KB

In this code, I first execute the query using SelectAsync which returns no results.
The generated log is:

[2023-06-01 08:42:42.558 DBG][ServiceStack.OrmLite.OrmLiteReadCommandExtensionsAsync] SQL: SELECT "order"."id", "order"."name", "order"."description", "order"."assigned_user", "order"."state", "order"."created_by", "order"."created_date", "order"."modified_by", "order"."modified_date", "order"."finished_date", "order"."customer_order_grouping_id"
FROM "customer_order" "order" INNER JOIN "customer_order" "addon" ON (:0 || "order"."name" = "addon"."name")
PARAMS: :0='Addon_'

In the Line after that, I convert the SQL to string and execute the string using SqlListAsync, which return 50 orders.

The log shows:

[2023-06-01 08:42:42.560 DBG][ServiceStack.OrmLite.OrmLiteReadCommandExtensionsAsync] SQL: SELECT "order"."id", "order"."name", "order"."description", "order"."assigned_user", "order"."state", "order"."created_by", "order"."created_date", "order"."modified_by", "order"."modified_date", "order"."finished_date", "order"."customer_order_grouping_id"
FROM "customer_order" "order" INNER JOIN "customer_order" "addon" ON ('Addon_' || "order"."name" = "addon"."name")

In the end, it’s the same SQL but without parameterisation.

I am very confused about that, and it looks for me like a bug

4

Yeah it’s trying to use parameterization to dynamically constructing symbol names when they should only be for parameterizing values, which the expression visitor that generates the parameterized SQL wont be able to differentiate from.

I’m confused by the intent here since it looks like it’s trying to reference a table that doesn’t exist, either way you should be able to achieve similar to your generated SQL output with:

var query = db.From<CustomerOrder>(db.TableAlias("order"))
    .Join<CustomerOrder>((c,a) => Sql.TableAlias(c.Name, addOnPrefix + "order") == a.Name, db.TableAlias("addon"));

Otherwise you’re likely going to use Custom SQL to generate that fragment of the query.

FYI OrmLite already has a built-in ToMergedParamsSelectStatement() method to serialize a typed SqlExpression into a SQL statement with its DB parameters merged, e.g:

var sql = sqlExpression.ToMergedParamsSelectStatement();
5

sorry for the confusion, i forgot to add the layout of the CustomerOrders table:

public class CustomerOrder : IIndexable, IAttributes<CustomerOrderAttribute>, IAudit
    {
        [AutoIncrement]
        public long Id { get; set; }

        [Required, Index, Unique]
        public string Name { get; set; }
        
        public string Description { get; set; }
        
        // .....
}

Name is in the sql not the name of the table (or alias) it’s a column. I join on the table itself.

The table contains for example orders with and without the addon prefix in the Name:
image

Thanks to point me the existing ToMergedParamsSelectStatement() function.

I’ll use it for the workaround