From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: 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 CA89E46970E for ; Sun, 12 Jan 2020 00:49:47 +0300 (MSK) References: <20200111133321.GA8486@tarantool.org> From: Peter Gulutzan Message-ID: Date: Sat, 11 Jan 2020 14:49:41 -0700 MIME-Version: 1.0 In-Reply-To: <20200111133321.GA8486@tarantool.org> Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit Content-Language: en-US Subject: Re: [Tarantool-discussions] Implicit cast in SQL List-Id: Tarantool development process List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , To: Mergen Imeev , tarantool-discussions@dev.tarantool.org Hi, On 2020-01-11 6:33 a.m., Mergen Imeev wrote: > Hi all, > I have a question to discuss. I know that we have discussed this > issue quite a few times. Very true! I thought that most of these matters were settled. Some of the discussions were with K. Osipov or CCed to dev, for example the long Re: [dev] cast thread. Should they be on the email "To:" list? > But, since we have a few open issues on > github, I think the final answer has not yet been found. I see two open issues on github say implicit cast: #3809 sql: implicit type cast problems during assignment https://github.com/tarantool/tarantool/issues/3809 #4230 sql: implicit type cast during comparison https://github.com/tarantool/tarantool/issues/4230 Are there others that I have missed? > I want to > completely close the issue this time. > > I would like to start with an implicit cast. Implicit cast > consists of implicit type conversion for COMPARISON and implicit > type conversion for ASSIGNMENT. I will compare your suggestion with the chart that I wrote for the documentation, which was the result of my understanding from prior discussions. The documentation has not been published, but can be seen in section Data type conversion, lines 1632 and following, in https://github.com/tarantool/doc/blob/pgulutzan-2.3/doc/reference/reference_sql/sql.rst > Currently, all types can be divided into four categories: > numeric (UNSIGNED, INTEGER, NUMBER, DOUBLE), string (STRING), > binary (VARBINARY) and boolean (BOOLEAN). We also have a scalar > type, but any value of a scalar type has some subtype that can be > placed in one of these four categories. > Please don't call them "subtypes". I proposed that we should regard SCALAR as a supertype (and therefore the rules for the supertype would apply for the subtype), but that was rejected. > So, I suggest this: > Implicit type conversion for COMPARISON: > 1) types of the same category can be implicitly cast to each > other; Or "implicitly cast to the type whose range includes both values". > 2) types of different categories cannot be implicitly cast to > each other. > The chart does not agree. * The chart says A (always) numeric can be compared to string. But I was fooled, I did not look in both directions. Now S (sometimes) a numeric can be compared to a string. For example, 1 = 'a' is legal, 'a' = 1 is illegal. But anyway, it is currently not always true that types of different categories cannot be implicitly cast to each other. Incidentally, I believe this behaviour is bad, I argued against implicit casting of numbers and strings for either comparison or assignment. But other people, probably the majority, argued that it was good, so it is the current behaviour. Also, in SCALAR columns, comparisons of values of different data types are allowed for the sake of ORDER BY (booleans are less than strings etc.) but not allowed for the sake of WHERE (searches for booleans cause an error if there are strings in the column etc.). This is, I believe, a bad thing. > Implicit type conversion for ASSIGNMENT: > 1) If a user-defined cast function (UDCF) is defined, then this > function is used; > 2) If UDCF is not defined, then types of the same category can be > implicitly cast to each other, and types of different categories > cannot be implicitly cast to each other. > The chart does not agree. * The chart says A (always) numeric can be assigned to string, and S (sometimes) string can be assigned to number. * The chart says numeric to numeric implicit conversion is S (sometimes allowed) not A (always allowed) because of range differences, for example -1 will not fit in UNSIGNED. Of course, you can reply that the cast is allowed but the assignment is not allowed, if you prefer. I have assumed that function calls may involve implicit assignment. > I think this is what ANSI says. > > We currently do not have such a thing as UDCF. I suggest to fill > an issue and use the same rules for ASSIGNMENT as for COMPARISON. > In fact, I think we can create a special option for assigning > UCDF during the rework of implicit type conversion for ASSIGNMENT. > > What do you think about this? > When you say user-defined casts, do you really mean that Tarantool's users will be given facilities that allow them to write the code that handles comparison and assignment? I think this might be hard, but will wait till I see the issue. Peter Gulutzan