Thread: [nhibernate-issues] [NHJIRA] Created: (NH-2546) MSSQL Like criteria doesn't work if expression leng

Brought to you by: ayenderahien, bahusoid, fabiomaulo, fdelaporte, and 5 others

nhibernate-issues

[nhibernate-issues] [NHJIRA] Created: (NH-2546) MSSQL Like criteria doesn't work if expression length is more then column length

From: Denis (JIRA) <nh...@gm...> - 2011-02-23 16:01:37

MSSQL Like criteria doesn't work if expression length is more then column length
--------------------------------------------------------------------------------

                 Key: NH-2546
                 URL: http://216.121.112.228/browse/NH-2546
             Project:  NHibernate
          Issue Type: Bug
          Components: DataProviders / Dialects
    Affects Versions: 3.0.0.GA
            Reporter: Denis
            Priority: Critical


Consider the following situation:
mapping file contains the following property
<property name="PostalCode" column="postal_code" type="String(10)"
not-null="true"/>
When I apply the following restriction 
Restrictions.Like("PostalCode", "%9108[ -]MC%")
it doesn't work as expected.

Generated query looks like this:
SELECT count(*) as y0_ FROM PostalCodes this_ WHERE this_.postal_code like @p0;@p0 = '%9108[ -]MC%' [Type: String (10)] 

Parameter value length ('%9108[ -]MC%') is 12 symbols and it is correct expression but the value is cutted to '%9108[ -]M' which is not correct. 

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://216.121.112.228/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

[nhibernate-issues] [NHJIRA] Updated: (NH-2546) MSSQL Like criteria doesn't work if expression length is more then column length

From: Julian M. (JIRA) <nh...@gm...> - 2011-03-03 09:18:17

     [ http://216.121.112.228/browse/NH-2546?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Julian Maughan updated NH-2546:
-------------------------------

    Priority: Minor  (was: Critical)

> MSSQL Like criteria doesn't work if expression length is more then column length
> --------------------------------------------------------------------------------
>
>                 Key: NH-2546
>                 URL: http://216.121.112.228/browse/NH-2546
>             Project:  NHibernate
>          Issue Type: Bug
>          Components: DataProviders / Dialects
>    Affects Versions: 3.0.0.GA
>            Reporter: Denis
>            Priority: Minor
>
> Consider the following situation:
> mapping file contains the following property
> <property name="PostalCode" column="postal_code" type="String(10)"
> not-null="true"/>
> When I apply the following restriction 
> Restrictions.Like("PostalCode", "%9108[ -]MC%")
> it doesn't work as expected.
> Generated query looks like this:
> SELECT count(*) as y0_ FROM PostalCodes this_ WHERE this_.postal_code like @p0;@p0 = '%9108[ -]MC%' [Type: String (10)] 
> Parameter value length ('%9108[ -]MC%') is 12 symbols and it is correct expression but the value is cutted to '%9108[ -]M' which is not correct. 

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://216.121.112.228/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

[nhibernate-issues] [NHJIRA] Commented: (NH-2546) MSSQL Like criteria doesn't work if expression length is more then column length

From: Julian M. (JIRA) <nh...@gm...> - 2011-03-03 09:40:29

    [ http://216.121.112.228/browse/NH-2546?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=20619#action_20619 ] 

Julian Maughan commented on NH-2546:
------------------------------------

Specifying the size of the Type [i.e. type="String(10)"] causes the NHibernate driver (SqlClientDriver) to explicitly set the IDbCommand parameter length to 10, causing the truncation.

A workaround is to exclude the size in the type mapping.

I can't think of a way to change the behaviour of NHibernate so that this issue doesn't occur. Alternatively it might be possible to log an error/warning, or throw a runtime exception, to let the user know about it? Happy to hear suggestions.

> MSSQL Like criteria doesn't work if expression length is more then column length
> --------------------------------------------------------------------------------
>
>                 Key: NH-2546
>                 URL: http://216.121.112.228/browse/NH-2546
>             Project:  NHibernate
>          Issue Type: Bug
>          Components: DataProviders / Dialects
>    Affects Versions: 3.0.0.GA
>            Reporter: Denis
>            Priority: Minor
>
> Consider the following situation:
> mapping file contains the following property
> <property name="PostalCode" column="postal_code" type="String(10)"
> not-null="true"/>
> When I apply the following restriction 
> Restrictions.Like("PostalCode", "%9108[ -]MC%")
> it doesn't work as expected.
> Generated query looks like this:
> SELECT count(*) as y0_ FROM PostalCodes this_ WHERE this_.postal_code like @p0;@p0 = '%9108[ -]MC%' [Type: String (10)] 
> Parameter value length ('%9108[ -]MC%') is 12 symbols and it is correct expression but the value is cutted to '%9108[ -]M' which is not correct. 

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://216.121.112.228/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

[nhibernate-issues] [NHJIRA] Commented: (NH-2546) MSSQL Like criteria doesn't work if expression length is more then column length

From: Denis (JIRA) <nh...@gm...> - 2011-03-03 22:08:38

    [ http://216.121.112.228/browse/NH-2546?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=20620#action_20620 ] 

Denis commented on NH-2546:
---------------------------

There are more than 100 mappings in my project, so excluding the size is not an efficient solution. My workaround is to switch to nHibernate v2.1.2 which doesnt have this kind of a problem. 

IMHO, The solution is to pass additional parameter at the moment of IDbCommand parameter generation so it will be clear if this parameter is used for LIKE orperator or not.

> MSSQL Like criteria doesn't work if expression length is more then column length
> --------------------------------------------------------------------------------
>
>                 Key: NH-2546
>                 URL: http://216.121.112.228/browse/NH-2546
>             Project:  NHibernate
>          Issue Type: Bug
>          Components: DataProviders / Dialects
>    Affects Versions: 3.0.0.GA
>            Reporter: Denis
>            Priority: Minor
>
> Consider the following situation:
> mapping file contains the following property
> <property name="PostalCode" column="postal_code" type="String(10)"
> not-null="true"/>
> When I apply the following restriction 
> Restrictions.Like("PostalCode", "%9108[ -]MC%")
> it doesn't work as expected.
> Generated query looks like this:
> SELECT count(*) as y0_ FROM PostalCodes this_ WHERE this_.postal_code like @p0;@p0 = '%9108[ -]MC%' [Type: String (10)] 
> Parameter value length ('%9108[ -]MC%') is 12 symbols and it is correct expression but the value is cutted to '%9108[ -]M' which is not correct. 

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://216.121.112.228/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

[nhibernate-issues] [NHJIRA] Updated: (NH-2546) MSSQL Like criteria doesn't work if expression length is more then column length

From: Julian M. (JIRA) <nh...@gm...> - 2011-03-05 17:08:14

     [ http://216.121.112.228/browse/NH-2546?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Julian Maughan updated NH-2546:
-------------------------------

    Attachment: NH-2546.patch

An easier/quicker/better(?) solution is a configuration setting that can turn on or off the setting of parameter sizes in the driver. The 'prepare_sql' setting used to serve this purpose prior to being commented out in r4915.

I've attached a quick implementation (a code spike).

Before:
2.x: prepare_sql = false (default) -> parameter sizes not set (ADO.NET infers the size from the parameter value) and IDbCommand.Prepare not called
2.x: prepare_sql = true -> parameter sizes are set and IDbCommand.Prepare called

After:
3.1: adonet.set_parameter_sizes = true (default) -> parameter sizes are set
3.1: adonet.set_parameter_sizes = false -> parameter sizes not set (ADO.NET infers the size from the parameter value)
3.1: prepare_sql = false (default): IDbCommand.Prepare is not called
3.1: prepare_sql = true: IDbCommand.Prepare is called

Note that allowing ADO.NET to infer the size of parameter can lead to sub-optimal performance because of reduced query-plan reuse: queries with command parameters of different size are regarded as different queries, causing a new query plan to be calculated rather than using a cached one. This applies to SQL Server, not sure about other RDBMSs. I believe this was the reason for the r4915 change (see NH-2484).

> MSSQL Like criteria doesn't work if expression length is more then column length
> --------------------------------------------------------------------------------
>
>                 Key: NH-2546
>                 URL: http://216.121.112.228/browse/NH-2546
>             Project:  NHibernate
>          Issue Type: Bug
>          Components: DataProviders / Dialects
>    Affects Versions: 3.0.0.GA
>            Reporter: Denis
>            Priority: Minor
>         Attachments: NH-2546.patch
>
>
> Consider the following situation:
> mapping file contains the following property
> <property name="PostalCode" column="postal_code" type="String(10)"
> not-null="true"/>
> When I apply the following restriction 
> Restrictions.Like("PostalCode", "%9108[ -]MC%")
> it doesn't work as expected.
> Generated query looks like this:
> SELECT count(*) as y0_ FROM PostalCodes this_ WHERE this_.postal_code like @p0;@p0 = '%9108[ -]MC%' [Type: String (10)] 
> Parameter value length ('%9108[ -]MC%') is 12 symbols and it is correct expression but the value is cutted to '%9108[ -]M' which is not correct. 

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://216.121.112.228/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

[nhibernate-issues] [NHJIRA] Commented: (NH-2546) MSSQL Like criteria doesn't work if expression length is more then column length

From: Denis (JIRA) <nh...@gm...> - 2011-05-11 09:34:00

    [ http://216.121.112.228/browse/NH-2546?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=21047#action_21047 ] 

Denis commented on NH-2546:
---------------------------

I'm wondering why the issue is not in the 3.1 release and why the priority is 'Minor'? 
Does it mean that nobody uses 'Like' criteria? 



> MSSQL Like criteria doesn't work if expression length is more then column length
> --------------------------------------------------------------------------------
>
>                 Key: NH-2546
>                 URL: http://216.121.112.228/browse/NH-2546
>             Project:  NHibernate
>          Issue Type: Bug
>          Components: DataProviders / Dialects
>    Affects Versions: 3.0.0.GA
>            Reporter: Denis
>            Priority: Minor
>         Attachments: NH-2546.patch
>
>
> Consider the following situation:
> mapping file contains the following property
> <property name="PostalCode" column="postal_code" type="String(10)"
> not-null="true"/>
> When I apply the following restriction 
> Restrictions.Like("PostalCode", "%9108[ -]MC%")
> it doesn't work as expected.
> Generated query looks like this:
> SELECT count(*) as y0_ FROM PostalCodes this_ WHERE this_.postal_code like @p0;@p0 = '%9108[ -]MC%' [Type: String (10)] 
> Parameter value length ('%9108[ -]MC%') is 12 symbols and it is correct expression but the value is cutted to '%9108[ -]M' which is not correct. 

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://216.121.112.228/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

[nhibernate-issues] [NHJIRA] Commented: (NH-2546) MSSQL Like criteria doesn't work if expression length is more then column length

From: Fabio M. (JIRA) <nh...@gm...> - 2011-05-12 18:49:05

    [ http://216.121.112.228/browse/NH-2546?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=21065#action_21065 ] 

Fabio Maulo commented on NH-2546:
---------------------------------

The patch is not acceptable because another parameter just to revert the behavior of a line in the injectable driver is completely unneeded.
You can configure your driver and disable what you need disabled.   

> MSSQL Like criteria doesn't work if expression length is more then column length
> --------------------------------------------------------------------------------
>
>                 Key: NH-2546
>                 URL: http://216.121.112.228/browse/NH-2546
>             Project:  NHibernate
>          Issue Type: Bug
>          Components: DataProviders / Dialects
>    Affects Versions: 3.0.0.GA
>            Reporter: Denis
>            Priority: Minor
>         Attachments: NH-2546.patch
>
>
> Consider the following situation:
> mapping file contains the following property
> <property name="PostalCode" column="postal_code" type="String(10)"
> not-null="true"/>
> When I apply the following restriction 
> Restrictions.Like("PostalCode", "%9108[ -]MC%")
> it doesn't work as expected.
> Generated query looks like this:
> SELECT count(*) as y0_ FROM PostalCodes this_ WHERE this_.postal_code like @p0;@p0 = '%9108[ -]MC%' [Type: String (10)] 
> Parameter value length ('%9108[ -]MC%') is 12 symbols and it is correct expression but the value is cutted to '%9108[ -]M' which is not correct. 

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://216.121.112.228/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

[nhibernate-issues] [NHJIRA] Closed: (NH-2546) MSSQL Like criteria doesn't work if expression length is more then column length

From: Fabio M. (JIRA) <nh...@gm...> - 2011-05-12 19:48:59

     [ http://216.121.112.228/browse/NH-2546?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Fabio Maulo closed NH-2546.
---------------------------

    Resolution: Won't Fix

You can use
Restrictions.Like("StringTypeWithLengthDefined", "[a-z][a-z][a-z]ian%", MatchMode.Exact, null)

to ensure that the field length will be ignored.

> MSSQL Like criteria doesn't work if expression length is more then column length
> --------------------------------------------------------------------------------
>
>                 Key: NH-2546
>                 URL: http://216.121.112.228/browse/NH-2546
>             Project:  NHibernate
>          Issue Type: Bug
>          Components: DataProviders / Dialects
>    Affects Versions: 3.0.0.GA
>            Reporter: Denis
>            Priority: Minor
>         Attachments: NH-2546.patch
>
>
> Consider the following situation:
> mapping file contains the following property
> <property name="PostalCode" column="postal_code" type="String(10)"
> not-null="true"/>
> When I apply the following restriction 
> Restrictions.Like("PostalCode", "%9108[ -]MC%")
> it doesn't work as expected.
> Generated query looks like this:
> SELECT count(*) as y0_ FROM PostalCodes this_ WHERE this_.postal_code like @p0;@p0 = '%9108[ -]MC%' [Type: String (10)] 
> Parameter value length ('%9108[ -]MC%') is 12 symbols and it is correct expression but the value is cutted to '%9108[ -]M' which is not correct. 

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://216.121.112.228/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira