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 B75A546970E for ; Wed, 22 Jan 2020 17:13:56 +0300 (MSK) Date: Wed, 22 Jan 2020 17:13:52 +0300 From: Mergen Imeev Message-ID: <20200122141352.GA3367@tarantool.org> References: <20200121111108.GA18881@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 Hi! Thanks for answering. On Tue, Jan 21, 2020 at 11:09:25AM -0700, Peter Gulutzan wrote: > Hi, > > On 2020-01-21 4:11 a.m., Mergen Imeev wrote: > > Hi all, > > I would like once again ask a question about implicit cast for > > COMPARISON. Currently the chart of implicit cast for COMPARISON > > looks this way: > >             To BOOL | To INT | To DBL | To STR | To BIN > > --------------------------------------------------------- > > From BOOL | A       | -      | -      | -      | - > > From INT  | -       | A      | A      | A      | - > > From DBL  | -       | A      | A      | A      | - > > From STR  | -       | S      | S      | A      | - > > From BIN  | -       | -      | -      | -      | A > > > > I suggested to change this chart so that is should look this way: > >             To BOOL | To NUM | To STR | To BIN > > ----------------------------------------------- > > From BOOL | A       | -      | -      | - > > From NUM  | -       | A      | -      | - > > From STR  | -       | -      | A      | - > > From BIN  | -       | -      | -      | A > > > > At the moment the only thing that different in these charts is > > impicit cast from STRING to number and from number to STRING. > > > > I asked the Russian community in Telegram about whether to allow > > this implicit cast for COMPARISON. At the moment, 31 people have > > voted, and 25 of them have voted for dropping this implicit cast. > > > > So, once again I suggest using these rules: > > 1) The values of numeric types are comparable without any implicit > > cast. > > 2) If the type of one of the values contains the type of another > > value, then they are comparable. > > 3) In any other cases, the values are not comparable. > > > > About "one type contains another type": we can say that INTEGER > > contains UNSIGNED; NUMBER contains INTEGER, UNSIGNED, DOUBLE; > > SCALAR contains all available in SQL types. > > > > Since all numeric types are comparable, my suggestion means that a > > value of any type can be compared with a value of type SCALAR. And > > in this comparison, the same rules will be used as in no-SQL > > Tarantool. For example, any value of type STRING is greater than > > any value of type INTEGER. > > > > What do you think about this? > > > > I believe that the words "compared with a value of type SCALAR" > are technically incorrect -- columns can be defined as having a > SCALAR type but the values in those columns are not SCALAR. > But (I hope) that detail probably does not affect the proposition. > Sorry, but I suggested a different approach: each value has its own type, and SCALAR is one of the types. This means that each value in a column of type SCALAR is of type SCALAR. If we compare a value of type SCALAR with a value of any other type, we will apply the SCALAR rules. If not both values have numeric types and none of them has SCALAR type, we get an error. > This would reverse what some developers decided earlier > in dev threads "Clarify the rule" and "[dev] cast". > There would be no implicit casts, the SCALAR rules would apply. > Therefore: > > SELECT 1 = '1'; > Currently, returns: TRUE. > After fix, returns: FALSE. > No, after fix it will throw an error. > CREATE TABLE t (s1 SCALAR PRIMARY KEY); > INSERT INTO t VALUES (FALSE),('A'); > SELECT COUNT(*) FROM t WHERE s1 <> 'A'; > Currently, returns: Type mismatch: can not convert text to boolean. > After fix, returns: 1. > This is true. > CREATE TABLE t (s1 INTEGER PRIMARY KEY); > INSERT INTO t VALUES (1); > SELECT COUNT(*) FROM t WHERE s1 <> 'A'; > Currently, returns: Type mismatch: can not convert text to boolean. > After fix, returns: 1. > Here we will see an error. > If the above example is correct, it differs from NoSQL behaviour: > box.schema.space.create('T') > box.space.T:create_index('I') > box.space.T:insert{1} > box.space.T:select('A') > Currently, returns: Supplied key type of part 0 does not match index part > type > After fix, returns: Supplied key type of part 0 does not match index part > type > (No change because the proposed fix is for SQL not Lua) > > And, since there is no change suggested for assignment, > implicit cast would still occur. Therefore: > > CREATE TABLE t (s1 INTEGER PRIMARY KEY); > INSERT INTO t VALUES ('1'); > Currently, returns: no error > After fix, returns: no error > (No change because assignments cause implicit cast) > > SELECT UPPER(FALSE) FROM t; > Currently, returns: 'FALSE' (string) > After fix, returns: 'FALSE' (string) > (No change because functions can cause assignments) > > I think that the proposed change is good. > > Peter Gulutzan > > Still, I think the idea of using SCALAR rules is viable. But I suggest adding this as one of the session settings.