Problems using group by about fmdb HOT 8 CLOSED

I'm not sure what the problem might be. However, if you can trim the problem set down to a single .m file which I can run and test with, that would help.

from fmdb.

//ViewController.h
//----------------------

//
// ViewController.h
// test
//
// Created by on 08/05/12.
// Copyright (c) 2012 MyCompanyName. All rights reserved.
//

#import <UIKit/UIKit.h>
#import "FMDatabase.h"

@interface ViewController : UIViewController {
FMDatabase *_database;
}

@EnD

// ViewController.m
// -----------------------

//
// ViewController.m
// test
//
// Created by on 08/05/12.
// Copyright (c) 2012 MyCompanyName. All rights reserved.
//

#import "ViewController.h"
#import "FMResultSet.h"
#import <CoreLocation/CoreLocation.h>

// Database
#define DB_name @"GPW.sql"

// Locales
#define DATE_FORMAT @"yyyy-MM-dd"

@interface Item : NSObject

@Property (nonatomic) NSInteger item_id;
@Property (nonatomic, copy) NSString *name;
@Property (nonatomic, copy) NSString *image_name;
@Property (nonatomic, copy) NSString *features;
@Property (nonatomic, strong) NSDate *date;
@Property (nonatomic, copy) NSString *store_name;
@Property (nonatomic) NSInteger price;
@Property (nonatomic) CLLocationCoordinate2D coordinate;

@EnD

@implementation Item

@synthesize item_id;
@synthesize name;
@synthesize image_name;
@synthesize features;
@synthesize date;
@synthesize store_name;
@synthesize price;
@synthesize coordinate;

@EnD

@interface ViewController ()

@EnD

@implementation ViewController

//---Returns a NSArray of type Item that match criteria, nil if error

• (NSArray *) getItemsWithItem_id:(NSInteger) item_id
  name:(NSString *) name
  image_name:(NSString *) image_name
  features:(NSString *) features
  date:(NSDate *) date
  store_name:(NSString *) store_name
  price:(NSInteger) price
  coordinate:(CLLocationCoordinate2D) coordinate {

  NSMutableArray *returnArrayOfItems = nil;

  if ([_database open]) {
  returnArrayOfItems = [[NSMutableArray alloc] init];

  //---Configure filter options
  NSString *sqlFilterOptions = @"";
  if (item_id >= 0) sqlFilterOptions = [sqlFilterOptions stringByAppendingString:[NSString stringWithFormat:@" AND i.item_id=%d", item_id]];
  if (name) sqlFilterOptions = [sqlFilterOptions stringByAppendingString:[NSString stringWithFormat:@" AND i.name='%@'", name]];
  if (image_name) sqlFilterOptions = [sqlFilterOptions stringByAppendingString:[NSString stringWithFormat:@" AND i.image_name='%@'", image_name]];
  if (date) {
      NSDateFormatter *dateFormat = [[NSDateFormatter alloc] init];
      [dateFormat setDateFormat:DATE_FORMAT];
      sqlFilterOptions = [sqlFilterOptions stringByAppendingString:[NSString stringWithFormat:@" AND ia.date=%g", [dateFormat stringFromDate:date]]];
  }
  if (store_name) sqlFilterOptions = [sqlFilterOptions stringByAppendingString:[NSString stringWithFormat:@" AND ia.store_name='%@'", store_name]];
  if (price >= 0) sqlFilterOptions = [sqlFilterOptions stringByAppendingString:[NSString stringWithFormat:@" AND ia.price=%d", price]];
  if (features) sqlFilterOptions = [sqlFilterOptions stringByAppendingString:[NSString stringWithFormat:@" AND i.features='%@'", features]];
  if (coordinate.latitude != 0  || coordinate.longitude != 0) sqlFilterOptions = [sqlFilterOptions stringByAppendingString:[NSString stringWithFormat:@" AND ia.latitude=%g AND ia.longitude=%g", coordinate.latitude, coordinate.longitude]];
  
  //---SQL query
  NSString *sqlQuery = [@"SELECT i.item_id, i.name, i.image_name, i.features, ia.date, MIN(ia.price) AS price, ia.store_name, ia.latitude, ia.longitude FROM Item i, ItemAttributes ia WHERE i.item_id=ia.item_id" stringByAppendingString:sqlFilterOptions];
  sqlQuery = [sqlQuery stringByAppendingString:@" GROUP BY i.name ORDER BY i.name"];
  

  #warning Waiting for answer from FMDB
  NSLog(@"SQL: %@", sqlQuery);

  //--Search for Items that match criteria
  FMResultSet *result = [_database executeQuery:sqlQuery];
  while ([result next]) {
      //retrieve values for each record
      Item *i = [[Item alloc] init];
  
      i.item_id = [result intForColumn:@"item_id"];
      i.name = [result stringForColumn:@"name"];
      i.image_name = [result stringForColumn:@"image_name"];
      i.features = [result stringForColumn:@"features"];
      NSDateFormatter *dateFormat = [[NSDateFormatter alloc] init];
      [dateFormat setDateFormat:DATE_FORMAT];
      NSString *dateString = [result stringForColumn:@"date"];
      i.date = [dateFormat dateFromString:dateString];
      i.store_name = [result stringForColumn:@"store_name"];
      i.price = (NSInteger)[result intForColumn:@"price"];
      CLLocationCoordinate2D tempCoordinate;
      tempCoordinate.latitude = (CLLocationDegrees)[result doubleForColumn:@"latitude"];
      tempCoordinate.longitude = (CLLocationDegrees)[result doubleForColumn:@"longitude"];
      i.coordinate = tempCoordinate;
  
      [returnArrayOfItems addObject:i];
  }
  
  [_database close];
  

  }

  return [NSArray arrayWithArray:returnArrayOfItems];
  }

• (void)viewDidLoad
  {
  [super viewDidLoad];

  //--- Check if the database has already been saved to the users phone, if not then copy it over

  // Get the path to the users filesystem
  NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
  NSString *path = [paths objectAtIndex:0];
  NSString *databasePath = [path stringByAppendingPathComponent:DB_name];
  NSLog(@"databasePath: %@", databasePath);

  // If the database has not already been created in the users filesystem, copy it form the application to the users filesystem
  NSFileManager *fileManager = [NSFileManager defaultManager];
  if ( ![fileManager fileExistsAtPath:databasePath]) {
  NSString *databasePathFromApp = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:DB_name];
  NSLog(@"databaseFromApp: %@", databasePathFromApp);
  [fileManager copyItemAtPath:databasePathFromApp toPath:databasePath error:nil];
  }

  _database = [FMDatabase databaseWithPath:databasePath];

  // Get all Items from database
  CLLocationCoordinate2D coord;
  coord.latitude = 0;
  coord.longitude = 0;
  NSMutableArray *allItemsList = [NSMutableArray arrayWithArray:[self getItemsWithItem_id:(-1)
  name:nil
  image_name:nil
  features:nil
  date:nil
  store_name:nil
  price:(-1)
  coordinate:coord]];
  for (Item *i in allItemsList) {
  NSLog(@"item_id: %d, name: %@, price:%d, store_name: %@", i.item_id, i.name, i.price, i.store_name);
  }

}

• (void)viewDidUnload
  {
  [super viewDidUnload];
  // Release any retained subviews of the main view.
  }
• (BOOL)shouldAutorotateToInterfaceOrientation:(UIInterfaceOrientation)interfaceOrientation
  {
  return (interfaceOrientation != UIInterfaceOrientationPortraitUpsideDown);
  }

@EnD

from fmdb.

Sorry, this doesn't help me. I need a single .m file with a main() method, which sets up tables, populates it, does the query, and maybe some assertions showing where the bug is. Check out FMDB's main.m file for an example.

from fmdb.

Try this code

https://www.dropbox.com/sh/89gxv0b912kzrem/ssynYoF-oS/test.zip

from fmdb.

I'm sorry, but I don't have time to debug an application. I need a reproducible case, all contained within a single .m file, which sets everything up, performs a query, and has some sort of assertion or check that says "this right here, this is wrong!".

You can even alter the main.m file in FMDB's distribution to do this, and send me that.

from fmdb.

I've added a new function to main.m in the FMDB project to help you out. Read the section on reporting bugs in the README: https://github.com/ccgus/fmdb#reporting-bugs

from fmdb.

Error can be reproduced much more simply here:

void FMDBReportABugFunction() {

NSString *dbPath = @"/tmp/bugreportsample.db";

// delete the old db if it exists
NSFileManager *fileManager = [NSFileManager defaultManager];
[fileManager removeItemAtPath:dbPath error:nil];

FMDatabaseQueue *queue = [FMDatabaseQueue databaseQueueWithPath:dbPath];

[queue inDatabase:^(FMDatabase *db) {

    /*
     Change the contents of this block to suit your needs.
     */

    BOOL worked = [db executeUpdate:@"create table stoptime (trip_id text, departure text)"];
    FMDBQuickCheck(worked);
    worked = [db executeUpdate:@"insert into stoptime (trip_id,departure) values ('a','07:00')"];
    FMDBQuickCheck(worked);
    worked = [db executeUpdate:@"insert into stoptime (trip_id,departure) values ('a','08:00')"];
    FMDBQuickCheck(worked);
    worked = [db executeUpdate:@"insert into stoptime (trip_id,departure) values ('a','09:00')"];
    FMDBQuickCheck(worked);
    worked = [db executeUpdate:@"insert into stoptime (trip_id,departure) values ('b','07:01')"];
    FMDBQuickCheck(worked);
    worked = [db executeUpdate:@"insert into stoptime (trip_id,departure) values ('b','08:01')"];
    FMDBQuickCheck(worked);
    worked = [db executeUpdate:@"insert into stoptime (trip_id,departure) values ('b','09:01')"];
    FMDBQuickCheck(worked);



    FMResultSet *rs = [db executeQuery:@"select MIN(departure),trip_id from stoptime"];
    while ([rs next]) {
        NSString *departure = [rs stringForColumnIndex:0];
        NSString *trip_id = [rs stringForColumnIndex:1];
        NSLog(@"T: %@ Trip ID: %@",[rs stringForColumnIndex:0],[rs stringForColumnIndex:1]);
        FMDBQuickCheck([departure isEqualToString:@"07:00"]);
        FMDBQuickCheck([trip_id isEqualToString:@"a"]);  // This is coming up 'b' instead of 'a'
    }
    [rs close];

}];


[queue close];


// uncomment the following line if you don't want to run through all the other tests.
//exit(0);

}

from fmdb.

Thanks - I got the problem to reproduce, but it doesn't look like a bug in FMDB. The same problem occurs with SQLite's tool:

sqlite> create table stoptime (trip_id text, departure text);
sqlite> insert into stoptime (trip_id,departure) values ('a','07:00');
sqlite> insert into stoptime (trip_id,departure) values ('a','08:00');
sqlite> insert into stoptime (trip_id,departure) values ('a','09:00');
sqlite> insert into stoptime (trip_id,departure) values ('b','07:01');
sqlite> insert into stoptime (trip_id,departure) values ('b','08:01');
sqlite> insert into stoptime (trip_id,departure) values ('b','09:01');
sqlite> select MIN(departure),trip_id from stoptime;
07:00|b

I'm willing to bet that it's just something about the query that needs to be cleaned up. Maybe ask on the SQLite mailing list?

from fmdb.