Issue Details (XML | Word | Printable)

Key: DNET-840
Type: Bug Bug
Status: Closed Closed
Resolution: Won't Fix
Priority: Major Major
Assignee: Jiri Cincura
Reporter: jack128
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
.NET Data provider

Bug if use Replace with parameter

Created: 09/Aug/18 08:36 AM   Updated: 08/Oct/18 08:29 AM
Component/s: ADO.NET Provider
Affects Version/s: 6.1.0.0
Fix Version/s: None

Environment: win64, fb3.0.1


 Description  « Hide
script to create db:

CREATE DATABASE 'localhost:C:\Data\TEMP_3ED4BB49-EE30-4D79-8038-EBC5C24C536D.FDB'
USER 'SYSDBA' PASSWORD 'masterkey';

create table TempTable (caption varchar(255));

insert into TempTable values('1');
commit;

test:
        static void Main(string[] args)
        {
            var filename = @"C:\Data\Temp_3ED4BB49-EE30-4D79-8038-EBC5C24C536D.fdb";
 
            var connectionString = new FbConnectionStringBuilder
            {
                UserID = "SYSDBA",
                Password = "masterkey",
                Database = filename,
            }.ConnectionString;

            using (var conn = new FbConnection(connectionString))
            {
                conn.Open();
                using (var command = conn.CreateCommand())
                {
                    const string sql = @"select * from TempTable t where t.caption like Replace(@p, '~', '~~')"; // this query does not return row, although should, I check it in IBExpert
                    //const string sql = @"select * from TempTable t where t.caption like @p"; // this query returns row as expected

                    command.CommandText = sql;
                    var p = command.CreateParameter();
                    p.ParameterName = "p";
                    p.Value = "1";
                    command.Parameters.Add(p);

                    using (var reader = command.ExecuteReader())
                    {
                        if (!reader.Read())
                            throw new Exception("1 row should be returned");
                    }
                }
            }
        }


 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Jiri Cincura added a comment - 08/Oct/18 08:29 AM
The problem is that the replacement "'~~'" is CHAR(2) and hence the comparison with "caption" does not match. If you'd do "Replace(@p, '~', cast('~~' as varchar(255)))", it would work as expected.