QUERIES: matching numbers in search queries

[radim]

In the new version of MERGADO (deployment on February 15, 2023) a bug concerning the matching of numbers in queries that can affect the behavior of current queries has been fixed .

In this post, we will take a closer look at how this works. We will be working with sample data the entire time:

1. <SHOP>
2.   <SHOPITEM>
3.     <ITEM_ID>12</ITEM_ID>
4.   </SHOPITEM>
5.   <SHOPITEM>
6.     <ITEM_ID>00012</ITEM_ID>
7.   </SHOPITEM>
8.   <SHOPITEM>
9.     <ITEM_ID>AB012</ITEM_ID>
10.   </SHOPITEM>
11.   <SHOPITEM>
12.     <ITEM_ID>12 Kč</ITEM_ID>
13.   </SHOPITEM>
14.   <SHOPITEM>
15.     <ITEM_ID>AB_12</ITEM_ID>
16.   </SHOPITEM>
17.   <SHOPITEM>
18.     <ITEM_ID>AB</ITEM_ID>
19.   </SHOPITEM>
21.   <SHOPITEM>
22.     <ITEM_ID>12.00</ITEM_ID>
23.   </SHOPITEM>
24.   <SHOPITEM>
25.     <ITEM_ID>012AB</ITEM_ID>
26.   </SHOPITEM>
24. </SHOP>

How does number matching work in search query evaluation?

The description of how it works in this section applies to all operators and queries (not just the IN operator).

The general behaviour has 2 principles:

1. Everything (including numbers) that is written in quotes is evaluated as text, so an exact text match is looked for in the search values. For example, if we have a query

   

   it will find products with ITEM_ID 12, AB and 12.00 (corresponding lines 3, 18 and 22).

   

2. Numbers that are not written in quotes are evaluated as numbers, so no exact text match is looked for in the search values, but values like 00012, 12.00 or 12 Kč are taken as a match to the number 12. For example, if we have a query

   

   it will find products with ITEM_ID 12, 00012, 12 Kč, AB, 12.00 and 012AB (corresponding rows 3, 6, 12, 18, 22 and 25).

   

IN operator - added behaviour

Now in the IN operator you can write values without quotes and values with quotes combined at the same time within one IN operator and the query will be functional (it was not possible before).

More examples

Search query	Result - matching values found
ITEM_ID = 12	12 , 00012 , 12 Kč , 12.00, 012AB
ITEM_ID IN (12)	12 , 00012 , 12 Kč , 12.00, 012AB
ITEM_ID IN (00012; "AB")	12, 00012 , 12 Kč , AB, 12.00, 012AB
ITEM_ID = "12"	12
ITEM_ID IN ("12")	12
ITEM_ID IN ("12.00")	12.00
ITEM_ID IN ("00012"; "AB")	00012, AB