|
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)
|
|