[FreeSql.Provider.ClickHouse] Select.WithSql() => Syntax error: ('@')

[ktb-dev]

问题描述及重现代码：

var query = _freeSql.GetRepository<MyClass>().Select.WithSql(SqlQuery(), new Dictionary<string, object>
{
    { "@prop1", prop1 },
    { "@prop2", prop2 },
    { "@prop3", prop3 }
}).AsQueryable();

数据库版本

ClickHouse: 23.3.2.37

安装的Nuget包

"FreeSql.Provider.ClickHouse" Version="3.2.833"

.net framework/. net core？ 及具体版本

net8.0

Error message:

Code: 62. DB::Exception: Syntax error: failed at position 785 ('@') (line 17, col 38): @Prop1
AND toDate(received) >= @Prop1
. Expected one of: CAST operator, ANY, ALL, NOT, INTERVAL, CASE, DATE, TIMESTAMP, tuple, collection of literals, array, number, literal, NULL, Bool, true, false, string literal, asterisk, qualified asterisk, compound identifier, list of elements, identifier, COLUMNS matcher, COLUMNS, qualified COLUMNS matcher, substitution, MySQL-style global variable. (SYNTAX_ERROR) (version 23.3.2.37 (official build))

---

To fix the bug, need to remove the "@" symbol from the parameter name:

FreeSql/Providers/FreeSql.Provider.ClickHouse/ClickHouseUtils.cs

Line 66 in a7ddaff

DbParameter ret = new ClickHouseDbParameter { ParameterName = $"@{name}", Value = value };

DbParameter ret = new ClickHouseDbParameter { ParameterName = $"{name}", Value = value };

[2881099]

ToLower

[ktb-dev]

ToLower

ah, no. My parameter name is written with a lowercase letter, everywhere it appears in the code.

The thing is that the github editor read @Prop1 as a nickname, and that's why my error message showed the name 'prop1' with an uppercase letter.

[2881099]

{
{ "prop1", prop1 },
{ "prop2", prop2 },
{ "prop3", prop3 }
}

[2881099]

My clickhouse server is not available here, but I tested the code using SQL Server and it worked fine.

var result1 = fsql.Select<object>().WithSql("select @Prop1 as1", new { Prop1 = 1 }).ToList();
var result2 = fsql.Select<object>().WithSql("select @Prop1 as1", new Dictionary<string, object> { { "Prop1", 1 } }).ToList();

[ktb-dev]

var result1 = fsql.Select<object>().WithSql("select @Prop1 as1", new { Prop1 = 1 }).ToList();
var result2 = fsql.Select<object>().WithSql("select @Prop1 as1", new Dictionary<string, object> { { "Prop1", 1 } }).ToList();

debug screenshot with an exception

FreeSql config:

 builder.Services.AddSingleton((provider) =>
     { 
         var connectionString = GetConnectionString(provider);
         return new FreeSqlBuilder()
             .UseConnectionString(DataType.ClickHouse, connectionString)
             .Build();
     }) ;

An exception was also thrown here:

var result3 = await _freeSql.Ado.QueryAsync<object>("select @Prop1 as1, @prop2 as2", new { Prop1 = 1, prop2 = 2 });

But with such a select, everything was successful:

ClickHouseDbParameter[] dbParams = [
    DbParameter("Prop1", 1), 
    DbParameter("prop2", 2)
];
var result4 = await _freeSql.Ado.QueryAsync<object>(System.Data.CommandType.Text, "select @Prop1 as1, @prop2 as2", dbParams);

@2881099, doesn't that seem strange to you?

[ktb-dev]

but I tested the code using SQL Server and it worked fine.

the issue arises precisely when executing a select with parameters via the clickhouse client