Можно ли написать SQL запрос с выражением в WHERE и в SELECT, но без повторения этого выражения и без вложенного SELECT?

Question

[eugene159]

Есть такой запрос:

SELECT product_id, MD5(`value`) AS val
FROM modx_ms2_product_options
WHERE val = 'e59253548ffcb090ecc0cd0944afd27f'
LIMIT 10

Он не работает - выдается ошибка:

Ошибка SQL (1054): Unknown column 'val' in 'where clause'

Я знаю, что можно его переписать - обернуть в другой запрос и WHERE val написать уже во внешнем запросе. Тогда все будет работать.
Но будет ведь 2 SELECT'а (внешний и вложенный). Мне кажется, что это нехорошо для производительности.

SELECT * 
FROM (
	SELECT `product_id`, MD5(`value`) AS myhash
	FROM modx_ms2_product_options
	LIMIT 10
) AS hashtable
WHERE `myhash` = 'e59253548ffcb090ecc0cd0944afd27f'

Можно переписать по-другому - в WHERE тоже написать MD5(`value`). Но будет 2 вычисления MD5. Если вместо MD5 в запросе будет какое-то другое выражение, то это тоже будет плохо для производительности. Да и принцип DRY не соблюдается - придется править выражение и в SELECT, и в WHERE.

SELECT product_id, MD5(`value`) AS val
FROM modx_ms2_product_options
WHERE MD5(`value`) = 'e59253548ffcb090ecc0cd0944afd27f'
LIMIT 10

Можно ли этот запрос написать с одним SELECT и с одним вычислением выражения - при помощи JOIN, например? Если да, то напишите, пожалуйста, код.

Comments

[Сергей c0re]

зачем вам md5 ?? посмотрите все таки ответ в - https://qna.habr.com/q/1257954

Answer 1

[Rsa97]

--WHERE val = 'e59253548ffcb090ecc0cd0944afd27f'
++HAVING val = 'e59253548ffcb090ecc0cd0944afd27f'

Answer 2

[alexalexes]

Но будет 2 вычисления MD5

Выражения в select вычисляются после того, как отобраны строки по условиям в where.
Если выходная выборка совсем небольшая, то оптимизировать работу функции в колонках не нужно - это экономия на спичках.
Другое дело, если вы используете вычисляемую функцию в where. Чтобы она быстро работала есть 3 пути:
1. Если функция вычисляется из данных, которые не зависят от контекста запроса, то нужно отказаться от этой функции, которая вычисляется при каждом запросе. Нужно хранить ее результат в отдельном атрибуте.
2. Сделать индекс с использованием этой функции на атрибуте, тогда при использовании этой же функции в where сработает индекс.
3. Если есть возможность, то нужно так преобразовать выражение where, чтобы все аргументы вычисляемой функции или выражения (складываем, вычитаем, умножаем делим, case-им) оказались входными параметрами или константами. Чтобы такое произошло, нужна обратная функция для md5 - reverse_md5. Но такой функции по математическим соображениям не существует.
Иначе выражение бы выглядело так:
where value = reverse_md5(:hache)

Comments

[Алексей Горбунов]

Странно, что ему в выборке понадобилось вычислять этот хэш, если он его уже знает.

SELECT product_id, 'e59253548ffcb090ecc0cd0944afd27f' AS val
FROM modx_ms2_product_options
WHERE MD5(`value`) = 'e59253548ffcb090ecc0cd0944afd27f'
LIMIT 10

Answer 3

[Дмитрий]

Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is evaluated, the column value may not yet have been determined.

https://dev.mysql.com/doc/refman/8.0/en/problems-w...

Однако у меня сильное подозрение что вряд ли у вас существует функциональный индекс по md5(value) и следовательно у вас sequence scan по таблице - и париться на тему 2 вызовов md5 при таком не стоит.

З.Ы. А какова причина все время на лету вычислять md5? Почему не воспользоваться виртуальной колонкой к примеру? Если уж мы хотим делать это вычисление именно в бд?

Answer 4

[mayton2019]

В SQL - много таких особенностей. Тоже самое с group by и оконными фунециями. Надо много повторяющихся expressions написать.

Попробуй еще так написать.

WITH HT AS (
 SELECT `product_id`, MD5(`value`) AS myhash
  FROM modx_ms2_product_options
  LIMIT 10
)
SELECT * FROM HT WHERE `myhash` = 'e59253548ffcb090ecc0cd0944afd27f'

Answer 5

[Руслан .]

Возможно вам подойдёт CTE, вот тут посмотреть можно
https://dev.mysql.com/doc/refman/8.0/en/with.html