Sqlite query returns different result when executed on iOS 5 compared to iOS 6 or a DB application

I'm executing this query in a Sqlite database app, namely "Base.app" (can be found on the Mac App Store) and I get three result rows. I also get three rows if I execute the code below on iOS6. On iOS 5.1 however only one row is returned. No error. I can reproduce the outcome of the one row on iOS6 and the DB app, if I remove the GROUP BY and the HAVING. But why would iOS5 ignore these?

SELECT COUNT(Sub.intItemID) AS intNumSubFolders, Items.*, Files.*
FROM Items
LEFT JOIN Items AS Sub ON Sub.intParentID = Items.intItemID AND Sub.intObjectType IN (2, 3, 6)
LEFT JOIN Files ON Files.intItemID = Items.intItemID 
WHERE Items.intParentID= 2905837
GROUP BY Items.intItemID HAVING Items.intItemVersion=MAX(Items.intItemVersion)

Here's the code I use:

using ( SqliteCommand oCmd = new SqliteCommand ( "SELECT COUNT(Sub.intItemID) AS intNumSubFolders, Items.*, Files.*" +
                                                                " FROM Items" +
                                                                " LEFT JOIN Items AS Sub ON Sub.intParentID = Items.intItemID AND Sub.intObjectType IN (2, 3, 6)" +
                                                                " LEFT JOIN Files ON Files.intItemID = Items.intItemID " +
                                                                " WHERE Items.intParentID=@intItemID" +
                                                                " GROUP BY Items.intItemID HAVING Items.intItemVersion=MAX(Items.intItemVersion)", oConn ) )
                {
                    oCmd.Parameters.AddWithValue ( "@intItemID", iID );
                    using ( var oReader = oCmd.ExecuteReader (  ) )
                    {
                        while ( oReader.Read (  ) )
                        {
                            // getting here only once
                        }
                        oReader.Close ();
                    }
                }

Can somebody explain why this would happen? Confirmed on iOS5. In iOS6 it is working as expected.

Answers


We "fixed" it in the end by running an iOS version check. For 5.x we run two queries and combine the results in code. Not nice, but working. After all, 90% of all users are on iOS 6.x anyway.


Need Your Help

Anyone who can suggest some good references for Httparty?

ruby-on-rails httparty

Hey I am new to Httparty. I can fetch data but have problem posting using Httparty. Anyone who can suggest some good references for that? Thanks a bunch.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.