Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-19779

Optimizer replacement of expressions leads to 'value is out of range' exception and ignores range restrictions.

    Details

    • Type: Bug
    • Status: Confirmed (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3, 10.4, 10.4.5
    • Fix Version/s: 5.5
    • Component/s: Optimizer
    • Labels:
    • Environment:
      MariaDB Docker (Bionic) on Ubuntu Linux 18.04

      Description

      Summary: The following sql query leads to ' BIGINT value is out of range in '"A"."B"."C" * "A"."B"."C" * "A"."B"."C" * "A"."B"."C" * "A"."B"."C" * "A"."B"."C"'', propably due to expression replacement:

      CREATE SCHEMA A;
      CREATE TABLE A.B (C BIGINT);
      INSERT INTO A.B VALUES (-5500);
      SELECT 1 AS A FROM (SELECT B.C * B.C AS A FROM A.B WHERE B.C * B.C < 10) A WHERE ( A.A * A.A * A.A * A.A ) = A.A ;
      

      Normally, the condition in the inner SELECT should restrict execution of the outher WHERE clause to 'valid' expressions, but it seems like the expression 'B.C * B.C' from the inner select replaces the outer ones without the check before.

      In Postgres, this is handled correctly, no exception occurs (and no result is returned because nothing machtes the query currently), in MariaDB, an exception occurs.

      The same exception occurs in MySQL 8.0.16. I'm not sure about other mariadb or mysql versions.

      Even though this example is a bit 'constructed', all WHERE conditions which restrict the range of expressions are affected.

        Attachments

          Activity

            People

            • Assignee:
              igor Igor Babaev
              Reporter:
              Alicen -
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated: