Inconsistent Sql Reult Sets

[shew]

I have an SQL query that should is working for most of the data. However in doing QA I have found that there are a few rows that are not being returned that should be. I have reduced the query to the simpliest form that still exhibits the problem:

 

This query does not return a row where seed = ‘1985.00-Miller-13’ (and it should):

 

SELECT g.dam_alias “Seed”

FROM genetic g LEFT OUTER JOIN (genetic g1d)

ON (g.dam_alias = g1d.genetic_alias)

GROUP BY g1d.dam_alias , g1d.sire_alias;

 

However this query does return a row where seed = ‘1985.00-Miller-13” (only difference is the highlighted line)

 

SELECT g.dam_alias “Seed”

FROM genetic g LEFT OUTER JOIN (genetic g1d)

ON (g.dam_alias = g1d.genetic_alias)

WHERE g.dam_alias = ‘1985.00-Miller-13’

GROUP BY g1d.dam_alias , g1d.sire_alias;

 

I have looked at this for days now and just don’t see the problem. Any ideas?

 

I am using SQLyog Ultimate version v11.4 (64bit) on Windows 7.

 

Thanks.

[peterlaursen]

Why do you think this is an issue with SQLyog? it is the MySQL server that executesthequery and returns the result set. Please try n another client (‘mysql’ command line, phpMyAdmin or whatever you have acces to) and check here. 

 

I think it would bemore appropriate tor eport this to bugs.mysql.com, if you think it is a bug in the server: And you should always mention the server version in such reports BTW. 

 

 

You are of course welcome to ask users of our Forums (and us). But we would need a full test case with table structure and data to verify. And in principle we support or own programs and not the MySQl server. 

[shew]

Hi Peter,

 

Fair enough. Thanks. You are right. I had just become so used to working within the SQLyog interface that I forgot what was really going on.

 

I have decided it must be an issue with the indexes and am going to pursue that angle.

[peterlaursen]

If indexes or not makes a difference on what result set is returned, it is a bug with the server.

 

Indexes should only matter for performance of the query and the amont of memory the server uses when executing the query – not the result.  If it is reproducible on a current server version, I think you should post a bug (with a test case suffiient to reproduce) to bugs.mysql.com

[Author]

Posts