sql: integer division returns float, sqllogic test expects int

[maddyblue]

The following (currently disabled) test performs integer division. Cockroach returns a float instead of the expected int. Integer division is the only binary operator that takes integers and returns a float.

https://github.com/cockroachdb/sqllogictest/blob/a88396b84bb1fe62edf2e072e585d68c0b2ecdca/test/select1.test#L101

I propose allowing the test logic to accept both ints and floats when it expects an int. This would allow any sqllogic test using integer division to succeed. It would be an additional if statement at

cockroach/sql/logic_test.go

Line 460 in 21e3e6c

case 'I':

.

[maddyblue]

Based on how postgres works, I think we should change integer division to return an integer:

postgres=# create table t (a INT, b INT, c FLOAT, d FLOAT);
CREATE TABLE
postgres=# insert into t values (2, 3, 4, 5);
INSERT 0 1
postgres=# select a/b, b/c, c/d from t;
 ?column? | ?column? | ?column? 
----------+----------+----------
        0 |     0.75 |      0.8
(1 row)

[knz]

Since #3543 fixes the original problem, and the proposed new typing system would type division coherently with its operands, is this issue still relevant?

[tamird]

#3543 was closed. This issue is still relevant.

[knz]

See also #6642.

[nvanbenschoten]

I don't see MySQL's handling of int division mentioned here yet. MySQL has a similar issue where INT/INT -> REAL. However, they also provide a floor division operator DIV, similar to our //. To get around this, there are a number of skipif mysql and onlyif mysql directives in the test files which use DIV in place of / when MySQL requires it.

Following this, we could write a script to generate appropriate alternatives for our FLOORDIV operator. This seems like a clean way to handle this issue.

[maddyblue]

@knz is this fixed with the -flex-types option?

[knz]

Yes it is alleviated by -flex-types and what isn't will be addressed by #7733. Let's close this.