[Dbix-class] Problem selecting distinct years from date column

Thu Jan 7 14:04:22 GMT 2010

OK, my mistake. Here are the results of your script when run against our MySQL server:

ok 1 - definitely not connected
ok 2 - sqlt_type correct pre-connection
INSERT INTO artist ( name) VALUES ( ? ): 'foo'
ok 3 - Auto-PK worked
INSERT INTO artist ( name) VALUES ( ? ): 'Artist 1'
INSERT INTO artist ( name) VALUES ( ? ): 'Artist 2'
INSERT INTO artist ( name) VALUES ( ? ): 'Artist 3'
INSERT INTO artist ( name) VALUES ( ? ): 'Artist 4'
INSERT INTO artist ( name) VALUES ( ? ): 'Artist 5'
INSERT INTO artist ( name) VALUES ( ? ): 'Artist 6'
SELECT COUNT( * ) FROM artist me: 
ok 4 - LIMIT count ok
SELECT me.artistid, me.name, me.rank, me.charfield FROM artist me ORDER BY artistid LIMIT 2, 3: 
ok 5 - iterator->next ok
ok 6 - next past end of resultset ok
INSERT INTO owners ( id, name) VALUES ( ?, ? ): 'NULL'
BEGIN WORK
COMMIT
INSERT INTO books ( owner, source, title) VALUES ( ?, ?, ? ): 'NULL'
BEGIN WORK
COMMIT
SELECT me.id, me.name, books.id, books.source, books.owner, books.title, books.price FROM (SELECT me.id, me.name FROM owners me LEFT JOIN books books ON books.owner = me.id WHERE ( books.id IS NOT NULL ) GROUP BY me.id, me.name) me LEFT JOIN books books ON books.owner = me.id WHERE ( books.id IS NOT NULL ) ORDER BY books.owner: 
ok 7 - Prefetched grouped search returns correct number of rows
SELECT COUNT( * ) FROM (SELECT me.id FROM owners me LEFT JOIN books books ON books.owner = me.id WHERE ( books.id IS NOT NULL ) GROUP BY me.id) count_subq: 
ok 8 - Prefetched grouped search returns correct count
SELECT me.id, me.name FROM owners me WHERE ( id IN ( SELECT me.id FROM owners me LEFT JOIN books books ON books.owner = me.id WHERE ( books.id IS NOT NULL ) GROUP BY me.id ) ): 
ok 9 - Prefetched grouped search returns correct number of rows
SELECT COUNT( * ) FROM owners me WHERE ( id IN ( SELECT me.id FROM owners me LEFT JOIN books books ON books.owner = me.id WHERE ( books.id IS NOT NULL ) GROUP BY me.id ) ): 
ok 10 - Prefetched grouped search returns correct count
SELECT me.id, me.source, me.owner, me.title, me.price, owner.id, owner.name FROM (SELECT me.id, me.source, me.owner, me.title, me.price FROM books me JOIN owners owner ON owner.id = me.owner WHERE ( ( owner.name = ? AND source = ? ) ) GROUP BY me.id, me.source, me.owner, me.title, me.price) me JOIN owners owner ON owner.id = me.owner WHERE ( ( owner.name = ? AND source = ? ) ): 'wiggle', 'Library', 'wiggle', 'Library'
ok 11 - Prefetched grouped search returns correct number of rows
SELECT COUNT( * ) FROM (SELECT me.id FROM books me JOIN owners owner ON owner.id = me.owner WHERE ( ( owner.name = ? AND source = ? ) ) GROUP BY me.id, me.source, me.owner, me.title, me.price) count_subq: 'wiggle', 'Library'
ok 12 - Prefetched grouped search returns correct count
SELECT me.id, me.source, me.owner, me.title, me.price FROM books me WHERE ( ( id IN ( SELECT me.id FROM books me JOIN owners owner ON owner.id = me.owner WHERE ( ( owner.name = ? AND source = ? ) ) GROUP BY me.id ) AND source = ? ) ): 'wiggle', 'Library', 'Library'
ok 13 - Prefetched grouped search returns correct number of rows
SELECT COUNT( * ) FROM books me WHERE ( ( id IN ( SELECT me.id FROM books me JOIN owners owner ON owner.id = me.owner WHERE ( ( owner.name = ? AND source = ? ) ) GROUP BY me.id ) AND source = ? ) ): 'wiggle', 'Library', 'Library'
ok 14 - Prefetched grouped search returns correct count
ok 15 - columns_info_for - column data types
INSERT INTO cd () VALUES (): 
INSERT INTO producer () VALUES (): 
DELETE FROM cd_to_producer WHERE ( cd = ? ): '1'
INSERT INTO cd_to_producer ( cd, producer) VALUES ( ?, ? ): '1', '1'
ok 16 - set_relationship doesnt die
SELECT me.artistid, me.name, me.rank, me.charfield FROM artist me: 
SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track FROM cd me: 
UPDATE cd SET artist = ? WHERE ( cdid = ? ): '1', '1'
SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track, artist.artistid, artist.name, artist.rank, artist.charfield FROM cd me JOIN artist artist ON artist.artistid = me.artist: 
ok 17 - Prefetched artist
ok 18 - join does not throw (mysql 3 test)
ok 19 - overriden default join type works
SET SQL_MODE = CONCAT('ANSI,TRADITIONAL,ONLY_FULL_GROUP_BY,', @@sql_mode): 
SET SQL_AUTO_IS_NULL = 0: 
INSERT INTO artist ( name) VALUES ( ? ): 'last created artist'
ok 20 - Created an artist resultset of 6666
SELECT COUNT( * ) FROM artist me WHERE ( artistid = ? ): '6666'
ok 21 - Got no returned rows
ok 22 - Created an artist resultset of undef
SELECT COUNT( * ) FROM artist me WHERE ( artistid IS NULL ): 
ok 23 - got no rows
SELECT me.artistid, me.name, me.rank, me.charfield FROM artist me WHERE ( artistid IS NULL ): 
ok 24 - Nothing Found!
DELETE FROM cd: 
INSERT INTO cd ( artist, title, year) VALUES ( ?, ?, ? ): '1', 'CD 2005-1', '2005-01-01'
INSERT INTO cd ( artist, title, year) VALUES ( ?, ?, ? ): '1', 'CD 2005-2', '2005-01-01'
INSERT INTO cd ( artist, title, year) VALUES ( ?, ?, ? ): '1', 'CD 2005-3', '2005-01-01'
INSERT INTO cd ( artist, title, year) VALUES ( ?, ?, ? ): '1', 'CD 2002-1', '2002-01-01'
INSERT INTO cd ( artist, title, year) VALUES ( ?, ?, ? ): '1', 'CD 2001-1', '2001-01-01'
INSERT INTO cd ( artist, title, year) VALUES ( ?, ?, ? ): '1', 'CD 2001-2', '2001-01-01'
SELECT COUNT( * ) FROM cd me:
ok 25 - CDs created successfully
SELECT YEAR( year ) FROM cd me GROUP BY year ORDER BY year: 
ok 26 - Years group successfully
INSERT INTO cd ( artist, title, year) VALUES ( ?, ?, ? ): '1', 'Jesus Rap', '0-1-1'
SELECT YEAR( year ) FROM cd me GROUP BY year ORDER BY year: 
ok 27 - Zero-year groups successfully
SELECT YEAR( year ) FROM cd me WHERE ( ( year != ? AND year IS NOT NULL ) ) GROUP BY year ORDER BY year: '0'
SELECT YEAR( year ) FROM cd me GROUP BY year ORDER BY year: 
ok 28 - Zero year was correctly excluded from resultset
SELECT me.artistid, me.name, me.rank, me.charfield FROM artist me WHERE ( me.artistid = ? ): '4'
ok 29 - The object isa DBIx::Class::SQLAHacks::MySQL
1..29

Thanks,

		Matt.

> -----Original Message-----
> From: Peter Rabbitson [mailto:rabbit+dbic at rabbit.us]
> Sent: 07 January 2010 11:08
> To: Class user and developer list
> Subject: Re: [Dbix-class] Problem selecting distinct years from date
> column
> 
> matthew couchman (JIC) wrote:
> > Hi,
> >
> > I came back to this problem and I think I've got it sorted now so I
> thought it might be useful to post the solution in case anybody else
> was having the same problem. My problem was that quotes were being
> added to the zero argument. So the revised query looks like this:
> >
> > my $years_rs = $c->model($table_name)->search(
> >     {
> >         date => { '!=', \"0" }
> >     },
> >     {
> >         select => [ \'YEAR(date) AS year' ],
> >     	  as => [ 'year' ],
> >         group_by => [ 'year' ]
> >     }
> > );
> >
> 
> This is not a solution, please do not popularize hideous crap like this
> in a public forum.
> 
> > My problem was that quotes were being added to the zero argument.
> 
> ^^ This can not be a problem with placeholders, as quotation is
> absolutely irrelevant. I took the time to write a test case and kindly
> asked you to try it against your own mysql server. Please do so and
> come back with the results.
> 
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive: http://www.grokbase.com/group/dbix-
> class at lists.scsys.co.uk