SQLinfo.ru - Все о MySQL Webew.ru: теория и практика веб-технологий

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 16.01.2014 06:07:13

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Неожиданное поведение подзапроса

Акутогава-сан, прошу простить мне слабоумие!

MariaDB [test]> select * from test_table order by user_id, `time` desc;
+---------+--------+---------------------+
| user_id | post   | time                |
+---------+--------+---------------------+
|       1 | post 3 | 2012-11-15 16:51:08 |
|       1 | post 2 | 2012-11-12 18:53:26 |
|       1 | post 1 | 2012-10-14 11:21:22 |
+---------+--------+---------------------+
3 rows in set (0.13 sec)

MariaDB [test]> select * from (select * from test_table order by user_id, `time`
 desc) t;
+---------+--------+---------------------+
| user_id | post   | time                |
+---------+--------+---------------------+
|       1 | post 1 | 2012-10-14 11:21:22 |
|       1 | post 2 | 2012-11-12 18:53:26 |
|       1 | post 3 | 2012-11-15 16:51:08 |
+---------+--------+---------------------+
3 rows in set (0.03 sec)


Если посмотреть на переписанный оптимизатором запрос, то видно, что сортировку он выкинул.

| Note  | 1003 | select `test`.`test_table`.`user_id` AS `user_id`,`test`.`test_
table`.`post` AS `post`,`test`.`test_table`.`time` AS `time` from `test`.`test_t
able` |


Это у меня счастливая версия 5.3.5-MariaDB-log или MySQL нынче тоже стала очень умной?

Неактивен

 

#2 16.01.2014 06:18:47

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Неожиданное поведение подзапроса

Вроде все логично. Во внешнем запросе сортировки нет, значит пользователю порядок не важен. Отсюда - сортировка в подзапросе лишняя.

Общее правило: если нет ORDER BY, то порядок не гарантирован, независимо от того откуда идет выборка.

Неактивен

 

#3 16.01.2014 06:22:43

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Неожиданное поведение подзапроса

Проверил на "5.5.22-log MySQL Community Server (GPL)" - он так себя не ведет, а зря.

Неактивен

 

#4 16.01.2014 06:25:52

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Неожиданное поведение подзапроса

У него так выглядит:

| Note  | 1003 | select `t`.`user_id` AS `user_id`,`t`.`post` AS `post`,`t`.`time` AS `time` from (select `test`.`test_table`.`user_id` AS `user_id`,`test`.`test_table`.`post` AS `post`,`test`.`test_table`.`time` AS `time` from `test`.`test_table` order by `test`.`test_table`.`user_id`,`test`.`test_table`.`time` desc) `t` |

Неактивен

 

#5 16.01.2014 06:29:28

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Неожиданное поведение подзапроса

Раньше было иначе, и трава зеленее и деревья выше..

Проблема в том, что когда хочу пройти переменными по таблице в определенном порядке, то order by не поможет, так как where применяется до него. Раньше с этой целью можно было использовать вложенный подзапрос, а теперь нет sad

Причина в том, что раньше подзапросы в части from первым делом материализовались во временную таблицу, а теперь оптимизатор их оптимизирует. В MySQL 5.6 такое же поведение будет.

Неактивен

 

#6 16.01.2014 07:25:26

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Неожиданное поведение подзапроса

Можно ли сделать что-то, чтобы подзапрос не подходил по условиям на оптимизацию?

select * from (select * from test_table WHERE user_id<10 UNION (SELECT * from test_table where user_id>=10) order by user_id, `time`  desc) t;

Неактивен

 

#7 16.01.2014 07:30:23

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Неожиданное поведение подзапроса

Поздравляю! Победа разума над машиной smile

Неактивен

 

#8 16.01.2014 12:43:48

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2419

Re: Неожиданное поведение подзапроса

Получается, что некоторые фишки с использованием переменных будут уже не очень-то и возможны?... Тот же легендарный топик про то, как выбирать по два и более элемента из группы путем пользовательских переменных - там решение забазировано в какой-то мере на то, что в подзапросе есть order by. Как поставлю MariaDB посвежее - проверю.

rgbeast, а есть уверенность, что оптимизатор не превратит
(select * from test_table WHERE user_id<10 UNION (SELECT * from test_table where user_id>=10)
в
(select * from test_table)?


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#9 16.01.2014 13:53:35

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Неожиданное поведение подзапроса

deadka написал:

rgbeast, а есть уверенность, что оптимизатор не превратит
(select * from test_table WHERE user_id<10 UNION (SELECT * from test_table where user_id>=10)
в
(select * from test_table)?

Когда-нибудь может быть, но пока что он не проводит анализ математических (логических) утверждений. Есть риск, что один из запросов будет давать пустой результат и оптимизатор это поймет, но раз этот запрос срабатывает, значит пока он так не действует.

Может быть будет опция отключить оптимизацию для конкретного подзапроса или уже существует какой-нибудь более надежный способ навязать материализацию.

Неактивен

 

#10 16.01.2014 14:31:47

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2419

Re: Неожиданное поведение подзапроса

Да, подозрения подтверждаются
По следам http://sqlinfo.ru/forum/viewtopic.php?id=3839

Код:

mysql> create table t_3839(id int, login varchar(255),`time` int);
Query OK, 0 rows affected (0.31 sec)

mysql> insert into t_3839 values(1,'log1',10),(2,'log1',15),(3,'log2',100),(4,'log2',80),(5,'log3',11),(6,'log4',8);
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> set @i=0,@n=1,@l=''; select t.* from ( select * from t_3839 order by login, time desc) t where if (@l=login, @i:=@i+1,(@i:=0) or (@l:=login) or 1) and @i<@n;
Query OK, 0 rows affected (0.02 sec)

+------+-------+------+
| id   | login | time |
+------+-------+------+
|    2 | log1  |   15 |
|    3 | log2  |  100 |
|    5 | log3  |   11 |
|    6 | log4  |    8 |
+------+-------+------+
4 rows in set (0.04 sec)
mysql> select version();
+------------+
| version()  |
+------------+
| 5.1.52-log |
+------------+
1 row in set (0.02 sec)

На этой версии mysql отрабатывает как и раньше.

Код:

MariaDB [sqlinfo]> create table t_3839(id int, login varchar(255),`time` int);
Query OK, 0 rows affected (0.23 sec)

MariaDB [sqlinfo]> insert into t_3839 values(1,'log1',10),(2,'log1',15),(3,'log2
',100),(4,'log2',80),(5,'log3',11),(6,'log4',8);
Query OK, 6 rows affected (0.28 sec)
Records: 6  Duplicates: 0  Warnings: 0

MariaDB [sqlinfo]>   set @i=0,@n=1,@l=''; select t.* from ( select * from t_3839 order by login, time desc) t where if (@l=login, @i:=@i+1,(@i:=0) or (@l:=login) or 1) and @i<@n;
Query OK, 0 rows affected (0.00 sec)

+------+-------+------+
| id   | login | time |
+------+-------+------+
|    1 | log1  |   10 |
+------+-------+------+
1 row in set (0.00 sec)

MariaDB [sqlinfo]> select version();
+--------------------+
| version()          |
+--------------------+
| 5.5.34-MariaDB-log |
+--------------------+
1 row in set (0.01 sec)

Не отработало, как ожидалось.

C выборкой элементов группы
MariaDB тоже выдал не ожидаемый результат:

CREATE TABLE  `g` (
  `x` int(4) unsigned default NULL
) ENGINE=MyISAM;
insert into `g` values(3),(2),(4),(5),(1);
CREATE TABLE  `p` (
  `y` int(4) unsigned default NULL,
  `data` int(4) unsigned default NULL
) ENGINE=MyISAM;
insert into `p` values
(3,172),(3,721),(3,652),(3,444),(3,781)
,(2,48),(2,27),(2,13),(2,16),(2,99)
,(4,4854),(4,2723),(4,1327),(4,1663),(4,9953)
,(5,48545),(5,27423),(5,12327),(5,16639),(5,99530)
,(1,4),(1,7),(1,3),(1,6),(1,9);
 

Код:

MariaDB [sqlinfo]> set @n=3, @i=0, @p=0; select * from (select * from g t1 join
p t2 on t1.x=t2.y order by t1.x) t WHERE if (@p=x, @i:=@i+1,(@i:=0) or (@p:=x))
and @i<@n;
Query OK, 0 rows affected (0.02 sec)

+------+------+------+
| x    | y    | data |
+------+------+------+
|    3 |    3 |  172 |
|    3 |    3 |  721 |
|    3 |    3 |  652 |
+------+------+------+
3 rows in set (0.05 sec)

MariaDB [sqlinfo]>

Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#11 19.01.2014 11:34:02

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Неожиданное поведение подзапроса

Можно инициализировать переменные внутри подзапроса. Например, если раньше при необходимости сделать одним запросом, я пару запросов

set @i=0,@n=1,@l='';
select t.* from ( select * from t_3839 order by login, time desc) t where if (@l=login, @i:=@i+1,(@i:=0) or (@l:=login) or 1) and @i<@n;
 
заменял на
select t.* from ( select * from t_3839 order by login, time desc) t, (select @i:=0,@n:=1,@l:='') x where if (@l=login, @i:=@i+1,(@i:=0) or (@l:=login) or 1) and @i<@n;
 
то теперь нужно
select t.* from ( select * from t_3839, (select @i:=0,@n:=1,@l:='') x order by login, time desc) t where if (@l=login, @i:=@i+1,(@i:=0) or (@l:=login) or 1) and @i<@n;

Неактивен

 

#12 19.01.2014 13:19:16

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2419

Re: Неожиданное поведение подзапроса

Есть такой трюк, да. Правда выполнению вышеприведенных запросов в mariaDB не способствует smile. Вот и еще один аргумент в сторону того, чтобы не использовать переменные таким вот образом..


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#13 19.01.2014 13:48:33

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Неожиданное поведение подзапроса

Поклёп! Для данных из прошлого примера:

MariaDB [test]> SELECT * FROM t_3839 t;
+------+-------+------+
| id   | login | time |
+------+-------+------+
|    1 | log1  |   10 |
|    2 | log1  |   15 |
|    3 | log2  |  100 |
|    4 | log2  |   80 |
|    5 | log3  |   11 |
|    6 | log4  |    8 |
+------+-------+------+

MariaDB [test]> select t.* from ( select * from t_3839 order by login, time desc) t, (select @i:=0,@n:=1,@l:='') x where if (@l=login, @i:=@i+1,(@i:=0) or (@l:=login) or 1) and @i<@n;
+------+-------+------+
| id   | login | time |
+------+-------+------+
|    1 | log1  |   10 |
|    3 | log2  |  100 |
|    5 | log3  |   11 |
|    6 | log4  |    8 |
+------+-------+------+
 

Как видим результат неправильный - для log1 выбрано не максимальное значение.
А так результат правильный:

MariaDB [test]> select t.* from ( select * from t_3839, (select @i:=0,@n:=1,@l:='') x order by login, time desc) t where if (@l=login, @i:=@i+1,(@i:=0) or (@l:=login) or 1) and @i<@n;
+------+-------+------+-------+-------+--------+
| id   | login | time | @i:=0 | @n:=1 | @l:='' |
+------+-------+------+-------+-------+--------+
|    2 | log1  |   15 |     0 |     1 |        |
|    3 | log2  |  100 |     0 |     1 |        |
|    5 | log3  |   11 |     0 |     1 |        |
|    6 | log4  |    8 |     0 |     1 |        |
+------+-------+------+-------+-------+--------+

Неактивен

 

#14 19.01.2014 13:52:47

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2419

Re: Неожиданное поведение подзапроса

Не, не поклеп.

Код:

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 30
Server version: 5.5.34-MariaDB-log mariadb.org binary distribution

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use sqlinfo;
Database changed
MariaDB [sqlinfo]> SELECT * FROM t_3839 t;
+------+-------+------+
| id   | login | time |
+------+-------+------+
|    1 | log1  |   10 |
|    2 | log1  |   15 |
|    3 | log2  |  100 |
|    4 | log2  |   80 |
|    5 | log3  |   11 |
|    6 | log4  |    8 |
+------+-------+------+
6 rows in set (0.00 sec)

MariaDB [sqlinfo]> select t.* from ( select * from t_3839, (select @i:=0,@n:=1,@
l:='') x order by login, time desc) t where if (@l=login, @i:=@i+1,(@i:=0) or (@
l:=login) or 1) and @i<@n;
+------+-------+------+-------+-------+--------+
| id   | login | time | @i:=0 | @n:=1 | @l:='' |
+------+-------+------+-------+-------+--------+
|    2 | log1  |   15 |     0 |     1 |        |
+------+-------+------+-------+-------+--------+
1 row in set (0.02 sec)

MariaDB [sqlinfo]> select version();
+--------------------+
| version()          |
+--------------------+
| 5.5.34-MariaDB-log |
+--------------------+
1 row in set (0.23 sec)

MariaDB [sqlinfo]>

А у тебя какая версия mariaDB?


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#15 19.01.2014 13:56:11

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Неожиданное поведение подзапроса

5.3.5-MariaDB-log

Но в ней уже есть оптимизация from подзапросов. Видимо ещё что-то сменили.

Неактивен

 

#16 19.01.2014 14:14:55

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Неожиданное поведение подзапроса

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

Неактивен

 

#17 20.01.2014 01:13:30

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Неожиданное поведение подзапроса

А покажите как в MySQL 5.1 или 5.5  выглядит

EXPLAIN EXTENDED select t.* from ( select * from t_3839, (select @i:=0,@n:=1,@l:='') x order by login, time desc) t where if (@l=login, @i:=@i+1,(@i:=0) or (@l:=login) or 1) and @i<@n\G
SHOW WARNINGS\G


Исходные данные Данил приводил в 10ом посте. А то я обновился до последней версии, а старых версий не оставил.

Неактивен

 

#18 20.01.2014 01:39:04

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Неожиданное поведение подзапроса

5.5.22-log MySQL Community Server (GPL):

select `t`.`id` AS `id`,`t`.`login` AS `login`,`t`.`time` AS `time`,`t`.`@i:=0` AS `@i:=0`,`t`.`@n:=1` AS `@n:=1`,`t`.`@l:=''` AS `@l:=''` from (select `test`.`t_3839`.`id` AS `id`,`test`.`t_3839`.`login` AS `login`,`test`.`t_3839`.`time` AS `time`,'0' AS `@i:=0`,'1' AS `@n:=1`,'' AS `@l:=''` from `test`.`t_3839` order by `test`.`t_3839`.`login`,`test`.`t_3839`.`time` desc) `t` where (if(((@l) = convert(`t`.`login` using utf8)),(@i:<img src="img/smilies/sad.png" width="15" height="15" alt="sad" />(@i) + 1)),(<cache>((@i:=0)) or (@l:=`t`.`login`) or 1)) and ((@i) < (@n)))

Неактивен

 

#19 20.01.2014 01:45:57

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Неожиданное поведение подзапроса

Тогда ещё один вопрос - а сам запрос правильно отрабатывает?

Неактивен

 

#20 20.01.2014 02:05:47

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Неожиданное поведение подзапроса

Вот результат:

+------+-------+------+-------+-------+--------+
| id   | login | time | @i:=0 | @n:=1 | @l:='' |
+------+-------+------+-------+-------+--------+
|    2 | log1  |   15 |     0 |     1 |        |
+------+-------+------+-------+-------+--------+
 

Неактивен

 

#21 20.01.2014 02:10:58

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Неожиданное поведение подзапроса

Ага, так я и думал. Похоже дело в
(<cache>((@i:=0)) or (@l:=`t`.`login`) or 1))
т.е. оптимизатор догоняет, что это выражение всегда будет истина и не вычисляет его в дальнейшем, т.е. не сбрасывает счетчик.

Но вот вопрос, если мы поле login определим как int, то уже нельзя будет заранее сказать, что выражение всегда будет истина, однако и в этом случае будет
(<cache>((@i:=0)) or (@l:=`t`.`login`)))

Имхо, это бага.

Неактивен

 

#22 20.01.2014 02:13:37

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Неожиданное поведение подзапроса

Если бага оптимизатора, то сделай пример, неправильность отработки которого очевидна и на bugs.mysql.com

Неактивен

 

#23 20.01.2014 02:52:45

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Неожиданное поведение подзапроса


MariaDB [test]> select version();
+--------------------+
| version()          |
+--------------------+
| 5.5.34-MariaDB-log |
+--------------------+
1 row in set (0.08 sec)

MariaDB [test]> create table t_3839(id int, login int,`time` int);
Query OK, 0 rows affected (0.33 sec)

MariaDB [test]> insert into t_3839 values(1,1,10),(2,1,15),(3,2,100),(4,2,80),(5,3,11),(6,4,8);
Query OK, 6 rows affected (0.13 sec)
Records: 6  Duplicates: 0  Warnings: 0

MariaDB [test]> select t.* from ( select t_3839.* from t_3839, (select @i:=0,@n:
=1,@l:=0) x order by login, time desc) t where if (@l=login, @i:=@i+1,(@i:=0) or
 (@l:=login)) and @i<@n;
+------+-------+------+
| id   | login | time |
+------+-------+------+
|    2 |     1 |   15 |
+------+-------+------+
1 row in set (0.00 sec)

Запрос отрабатывает неправильно, должен выдать (например, в версии 5.0.51a-community-log)

+------+-------+------+
| id   | login | time |
+------+-------+------+
|    2 | log1  |   15 |
|    3 | log2  |  100 |
|    5 | log3  |   11 |
|    6 | log4  |    8 |
+------+-------+------+


Если же выполнить explain extended и потом

MariaDB [test]> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select `t`.`id` AS `id`,`t`.`login` AS `login`,`t`.`time` AS `time` fro
m (select `test`.`t_3839`.`id` AS `id`,`test`.`t_3839`.`login` AS `login`,`test`
.`t_3839`.`time` AS `time` from `test`.`t_3839` order by `test`.`t_3839`.`login`
,`test`.`t_3839`.`time` desc) `t` where (if(((@`l`) = `t`.`login`),(@i:=((@`i`)
+ 1)),(<cache>((@i:=0)) or (@l:=`t`.`login`))) and ((@`i`) < (@`n`)))
1 row in set (0.00 sec)


то видно, что условие (<cache>((@i:=0)) or (@l:=`t`.`login`))) оптимизатор вычислил единожды. Но это неправильно, так как в зависимости от значения поля login результат этого выражения может быть равен как 1, так и 0.


-----------------------------------
Это наглядный пример? Верны ли рассуждения?

Неактивен

 

#24 20.01.2014 03:16:16

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Неожиданное поведение подзапроса

Кстати, логично, что запрос работает в MariaDB 5.3 и неправильно работает в MariaDB 5.5 и MySQL 5.5, так как условно можно сказать, что
MariaDB 5.5 = MariaDB 5.3 + MySQL 5.5
(т.е. в версию 5.3 импортировали улучшения из MySQL 5.5 отсюда и такое распределение ошибки).

Неактивен

 

#25 20.01.2014 05:28:36

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Неожиданное поведение подзапроса

vasya, пример пока не понял, но его наглядность привела к идее демотиватора
http://sqlinfo.ru/forum/attachment.php?item=475&amp;download=1


Прикрепленные файлы:
Attachment Icon visual_example.png, Размер: 43,886 байт, Скачано: 1,148

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson