Я рассматриваю предложение 4 новых операторов сравнения в SQL. Они похожи на операторы >, <, >= и <=, но являются истинными, только если значение каждого операнда является среди всех значений, удовлетворяющих неравенству, одной ближайшей к значению другого операнда. Поскольку одно значение почти на другое значение, я пришел к выводу (после осознания отсутствия ключевого слова first и после отбрасывания ключевого слова unique), что хорошим выбором будет для определения этих 4 новых операторов:
-
a @> b: true, еслиa > bи noa' < aудовлетворяетa' > b, а nob' > bудовлетворяетa > b' -
a @< b: true, еслиb @> a -
a @>= b: true, еслиa ≥ bи noa' < aудовлетворяетa' ≥ b, а nob' > bудовлетворяетa ≥ b' -
a @<= b: true, еслиb @>= a
Вопрос: есть ли веская причина, почему такие операторы уже не существуют?
(2014-03-20) Я переформулирую вопрос, потому что вышеуказанная формулировка, по-видимому, недостаточно ясна:
Есть ли причина, по которой такие операторы не должны существовать?
Следующие примеры предназначены в качестве отправной точки для выяснения, где могут быть проблемы с операторами @.... Я буду использовать 3 таблицы MySQL:
create table ta (id int auto_increment, ca char, primary key(id), unique index(ca));
create table tb (id int auto_increment, cb char, primary key(id), index(cb));
create table tc (id int auto_increment, cc char, primary key(id));
insert into ta (ca) values ('A'),('E'),('I'),('O'),('U');
insert into tb (cb) values ('C'),('D'),('E'),('F'),('F'),('M'),('N'),('O'),('Z');
insert into tc (cc) values ('C'),('D'),('E'),('F'),('F'),('M'),('N'),('O'),('Z');
Пример # 1
Когда столбцы имеют уникальные значения, эффект операторов @... можно получить, ограничив вывод запросов или подзапросов одной строкой, хотя с несколько более неуклюжим синтаксисом:
?> select * from ta where ca @> 'B'; -- currently not valid, equivalent to:
!> select * from ta where ca > 'B' order by ca limit 1;
+----+------+
| id | ca |
+----+------+
| 2 | E |
+----+------+
(limit 1 специфичен для MySQL, MariaDB, PostgreSQL и т.д., другие РСУБД имеют select top 1, where rownum = 1 и т.д.)
В таблице ta мы имеем уникальный индекс в столбце ca. Этот индекс можно использовать для перехода к выбранному значению с той же скоростью, что и для ca = 'E'. Оптимизатор может это реализовать, но если это не так, структуры данных могут быть настроены для ненужного сканирования, начиная с выбранного значения (MySQL explain говорит, что это запрос типа range).
Пример # 2
Когда столбцы имеют не уникальные значения, ограничение выходных строк бесполезно, а синтаксис становится еще более неуклюжим:
?> select * from tb where cb @> 'E'; -- currently not valid, equivalent to:
!> select * from tb where cb = (select min(cb) from tb where cb > 'E');
+----+------+
| id | cb |
+----+------+
| 4 | F |
| 5 | F |
+----+------+
К счастью, если я правильно прочитал вывод explain, MySQL достаточно умен, чтобы оптимизировать подзапрос, но если бы он не был, индекс будет использоваться дважды, а не один раз.
В случае таблицы tc, которая не имеет индекса в столбце cc, MySQL выполняет два сканирования таблицы. Это понятно, поскольку односкатное сканирование будет означать использование неизвестного объема памяти для временного результата.
Пример # 3
Предположим, что вам нужны все пары, состоящие из значения и его преемника:
?> select t1.ca as c1, t2.ca as c2
from ta t1
join ta t2 on t1.ca @< t2.ca; -- currently not valid, equivalent to:
!> select t1.ca as c1, t2.ca as c2
from ta t1
join ta t2 on t2.ca = (select min(ca) from ta where ca > t1.ca);
+------+------+
| c1 | c2 |
+------+------+
| A | E |
| E | I |
| I | O |
| O | U |
+------+------+
Если я правильно прочитал вывод explain, оптимизатор MySQL не сможет обойтись без коррелированного подзапроса, тогда как мы, люди, узнаем лучше. Возможно, с помощью специальной обработки операторов @..., подключенных к сети, оптимизатор выполнит одно сканирование?
Пример # 4
Это похоже, но в двух таблицах, один из которых имеет уникальный код:
?> select * from ta join tb on ca @< cb; -- currently not valid, equivalent to:
!> select * from ta join tb on cb = (select min(cb) from tb where cb > ca);
+----+------+----+------+
| id | ca | id | cb |
+----+------+----+------+
| 1 | A | 1 | C |
| 2 | E | 4 | F |
| 2 | E | 5 | F |
| 3 | I | 6 | M |
| 4 | O | 9 | Z |
| 5 | U | 9 | Z |
+----+------+----+------+
Здесь тоже оптимизатор MySQL не оптимизирует подзапрос, хотя (возможно, с подсказкой @<) он мог бы.
Пример # 5
(Добавлено в 2014-03-20 гг.) Операторы @..., по-видимому, имеют смысл везде, где их @ не имеют аналогов. Вот надуманный пример выражения в условии where:
?> select * from ta join tb
where round((ascii(ca)+ascii(cb))/2) @> ascii('E');
-- currently not valid, equivalent to:
!> select * from ta join tb
where round((ascii(ca)+ascii(cb))/2) = (
select min(round((ascii(ca)+ascii(cb))/2)) from ta, tb
where round((ascii(ca)+ascii(cb))/2) > ascii('E')
);
+----+------+----+------+
| id | ca | id | cb |
+----+------+----+------+
| 3 | I | 1 | C |
| 2 | E | 4 | F |
| 2 | E | 5 | F |
+----+------+----+------+
Пример # 6
... и это еще один пример, на этот раз выражения select:
?> select *, cb @< ca
from tb, ta; -- currently not valid, equivalent to:
!> select *, ifnull(cb = (select max(cb) from tb where cb < ca), 0) as 'cb @< ca'
from tb, ta;
+----+------+----+------+----------+
| id | cb | id | ca | cb @< ca |
+----+------+----+------+----------+
| 1 | C | 1 | A | 0 |
| 1 | C | 2 | E | 0 |
| 1 | C | 3 | I | 0 |
| 1 | C | 4 | O | 0 |
| 1 | C | 5 | U | 0 |
| 2 | D | 1 | A | 0 |
| 2 | D | 2 | E | 1 |
| -- (omitting rows with cb @< ca equal to 0 from here on)
| 4 | F | 3 | I | 1 |
| 5 | F | 3 | I | 1 |
| 7 | N | 4 | O | 1 |
| 8 | O | 5 | U | 1 |
Мне известны следующие оговорки:
Предостережение № 1
Операторы @... являются "нелокальными", потому что им нужно знать все возможные значения их операндов. Это, кажется, не проблема во всех условиях видов, показанных в приведенных выше примерах, но может быть проблемой в других местах (хотя я еще не нашел пример, который не может быть разрешен дополнительным подзапросом).
Предостережение # 2
Операторы @..., в отличие от своих @ -свободных аналогов, не являются транзитивными. Они делят это свойство с оператором <>.
Предостережение № 3
Полностью использование операторов @... может означать введение новых типов доступа к индексам и таблицам (как описано в примерах).
Обратите внимание, что этот вопрос не означает отправную точку для обсуждения. Я ищу причины, по которым что-то вроде операторов @... не находится в стандарте или в любом диалоговом диалекте SQL, который мне известен. Я ожидаю, что эти причины будут связаны с некоторой проблемой с определением и/или внедрением этих операторов, что я упустили.
Я знаю, что одной из причин является "бритва Оккама" (pluralitas non est ponenda sine необходимо), но, как я попытался показать выше, плюралиты здесь также приносят некоторые преимущества (краткость и легкость оптимизации). Я ищу более веские причины.
(2014-03-31) @>, @<, @>= и @<= могут стать |>, |<, |>= и |<= или аналогичными (читайте: first more/менее [равно]), чтобы не сталкиваться с установленными обычаями префикса идентификатора @.
