Why does this query give a different result when it's used as a sub-query?

Question

I have this data:

"config_timeslice_id","config_id","created"
14326,1145,"2021-08-31 13:45:00"
14325,1145,"2021-08-22 13:34:51"
14321,1145,"2021-06-16 10:47:59"
2357,942,"2019-12-24 10:09:38"

When I run this query:

SELECT config_timeslice_id 
FROM config_timeslice 
WHERE config_id = 1145 
AND created <= CURRENT_TIMESTAMP 
ORDER BY created DESC 
LIMIT 1

I get 14325, as I would expect, because today is 2021-08-23.

But when I run this query:

SELECT DISTINCT t.config_id,
(
  SELECT config_timeslice_id 
  FROM config_timeslice 
  WHERE config_id = t.config_id 
  AND created <= CURRENT_TIMESTAMP 
  ORDER BY created DESC 
  LIMIT 1
) AS ts_id
FROM config_timeslice t

I get:

config_id,ts_id
942,2357
1145,14321

I can’t figure out why the second row doesn’t give 14325

Possibly because `CURRENT_TIMESTAMP` has changed between the two times you run the query. — Gordon Linoff, Aug 23 '21 at 19:38
The code works fine for me: https://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=a9ba89495327a32b3c9e5efc082143f4 — forpas, Aug 23 '21 at 19:43
@forpas - Thanks! MariaDB 10.4.18 must have a bug. When I upgraded to 10.4.21, which is what dbfiddle uses, it works. — John Lindal, Aug 23 '21 at 19:53

score 0 · Answer 1 · answered Aug 23 '21 at 19:54

0

MariaDB 10.4.18 must have a bug. When I upgraded to 10.4.21, it works.

answered Aug 23 '21 at 19:54

John Lindal

616
5
11

Why does this query give a different result when it's used as a sub-query?

1 Answers1