From mboxrd@z Thu Jan  1 00:00:00 1970
Return-Path: <pgulutzan@ocelot.ca>
Received: from rhino.ch-server.com (rhino.ch-server.com [209.59.190.103])
 (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 18FBF46970E
 for <tarantool-discussions@dev.tarantool.org>;
 Wed,  5 Feb 2020 05:57:14 +0300 (MSK)
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>
From: Peter Gulutzan <pgulutzan@ocelot.ca>
Message-ID: <c75e0748-c35d-77f3-a80b-12dd249a8208@ocelot.ca>
Date: Tue, 4 Feb 2020 19:57:09 -0700
MIME-Version: 1.0
In-Reply-To: <20200204185011.GF1049@tarantool.org>
Content-Type: text/plain; charset="utf-8"; format="flowed"
Content-Transfer-Encoding: 8bit
Content-Language: en-US
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>, Mergen Imeev <imeevma@tarantool.org>
Cc: tarantool-discussions@dev.tarantool.org

Hi,

On 2020-02-04 11:50 a.m., Nikita Pettik wrote:

 > On 03 Feb 14:35, Mergen Imeev 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.

I do not think it is always so simple.

First, with regard to the implicit cast part of the question,
the current situation is:
CREATE TABLE t (A SCALAR PRIMARY KEY, B INTEGER);
INSERT INTO t VALUES (1,1);
SELECT * FROM t WHERE A = B AND A < '0' AND B > '0';
Result: 1 row.
It looks odd, but that is what happens if the SCALAR rules
and the non-SCALAR rules can fit in the same statement.
This could be solved by making implicit cast illegal.

But you still want to have two sets of rules, and (behaviour change) 
values can be SCALAR.

Suppose CREATE TABLE t (scalar_column SCALAR PRIMARY KEY,
                         non_scalar_column INT);

CAST(non_scalar_column AS SCALAR) result data type is SCALAR?

scalar_column < non_scalar_column is legal?

scalar_column < 1 /* data type of 1 is INTEGER */ ... is legal?

SUM(scalar_column) is SCALAR?

scalar_column + non_scalar_column is SCALAR?

SELECT scalar_column UNION SELECT non_scalar_column is SCALAR?

UPDATE t SET non_scalar_column = scalar_column is legal?

Peter Gulutzan