From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from smtpng2.m.smailru.net (smtpng2.m.smailru.net [94.100.179.3]) (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 C116D46970E for ; Fri, 7 Feb 2020 17:24:47 +0300 (MSK) Date: Fri, 7 Feb 2020 17:24:46 +0300 From: Nikita Pettik Message-ID: <20200207142446.GA1110@tarantool.org> References: <20200121111108.GA18881@tarantool.org> <20200130185216.GC26109@atlas> <20200203113519.GA9896@tarantool.org> <20200204185011.GF1049@tarantool.org> MIME-Version: 1.0 Content-Type: text/plain; charset="utf-8" Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: Subject: Re: [Tarantool-discussions] Implicit cast for COMPARISON List-Id: Tarantool development process List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , To: Peter Gulutzan Cc: tarantool-discussions@dev.tarantool.org On 04 Feb 19:57, Peter Gulutzan wrote: > Hi, > > On 2020-02-04 11:50 a.m., Nikita Pettik wrote: > > > On 03 Feb 14:35, Mergen Imeev wrote: > > >> 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. Yes, I stick to the point that implicit cast should be 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? Yes. > scalar_column < non_scalar_column is legal? Yes, comparison rules are supposed to be the same as in NoSQL Tarantool. > scalar_column < 1 /* data type of 1 is INTEGER */ ... is legal? Yes. > SUM(scalar_column) is SCALAR? No, it is not. Taking into account removing implicit cast between numeric and string types, SUM() is assumed to be finished only in case all arguments are of numeric type. What is more SUM() features NUMBER return type (in its function definition). > scalar_column + non_scalar_column is SCALAR? No. If addition is allowed, type of resulting value will be numeric (integer or float). > SELECT scalar_column UNION SELECT non_scalar_column is SCALAR? I guess yes (at least it seems to be reasonable to me). > UPDATE t SET non_scalar_column = scalar_column is legal? It depends on particular value of scalar_column. To sum up, my proposal is to allow comparing scalar values with values of any other type. On the other hand, result of such operations like assignment or addition should depend on particular values of SCALAR type. Alternatively, we can abandon this idea and operate apply comparison rules depending purely on mp_ types of particular scalar values. But then rules in NoSQL and SQL will be different.