2

Reading the documentation. In the comparison expressions it says:

  • If both arguments are integers, they are compared as integers. ...

  • If one of the arguments is a decimal value, comparison depends on the other argument. The arguments are compared as decimal values if the other argument is a decimal or integer value, or as floating-point values if the other argument is a floating-point value.

  • In all other cases, the arguments are compared as floating-point (double-precision) numbers. For example, a comparison of string and numeric operands takes place as a comparison of floating-point numbers. ...

There are two things I don't understand:

  1. What does the documentation mean by "...they are compared as integers"? Does the documentation refer to what is compared as INT or BIGINT or TINYINT, etc?

  2. What does the documentation mean by "...or as floating-point values if the other argument is a floating-point value"? Does the documentation refer to a comparison as FLOAT (single precision) or DOUBLE (double precision)? Or does it depend on the context? For example, if it is:

CREATE OR REPLACE TABLE my_table (
    id INT NOT NULL PRIMARY KEY,
    float_v FLOAT,
    double_v DOUBLE,
    decimal_v DECIMAL
);

SELECT 
float_v = double_v as "The precision is DOUBLE",
float_v = float_v as "The precision is FLOAT",
float_v = decimal_v as "The precision is FLOAT",
double_v = decimal_v as "The precision is DOUBLE",
decimal_v = decimal_v as "The precision is DECIMAL",
decimal_v = 1 as "The precision is DECIMAL";
/* etc.*/

This is correct?

As for arithmetic expressions:

If any approximate values are present, the expression is approximate and is evaluated using floating-point arithmetic.

If no approximate values are present, the expression contains only exact values. If any exact value contains a fractional part (a value following the decimal point), the expression is evaluated using DECIMAL exact arithmetic and has a precision of 65 digits. The term “exact” is subject to the limits of what can be represented in binary. For example, 1.0/3.0 can be approximated in decimal notation as .333..., but not written as an exact number, so (1.0/3.0)*3.0 does not evaluate to exactly 1.0.

Otherwise, the expression contains only integer values. The expression is exact and is evaluated using integer arithmetic and has a precision the same as BIGINT (64 bits).

If a numeric expression contains any strings, they are converted to double-precision floating-point values and the expression is approximate.

Also, there are two things I don't understand:

  1. Approximate data types are FLOAT and DOUBLE, so what do you mean by "... floating-point arithmetic"? Does it use single (FLOAT) or double (DOUBLE) arithmetic precision? Or just like comparison expressions, these are context dependent, for example:
SELECT 
float_v + double_v as "The precision is DOUBLE",
float_v + float_v as "The precision is FLOAT",
float_v + decimal_v as "The precision is FLOAT",
double_v + decimal_v as "The precision is DOUBLE",
decimal_v + decimal_v as "The precision is DECIMAL",
decimal_v + 1 as "The precision is DECIMAL";
/* etc.*/
  1. Expression handling Does it apply to expression comparisons, or even to expressions in general? For example, when it says "...Otherwise, the expression contains only integer values. The expression is exact and is evaluated using integer arithmetic and has a precision the same as BIGINT (64 bits)", Does this also apply to arithmetic, relational and logical expressions?

Thanks.

Grizzly
  • 371
  • 2
  • 13
  • 1
    1. Yes, since they have an integer form, equality can be computed in exact form, unlike flatingpoint where equality is tricky. – The Impaler Jul 07 '22 at 01:16
  • 1
    2. If one is floating point (float or double) the integer value is converted to the other format, and then the comparison is computed. Equality may work well, or not, depending on the specific values. – The Impaler Jul 07 '22 at 01:17
  • 1
    3. If both are float, then the comparison is in float precision. If one has higher precision, the lowest one is casted to the highest precision and then the comparison is made. – The Impaler Jul 07 '22 at 01:20
  • 1
    4. Expression handling applies to all math operation between numbers and comparison operators as well. Relational operators are also included since behind the scenes they need to use the previous ones (for example `IN` or `< ALL`). – The Impaler Jul 07 '22 at 01:22
  • @TheImpaler now I understand much better. but, Do you know how this behaves in `CREATE TABLE ... SELECT` statements? Since when doing the same, the values are always saved as `DOUBLE`. – Grizzly Jul 07 '22 at 15:56
  • When you run a `CREATE TABLE T AS SELECT ...` the engine computes each one of the expressions in the `list-of-expressions` separately. MySQL in particular, ends up converting the result to `DECIMAL`, `FLOAT`, or `DOUBLE` only. It cannot cast to integer values. See example https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=58516e6512fa9d449cc8a210944acffe – The Impaler Jul 07 '22 at 16:31

0 Answers0