[Dbix-class] Iteration with ->next versus using ->all, and count

[Adam Sjøgren]

Last week I upgraded to Ubuntu 10.04, and went from DBIx::Class 0.08107
to 0.08115.

In an application I noticed a change in iterating using ->next, compared
to calling ->all (on a resultset obtained with search_related), that I
find a little odd: One returns one record, the other returns three.

Here is an attempt at a minimal example (using Postgres and DBIx::Class
from git (93d0eb5)):

    $ createdb cardb
    $ PGOPTIONS='--client-min-messages=warning' psql cardb --quiet -f init.sql
    $ DBIC_TRACE=1 ./test.pl 
    DBIx::Class::VERSION: 0.08115

    SELECT me.id, me.company FROM fleet me WHERE ( me.id = ? ): '1'
    SELECT COUNT( * ) FROM car me JOIN driver drivers ON drivers.car_id = me.id WHERE ( me.fleet_id = ? ): '1'
    Count: 3

    Iterator:
    SELECT drivers.id, drivers.name, drivers.car_id FROM car me LEFT JOIN driver drivers ON drivers.car_id = me.id WHERE ( me.fleet_id = ? ): '1'
     Christensen

    All:
    SELECT drivers.id, drivers.name, drivers.car_id FROM car me LEFT JOIN driver drivers ON drivers.car_id = me.id WHERE ( me.fleet_id = ? ): '1'
     Christensen
     Elgaard
     Magnussen
    $ cat test.pl 
    #!/usr/bin/perl

    use warnings;
    use strict;

    use CarDB;

    print 'DBIx::Class::VERSION: ' . $DBIx::Class::VERSION . "\n\n";

    my $schema=CarDB->connect('dbi:Pg:dbname=cardb', 'core', 'core', { AutoCommit=>0 });

    my $fleet=$schema->resultset('CarDB::Fleet')->find({ id=>1 });

    my $drivers=$fleet->cars->search_related('drivers');
    print "Count: " . $drivers->count . "\n";

    print "\nIterator:\n";
    while (my $driver=$drivers->next) {
        print " " . $driver->name . "\n";
    }

    print "\nAll:\n";
    foreach my $driver ($drivers->all) {
        print " " . $driver->name . "\n";
    }
    $ cat init.sql 
    CREATE TABLE fleet (
      id integer NOT NULL,
      company text NOT NULL,
      PRIMARY KEY (id)
    );

    CREATE TABLE car (
      id integer NOT NULL,
      plate text NOT NULL,
      fleet_id integer REFERENCES fleet NOT NULL,
      PRIMARY KEY (id)
    );

    CREATE TABLE driver (
      id integer NOT NULL,
      name text NOT NULL,
      car_id integer REFERENCES car NOT NULL,
      PRIMARY KEY (id)
    );

    INSERT INTO fleet (id, company) VALUES (1, 'Hertz');

    INSERT INTO car (id, plate, fleet_id) VALUES (1, 'AAA 101', 1);
    INSERT INTO car (id, plate, fleet_id) VALUES (2, 'BBB 202', 1);
    INSERT INTO car (id, plate, fleet_id) VALUES (3, 'CCC 303', 1);
    INSERT INTO car (id, plate, fleet_id) VALUES (4, 'DDD 404', 1);

    INSERT INTO driver (id, name, car_id) VALUES (1, 'Christensen', 1);
    INSERT INTO driver (id, name, car_id) VALUES (2, 'Elgaard',     3);
    INSERT INTO driver (id, name, car_id) VALUES (3, 'Magnussen',   4);
    $ cat CarDB.pm 
    package CarDB;

    use strict;
    use warnings;

    use base qw(DBIx::Class::Schema);

    __PACKAGE__->load_classes();

    1;
    $ cat CarDB/
    Car.pm     Driver.pm  Fleet.pm   
    $ cat CarDB/Fleet.pm 
    package CarDB::Fleet;

    use strict;
    use warnings;

    use base qw(DBIx::Class);

    __PACKAGE__->load_components('PK::Auto', 'Core');
    __PACKAGE__->table('fleet');
    __PACKAGE__->add_columns(qw(id company));
    __PACKAGE__->set_primary_key('id');

    __PACKAGE__->has_many(cars=>'CarDB::Car', 'fleet_id', { cascade_delete=>0 });

    1;
    $ cat CarDB/Car.pm 
    package CarDB::Car;

    use strict;
    use warnings;

    use base qw(DBIx::Class);

    __PACKAGE__->load_components('PK::Auto', 'Core');
    __PACKAGE__->table('car');
    __PACKAGE__->add_columns(qw(id plate fleet_id));
    __PACKAGE__->set_primary_key('id');

    __PACKAGE__->belongs_to(fleet=>'CarDB::Fleet', 'fleet_id');

    __PACKAGE__->has_many(drivers=>'CarDB::Driver', 'car_id', { cascade_delete=>0 });

    1;
    $ cat CarDB/Driver.pm 
    package CarDB::Driver;

    use strict;
    use warnings;

    use base qw(DBIx::Class);

    __PACKAGE__->load_components('PK::Auto', 'Core');
    __PACKAGE__->table('driver');
    __PACKAGE__->add_columns(qw(id name car_id));
    __PACKAGE__->set_primary_key('id');

    __PACKAGE__->belongs_to(car=>'CarDB::Car', 'car_id');

    1;
    $ 

Also, as seen above, if I call ->count on the resultset, the generated
SQL uses JOIN, while ->next and ->all both use LEFT JOIN - I was
expecting all three to run the "same" query?

Running the same example with DBIx::Class 0.08107 gives this result:

    $ DBIC_TRACE=1 ./test.pl 
    DBIx::Class::VERSION: 0.08107

    SELECT me.id, me.company FROM fleet me WHERE ( me.id = ? ): '1'
    SELECT COUNT( * ) FROM car me LEFT JOIN driver drivers ON drivers.car_id = me.id WHERE ( me.fleet_id = ? ): '1'
    Count: 4

    Iterator:
    SELECT drivers.id, drivers.name, drivers.car_id FROM car me LEFT JOIN driver drivers ON drivers.car_id = me.id WHERE ( me.fleet_id = ? ): '1'
     Christensen
    Use of uninitialized value in concatenation (.) or string at ./test.pl line 19.

     Elgaard
     Magnussen

    All:
    SELECT drivers.id, drivers.name, drivers.car_id FROM car me LEFT JOIN driver drivers ON drivers.car_id = me.id WHERE ( me.fleet_id = ? ): '1'
     Christensen
    Use of uninitialized value in concatenation (.) or string at ./test.pl line 24.

     Elgaard
     Magnussen
    Issuing rollback() for database handle being DESTROY'd without explicit disconnect().
    $ 

So in 0.08107 ->next and ->all do the same thing (albeit arguably the
empty driver shouldn't have been returned).

In 0.08115 ->count and ->all return the results I was expecting, but
->next doesn't.

Shouldn't it skip the record with no driver? (Or maybe both ->next and
->all should use JOIN, like ->count); I am not quite sure.

Maybe it is all driver error?


  Best regards,

    Adam

-- 
                                                          Adam Sjøgren
                                                    adsj at novozymes.com