[Tarantool-discussions] Implicit cast for COMPARISON

[Mergen Imeev]

On Thu, Feb 06, 2020 at 03:41:06PM +0300, Mergen Imeev wrote:
> On Tue, Feb 04, 2020 at 09:50:11PM +0300, Nikita Pettik wrote:
> <cut>
> 
> > > 7) We need to clarify the rules when comparing SCALAR values. I
> > > think we cannot use the Tarantool rules here, as the Tarantool
> > > rules indicate that “100 < '2' == true”, but we decided that
> > > "100 > '2' == true", since '2' implicitly cast to 2. Could you
> > > suggest the rules that we should use here?
> > 
> > There's already existing solution: while fetching value from space,
> > we preserve its initial field type. For SCALAR values we may use one
> > rules, for values fetched from INTEGER/STRING fields - apply another ones.
> > 
> 
> Hi,
> After some thinking, I came to the conclusion that your idea is
> pretty good. I suggest this algorithm for comparison:
> if (has NULL)
> 	<return NULL>
> else if (same mp_type) \\MP_TYPE == MEM_TYPE here.
> 	<compare>
> else if (both numeric)
> 	<compare>
> else if (has SCALAR)
> 	<compare using SCALAR rules>
> else if (STRING compared with numeric)
> 	<if possible cast string to number and compare, else error>
> else
> 	<error>
> 
> In this case, if we still decide to remove the implicit cast from
> STRING, we can do this quite easily.
> 
> Please note that this is a simplified version. For example, we do
> not always need to return NULL if one of the operands is NULL.
> 
> In addition, this implementation means that we are moving the
> implicit cast for comparison from OP_ApplyType to opcodes
> responsible for comparing.
> 
> What do you think about this?
> 

Actually, currently it partly works this way:

tarantool> CREATE TABLE t(i INT PRIMARY KEY, a SCALAR);
---
- row_count: 1
...

tarantool> INSERT INTO t VALUES (1,1), (2,2), (3,3), (4,4), (5,5);
---
- row_count: 5
...

tarantool> SELECT COUNT(i) FROM t WHERE '2' > a;
---
- metadata:
  - name: COUNT(i)
    type: integer
  rows:
  - [5]
...


Still, not always:

tarantool> SELECT COUNT(i) FROM t WHERE '2' > a + 1;
---
- metadata:
  - name: COUNT(i)
    type: integer
  rows:
  - [0]
...


Also, it doesn't work for constants:

tarantool> select 2 > '1';
---
- metadata:
  - name: 2 > '1'
    type: boolean
  rows:
  - [true]
...

tarantool> select 2 > CAST('1' AS SCALAR);
---
- metadata:
  - name: 2 > CAST('1' AS SCALAR)
    type: boolean
  rows:
  - [true]
...