From mboxrd@z Thu Jan  1 00:00:00 1970
Return-Path: <imeevma@tarantool.org>
Received: from smtpng3.m.smailru.net (smtpng3.m.smailru.net [94.100.177.149])
 (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256
 bits)) (No client certificate requested)
 by dev.tarantool.org (Postfix) with ESMTPS id C803246970E
 for <tarantool-discussions@dev.tarantool.org>;
 Thu,  6 Feb 2020 16:09:42 +0300 (MSK)
Date: Thu, 6 Feb 2020 16:09:40 +0300
From: Mergen Imeev <imeevma@tarantool.org>
Message-ID: <20200206130940.GA22763@tarantool.org>
References: <20200121111108.GA18881@tarantool.org>
 <fe689ee4-22f7-8d0c-6845-7c806c6692f7@ocelot.ca>
 <a6192f3a-a60c-e2e8-8ec5-b9e99f9bfb93@ocelot.ca>
 <20200130185216.GC26109@atlas>
 <20200203113519.GA9896@tarantool.org>
 <20200204185011.GF1049@tarantool.org>
 <20200206124106.GA22195@tarantool.org>
MIME-Version: 1.0
Content-Type: text/plain; charset="utf-8"
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <20200206124106.GA22195@tarantool.org>
Subject: Re: [Tarantool-discussions] Implicit cast for COMPARISON
List-Id: Tarantool development process
 <tarantool-discussions.dev.tarantool.org>
List-Unsubscribe: <https://lists.tarantool.org/mailman/options/tarantool-discussions>, 
 <mailto:tarantool-discussions-request@dev.tarantool.org?subject=unsubscribe>
List-Archive: <https://lists.tarantool.org/pipermail/tarantool-discussions/>
List-Post: <mailto:tarantool-discussions@dev.tarantool.org>
List-Help: <mailto:tarantool-discussions-request@dev.tarantool.org?subject=help>
List-Subscribe: <https://lists.tarantool.org/mailman/listinfo/tarantool-discussions>, 
 <mailto:tarantool-discussions-request@dev.tarantool.org?subject=subscribe>
To: Nikita Pettik <korablev@tarantool.org>
Cc: tarantool-discussions@dev.tarantool.org

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]
...