MariaDB 10.3

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


10.3.32-MariaDB


CREATE TABLE tmp_dba 
(
  ID        INT  NOT NULL PRIMARY KEY,
  case_id   INT  DEFAULT NULL,
  client_id INT  NOT NULL,
  arrival   DATE DEFAULT NULL,
  departure DATE DEFAULT NULL

);
INSERT INTO tmp_dba 
VALUES
(1,  10, 1000,'2018-10-02','2019-04-25'),
(2,  10, 1000,'2019-04-26','2019-05-01'),
(3,  10, 1000,'2019-05-02',NULL),

(4,  20, 2000,'2018-11-21',NULL),

(5,  20, 2001,'2018-11-21',NULL),

(6,  20, 2002,'2018-11-21',NULL),

(7,  30, 3000,'2019-03-04','2022-01-01'),

(8,  30, 3001,'2019-04-04','2022-02-01'),

(9,  30, 3002,'2019-05-04','2022-03-01'),

(10, 30, 3003,'2019-06-04','2022-04-01'),

(11, 30, 3004,'2019-07-04','2022-05-01');
--
-- Try seeing which queries will (and will not) run under different versions of
-- PostgreSQL, MySQL and MariaDB. Google about ONLY_FULL_GROUP_BY!
--
--
--
-- Works with all versions of PostgreSQL, MySQL and MariaDB!
--
-- uses standard constructs!
--

SELECT
  case_id, client_id,
  MIN(arrival) AS m_d,
  CASE
    WHEN MAX(COALESCE(departure, '2038-01-01')) = '2038-01-01' THEN NULL
    ELSE MAX(COALESCE(departure, '2038-01-01'))
  END AS m_d
FROM
  tmp_dba
GROUP BY case_id, client_id
ORDER BY case_id, client_id;


case_id
client_id
m_d
m_d


10
1000
2018-10-02
null

20
2000
2018-11-21
null

20
2001
2018-11-21
null

20
2002
2018-11-21
null

30
3000
2019-03-04
2022-01-01

30
3001
2019-04-04
2022-02-01

30
3002
2019-05-04
2022-03-01

30
3003
2019-06-04
2022-04-01

30
3004
2019-07-04
2022-05-01


SHOW VARIABLES LIKE 'sql_mode';


Variable_name
Value


sql_mode
STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION


SELECT 
ID, case_id, client_id,
MIN(arrival) AS arrival,
IF(COUNT(departure) = COUNT(*), MAX(departure), NULL) AS departure  -- non-standard!
FROM tmp_dba
GROUP BY case_id, client_id
ORDER BY case_id;


ID
case_id
client_id
arrival
departure


1
10
1000
2018-10-02
null

4
20
2000
2018-11-21
null

5
20
2001
2018-11-21
null

6
20
2002
2018-11-21
null

7
30
3000
2019-03-04
2022-01-01

8
30
3001
2019-04-04
2022-02-01

9
30
3002
2019-05-04
2022-03-01

10
30
3003
2019-06-04
2022-04-01

11
30
3004
2019-07-04
2022-05-01


SHOW VARIABLES LIKE 'sql_mode';  --  This is MySQL/MariaDB specific!


Variable_name
Value


sql_mode
STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION


SET SESSION sql_mode = 	
'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
SHOW VARIABLES LIKE 'sql_mode';


Variable_name
Value


sql_mode
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION


SELECT 
  case_id, client_id,     -- removed ID fields!
MIN(arrival) AS arrival,
IF(COUNT(departure) = COUNT(*), MAX(departure), NULL) AS departure  -- non-standard!
FROM tmp_dba
GROUP BY case_id, client_id
ORDER BY case_id;


case_id
client_id
arrival
departure


10
1000
2018-10-02
null

20
2000
2018-11-21
null

20
2001
2018-11-21
null

20
2002
2018-11-21
null

30
3000
2019-03-04
2022-01-01

30
3001
2019-04-04
2022-02-01

30
3002
2019-05-04
2022-03-01

30
3003
2019-06-04
2022-04-01

30
3004
2019-07-04
2022-05-01


--
-- Dodgy implementation of FIRST_VALUE/LAST_VALUE... in MariaDB.
--
--

SELECT 
  case_id, client_id, fva AS first_arrival, lvd AS last_departure
FROM
(
  SELECT
    case_id, client_id, 
    FIRST_VALUE(arrival)  OVER (PARTITION BY case_id) AS fva,
    LAST_VALUE(departure) OVER (PARTITION BY case_id) AS lvd
  FROM
    tmp_dba
  ORDER BY ID
) AS sq
GROUP BY case_id, client_id, fva, lvd
ORDER BY case_id, client_id;  
Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

SELECT
  case_id, client_id, arrival
  departure
FROM tmp_dba
ORDER BY case_id, client_id, departure


case_id
client_id
departure


10
1000
2018-10-02

10
1000
2019-04-26

10
1000
2019-05-02

20
2000
2018-11-21

20
2001
2018-11-21

20
2002
2018-11-21

30
3000
2019-03-04

30
3001
2019-04-04

30
3002
2019-05-04

30
3003
2019-06-04

30
3004
2019-07-04


SELECT
  *, 
  FIRST_VALUE(arrival)  OVER (PARTITION BY case_id) AS fva,
  LAST_VALUE(departure) OVER (PARTITION BY case_id) AS lvd,
  
  LAST_VALUE
  (
  CASE
    WHEN departure IS NULL THEN CAST('2038-01-01' AS DATE)
    ELSE departure
  END) OVER (PARTITION BY case_id) AS lvd_1,
  
  
  LAST_VALUE(departure) 
    OVER 
      (
        PARTITION BY case_id
        ORDER BY 
          CASE
            WHEN departure IS NULL THEN '2038-01-01'
            ELSE departure
          END
      ) AS lvd_2
FROM
  tmp_dba
ORDER BY ID;
Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

SELECT 
  case_id, client_id, fva AS first_arrival, lvd AS last_departure
FROM
(
  SELECT
    case_id, client_id, 
    FIRST_VALUE(arrival)  OVER (PARTITION BY case_id) AS fva,
    LAST_VALUE(departure) OVER (PARTITION BY case_id) AS lvd
  FROM
    tmp_dba
  ORDER BY ID
) AS sq
GROUP BY case_id, client_id, fva, lvd
ORDER BY case_id, client_id
Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

EXPLAIN ANALYZE
SELECT 
  case_id, client_id,     -- removed ID fields!
MIN(arrival) AS arrival,
IF(COUNT(departure) = COUNT(*), MAX(departure), NULL) AS departure  -- non-standard!
FROM tmp_dba
GROUP BY case_id, client_id
ORDER BY case_id;
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 'ANALYZE
SELECT 
  case_id, client_id,     -- removed ID fields!
MIN(arrival) ...' at line 1

EXPLAIN ANALYZE  -- same plans as for IF - why not use standard construct?
SELECT
  case_id, client_id,
  MIN(arrival) AS m_d,
  CASE
    WHEN MAX(COALESCE(departure, '2038-01-01')) = '2038-01-01' THEN NULL
    ELSE MAX(COALESCE(departure, '2038-01-01'))
  END AS m_d
FROM
  tmp_dba
GROUP BY case_id, client_id
ORDER BY case_id, client_id;
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 'ANALYZE  -- same plans as for IF - why not use standard construct?
SELECT
  c...' at line 1

EXPLAIN ANALYZE
SELECT 
  case_id, client_id, fva AS first_arrival, lvd AS last_departure
FROM
(
  SELECT
    case_id, client_id, 
    FIRST_VALUE(arrival)  OVER (PARTITION BY case_id) AS fva,
    LAST_VALUE(departure) OVER (PARTITION BY case_id) AS lvd
  FROM
    tmp_dba
  ORDER BY ID
) AS sq
GROUP BY case_id, client_id, fva, lvd
ORDER BY case_id, client_id
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 'ANALYZE
SELECT 
  case_id, client_id, fva AS first_arrival, lvd AS last_depar...' at line 1

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT
  case_id, client_id,
  MIN(arrival) AS m_d,
  CASE
    WHEN MAX(COALESCE(departure, '2038-01-01')) = '2038-01-01' THEN NULL
    ELSE MAX(COALESCE(departure, '2038-01-01'))
  END AS m_d
FROM
  tmp_dba
GROUP BY case_id, client_id
ORDER BY case_id, client_id;
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 'ANALYZE, BUFFERS, VERBOSE)
SELECT
  case_id, client_id,
  MIN(arrival) AS m_d...' at line 1

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT 
  case_id, client_id, fva AS first_arrival, lvd AS last_departure
FROM
(
  SELECT
    case_id, client_id, 
    FIRST_VALUE(arrival)  OVER (PARTITION BY case_id) AS fva,
    LAST_VALUE(departure) OVER (PARTITION BY case_id) AS lvd
  FROM
    tmp_dba
  ORDER BY ID
) AS sq
GROUP BY case_id, client_id, fva, lvd
ORDER BY case_id, client_id
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 'ANALYZE, BUFFERS, VERBOSE)
SELECT 
  case_id, client_id, fva AS first_arrival...' at line 1
case_id	client_id	m_d	m_d
10	1000	2018-10-02	null
20	2000	2018-11-21	null
20	2001	2018-11-21	null
20	2002	2018-11-21	null
30	3000	2019-03-04	2022-01-01
30	3001	2019-04-04	2022-02-01
30	3002	2019-05-04	2022-03-01
30	3003	2019-06-04	2022-04-01
30	3004	2019-07-04	2022-05-01