Hallo,
die folgende Abfrage in MariaDB 10.4 hat "no rows" als Ergebnis - was nicht korrekt ist. In MariaDB 10.1 sowie anderen MySQL Datenbanken erhalte ich für die gleiche Abfrage immer das richtige Ergebnis. Wäre super, wenn mir jemand helfen könnte zu verstehen warum MariaDB 10.4 "no rows" als Ergebnis für diese Abfrage liefert?
Vielen Dank!
SELECT SQL_CALC_FOUND_ROWS DISTINCT wp_posts.ID FROM wp_posts LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )
LEFT JOIN wp_postmeta as priority_meta ON(
wp_posts.ID = priority_meta.post_id AND priority_meta.meta_key = '_featured'
)
LEFT JOIN( SELECT listing_id,
MIN( CASE
WHEN (repeat_unit = 'NONE')
THEN IF(start_date >= '2021-05-20 08:11:32', start_date, NULL)
WHEN (start_date >= '2021-05-20 08:11:32')
THEN start_date
WHEN (repeat_unit = 'DAY') THEN (
IF(
DATE_ADD( start_date, INTERVAL ( frequency * CEIL(
( TIMESTAMPDIFF( DAY, start_date, '2021-05-20 08:11:32' ) / frequency ) + 0.00001
) ) DAY ) <= repeat_end,
DATE_ADD( start_date, INTERVAL ( frequency * CEIL(
( TIMESTAMPDIFF( DAY, start_date, '2021-05-20 08:11:32' ) / frequency ) + 0.00001
) ) DAY ),
NULL
)
)
ELSE (
IF (
DATE_ADD( start_date, INTERVAL ( frequency * CEIL(
( TIMESTAMPDIFF( MONTH, start_date, '2021-05-20 08:11:32' ) / frequency ) + 0.00001
) ) MONTH ) <= repeat_end,
DATE_ADD( start_date, INTERVAL ( frequency * CEIL(
( TIMESTAMPDIFF( MONTH, start_date, '2021-05-20 08:11:32' ) / frequency ) + 0.00001
) ) MONTH ),
NULL
)
)
END ) AS next_start,
MAX( CASE
WHEN (repeat_unit = 'NONE')
THEN IF(end_date >= '2021-05-20 08:11:32' AND start_date <= '2021-05-20 08:11:32', end_date, NULL)
WHEN (end_date >= '2021-05-20 08:11:32')
THEN IF(start_date <= '2021-05-20 08:11:32', end_date, NULL)
WHEN (repeat_unit = 'DAY') THEN (
IF (
DATE_ADD( start_date, INTERVAL ( frequency * FLOOR(
TIMESTAMPDIFF( DAY, start_date, '2021-05-20 08:11:32' ) / frequency
) ) DAY ) <= repeat_end,
DATE_ADD( end_date, INTERVAL ( frequency * FLOOR(
TIMESTAMPDIFF( DAY, start_date, '2021-05-20 08:11:32' ) / frequency
) ) DAY ),
NULL
)
)
ELSE (
IF (
DATE_ADD( start_date, INTERVAL ( frequency * FLOOR(
TIMESTAMPDIFF( MONTH, start_date, '2021-05-20 08:11:32' ) / frequency
) ) MONTH ) <= repeat_end,
DATE_ADD( end_date, INTERVAL ( frequency * FLOOR(
TIMESTAMPDIFF( MONTH, start_date, '2021-05-20 08:11:32' ) / frequency
) ) MONTH ),
NULL
)
)
END ) AS prev_end,
MAX( CASE
WHEN (repeat_unit = 'NONE')
THEN IF(end_date >= '2021-05-20 08:11:32' AND start_date <= '2021-05-20 08:11:32', start_date, NULL)
WHEN (end_date >= '2021-05-20 08:11:32')
THEN IF(start_date <= '2021-05-20 08:11:32', start_date, NULL)
WHEN (repeat_unit = 'DAY') THEN (
IF (
DATE_ADD( start_date, INTERVAL ( frequency * FLOOR(
TIMESTAMPDIFF( DAY, start_date, '2021-05-20 08:11:32' ) / frequency
) ) DAY ) <= repeat_end,
DATE_ADD( start_date, INTERVAL ( frequency * FLOOR(
TIMESTAMPDIFF( DAY, start_date, '2021-05-20 08:11:32' ) / frequency
) ) DAY ),
NULL
)
)
ELSE (
IF (
DATE_ADD( start_date, INTERVAL ( frequency * FLOOR(
TIMESTAMPDIFF( MONTH, start_date, '2021-05-20 08:11:32' ) / frequency
) ) MONTH ) <= repeat_end,
DATE_ADD( start_date, INTERVAL ( frequency * FLOOR(
TIMESTAMPDIFF( MONTH, start_date, '2021-05-20 08:11:32' ) / frequency
) ) MONTH ),
NULL
)
)
END ) AS prev_start
FROM wp_mylisting_events
WHERE ( field_key = 'event-date' )
GROUP BY listing_id
) AS `recur_event-date` ON (wp_posts.ID = `recur_event-date`.listing_id) WHERE 1=1 AND (
wp_term_relationships.term_taxonomy_id IN (18)
) AND (
( wp_postmeta.meta_key = '_case27_listing_type' AND wp_postmeta.meta_value = 'event' )
) AND wp_posts.post_type = 'job_listing' AND ((wp_posts.post_status = 'publish')) AND (
`recur_event-date`.next_start >= '2021-05-20 08:11:32'
OR `recur_event-date`.prev_end >= '2021-05-20 08:11:32'
) GROUP BY wp_posts.ID ORDER BY CAST( COALESCE( priority_meta.meta_value, 0 ) AS UNSIGNED ) DESC , IF(
`recur_event-date`.prev_end >= '2021-05-20 08:11:32',
`recur_event-date`.prev_start,
`recur_event-date`.next_start
) ASC LIMIT 0, 6
die folgende Abfrage in MariaDB 10.4 hat "no rows" als Ergebnis - was nicht korrekt ist. In MariaDB 10.1 sowie anderen MySQL Datenbanken erhalte ich für die gleiche Abfrage immer das richtige Ergebnis. Wäre super, wenn mir jemand helfen könnte zu verstehen warum MariaDB 10.4 "no rows" als Ergebnis für diese Abfrage liefert?
Vielen Dank!
SELECT SQL_CALC_FOUND_ROWS DISTINCT wp_posts.ID FROM wp_posts LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )
LEFT JOIN wp_postmeta as priority_meta ON(
wp_posts.ID = priority_meta.post_id AND priority_meta.meta_key = '_featured'
)
LEFT JOIN( SELECT listing_id,
MIN( CASE
WHEN (repeat_unit = 'NONE')
THEN IF(start_date >= '2021-05-20 08:11:32', start_date, NULL)
WHEN (start_date >= '2021-05-20 08:11:32')
THEN start_date
WHEN (repeat_unit = 'DAY') THEN (
IF(
DATE_ADD( start_date, INTERVAL ( frequency * CEIL(
( TIMESTAMPDIFF( DAY, start_date, '2021-05-20 08:11:32' ) / frequency ) + 0.00001
) ) DAY ) <= repeat_end,
DATE_ADD( start_date, INTERVAL ( frequency * CEIL(
( TIMESTAMPDIFF( DAY, start_date, '2021-05-20 08:11:32' ) / frequency ) + 0.00001
) ) DAY ),
NULL
)
)
ELSE (
IF (
DATE_ADD( start_date, INTERVAL ( frequency * CEIL(
( TIMESTAMPDIFF( MONTH, start_date, '2021-05-20 08:11:32' ) / frequency ) + 0.00001
) ) MONTH ) <= repeat_end,
DATE_ADD( start_date, INTERVAL ( frequency * CEIL(
( TIMESTAMPDIFF( MONTH, start_date, '2021-05-20 08:11:32' ) / frequency ) + 0.00001
) ) MONTH ),
NULL
)
)
END ) AS next_start,
MAX( CASE
WHEN (repeat_unit = 'NONE')
THEN IF(end_date >= '2021-05-20 08:11:32' AND start_date <= '2021-05-20 08:11:32', end_date, NULL)
WHEN (end_date >= '2021-05-20 08:11:32')
THEN IF(start_date <= '2021-05-20 08:11:32', end_date, NULL)
WHEN (repeat_unit = 'DAY') THEN (
IF (
DATE_ADD( start_date, INTERVAL ( frequency * FLOOR(
TIMESTAMPDIFF( DAY, start_date, '2021-05-20 08:11:32' ) / frequency
) ) DAY ) <= repeat_end,
DATE_ADD( end_date, INTERVAL ( frequency * FLOOR(
TIMESTAMPDIFF( DAY, start_date, '2021-05-20 08:11:32' ) / frequency
) ) DAY ),
NULL
)
)
ELSE (
IF (
DATE_ADD( start_date, INTERVAL ( frequency * FLOOR(
TIMESTAMPDIFF( MONTH, start_date, '2021-05-20 08:11:32' ) / frequency
) ) MONTH ) <= repeat_end,
DATE_ADD( end_date, INTERVAL ( frequency * FLOOR(
TIMESTAMPDIFF( MONTH, start_date, '2021-05-20 08:11:32' ) / frequency
) ) MONTH ),
NULL
)
)
END ) AS prev_end,
MAX( CASE
WHEN (repeat_unit = 'NONE')
THEN IF(end_date >= '2021-05-20 08:11:32' AND start_date <= '2021-05-20 08:11:32', start_date, NULL)
WHEN (end_date >= '2021-05-20 08:11:32')
THEN IF(start_date <= '2021-05-20 08:11:32', start_date, NULL)
WHEN (repeat_unit = 'DAY') THEN (
IF (
DATE_ADD( start_date, INTERVAL ( frequency * FLOOR(
TIMESTAMPDIFF( DAY, start_date, '2021-05-20 08:11:32' ) / frequency
) ) DAY ) <= repeat_end,
DATE_ADD( start_date, INTERVAL ( frequency * FLOOR(
TIMESTAMPDIFF( DAY, start_date, '2021-05-20 08:11:32' ) / frequency
) ) DAY ),
NULL
)
)
ELSE (
IF (
DATE_ADD( start_date, INTERVAL ( frequency * FLOOR(
TIMESTAMPDIFF( MONTH, start_date, '2021-05-20 08:11:32' ) / frequency
) ) MONTH ) <= repeat_end,
DATE_ADD( start_date, INTERVAL ( frequency * FLOOR(
TIMESTAMPDIFF( MONTH, start_date, '2021-05-20 08:11:32' ) / frequency
) ) MONTH ),
NULL
)
)
END ) AS prev_start
FROM wp_mylisting_events
WHERE ( field_key = 'event-date' )
GROUP BY listing_id
) AS `recur_event-date` ON (wp_posts.ID = `recur_event-date`.listing_id) WHERE 1=1 AND (
wp_term_relationships.term_taxonomy_id IN (18)
) AND (
( wp_postmeta.meta_key = '_case27_listing_type' AND wp_postmeta.meta_value = 'event' )
) AND wp_posts.post_type = 'job_listing' AND ((wp_posts.post_status = 'publish')) AND (
`recur_event-date`.next_start >= '2021-05-20 08:11:32'
OR `recur_event-date`.prev_end >= '2021-05-20 08:11:32'
) GROUP BY wp_posts.ID ORDER BY CAST( COALESCE( priority_meta.meta_value, 0 ) AS UNSIGNED ) DESC , IF(
`recur_event-date`.prev_end >= '2021-05-20 08:11:32',
`recur_event-date`.prev_start,
`recur_event-date`.next_start
) ASC LIMIT 0, 6