[MDEV-3420] LP:736780 - BETWEEN with datetime + NULL does not return NULL in 5.1-micro Created: 2011-03-17  Updated: 2020-09-08  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Philip Stoev (Inactive) Assignee: Sergei Golubchik
Resolution: Won't Fix Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug736780.xml    

 Description   

Not repeatable in mysql-5.1. In 5.1-micro, BETWEEN expressions that contain a datetime and NULL do not return NULL. Instead, they return FALSE, which is incorrect.

To repeat:

MySQL [test]> SELECT NOW() BETWEEN NULL AND '2011-01-01' ;
-------------------------------------

NOW() BETWEEN NULL AND '2011-01-01'

-------------------------------------

0

-------------------------------------
1 row in set (0.01 sec)

should have returned NULL

 Comments   
Comment by Rasmus Johansson (Inactive) [ 2011-03-17 ]

Launchpad bug id: 736780

Comment by Sergei Golubchik [ 2011-03-17 ]

Re: BETWEEN with datetime + NULL does not return NULL in 5.1-micro
This is intentional and correct.

Compare with SELECT 10 BETWEEN NULL AND 5;
Or with SELECT "Z" BETWEEN NULL AND "A";

Because the compared value is larger than the upper bound, the value of BETWEEN is false, even if the lower bound is unknown.

Generated at Thu Feb 08 06:48:30 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.