Types of mismatches
=================================
-----------------------------------------
delete_time, update_time
Caused by wrong behavior in Oracle MySQL, and correspondingly wrong result file. Here is the simplified version of the problem:
CREATE TABLE t (c1 TIME);
INSERT INTO t VALUES ('02:02:02'),(105454);
SELECT c1 FROM t WHERE c1 IN ('2:2:2',105454);
c1
10:54:54
SELECT c1 FROM t WHERE c1 IN ('2:2:2');
c1
02:02:02
Filed as bug http://bugs.mysql.com/bug.php?id=66034.
MariaDB 5.3 and higher produces the correct result, so the fix for this test is updating the result file.
-----------------------------------------
iuds.insert_time, update_delete_calendar
Caused by different approach to invalid values handling. Here is the example:
MySQL:
CREATE TABLE t (c1 TIME, c2 TIME);
INSERT INTO t VALUES (1234567, 123456789);
Warnings:
Warning 1264 Out of range value for column 'c1' at row 1
Warning 1264 Out of range value for column 'c2' at row 1
SELECT * FROM t;
c1 c2
00:00:00 838:59:59
MariaDB:
CREATE TABLE t (c1 TIME, c2 TIME);
INSERT INTO t VALUES (1234567, 123456789);
Warnings:
Warning 1265 Data truncated for column 'c1' at row 1
Warning 1265 Data truncated for column 'c2' at row 1
SELECT * FROM t;
c1 c2
00:00:00 00:00:00
So, mismatches include different warning code/text and different results. MariaDB behavior seems to be consistent, so result files can be updated. At the same time, there is nothing obviously wrong with MySQL behavior, either, so no good reason to report a bug.
-----------------------------------------
insert_time
MySQL:
CREATE TABLE t (c1 TIME);
INSERT INTO t VALUES ('11:11:11');
SELECT * FROM t WHERE c1 IN (NULL,'11:11:11');
c1
MariaDB:
CREATE TABLE t (c1 TIME);
INSERT INTO t VALUES ('11:11:11');
SELECT * FROM t WHERE c1 IN (NULL,'11:11:11');
c1
11:11:11
MariaDB result seems correct (btw postgres also returns the row). Not filing another MySQL bug as it might be a variation of MySQL bug#66034. Updating the result file.
-----------------------------------------
insert_time:
MySQL 5.1, 5.5:
SELECT CAST(235959.123456 AS TIME);
CAST(235959.123456 AS TIME)
23:59:59.123456
MariaDB 5.3+, MySQL 5.6:
SELECT CAST(235959.123456 AS TIME);
CAST(235959.123456 AS TIME)
23:59:59
Apparently MySQL considered it a bug, since it has been changed in 5.6, and there is nothing in manual that suggests that cast should return fractional seconds, so MariaDB results look correct.
Variation of the same problem (comparing to the previous case, the one below has a longer fractional part and causes a warning in MySQL 5.1/5.5):
MySQL 5.1, 5.5:
SELECT CAST(235959.1234567 AS TIME);
CAST(235959.1234567 AS TIME)
23:59:59.123456
Warnings:
Warning 1292 Truncated incorrect time value: '235959.1234567'
MariaDB 5.3+, MySQL 5.6:
SELECT CAST(235959.1234567 AS TIME);
CAST(235959.1234567 AS TIME)
23:59:59
-----------------------------------------
insert_time
Caused by microseconds being supported in MariaDB. Here is the simplified difference:
MySQL:
CREATE TABLE t (c1 TIME);
INSERT INTO t VALUES ('10:11:12.123');
SELECT * FROM t WHERE c1 = '10:11:12.123';
c1
10:11:12
DROP TABLE t;
MariaDB:
CREATE TABLE t (c1 TIME);
INSERT INTO t VALUES ('10:11:12.123');
SELECT * FROM t WHERE c1 = '10:11:12.123';
c1
DROP TABLE t;
The result is understandable considering documentation on microseconds in MariaDB (http://kb.askmonty.org/en/microseconds-in-mariadb/) – here TIME is TIME(0), and the inserted value is truncated. However, it does not look right that truncation is performed without a warning. Filed a low-priority bug MDEV-413 about the missing warning.
-----------------------------------------
insert time
Comes from the fact that in MySQL values are compared as strings, while in MariaDB as temporal structures:
MySQL:
CREATE TABLE t (c1 TIME);
INSERT INTO t VALUES ('00:00:00'),('12:00:00'),('22:23:24');
SELECT * FROM t WHERE c1 >= '12colon34colon56';
c1
22:23:24
Warnings:
Warning 1292 Incorrect time value: '12colon34colon56' for column 'c1' at row 1
MariaDB:
CREATE TABLE t (c1 TIME);
INSERT INTO t VALUES ('00:00:00'),('12:00:00'),('22:23:24');
SELECT * FROM t WHERE c1 >= '12colon34colon56';
c1
00:00:00
12:00:00
22:23:24
Warnings:
Warning 1292 Truncated incorrect time value: '12colon34colon56'
Since it is by design (http://kb.askmonty.org/en/microseconds-in-mariadb/), it should also be modified in the result file.
-----------------------------------------
insert_decimal, type_bit_iuds
Differences in error messages:
-ERROR 42000: Display width out of range for column 'c1' (max = 64)
+ERROR 42000: Display width out of range for 'c1' (max = 64)
and alike
Introduced in revno 2502.1147.39, 2502.1147.41 by serg ('column' was removed, apparently on purpose, as it was the only change)
Update result files
-----------------------------------------
type_bit_iuds, insert_number
Differences in error codes:
-Error 1292 Truncated incorrect DECIMAL value: ''
+Error 1916 Got overflow when converting '101010101010101010101010101010' to INT. Value truncated.
Introduced in revno 3005 by Monty (specifically changed error numbers to avoid conflicts)
Update result files
-----------------------------------------
update_delete_calendar
Some expected warnings are missing:
SELECT count
FROM t1 WHERE c2='10:45:15' OR c2='2009-01-32' OR c2='2009-02-30' OR c2='2009-04-31' OR c2='2009-06-31' OR c2='2009-09-31' /* returns 0 */;
count
0
-Warnings:
-Warning 1292 Incorrect date value: '10:45:15' for column 'c2' at row 1
-Warning 1292 Incorrect date value: '2009-01-32' for column 'c2' at row 1
-Warning 1292 Incorrect date value: '10:45:15' for column 'c2' at row 1
-Warning 1292 Incorrect date value: '2009-01-32' for column 'c2' at row 1
-Warning 1292 Incorrect date value: '10:45:15' for column 'c2' at row 1
-Warning 1292 Incorrect date value: '2009-01-32' for column 'c2' at row 1
MySQL produces them 3 times each, which is hardly correct (actually, there should be one per wrong value), but MariaDB does not produce any at all. It does not look right, filed bug MDEV-414 about warnings missing depending on the execution plan.
Since it's a low-priority issue, I will update the result file according to the current behavior; when/if the bug gets fixed, we will need to check and re-create the result files.
Similar problem in insert_number and update_delete_number tests, only in this case MariaDB produces a warning, but it's not consistent (depends on the execution plan). Added a case to MDEV-414.
+Warnings:
+Warning 1264 Out of range value for column 'c2' at row 1
-----------------------------------------
update_delete_calendar
Another difference in warnings:
SELECT * FROM t1 WHERE c2='0000-00-00';
..
-Warning 1292 Incorrect date value: '0000-00-00' for column 'c2' at row 1
-Warning 1292 Incorrect date value: '0000-00-00' for column 'c2' at row 1
+Warning 1264 Out of range value for column 'c2' at row 1
-Warning 1292 Incorrect datetime value: '20020109 01:30:40' for column 'c2' at row 1
-Warning 1292 Incorrect datetime value: '20020109 01:30:40' for column 'c2' at row 1
-Warning 1292 Incorrect datetime value: '20020109 01:30:50' for column 'c2' at row 1
+Warning 1292 Incorrect datetime value: '20020109 01:30:40'
+Warning 1292 Incorrect datetime value: '20020109 01:30:50'
These ones look harmless, and it's also good that there is no duplication. Should be safe to update the result file.
-----------------------------------------
funcs.an_calendar, funcs.datetime_function, funcs.de_calendar_range, funcs.in_calendar_pk_constraint_duplicate_update, funcs.in_calendar_pk_constraint_error, funcs.in_calendar_pk_constraint_ignore, funcs.in_calendar_unique_constraint_duplicate_update, funcs.in_calendar_unique_constraint_error, funcs.in_calendar_unique_constraint_ignore, funcs.in_multicolumn_calendar_pk_constraint_duplicate_update, funcs.in_multicolumn_calendar_pk_constraint_error, funcs.in_multicolumn_calendar_pk_constraint_error, funcs.in_multicolumn_calendar_unique_constraint_duplicate_update, funcs.in_multicolumn_calendar_unique_constraint_error, funcs.in_multicolumn_calendar_unique_constraint_ignore, funcs.in_multicolumn_number_pk_constraint_duplicate_update, funcs.ld_all_number_string_calendar_types, funcs.up_calendar_range:
MariaDB produces warnings while inserting a datetime value into a time column:
CREATE TABLE t1(c1 TIME NULL, c2 TIME NULL);
SET TIMESTAMP=1171346973;
INSERT INTO t1 VALUES(NOW(),NOW());
+Warnings:
+Note 1265 Data truncated for column 'c1' at row 1
+Note 1265 Data truncated for column 'c2' at row 1
Nothing wrong with that, adding to the result files.
-----------------------------------------
funcs.db_alter_collate_ascii, funcs.db_alter_collate_utf8
Additional croatian collations in MariaDB
+utf8_croatian_ci utf8 213 # #
+ucs2_croatian_ci ucs2 149 # #
Adding to result file
-----------------------------------------
funcs.de_calendar_range, funcs.up_calendar_range
Difference in result:
DELETE FROM t1 WHERE c1 <= ADDTIME(NOW(),'2 02:01:01');
SELECT * FROM t1 ORDER BY c1;
c1
-12:10:34
-13:10:34
It works as specified in http://kb.askmonty.org/en/microseconds-in-mariadb/ (when a DATETIME value is compared to a TIME value, the latter is treated as a full datetime with a zero date part, similar to comparing DATE to a DATETIME, or to comparing DECIMAL numbers. Earlier versions of MariaDB used to compare only the time part of both operands in such a case.)
Adding to result file
-----------------------------------------
funcs.in_number_boundary_error, funcs.in_number_decimal_boundary_error
-ERROR HY000: Incorrect integer value: 'x' for column 'c1' at row 1
+ERROR 22007: Incorrect integer value: 'x' for column 'c1' at row 1
Specific error code looks better, adding to the result file.
-----------------------------------------
funcs.sq_all
SELECT c1 FROM t1 WHERE c1 >= ALL (SELECT c1 FROM t2);
c1
-100
and alike. It's a difference in MySQL and MariaDB handling of ALL ( .. ) where the ALL subquery has NULLs among other values.
MySQL:
INSERT INTO t1 VALUES (1),(5),(NULL);
SELECT * FROM t1 WHERE 6 > ALL (SELECT i FROM t1);
i
1
5
NULL
MariaDB:
INSERT INTO t1 VALUES (1),(5),(NULL);
SELECT * FROM t1 WHERE 6 > ALL (SELECT i FROM t1);
i
SELECT 5 > NULL;
At the same time, both return
SELECT 5 > NULL;
5 > NULL
NULL
So, MariaDB behavior for ALL seems consistent (6>NULL is NULL, to the whole ALL clause is null, which is not TRUE).
Updating result files.
-----------------------------------------
funcs.ld_all_number_string_calendar_types, funcs.se_join_left_outer, funcs.se_join_natural_left. funcs.se_join_natural_left_outer
Mismatches due to different result sorting. The tests run numerous SELECTs without ORDER BY and without sorted_result. Adding sorted_result to the test files (to all SELECTs, although not all of the cause mismatches at the moment).
The fixed suite is in https://code.launchpad.net/~elenst/maria/5.3-mdev-369, the merge proposal in
https://code.launchpad.net/~elenst/maria/5.3-mdev-369/+merge/117210
please, push
pushed to 5.3 http://bazaar.launchpad.net/~maria-captains/maria/5.3/revision/3553.
5.5 requires some more changes.
Additional failures observed on 5.5 only. 8 tests failed after the merge. 5 conflicts, due changes in Oracle test/result files between MySQL 5.1 and 5.5, and also the following:
-----------------------------------------
insert_number:
-Warnings:
-Warning 1292 Truncated incorrect DECIMAL value: ''
A warning on a truncated value was changed in 5.3 (a different code, different text), and it was okay; but now it disappeared at all, which does not look right. Filed bug MDEV-424 (Overflow / value truncated warning is not produced on a SELECT containing such a value in WHERE clause), could be a variation of MDEV-414, but only reproducible on 5.5.
Updating result files for now, but they will need to be modified if/when the bug is fixed.
-----------------------------------------
Some more mismatches observed in 5.5 only:
-----------------------------------------
funcs.db_use_error:
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DATABASE nond6' at line 1
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to
-----------------------------------------
funcs.sq_error:
-ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
+ERROR 42000: This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
-----------------------------------------
iuds.update_time
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':11:11 WHERE c2=NULL' at line 1
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ':11:11 WHERE c2=NULL' at line 1
All of the same obvious nature, updating result files.
-----------------------------------------
iuds.update_time
-Warning 1264 Out of range value for column 'c2' at row 2
+Warning 1265 Data truncated for column 'c2' at row 2
Updating the result file.
===========================================
Pushed the merge and additional changes to maria/5.5 http://bazaar.launchpad.net/~maria-captains/maria/5.5/revision/3481