Re: weird behaviour...

Dan Bolser writes:
> Sinisa Milivojevic said:
> > Dan Bolser writes:
> >> >
> >> >SELECT *
> >> >A RIGHT join B
> >> >on A.one = B.one and A.two = B.two
> >> >WHERE B.one IS NULL # <--
> >> >= 0 rows # < !!!
> >
> > Why would this query return rows ??
>
> **RIGHT JOIN!!!
>

It is possible that there are no un-matched rows in table A. Omit
WHERE B.one IS NULL and see if there are any.

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com

Join MySQL Users Conference and Expo:
http://www.mysql.com/events/uc2003/


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13877@lists.mysql.com
To unsubscribe, e-mail

Re: weird behaviour...

On Mon, 3 Mar 2003, Sinisa Milivojevic wrote:

> Dan Bolser writes:
> > Sinisa Milivojevic said:
> > > Dan Bolser writes:
> > >> >
> > >> >SELECT *
> > >> >A RIGHT join B
> > >> >on A.one = B.one and A.two = B.two
> > >> >WHERE B.one IS NULL # <--
> > >> >= 0 rows # < !!!
> > >
> > > Why would this query return rows ??
> >
> > **RIGHT JOIN!!!
> >
>
> It is possible that there are no un-matched rows in table A. Omit
> WHERE B.one IS NULL and see if there are any.

Anything is possible, this is a test case which dosn't show
any error - it is only when I start grouping, having and
ifing that I get a problem. Please reread my orginal post.

Cheers, Dan.


>
>


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13881@lists.mysql.com
To unsubscribe, e-mail

Re: weird behaviour...

Dan Bolser writes:
> On Mon, 3 Mar 2003, Sinisa Milivojevic wrote:
>
>
> Anything is possible, this is a test case which dosn't show
> any error - it is only when I start grouping, having and
> ifing that I get a problem. Please reread my orginal post.
>
> Cheers, Dan.

This can happen as HAVING is applied ONLY to final results, after
GROUPing has finished.

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com

Join MySQL Users Conference and Expo:
http://www.mysql.com/events/uc2003/


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13882@lists.mysql.com
To unsubscribe, e-mail

Re: weird behaviour...

Dan Bolser writes:
>
> Sinisa Milivojevic wrote:
>
>
> Of course, so the difference between RIGHT JOIN and
> RIGHT JOIN WHERE NOT NULL should be selected
> by the RIGHT JOIN WHERE NULL query, which it
> isn't, which is why I think their is a bug.
>
> Do the if's always evaluate correctly before the grouping?
>
> Will max(if()) work properly?
>
> The error is still there if you remove the having clause
> and select all rows (combinations).
>
> Let me know if you dont understand what the query is doing,
>
> Thanks again,
> Dan.

Hi!

Query is working properly.

You have aliased VECTOR as max(if(a.tax_id = 2759,1,0), so it can only
takes values or 1 and 0.

It therefore can not be equal to "111".

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com

Join MySQL Users Conference and Expo:
http://www.mysql.com/events/uc2003/


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13884@lists.mysql.com
To unsubscribe, e-mail

Re: weird behaviour...

Dan Bolser writes:
>
> Sinisa Milivojevic wrote:
>
> Of course, so the difference between RIGHT JOIN and
> RIGHT JOIN WHERE NOT NULL should be selected
> by the RIGHT JOIN WHERE NULL query, which it
> isn't, which is why I think their is a bug.
>
> Do the if's always evaluate correctly before the grouping?
>
> Will max(if()) work properly?
>
> The error is still there if you remove the having clause
> and select all rows (combinations).
>
> Let me know if you dont understand what the query is doing,
>
> Thanks again,
> Dan.

I now see that you have used CONCAT(). But, you have used concat on
integer values, not on string values.

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com

Join MySQL Users Conference and Expo:
http://www.mysql.com/events/uc2003/


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13885@lists.mysql.com
To unsubscribe, e-mail

Re: weird behaviour...

On Mon, 3 Mar 2003, Sinisa Milivojevic wrote:

> Dan Bolser writes:
> >
> > Sinisa Milivojevic wrote:
> >
> > Of course, so the difference between RIGHT JOIN and
> > RIGHT JOIN WHERE NOT NULL should be selected
> > by the RIGHT JOIN WHERE NULL query, which it
> > isn't, which is why I think their is a bug.
> >
> > Do the if's always evaluate correctly before the grouping?
> >
> > Will max(if()) work properly?
> >
> > The error is still there if you remove the having clause
> > and select all rows (combinations).
> >
> > Let me know if you dont understand what the query is doing,
> >
> > Thanks again,
> > Dan.
>
> I now see that you have used CONCAT(). But, you have used concat on
> integer values, not on string values.

Look at the following....


SELECT a.sccs, c.name,

CONCAT(
max(if(b.tax_id = 1, 1,0)),
max(if(a.tax_id = 2157,1,0)), # Archea in the full table.
max(if(a.tax_id = 2, 1,0)), # Bacteria in the full table.
max(if(a.tax_id = 2759,1,0)) # Eukaryotes in the full table.

) AS vector # Vector is set in the order above

FROM
ncbi_scop.sun_tax_tree a

LEFT JOIN
intemit.mito_sun_tax_tree b on a.tax_id = b.tax_id and a.sccs = b.sccs

INNER JOIN
scop_1_59.scop c on c.sccs = a.sccs

#WHERE b.tax_id IS NOT NULL

GROUP BY a.sccs # Creates a list of tax_ids for
# each SCCS which is checked by
# 'if' above?

HAVING vector LIKE "1010";

With WHERE IS NOT NULL (above) = 82 rows
Without WHERE IS NOT NULL (above) = 21 rows

This is weird dont you think? The addition of a constrint
increasing the number of rows returned?

If instead you use WHERE IS NULL above, you get 0 rows, where
techically you would expect minus 41 rows from above!

This must be to do with grouping across a muli part directed join,
I just cant work out the logic in my head, so I dont know
what behaviour to expect...

At least we are getting closer to the actuall problem...

Thanks again, Dan.



------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13886@lists.mysql.com
To unsubscribe, e-mail

Re: weird behaviour...

> It therefore can not be equal to "111".

yes it can....

>
>


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13887@lists.mysql.com
To unsubscribe, e-mail

Re: weird behaviour...

regarding the last suggestion about concat
of an integer, not a string, changing
the ints to strings has no effect
on the behaviour I have been observing.

Dan.


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13888@lists.mysql.com
To unsubscribe, e-mail

Re: weird behaviour...

On Mon, 2003-03-03 at 21:04, Dan Bolser wrote:

Dear Dan,

Unfortunately the current discussion does not really leads to
anything other than adding a lot of traffic to bugs@ list
which we try to keep really low traffic.

The reason for it is simple. We do not have the test case.

Could you please do us a favor and create repeatable case for this
problem, meaning table structure + data + query repeated
to demonstrate the problem.

This is usually the best way to get the quick and accurate
response if it is a bug or expected MySQL functionality.


Thank you for your cooperation.

> regarding the last suggestion about concat
> of an integer, not a string, changing
> the ints to strings has no effect
> on the behaviour I have been observing.
>
> Dan.




--
MySQL 2003 Users Conf. -> http://www.mysql.com/events/uc2003/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Peter Zaitsev
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Moscow, Russia
<___/ www.mysql.com


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13889@lists.mysql.com
To unsubscribe, e-mail

Re: weird behaviour...

Dan Bolser writes:
> On Mon, 3 Mar 2003, Sinisa Milivojevic wrote:
>
>
> Look at the following....
>
>
> SELECT a.sccs, c.name,
>
> CONCAT(
> max(if(b.tax_id = 1, 1,0)),
> max(if(a.tax_id = 2157,1,0)), # Archea in the full table.
> max(if(a.tax_id = 2, 1,0)), # Bacteria in the full table.
> max(if(a.tax_id = 2759,1,0)) # Eukaryotes in the full table.
>
> ) AS vector # Vector is set in the order above
>
> FROM
> ncbi_scop.sun_tax_tree a
>
> LEFT JOIN
> intemit.mito_sun_tax_tree b on a.tax_id = b.tax_id and a.sccs = b.sccs
>
> INNER JOIN
> scop_1_59.scop c on c.sccs = a.sccs
>
> #WHERE b.tax_id IS NOT NULL
>
> GROUP BY a.sccs # Creates a list of tax_ids for
> # each SCCS which is checked by
> # 'if' above?
>
> HAVING vector LIKE "1010";
>
> With WHERE IS NOT NULL (above) = 82 rows
> Without WHERE IS NOT NULL (above) = 21 rows
>
> This is weird dont you think? The addition of a constrint
> increasing the number of rows returned?
>
> If instead you use WHERE IS NULL above, you get 0 rows, where
> techically you would expect minus 41 rows from above!
>
> This must be to do with grouping across a muli part directed join,
> I just cant work out the logic in my head, so I dont know
> what behaviour to expect...
>
> At least we are getting closer to the actuall problem...
>
> Thanks again, Dan.
>

Yes, it is .

But your first condition was .. HAVING vector like "111", while there
are four max's...

What version of MySQL are you using and is it our binary ??

Also, check the indices on the tables.

If it is our binary and all indices are OK, then this is something we
shall have to test ourselves.

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com

Join MySQL Users Conference and Expo:
http://www.mysql.com/events/uc2003/


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13890@lists.mysql.com
To unsubscribe, e-mail