[MDEV-13358] FIRST_V throw SQL Fehler (1292): Incorrect datetime value: '' Created: 2017-07-20  Updated: 2020-08-25  Resolved: 2017-10-11

Status: Closed
Project: MariaDB Server
Component/s: Optimizer - Window functions, Temporal Types
Affects Version/s: 10.2.6, 10.2.7, 10.2
Fix Version/s: 10.2.9

Type: Bug Priority: Major
Reporter: Richard Stracke Assignee: Vicențiu Ciorbaru
Resolution: Fixed Votes: 1
Labels: None
Environment:

tested on 10.2.7 Ubuntu and 10.2.6 Docker (official container)


 Description   

To reproduce:

CREATE TABLE IF NOT EXISTS `fv_test` (
 
  `SOME_DATE` datetime NOT NULL
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 
 
INSERT INTO `aa`.`fv_test` (`SOME_DATE`) VALUES ('2017-07-20 12:47:56');


create table fv_result
 
SELECT 
FIRST_VALUE(SOME_DATE) OVER(ORDER BY SOME_DATE DESC) AS somedate
 
FROM fv_test;


SQL Fehler (1292): Incorrect datetime value: '' for column 'somedate'

The select (without create table) throw only a warning.

It will caused by FIRST_VALUE, the error persists if the datatype in the over clause will be changed to non-datetime, but vanished if the datatype/column in the first_value will be changed to non-datetime.

 Comments   
Comment by Elena Stepanova [ 2017-07-20 ]

Thanks for the report, reproducible as described. Same test case as above:

CREATE TABLE IF NOT EXISTS `fv_test` (
 
  `SOME_DATE` datetime NOT NULL
 
);
 
 
INSERT INTO `fv_test` (`SOME_DATE`) VALUES ('2017-07-20 12:47:56');
 
 
 
create table fv_result
 
SELECT 
FIRST_VALUE(SOME_DATE) OVER(ORDER BY SOME_DATE DESC) AS somedate
 
FROM fv_test;
 
 
 
drop table fv_test, fv_result;

Comment by Vicențiu Ciorbaru [ 2017-09-11 ]

The root cause is the same as the one for MDEV-13240. Backporting the patch to 10.2 fixes the issue.

Generated at Thu Feb 08 08:04:59 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.