List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:January 3 2000 10:06pm
Subject:MYSQL 3.23.8 Wron query Result.
View as plain text  
Hi!

>>>>> "Peter" == Peter Zaitsev <pz@stripped> writes:

Peter> Hello mysql,
Peter> I have MYSQL 3.23.8 running under solaris 2.6 with win1251 chracter
Peter> set.

Peter> select layers.stat_keywords.hits as val,layers.keywords.keyword as str,
> layers.keywords.id as keyword from
> layers.layers_stat_keywords,layers.stat_keywords,layers.keywords where
> layers.layers_stat_keywords.page_group=255 and layers.layers_stat_keywords.counter=101 and
> ( (layers.layers_stat_keywords.begin='1999-12-01' AND layers.layers_stat_keywords.type=4)
> OR (layers.layers_stat_keywords.begin='2000-01-01' AND
> layers.layers_stat_keywords.type=2)) and layers.layers_stat_keywords.id =
> layers.stat_keywords.layer_id and layers.stat_keywords.id = layers.keywords.id

Peter> +-----+--------------------------------------------------+---------+
Peter> | val | str                                              | keyword |
Peter> +-----+--------------------------------------------------+---------+
Peter> |   2 | zemfira                                          |      14 |
Peter> |   5 | %C7%E5%EC%F4%E8%F0%E0                            |      15 |
Peter> |   2 | %E7%E5%EC%F4%E8%F0%E0                            |      21 |
Peter> |   1 | %E7%E5%EC%F4%E8%F0%E0 %F4%EE%F2%EE               |      25 |
Peter> |   2 | Base=All%20World                                 |      26 |
Peter> |   1 | %D0%E0%EC%F8%F2%E0%E9%ED                         |     119 |
Peter> |   1 | %D3%D4%D2%C1%CE%C9%C3%C1%20%DA%C5%CD%C6%C9%D2%D9 |   45103 |
Peter> |   1 | %E6%F3%F0%ED%E0%EB %D2%F3%F1%EE%E2%EE%F7%EA%E0   |   45104 |
Peter> +-----+--------------------------------------------------+---------+
Peter> 8 rows in set (0.00 sec)

Peter> select layers.stat_keywords.hits as val,layers.keywords.keyword as str,
> layers.keywords.id as keyword from
> layers.layers_stat_keywords,layers.stat_keywords,layers.keywords where
> layers.layers_stat_keywords.page_group=255 and layers.layers_stat_keywords.counter=101 and
> ( (layers.layers_stat_keywords.begin='1999-12-01' AND layers.layers_stat_keywords.type=4))
> and layers.layers_stat_keywords.id = layers.stat_keywords.layer_id and
> layers.stat_keywords.id = layers.keywords.id group by layers.stat_keywords.id

Peter> .....................................................

Peter> |   1 | %CE%EA%E5%E0%ED %DD%EB%FC%E7%FB                                         
>                                                  |     151 |
Peter> |   1 |  %ED%F3%E4%E8%F1%F2%F1%EA%E8%E5 %F4%EE%F2%EE                            
>                                                  |     152 |
Peter> |   1 | %EE%EA%E5%E0%ED %FD%EB%FC%E7%FB                                         
>                                                  |     153 |
Peter> |   1 | %D2%E0%ED%F6%FB %EC%E8%ED%F3%F1                                         
>                                                  |     154 |
Peter> |   1 | %D1%CB%CE%C2%C0 %CF%C5%D1%C5%CD %CC%D3%CC%CC%C8%C9 %D2%D0%CE%CB%CB%DF   
>                                                  |     155 |
Peter> |   1 | Zemfira%20online                                                        
>                                                  |     156 |
Peter> |   1 | %CE%C5%C2%CF%20%CC%CF%CE%C4%CF%CE%C1                                    
>                                                  |     157 |
Peter> |   1 | %E7%E5%EC%F4%E8%F0%E0 %EB%EE%ED%E4%EE%ED                                
>                                                  |     158 |
Peter> |   1 | %EA%EE%F0%EE%EB%E5%E2%F1%EA%E8%EC%E8 %F3%F5%EE%E6%F3
> %F5%EE%F7%E5%F2%F1%FF %F1%E1%F3%E4%E5%F2%F1%FF %F1%EA%E0%ED%E4%E0%EB |     159 |
Peter> |   1 | %CC%C1%D3%CB%CF%D7%D9%CA%20%CD%C1%CA%2C%20%D0%C5%D3%CE%C9               
>                                                  |    3753 |
Peter> |   1 | %F0%E0%EC%F8%F2%E0%E9%ED                                                
>                                                  |    3754 |
Peter>
> +-----+---------------------------------------------------------------------------------------------------------------------------+---------+
Peter> 85 rows in set (0.02 sec)



Peter> But the first query should return at least such many rows as seccond
Peter> because query because where clause in in should return all rows from
Peter> the second query.

<cut>

Peter> May be this is a problem with too complicated where clause ?

On big difference is that the second query uses a GROUP BY, but this
shouldn't normally produce more rows.

As always, I would be appreciate if you can send me a full example;  The
WHERE handling is completely general and should be safe;  The range
optimizer is the one that may be hit by hard queries, but this part of
the code has been stable for a real long time now...

Regards
Monty

Thread
MYSQL 3.23.8 Wron query Result.Peter Zaitsev3 Jan
  • Re: MYSQL 3.23.8 Wron query Result.Sasha Pachev3 Jan
  • MYSQL 3.23.8 Wron query Result.Michael Widenius4 Jan
    • Re: MYSQL 3.23.8 Wron query Result.Benjamin Pflugmann4 Jan