String (char(12)) comparison problem

5 posts, 0 answers
  1. Petr Šatka
    Petr Šatka avatar
    21 posts
    Member since:
    Mar 2011

    Posted 24 May 2011 Link to this post

    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

  2. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 25 May 2011 Link to this post

    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
    Q1’11 SP1 of Telerik OpenAccess is available for download; also available is the Q2'11 Roadmap for Telerik OpenAccess ORM.
  3. DevCraft banner
  4. Petr Šatka
    Petr Šatka avatar
    21 posts
    Member since:
    Mar 2011

    Posted 25 May 2011 Link to this post

    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
  5. Petr Šatka
    Petr Šatka avatar
    21 posts
    Member since:
    Mar 2011

    Posted 25 May 2011 Link to this post

    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
  6. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 20 Jun 2011 Link to this post

    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
    Q1’11 SP1 of Telerik OpenAccess is available for download; also available is the Q2'11 Roadmap for Telerik OpenAccess ORM.
Back to Top
DevCraft banner