Search on Query goes wrong if one of the fields is NULL

[Wietze Blokstra]

Hi,

We use the following:

string searchtext = "lan";

var fields = new List<string> { nameof(SomeVm.Code), nameof(SomeVm.FirstName), nameof(SomeVm.LastName) };

var query = new Query().Search(searchtext, fields, "contains", true);

If one of the fields has the value NULL and the searchtext contains the word "blank" or one or more of its letters, all records are returned. This is because the query contains the following:

CASE 

WHEN LOWER(COALESCE([h].[FirstName], N'Blanks')) LIKE N'%lan%' THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)

As you can see, if the value of for example FirstName is NULL you set the text to "Blanks". I would expext you would use string.Empty.

Kind regards,

Wietze Blokstra

[Rahul Narayanasamy]

Hi Wietze, 

Greetings from Syncfusion. 

Query: Search on Query goes wrong if one of the fields is NULL 

We have validated your query and checked the reported problem. Here, we have prepared a sample based on your code snippets and scenario. We have defined some null values in Name column and perform filtering operation using Query with blank word. While performing search operation it will return only the blank values instead of all records. Find the below sample for your reference. 

 

<button @onclick="Clear">Clear</button> 

 

<SfButton Content="Modify query data" OnClick="BtnClick"></SfButton> 

 

@{ 

    var Tool = (new List<string>() { "Search" }); 

} 

 

<SfGrid DataSource="@Orders" Query="@QueryData" Toolbar=@Tool> 

    <GridColumns> 

        . . . 

   </GridColumns> 

</SfGrid> 

 

@code{ 

    public List<Order> Orders { get; set; } 

    private Query QueryData = new Query(); 

    static string searchtext = "blank"; 

    static List<string> fields = new List<string> { nameof(Order.CustomerID), nameof(Order.Name) }; 

 

    private Query UpdatedQueryData = new Query().Search(searchtext, fields, "contains", true); 

 

    protected override void OnInitialized() 

    { 

        Orders = Enumerable.Range(1, 75).Select(x => new Order() 

        { 

            OrderID = 1000 + x, 

            CustomerID = (new string[] { "ALFKI", "ANANTR", "ANTON", "BLONP", "BOLID" })[new Random().Next(5)], 

            Name = (new string[] { "Berlin", "Tokyo", "ELeven", null, null })[new Random().Next(5)], 

            Freight = 2.1 * x, 

            OrderDate = DateTime.Now.AddDays(-x), 

        }).ToList(); 

    } 

 

    . . . 

   public void BtnClick() 

    { 

        QueryData = UpdatedQueryData; 

    } 

    public void Clear() 

    { 

        QueryData = new Query(); 

    } 

} 

Sample: https://www.syncfusion.com/downloads/support/forum/165204/ze/SyncfusionBlazorApp11084004001 

If you are still facing the problem, then could you please share the below details. It will be helpful to validate and provide a better solution. 

• Full Grid code snippets.
• Syncfusion NuGet version details.
• Reproduce the problem in the provided sample and revert back to us.
• Share a simple reproduceable sample if possible.

Regards, 

Rahul 

 

[Wietze Blokstra]

Hi Rahul,

This is not exactly expected behaviour. If, in your example I want to find the customer with customer name BLONP, and I type BL, I will find this customer, but also all records where Name column is NULL. That is really confusing for the user.

Kind regards,

Wietze Blokstra

[Rahul Narayanasamy]

Hi Wietze, 

Thanks for sharing the details.  

We have validated your query with the provided details and we are able to reproduce the problem(While searching the word BL, it will return the corresponding BLONP results and with null(Blanks) value results also) at our end. We are currently checking the reported problem at our end and we will update the further details within two business days. 

Regards, 

Rahul 

[Rahul Narayanasamy]

Hi Wietze, 

Thanks for your patience. 

Query: Incorrect search results are returned when one of the Grid columns having null values - While searching the word BL, it will return the corresponding BLONP results and with null(Blanks) value results also 

We have checked your query and we have confirmed it as a bug and logged the defect report “Incorrect search results are returned when one of the Grid columns having null values” for the same. Thank you for taking the time to report this issue and helping us improve our product. At Syncfusion, we are committed to fixing all validated defects (subject to technological feasibility and Product Development Life Cycle ) and including the defect fix in our upcoming bi-weekly release which is expected to be rolled on or before mid of June, 2021.   

      

You can now track the current status of your request, review the proposed resolution timeline, and contact us for any further inquiries through this link.   

     

https://www.syncfusion.com/feedback/25589/incorrect-search-results-are-returned-when-one-of-the-grid-columns-having-null 

Until then we appreciate your patience.  

Regards, 

Rahul