String (char(12)) comparison problem

[Petr Šatka]

Hi Telerik.

I have a problem. Some queries returns unpredictable results. The problem is string comparison. The problem is with the data type char(12) and dynamic queries. For example, a query like .where(i => i.IXS_STR == "11400A") is working well, while query like string s = "11400A"; ... .where(i=>i.IXS_STR == s) is behaving strangely (randomly).

Sorry for my English.

Thank you for your help.

Peter

Log:

The first query returned the correct result, but the other did not. I do not know why.

Telerik.OpenAccess Information: 2842 : sm.compile                 3 LINQ WebTime11.Web.ANeTCommon.V_W_IPOSTR_PRAVA Extent<WebTime11.Web.ANeTCommon.V_W_IPOSTR_PRAVA>.Where(ipo => (ipo.IXS_REF == value(WebTime11.Web.ANeTCommon.ANeTCommonDomainService).authUserIxsref)).Where(ipostr => (ipostr.IXS_STR.Trim() == value(WebTime11.Web.ANeTCommon.ANeTCommonDomainService+<>c__DisplayClass0).ixsStr.Trim())) {Options:ParallelFetchTake} fg:[default, -jdoreq]
Telerik.OpenAccess Information: 2843 : sm.exec.all                3 LINQ WebTime11.Web.ANeTCommon.V_W_IPOSTR_PRAVA Extent<WebTime11.Web.ANeTCommon.V_W_IPOSTR_PRAVA>.Where(ipo => (ipo.IXS_REF == value(WebTime11.Web.ANeTCommon.ANeTCommonDomainService).authUserIxsref)).Where(ipostr => (ipostr.IXS_STR.Trim() == value(WebTime11.Web.ANeTCommon.ANeTCommonDomainService+<>c__DisplayClass0).ixsStr.Trim())) {Options:ParallelFetchTake} fg:[default, -jdoreq]
Telerik.OpenAccess Information: 2844 : driver.pool.alloc          3 active=1/10 idle=2/10 con=-843729009
Telerik.OpenAccess Information: 2845 : driver.pspool.alloc        51 -843729009 SELECT  TOP(??T) a.[prava] AS COL1, a.[ixs_str] AS COL2, a.[ixs_ref] AS COL3, a.[cislo_firmy] AS COL4, a.[stredisko] AS COL5, a.[stredisko_x] AS COL6, a.[str_sap] AS COL7, a.[str_text] AS COL8, a.[userid] AS COL9 FROM [v_w_ipostr_prava] a WHERE a.[ixs_ref] = ?                       AND ltrim(rtrim(a.[ixs_str])) = ?
Telerik.OpenAccess Information: 2846 : driver.stat.maxrows        1
Telerik.OpenAccess Information: 2847 : driver.stat.maxrows        1
Telerik.OpenAccess Information: 2848 : driver.stat.execQuery      -843729009 SELECT  TOP(@__TAKE) a.[prava] AS COL1, a.[ixs_str] AS COL2, a.[ixs_ref] AS COL3, a.[cislo_firmy] AS COL4, a.[stredisko] AS COL5, a.[stredisko_x] AS COL6, a.[str_sap] AS COL7, a.[str_text] AS COL8, a.[userid] AS COL9 FROM [v_w_ipostr_prava] a WHERE a.[ixs_ref] = @p0                       AND ltrim(rtrim(a.[ixs_str])) = @p1                        [@p0="TEST        " @p1="1140" @__TAKE=1]
Telerik.OpenAccess Information: 2849 : driver.rs.next
Telerik.OpenAccess Information: 2850 : driver.rs.getrow           [1, "1140        ", "TEST        ", DBNull, DBNull, DBNull, "Bosch02   ", "Bosch02                       ", DBNull]
Telerik.OpenAccess Information: 2851 : driver.rs.getrow
Telerik.OpenAccess Information: 2852 : driver.rs.next
Telerik.OpenAccess Information: 2853 : driver.rs.close
.
.
.
Telerik.OpenAccess Information: 2890 : sm.compile                 3 LINQ WebTime11.Web.ANeTCommon.V_W_IPOSTR_PRAVA Extent<WebTime11.Web.ANeTCommon.V_W_IPOSTR_PRAVA>.Where(ipo => (ipo.IXS_REF == value(WebTime11.Web.ANeTCommon.ANeTCommonDomainService).authUserIxsref)).Where(ipostr => (ipostr.IXS_STR.Trim() == value(WebTime11.Web.ANeTCommon.ANeTCommonDomainService+<>c__DisplayClass0).ixsStr.Trim())) {Options:ParallelFetchTake} fg:[default, -jdoreq]
Telerik.OpenAccess Information: 2891 : sm.exec.all                3 LINQ WebTime11.Web.ANeTCommon.V_W_IPOSTR_PRAVA Extent<WebTime11.Web.ANeTCommon.V_W_IPOSTR_PRAVA>.Where(ipo => (ipo.IXS_REF == value(WebTime11.Web.ANeTCommon.ANeTCommonDomainService).authUserIxsref)).Where(ipostr => (ipostr.IXS_STR.Trim() == value(WebTime11.Web.ANeTCommon.ANeTCommonDomainService+<>c__DisplayClass0).ixsStr.Trim())) {Options:ParallelFetchTake} fg:[default, -jdoreq]
Telerik.OpenAccess Information: 2892 : driver.pool.alloc          3 active=1/10 idle=2/10 con=-843729009
Telerik.OpenAccess Information: 2893 : driver.pspool.alloc        51 -843729009 SELECT  TOP(??T) a.[prava] AS COL1, a.[ixs_str] AS COL2, a.[ixs_ref] AS COL3, a.[cislo_firmy] AS COL4, a.[stredisko] AS COL5, a.[stredisko_x] AS COL6, a.[str_sap] AS COL7, a.[str_text] AS COL8, a.[userid] AS COL9 FROM [v_w_ipostr_prava] a WHERE a.[ixs_ref] = ?                       AND ltrim(rtrim(a.[ixs_str])) = ?
Telerik.OpenAccess Information: 2894 : driver.stat.maxrows        1
Telerik.OpenAccess Information: 2895 : driver.stat.maxrows        1
Telerik.OpenAccess Information: 2896 : driver.stat.execQuery      -843729009 SELECT  TOP(@__TAKE) a.[prava] AS COL1, a.[ixs_str] AS COL2, a.[ixs_ref] AS COL3, a.[cislo_firmy] AS COL4, a.[stredisko] AS COL5, a.[stredisko_x] AS COL6, a.[str_sap] AS COL7, a.[str_text] AS COL8, a.[userid] AS COL9 FROM [v_w_ipostr_prava] a WHERE a.[ixs_ref] = @p0                       AND ltrim(rtrim(a.[ixs_str])) = @p1                        [@p0="TEST        " @p1="11400A" @__TAKE=1]
Telerik.OpenAccess Information: 2897 : driver.rs.next
Telerik.OpenAccess Information: 2898 : driver.rs.getrow           [1, "1140        ", "TEST        ", DBNull, DBNull, DBNull, "Bosch02   ", "Bosch02                       ", DBNull]
Telerik.OpenAccess Information: 2899 : driver.rs.getrow
Telerik.OpenAccess Information: 2900 : driver.rs.next
Telerik.OpenAccess Information: 2901 : driver.rs.close

[Thomas]

Hello Petr Šatka,

I believe the issue is that you should not perform an == between a char(12) and a varchar. Both are mapped to strings, but the in the server those strings look different, as the char(12) might contain spaces at the end.
So better would be x.column12.Trim() == y .

Kind regards,
Thomas
the Telerik team

[Petr Šatka]

Hello Thomas.

I tried Trim(), but with no effect. As you can see in the log, ltrim(rtrim(a.[ixs_str])) is trimmed and @p1 does not contain any spaces.

By the way, queries in the log are both dynamic queries. It's one query called twice (in one application run), with a different parameter. But always returns the same result.

The problem is only with dynamic queries. If I use a constant string, or generate a query using Expression Trees, everything works fine. When using a constant string, then the generated query looks like this: AND ltrim (rtrim (a. [ixs_str])) = "1140". I think there is something wrong with the parameters (@p1...).

Regards
Peter

[Petr Šatka]

Hello Thomas.

When I change the column data type from char to varchar by Table Editor, then queries work correctly. (It is not a change in the database. I only changed data type in the model.)

It is not a solution, but I believe it can help identify the problem.

Regards
Peter

[Thomas]

Hi Petr Šatka,

the upcoming Q2 2011 release will fix this issue. Thanks for reporting this problem; I've updated your Telerik Points.

Regards,
Thomas
the Telerik team