search

dotnetcore / FreeSql

🦄 .NET aot orm, C# orm, VB.NET orm, Mysql orm, Postgresql orm, SqlServer orm, Oracle orm, Sqlite orm, Firebird orm, 达梦 orm, 人大金仓 orm, 神通 orm, 翰高 orm, 南大通用 orm, 虚谷 orm, 国产 orm, Clickhouse orm, DuckDB orm, TDengine orm, QuestDB orm, MsAccess orm.
https://freesql.net
MIT License
4.12k stars 859 forks source link

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

Open ktb-dev opened 3 weeks ago

ktb-dev commented 3 weeks ago

问题描述及重现代码：

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:

https://github.com/dotnetcore/FreeSql/blob/a7ddaff4707e9cd5a8e30789b51a6187f87baeaa/Providers/FreeSql.Provider.ClickHouse/ClickHouseUtils.cs#L66

DbParameter ret = new ClickHouseDbParameter { ParameterName = $"{name}", Value = value };
2881099 commented 3 weeks ago

ToLower

ktb-dev commented 3 weeks ago

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 commented 3 weeks ago

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

2881099 commented 3 weeks ago

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 commented 3 weeks ago 
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

1111111111111111

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 commented 3 weeks ago

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