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

Confusing row numbers upon ER_TRUNCATED_WRONG_VALUE with ROLLUP

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Trivial
    • Resolution: Unresolved
    • 10.2, 10.3, 10.4, 10.5, 10.6, 10.7
    • 10.4, 10.5, 10.6
    • Server
    • None

    Description

      create table t (c int);
      insert into t values (20121212),(9),(19800101),(20121212),(19800101);
      select c, count(*) from t group by convert(c, date) desc with rollup;
       
      # Cleanup
      drop table t;
      

      10.2 ceb40ef4

      +----------+----------+
      | c        | count(*) |
      +----------+----------+
      | 20121212 |        2 |
      | 19800101 |        2 |
      |        9 |        1 |
      |        9 |        5 |
      +----------+----------+
      4 rows in set, 3 warnings (0.002 sec)
       
      MariaDB [test]> show warnings;
      +---------+------+------------------------------------------------------------------+
      | Level   | Code | Message                                                          |
      +---------+------+------------------------------------------------------------------+
      | Warning | 1292 | Incorrect datetime value: '9' for column `test`.`t`.`c` at row 1 |
      | Warning | 1292 | Incorrect datetime value: '9' for column `test`.`t`.`c` at row 5 |
      | Warning | 1292 | Incorrect datetime value: '9' for column `test`.`t`.`c` at row 5 |
      +---------+------+------------------------------------------------------------------+
      3 rows in set (0.000 sec)
      

      Maybe the values are even correct from the internal perspective, but they look rather weird.

      ROW_NUMBER from MDEV-10075 in 10.7 returns the same value as included in the error message.

      The test case is a bit artificial, it's because the error messages contain the row number only in some specific cases. For example, this one doesn't display row numbers, even though according to ROW_NUMBER from MDEV-10075, at least in 10.7 they are the same as above:

      MariaDB [test]> create table t (c varchar(10));
      ERROR 1050 (42S01): Table 't' already exists
      MariaDB [test]> create or replace table t (c varchar(10));
      Query OK, 0 rows affected (0.074 sec)
       
      MariaDB [test]> insert into t values ('2012-12-12'),('foo'),('1980-01-01'),('2012-12-12'),('1980-01-01');
      Query OK, 5 rows affected (0.015 sec)
      Records: 5  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> select c, count(*) from t group by convert(c, date) desc with rollup;
      +------------+----------+
      | c          | count(*) |
      +------------+----------+
      | 2012-12-12 |        2 |
      | 1980-01-01 |        2 |
      | foo        |        1 |
      | foo        |        5 |
      +------------+----------+
      4 rows in set, 3 warnings (0.002 sec)
      

      MariaDB [test]> show warnings;
      +---------+------+---------------------------------+
      | Level   | Code | Message                         |
      +---------+------+---------------------------------+
      | Warning | 1292 | Incorrect datetime value: 'foo' |
      | Warning | 1292 | Incorrect datetime value: 'foo' |
      | Warning | 1292 | Incorrect datetime value: 'foo' |
      +---------+------+---------------------------------+
      3 rows in set (0.000 sec)
      

      bb-10.7-row_number c27f04ede5

      MariaDB [test]> get diagnostics condition 1 @n1 = row_number;
      Query OK, 0 rows affected (0.000 sec)
       
      MariaDB [test]> get diagnostics condition 2 @n2 = row_number;
      Query OK, 0 rows affected (0.000 sec)
       
      MariaDB [test]> get diagnostics condition 3 @n3 = row_number;
      Query OK, 0 rows affected (0.000 sec)
       
      MariaDB [test]> select @n1, @n2, @n3;
      +------+------+------+
      | @n1  | @n2  | @n3  |
      +------+------+------+
      |    1 |    5 |    5 |
      +------+------+------+
      1 row in set (0.001 sec)
      

      Attachments

        Issue Links

          Activity

            People

              rucha174 Rucha Deodhar
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.