Duplicate last row with group by query

[napcs]

Using this query:

select distinct terms.id, count(workshops.id) as workshop_count, sum(workshops.time_spent)/3600 as term_time_spent, terms.semester, terms.year
from terms left join workshops on workshops.term_id=terms.id
and workshops.cancelled = 0
and workshops.visible = 1
and workshops.enabled = 1
where terms.id like '1'
group by terms.id, terms.semester, terms.year
order by terms.year

We're always getting one additional row, which is a duplicate row.

Table definitions are:

create_table "workshops", :force => true do |t|
  t.text     "prerequisites"
  t.integer  "department_id"
  t.integer  "seats"
  t.text     "comments"
  t.string   "number"
  t.integer  "offering_id"
  t.integer  "location_id"
  t.datetime "starts_at"
  t.datetime "ends_at"
  t.boolean  "visible"
  t.boolean  "enabled"
  t.datetime "created_at"
  t.boolean  "cancelled"
  t.boolean  "attendance_submitted"
  t.integer  "term_id"
end

create_table "terms", :force => true do |t|
 t.string  "semester"
 t.string  "year"
 t.boolean "is_current"
end

This seems like a fairly simple query, and we did run this through SQLServer 2005's console directly and received only one record.

Rails 2.3.2
Adapter version 2.2.19

Any help would greatly be appreciated.

[metaskills]

Depends on where you are using the query. If it is in a association or other finder conditions string or perhaps even a :select option, then you might just have to break down to using a class :finder_sql, :counter_sql option for the association/method. There are tons of hacks in the adapter code to augment SQL to perform the association limiting SQL.

Please investigate more on where this is being used and what SQL is being generated and if what I am saying is happening and you can use those bail options, please do so.

[napcs]

We're actually doing a find_by_sql. I traced it as far down as I can, to the raw_select method in the adapter, and it gets two elements in the array there. I dug around in the format_as_array and couldn't quite figure out what you were trying to do with the inject within the inject. The entries in the array are exactly the same for this query - no matter how many terms I have in the database, the last one is always returned twice. Even with one term and no workshops, we get two results back.

[metaskills]

So are you telling me the SQL that the adapter sends down to ODBC is getting a different result in QA? That should not be possible.

What does your ruby look like. I need more to go on.

[napcs]

We pop open the console and type

 Term.find_by_sql("
 select distinct terms.id, count(workshops.id) as workshop_count, 
 sum(workshops.time_spent)/3600 as term_time_spent, terms.semester, terms.year
 from terms left join workshops on workshops.term_id=terms.id
 and workshops.cancelled = 0
 and workshops.visible = 1
 and workshops.enabled = 1
 where terms.id like '1' 
 group by terms.id, terms.semester, terms.year
 order by terms.year")

We get an array back, as expected, but the last result is repeated in the array. Nothing else. This is a simple query, too.

using SQL Server 2005, and when we run this exact query from SQL Management console, (with one record in the term table and 0 records in the workshops table) we get a single row.

Seriously, nothing fancy at all.

[metaskills]

Here is the important part. Have you looked at the SQL generated (logged in standard log) to see if it lines up and is unaltered?

[napcs]

Here's our development log:

 ^[[4;36;1mSQL (0.9ms)^[[0m   ^[[0;1mSELECT @@ROWCOUNT AS AffectedRows^[[0m
 ^[[4;35;1mEXECUTE (1.2ms)^[[0m   ^[[0mCOMMIT TRANSACTION^[[0m
 ^[[4;36;1mTerm Load (7.6ms)^[[0m   ^[[0;1m
select distinct terms.id, count(workshops.id) as workshop_count,
sum(workshops.time_spent)/3600 as term_time_spent, terms.semester, terms.year
from terms left join workshops on workshops.term_id=terms.id
and workshops.cancelled = 0
and workshops.visible = 1
and workshops.enabled = 1
where terms.id like '1'
group by terms.id, terms.semester, terms.year
order by terms.year

Unless I'm just not seeing it, it looks just like it did when we sent it in. I also threw my own logger in raw_select and saw that nothing had happened there.

If it helps we're using the exact versions for the dbi, etc that you specifyin the readme.

[metaskills]

Perpahs it is a DBI/DBD-ODBC issue. I doubt this is rails or the adapter related. Can you test what is returned with just the #raw_connection via your own statement handle? Also... what do you get back if you just use the select_rows.

[metaskills]

No activity, closing.

[ebryn]

I've just stumbled upon this issue myself. I've traced it down to Ruby-ODBC. Tried the query with sqsh and the results are correct, so that'd remove TDS/MSSQL from being the cause. I'll attempt to contact the Ruby-ODBC guy about it.

I'm using the latest version of Ruby-ODBC (0.9999) with SQL Server 2008.