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

Analyze table returns warnings due to rounding error or overflow while collection of EITS

Details

    • Bug
    • Status: Stalled (View Workflow)
    • Minor
    • Resolution: Unresolved
    • 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
    • 10.4(EOL)
    • Optimizer
    • None

    Description

      --source include/have_innodb.inc
      		 
       
      		 
      set @@optimizer_use_condition_selectivity=4;
      		 
      set @@use_stat_tables=PREFERABLY;
      		 
       
      		 
      create table t1 (a int)engine=InnoDB;
      		 
      insert into t1 values (1),(1),(2),(3);
      		 
       
      		 
      MariaDB [test]> analyze table t1;
      		 
      +---------+---------+----------+-----------------------------------------+
      		 
      | Table   | Op      | Msg_type | Msg_text                                |
      		 
      +---------+---------+----------+-----------------------------------------+
      		 
      | test.t1 | analyze | status   | Engine-independent statistics collected |
      		 
      | test.t1 | analyze | status   | OK                                      |
      		 
      +---------+---------+----------+-----------------------------------------+
      		 
       
      		 
      alter table t1 change a b int;
      		 
       
      		 
      MariaDB [test]> analyze table t1;
      		 
      +---------+---------+----------+----------------------------------------------------+
      		 
      | Table   | Op      | Msg_type | Msg_text                                           |
      		 
      +---------+---------+----------+----------------------------------------------------+
      		 
      | test.t1 | analyze | status   | Engine-independent statistics collected            |
      		 
      | test.t1 | analyze | Note     | Data truncated for column 'avg_frequency' at row 1 |
      		 
      | test.t1 | analyze | status   | OK                                                 |
      		 
      +---------+---------+----------+----------------------------------------------------+
      		 
       
      		 
      select * from mysql.column_stats;
      		 
      drop table t1;

      So here we see a truncation that happens due
      rounding off (we should suppress this because the user doesn't expects this)
      But we should be throwing a warning if we have an overflow.

      The current situations is like for ANALYZE command we don't make a choice if we want to throw a warning on truncation or not. The above case happens due to the bug MDEV-17778. So this MDEV is about making the choice as to if we want to throw warning or errors for truncation or overflow while calculating the statistics

      Attachments

        Issue Links

          is part of

          Task - A task that needs to be done. MDEV-15253 Default optimizer setting changes for MariaDB 10.4

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            varun Varun Gupta (Inactive) created issue -
            varun Varun Gupta (Inactive) made changes -
            Field Original Value New Value
            Description
            {code:sql}
            --source include/have_innodb.inc

            set @@optimizer_use_condition_selectivity=4;
            set @@use_stat_tables=PREFERABLY;

            create table t1 (a int)engine=InnoDB;
            insert into t1 values (1),(1),(2),(3);

            analyze table t1;
            alter table t1 change a b int;
            analyze table t1;
            show create table t1;
            select * from mysql.column_stats;
            drop table t1;
            {code}
            		{code:sql}
            --source include/have_innodb.inc

            set @@optimizer_use_condition_selectivity=4;
            set @@use_stat_tables=PREFERABLY;

            create table t1 (a int)engine=InnoDB;
            insert into t1 values (1),(1),(2),(3);

            MariaDB [test]> analyze table t1;
            +---------+---------+----------+-----------------------------------------+
            | Table | Op | Msg_type | Msg_text |
            +---------+---------+----------+-----------------------------------------+
            | test.t1 | analyze | status | Engine-independent statistics collected |
            | test.t1 | analyze | status | OK |
            +---------+---------+----------+-----------------------------------------+

            alter table t1 change a b int;

            MariaDB [test]> analyze table t1;
            +---------+---------+----------+----------------------------------------------------+
            | Table | Op | Msg_type | Msg_text |
            +---------+---------+----------+----------------------------------------------------+
            | test.t1 | analyze | status | Engine-independent statistics collected |
            | test.t1 | analyze | Note | Data truncated for column 'avg_frequency' at row 1 |
            | test.t1 | analyze | status | OK |
            +---------+---------+----------+----------------------------------------------------+

            select * from mysql.column_stats;
            drop table t1;
            {code}
            varun Varun Gupta (Inactive) made changes -
            Description {code:sql}
            --source include/have_innodb.inc

            set @@optimizer_use_condition_selectivity=4;
            set @@use_stat_tables=PREFERABLY;

            create table t1 (a int)engine=InnoDB;
            insert into t1 values (1),(1),(2),(3);

            MariaDB [test]> analyze table t1;
            +---------+---------+----------+-----------------------------------------+
            | Table | Op | Msg_type | Msg_text |
            +---------+---------+----------+-----------------------------------------+
            | test.t1 | analyze | status | Engine-independent statistics collected |
            | test.t1 | analyze | status | OK |
            +---------+---------+----------+-----------------------------------------+

            alter table t1 change a b int;

            MariaDB [test]> analyze table t1;
            +---------+---------+----------+----------------------------------------------------+
            | Table | Op | Msg_type | Msg_text |
            +---------+---------+----------+----------------------------------------------------+
            | test.t1 | analyze | status | Engine-independent statistics collected |
            | test.t1 | analyze | Note | Data truncated for column 'avg_frequency' at row 1 |
            | test.t1 | analyze | status | OK |
            +---------+---------+----------+----------------------------------------------------+

            select * from mysql.column_stats;
            drop table t1;
            {code}
            		{code:sql}
            --source include/have_innodb.inc

            set @@optimizer_use_condition_selectivity=4;
            set @@use_stat_tables=PREFERABLY;

            create table t1 (a int)engine=InnoDB;
            insert into t1 values (1),(1),(2),(3);

            MariaDB [test]> analyze table t1;
            +---------+---------+----------+-----------------------------------------+
            | Table | Op | Msg_type | Msg_text |
            +---------+---------+----------+-----------------------------------------+
            | test.t1 | analyze | status | Engine-independent statistics collected |
            | test.t1 | analyze | status | OK |
            +---------+---------+----------+-----------------------------------------+

            alter table t1 change a b int;

            MariaDB [test]> analyze table t1;
            +---------+---------+----------+----------------------------------------------------+
            | Table | Op | Msg_type | Msg_text |
            +---------+---------+----------+----------------------------------------------------+
            | test.t1 | analyze | status | Engine-independent statistics collected |
            | test.t1 | analyze | Note | Data truncated for column 'avg_frequency' at row 1 |
            | test.t1 | analyze | status | OK |
            +---------+---------+----------+----------------------------------------------------+

            select * from mysql.column_stats;
            drop table t1;
            {code}

            So here we see a truncation that happens due
            rounding off (we should suppress this because the user doesn't expects this)
            But we should be throwing a warning if we have an overflow.
            varun Varun Gupta (Inactive) made changes -
            varun Varun Gupta (Inactive) made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            varun Varun Gupta (Inactive) made changes -
            Description {code:sql}
            --source include/have_innodb.inc

            set @@optimizer_use_condition_selectivity=4;
            set @@use_stat_tables=PREFERABLY;

            create table t1 (a int)engine=InnoDB;
            insert into t1 values (1),(1),(2),(3);

            MariaDB [test]> analyze table t1;
            +---------+---------+----------+-----------------------------------------+
            | Table | Op | Msg_type | Msg_text |
            +---------+---------+----------+-----------------------------------------+
            | test.t1 | analyze | status | Engine-independent statistics collected |
            | test.t1 | analyze | status | OK |
            +---------+---------+----------+-----------------------------------------+

            alter table t1 change a b int;

            MariaDB [test]> analyze table t1;
            +---------+---------+----------+----------------------------------------------------+
            | Table | Op | Msg_type | Msg_text |
            +---------+---------+----------+----------------------------------------------------+
            | test.t1 | analyze | status | Engine-independent statistics collected |
            | test.t1 | analyze | Note | Data truncated for column 'avg_frequency' at row 1 |
            | test.t1 | analyze | status | OK |
            +---------+---------+----------+----------------------------------------------------+

            select * from mysql.column_stats;
            drop table t1;
            {code}

            So here we see a truncation that happens due
            rounding off (we should suppress this because the user doesn't expects this)
            But we should be throwing a warning if we have an overflow.
            		{code:sql}
            --source include/have_innodb.inc

            set @@optimizer_use_condition_selectivity=4;
            set @@use_stat_tables=PREFERABLY;

            create table t1 (a int)engine=InnoDB;
            insert into t1 values (1),(1),(2),(3);

            MariaDB [test]> analyze table t1;
            +---------+---------+----------+-----------------------------------------+
            | Table | Op | Msg_type | Msg_text |
            +---------+---------+----------+-----------------------------------------+
            | test.t1 | analyze | status | Engine-independent statistics collected |
            | test.t1 | analyze | status | OK |
            +---------+---------+----------+-----------------------------------------+

            alter table t1 change a b int;

            MariaDB [test]> analyze table t1;
            +---------+---------+----------+----------------------------------------------------+
            | Table | Op | Msg_type | Msg_text |
            +---------+---------+----------+----------------------------------------------------+
            | test.t1 | analyze | status | Engine-independent statistics collected |
            | test.t1 | analyze | Note | Data truncated for column 'avg_frequency' at row 1 |
            | test.t1 | analyze | status | OK |
            +---------+---------+----------+----------------------------------------------------+

            select * from mysql.column_stats;
            drop table t1;
            {code}

            So here we see a truncation that happens due
            rounding off (we should suppress this because the user doesn't expects this)
            But we should be throwing a warning if we have an overflow.

            The current situations is like for ANALYZE command we don't make a choice if we want to throw a warning on truncation or not. The above case happens due to the bug [MDEV-17778|https://jira.mariadb.org/browse/MDEV-17778]. So this MDEV is about making the choice as to if we want to throw warning or errors for truncation or overflow while calculating the statistics
            varun Varun Gupta (Inactive) added a comment -

            As discussed with psergey, reducing the priority of this issue

            varun Varun Gupta (Inactive) added a comment - As discussed with psergey , reducing the priority of this issue
            varun Varun Gupta (Inactive) made changes -
            Priority Major [ 3 ] Minor [ 4 ]
            varun Varun Gupta (Inactive) made changes -
            Status In Progress [ 3 ] Stalled [ 10000 ]
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 10.0 [ 16000 ]
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 10.1 [ 16100 ]
            julien.fritsch Julien Fritsch made changes -
            Assignee Varun Gupta [ varun ] Sergei Petrunia [ psergey ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 90792 ] MariaDB v4 [ 143565 ]
            ralf.gebhardt Ralf Gebhardt made changes -
            Fix Version/s 10.2 [ 14601 ]
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 10.3 [ 22126 ]

            People

              psergei Sergei Petrunia
              varun Varun Gupta (Inactive)
              Votes:
              1 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.