Неожиданные результаты простого MySQL-запроса

А

SELECT DATE(fp.created) AS dt, COUNT(1) AS qnt
FROM forum_posts AS fp
WHERE fp.created >= '2013-01-01'
AND fp.createde < '2013-02-01'
GROUP BY DATE(fp.created)

не такой же результат даст?

Ну и подзапрос у вас:

...
SELECT fp2.id
    FROM forum_posts fp2
    WHERE fp2.created >= '2013-01-01'
    AND fp2.created < '2013-02-01'
    GROUP BY DATE(fp2.created), fp2.user_id
...

Группируете по DATE(fp2.created), fp2.user_id, а выводите fp2.id. Обычно выводят либо поля, по которым группировка, либо другие, обернутые в агрегатные функции.

у Вас в данных 3 уникальные даты — 2013-01-01, 2013-01-02, 2013-02-02

Условию WHERE fp2.created >= '2013-01-01' AND fp2.created < '2013-02-01' удовлетворяют 2013-01-01 и 2013-01-02

Результаты подзапроса Вы группируете по дате.
Ожидаемо получаете 2 результата. А что неожиданного?

А на mysql 5.6 кто-нибудь может проверить, что там будет?

Вот это вернет вам 5 и 4:

SELECT COUNT(DISTINCT fp2.user_id)
    FROM forum_posts fp2
    WHERE fp2.created >= '2013-01-01'
    AND fp2.created < '2013-02-01'
    GROUP BY DATE(fp2.created)

Garbage In, Garbage Out
Говорим MySQL, подразумеваем MariaDB.
Пишем кривой запрос, ищем причину в MySQL. Ой, извините, в MariaDB.

Правильно ли я понял суть, не пишите кривые запросы, порой, они выдают неожиданные результаты? =)

Я предпочитаю считать это багом. Использовать негруппируемые столбцы в запросе можно — это MySQL-ская нестандартная фича. А раз фича, значит оптимайзер должен ее поддерживать, в том числе и в подзапросах.

mariadb.atlassian.net/browse/MDEV-4595

Но все-таки дело тут не в «полной совместимости и прозрачности перехода», а в намного более умном оптимизаторе подзапросов. Если перейти с MySQL 5.6 на MariaDB 5.5 — то в запросе топикстартера будет как раз полная совместимость и прозрачность. Одинаково неправильный результат.

Вложенные SELECTы имхо вообще плохая затея, 2 раза пробовал использовать и оба раза приходил к выводу что надо или небольшую переделку базы/таблиц сделать (чтобы избавиться от вложенности), выполнить на стороне скрипта вложенный SELECT и скормить его результать в IN или исхитриться с JOIN. В любом случа вложенные запросы клали базу на неопределенный период.
ps: хотя всегда есть вариант что я просто не умею вложенными запросами пользоваться.

Не понятно зачем в указанном вами случае использовать вложенный запрос. Более корректно использовать таки join. В любом случае нормальный оптимизатор именно в него и развернет этот запрос.

Я тоже сталкивался с подобной проблемой при переходе на MariaDB.
Смысл запроса был примерно такой:

SELECT * FROM (SELECT a, b FROM t1 ORDER BY b) GROUP BY a

Прикол в том, что MySQL возвращал для каждого a наименьший b, а MariaDB для каждого a — черте-какой b.
Я написал баг-репорт, а мне объяснили так:

>> I know that while grouping MySQL uses first met value for those columns that are not grouped.

No, it doesn't. Again, you rely on pure luck; and now, *this* is explicitly documented in MySQL manual:

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. <..> You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate.

Поэтому советую очень внимательно относиться к группировке и сортировке во вложенных запросах.

А в Вашем случае, конечно, всё без проблем делается без подзапросов.
Помните, что MySQL любит делать декартово произведение кортежей, где надо, и где не надо.

Для MySQL и его форков крайне предпочтительно настраивать SQL modes: по умолчанию (для обратной совместимости) mysql ведет себя весьма, кхм, вольготно.

Если бы был выставлен ONLY_FULL_GROUP_BY (или любой combination mode, его включающий), проблема бы не возникла — такой запрос бы просто не выполнился. При выключенном же ONLY_FULL_GROUP_BY надо всегда быть готовым к undefined behavior.

для того чтобы сделать джойн, необходимо сначала описать связь.

В 2.3 появилась возможность делать произвольные джоины: github.com/doctrine/doctrine2/pull/368

У вас проблема не в запросе, а в подходе к решению задачи. Вы ее пытаетесь решать в лоб вместо того, чтобы решать ее правильно.
У вас есть конкретная задача «Необходимо посчитать количество пользователей, которые хотя бы раз писали на форум в течение дня за последний месяц». Звучит очень просто, верно? Перефразирую, чтобы было более понятно ДБА: необходимы каунтеры постов в день по каждому юзеру за месяц.

То есть нужно получить user_id, date, posts_cnt.

Но, вы отталкиваетесь от текущей реализации структуры БД. На самом деле с ней нет никаких проблем кроме одной, в вашем случае, вы не можете выполнить задачу, стояющую перед вами не прибегая к таким вот запросам. Каст DATE(fp.created) убивает индекс по fp.created, например. Если у вас хотябы 10 миллионов постов в базе, вы в глубокой ****. Что делать? Всё просто — при инсерте поста в базу подготавливайте данные заранее:

INSERT INTO forum_posts SET post = 'post text', created = NOW(), user_id = 'user id'

Помните, «необходимы каунтеры постов в день по каждому юзеру»?
Получите:
INSERT INTO user_posts_by_date SET user_id = 'user id', `date` = CURDATE(), posts_cnt = 1 ON DUPLICATE KEY UPDATE posts_cnt = posts_cnt +1

несложно догадаться, что date + user_id должен быть unique. Можете еще партицирование по месяцам сделать

Тогда ваш запрос превратится в:

select * from user_posts_by_date where `date` between 2013-01-01' and 2013-02-01'

это будет выполняться В РАЗЫ быстрее не говоря о том, что это более наглядно.