Did you know -
The integer division operator in MySQL is DIV? The operator we all know and love, / , will always produce a DECIMAL as its output. so if you want it to yield an integer, use DIV:
mysql> CREATE TABLE T AS SELECT 4 DIV 3 AS DUMMY1, 4/3 AS DUMMY2, 4/4 AS DUMMY3;
Query OK, 1 row affected, 1 warning (0.03 sec)
Records: 1 Duplicates: 0 Warnings: 1
mysql> SELECT * FROM T; /* observe the results */
+--------+--------+--------+
| DUMMY1 | DUMMY2 | DUMMY3 |
+--------+--------+--------+
| 1 | 1.3333 | 1.0000 |
+--------+--------+--------+
1 row in set (0.01 sec)
mysql> DESC T; /* observe the data types each kind of division yielded */
+--------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| DUMMY1 | int | YES | | NULL | |
| DUMMY2 | decimal(5,4) | YES | | NULL | |
| DUMMY3 | decimal(5,4) | YES | | NULL | |
+--------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
How about other DBMSs? Well in Postgres, there is no DIV but there is of course /- and basically the way it works is if you divide integers, it will produce an integer. But say you divide floats or numeric types, then it will produce floats or numeric types.
postgres=# CREATE TABLE T AS SELECT 4/3 AS DUMMY1, 4::float/3::float AS DUMMY2, 4.0/3.0 AS DUMMY3, 4/4 AS DUMMY4;
SELECT 1
postgres=# SELECT * FROM T;
dummy1 | dummy2 | dummy3 | dummy4
--------+--------------------+--------------------+--------
1 | 1.3333333333333333 | 1.3333333333333333 | 1
(1 row)
postgres=# \d T
Table "public.t"
Column | Type | Collation | Nullable | Default
--------+------------------+-----------+----------+---------
dummy1 | integer | | |
dummy2 | double precision | | |
dummy3 | numeric | | |
dummy4 | integer | | |
This is something like exhibit #5001 as to why, when the topic of database interchangeability comes up - because “SQL is SQL” - I think people tend to underestimate the huge number of “gotchas” that can be encountered.
SQL here != SQL there.