[Rails] ActiveRecord SQL Server Adapter Bug...
Stuart Holden
Stuart.Holden at baring-asset.com
Wed Jul 26 11:38:28 GMT 2006
I think I've managed to fix the column quoting issue by making three
small changes to the add_limit_offset! method in the
sqlserver_adapter.rb file:
def add_limit_offset!(sql, options)
if options[:limit] and options[:offset]
total_rows = @connection.select_all("SELECT count(*) as
TotalRows from (#{sql.gsub(/\bSELECT(\s+DISTINCT)?\b/i, "SELECT#{$1} TOP
1000000000")}) tally")[0][:TotalRows].to_i
if (options[:limit] + options[:offset]) >= total_rows
options[:limit] = (total_rows - options[:offset] >= 0) ?
(total_rows - options[:offset]) : 0
end
sql.sub!(/^\s*SELECT(\s+DISTINCT)?/i, "SELECT * FROM (SELECT
TOP #{options[:limit]} * FROM (SELECT#{$1} TOP #{options[:limit] +
options[:offset]} ")
sql << ") AS tmp1"
if options[:order]
options[:order] = options[:order].split(',').map do |field|
parts = field.split(" ")
tc = parts[0]
if sql =~ /\.\[/ and tc =~ /\./ # if column quoting used
in query
####-- ORIGINAL : tc.gsub!(/\./, '\\.\\[')
####-- ORIGINAL : tc << '\\]'
tc.gsub!(/\./, '.[') #NEW: -- do not escape
column quotes
tc << ']' #NEW: -- do not
escape column quotes
end
if sql =~ /#{tc} AS (t\d_r\d\d?)/
parts[0] = $1
####-- ORIGINAL : elsif parts[0] =~ /\w+\.(\w+)/
elsif parts[0] =~ /\w+\.([\[\]\w]+)/
#NEW: ensure that column quotes are picked up when matching to strip the
tablename
parts[0] = $1
end
parts.join(' ')
end.join(', ')
sql << " ORDER BY
#{change_order_direction(options[:order])}) AS tmp2 ORDER BY
#{options[:order]}"
else
sql << " ) AS tmp2"
end
elsif sql !~ /^\s*SELECT (@@|COUNT\()/i
sql.sub!(/^\s*SELECT(\s+DISTINCT)?/i) do
"SELECT#{$1} TOP #{options[:limit]}"
end unless options[:limit].nil?
end
end
This has fixed the column quoting issue, but there are still some
problems with the SQL that ActiveRecord is generating. The original
issue of selecting by offset now works...
Article.find( :all,
:offset => 0,
:limit => 1,
:order => "contents.published_at DESC",
:conditions => ['published = ? AND contents.published_at <
? AND blog_id = ?',true, Time.now, 1])
[#<Article:0x32acb34 @attributes={... (snip)
And selecting with the :include tag works...
Article.find( :all,
:order => "contents.published_at DESC",
:include => [:categories, :tags, :user, :blog],
:conditions => ['published = ? AND contents.published_at <
? AND blog_id = ?',true, Time.now, 1])
[#<Article:0x3848110 @user=#<User:0x38476fc @attributes={...
(snip)
But a find that combines both the :offset and :include will fail due to
bad SQL...
Article.find( :all,
:offset => 0,
:limit => 1,
:order => "contents.published_at DESC",
:include => [:categories, :tags, :user, :blog],
:conditions => ['published = ? AND contents.published_at < ? AND
blog_id = ?',true, Time.now, 1])
ActiveRecord::StatementInvalid: DBI::DatabaseError: S0022 (207)
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name
'published_at'.: SELECT * FROM (SELECT TOP 1 * FROM (SELECT TOP 1 id
FROM contents WHERE (published = 1 AND contents.published_at <
'2006-07-26 12:24:01' AND blog_id = 1) AND ( (contents.[type] =
'Article' ) ) ORDER BY contents.published_at DESC ) AS tmp1 ORDER BY
[published_at] ASC) AS tmp2 ORDER BY [published_at] DESC
from
./script/../config/../config/../vendor/rails/activerecord/lib/active_rec
ord/connection_adapters/abstract_adapter.rb:120:in `log'
from
./script/../config/../config/../vendor/rails/activerecord/lib/active_rec
ord/connection_adapters/sqlserver_adapter.rb:521:in `select'
from
./script/../config/../config/../vendor/rails/activerecord/lib/active_rec
ord/connection_adapters/sqlserver_adapter.rb:257:in `select_all'
from
./script/../config/../config/../vendor/rails/activerecord/lib/active_rec
ord/associations.rb:1166:in `select_limited_ids_list'
from
./script/../config/../config/../vendor/rails/activerecord/lib/active_rec
ord/associations.rb:1158:in `add_limited_ids_condition!'
from
./script/../config/../config/../vendor/rails/activerecord/lib/active_rec
ord/associations.rb:1148:in
`construct_finder_sql_with_included_associations'
from
./script/../config/../config/../vendor/rails/activerecord/lib/active_rec
ord/associations.rb:1111:in `select_all_rows'
from
./script/../config/../config/../vendor/rails/activerecord/lib/active_rec
ord/associations.rb:975:in `find_with_associations'
from
./script/../config/../config/../vendor/rails/activerecord/lib/active_rec
ord/associations.rb:973:in `find_with_associations'
from
./script/../config/../config/../vendor/rails/activerecord/lib/active_rec
ord/base.rb:923:in `find_every'
from
./script/../config/../config/../vendor/rails/activerecord/lib/active_rec
ord/base.rb:381:in `find'
from (irb):104
from :0
This one is caused by just selecting the [id] in the inner select, and
not exposing the the [published_at] column to be sorted on in the outer
selects. Sadly I haven't been able to get anywhere with debugging this
one. Also, please note that this is being run against the Typo 4
database, and the test are run via the script/console util. The issues
do also occur through the web frontend.
Regards,
Stu
-----Original Message-----
From: rails-bounces at lists.rubyonrails.org
[mailto:rails-bounces at lists.rubyonrails.org] On Behalf Of Tom Ward
Sent: 25 July 2006 16:18
To: rails at lists.rubyonrails.org
Subject: Re: [Rails] ActiveRecord SQL Server Adapter Bug...
Interesting. I'll take a look this evening. Please let me know if
you find out any more.
Tom
On 25/07/06, Stuart Holden <Stuart.Holden at baring-asset.com> wrote:
> Hi,
>
>
> I've tried creating a new ticket for this issue, but I'm just getting
> 500 server errors.
>
> There appears to be a problem with the ActiveRecord SqlServer adapter
> that results in illegal SQL being generated...
>
>
> ActiveRecord::StatementInvalid in ArticlesController#index
> DBI::DatabaseError: 37000 (170) [Microsoft][ODBC SQL Server
Driver][SQL
> Server]Line 1: Incorrect syntax near '\'.: SELECT * FROM (SELECT TOP
1
> *
> FROM (SELECT TOP 1 id FROM contents WHERE (published = 1 AND
> contents.published_at < '2006-07-25 11:59:31' AND blog_id = 1) AND (
> (contents.[type] = 'Article' ) ) ORDER BY contents.published_at DESC
)
> AS
> tmp1 ORDER BY contents\.\[published_at\] ASC) AS tmp2 ORDER BY
> contents\.\[published_at\] DESC
>
> This is due to messed up column quoting. I've tracked it down to the
> 'add_limit_offset!' method in sqlserver_adapter.rb, but I can't
decipher
> enough of what is going on to suggest the fix just yet.
>
> Full stack trace also included below.
>
> Regards,
> Stu
>
> RAILS_ROOT: ./script/../config/..
> Application Trace | Framework Trace | Full Trace
>
>
#{RAILS_ROOT}/vendor/rails/activerecord/lib/active_record/connection_ada
> pters/abstract_adapter.rb:120:in
> `log'
>
>
#{RAILS_ROOT}/vendor/rails/activerecord/lib/active_record/connection_ada
> pters/sqlserver_adapter.rb:485:in
> `select'
>
>
#{RAILS_ROOT}/vendor/rails/activerecord/lib/active_record/connection_ada
> pters/sqlserver_adapter.rb:233:in
> `select_all'
>
>
#{RAILS_ROOT}/vendor/rails/activerecord/lib/active_record/associations.r
> b:1166:in
> `select_limited_ids_list'
>
>
#{RAILS_ROOT}/vendor/rails/activerecord/lib/active_record/associations.r
> b:1158:in
> `add_limited_ids_condition!'
>
>
#{RAILS_ROOT}/vendor/rails/activerecord/lib/active_record/associations.r
> b:1148:in
> `construct_finder_sql_with_included_associations'
>
>
#{RAILS_ROOT}/vendor/rails/activerecord/lib/active_record/associations.r
> b:1111:in
> `select_all_rows'
>
>
#{RAILS_ROOT}/vendor/rails/activerecord/lib/active_record/associations.r
> b:975:in
> `find_with_associations'
>
>
#{RAILS_ROOT}/vendor/rails/activerecord/lib/active_record/associations.r
> b:973:in
> `find_with_associations'
>
>
#{RAILS_ROOT}/vendor/rails/activerecord/lib/active_record/base.rb:923:in
> `find_every'
>
>
#{RAILS_ROOT}/vendor/rails/activerecord/lib/active_record/base.rb:381:in
> `find'
> #{RAILS_ROOT}/app/controllers/articles_controller.rb:27:in `index'
>
>
#{RAILS_ROOT}/vendor/rails/actionpack/lib/action_controller/base.rb:910:
> in
> `perform_action_without_filters'
>
>
#{RAILS_ROOT}/vendor/rails/actionpack/lib/action_controller/filters.rb:3
> 68:in
> `perform_action_without_benchmark'
>
>
#{RAILS_ROOT}/vendor/rails/actionpack/lib/action_controller/benchmarking
> .rb:69:in
> `perform_action_without_rescue'
> c:/ruby/lib/ruby/1.8/benchmark.rb:293:in `measure'
>
>
#{RAILS_ROOT}/vendor/rails/actionpack/lib/action_controller/benchmarking
> .rb:69:in
> `perform_action_without_rescue'
>
>
#{RAILS_ROOT}/vendor/rails/actionpack/lib/action_controller/rescue.rb:82
> :in
> `perform_action'
>
>
#{RAILS_ROOT}/vendor/rails/actionpack/lib/action_controller/base.rb:381:
> in
> `process_without_filters'
>
>
#{RAILS_ROOT}/vendor/rails/actionpack/lib/action_controller/filters.rb:3
> 77:in
> `process_without_session_management_support'
>
>
#{RAILS_ROOT}/vendor/rails/actionpack/lib/action_controller/session_mana
> gement.rb:117:in
> `process'
> #{RAILS_ROOT}/vendor/rails/railties/lib/dispatcher.rb:38:in
`dispatch'
> #{RAILS_ROOT}/vendor/rails/railties/lib/webrick_server.rb:115:in
> `handle_dispatch'
> #{RAILS_ROOT}/vendor/rails/railties/lib/webrick_server.rb:81:in
> `service'
> c:/ruby/lib/ruby/1.8/webrick/httpserver.rb:104:in `service'
> c:/ruby/lib/ruby/1.8/webrick/httpserver.rb:65:in `run'
> c:/ruby/lib/ruby/1.8/webrick/server.rb:173:in `start_thread'
> c:/ruby/lib/ruby/1.8/webrick/server.rb:162:in `start_thread'
> c:/ruby/lib/ruby/1.8/webrick/server.rb:95:in `start'
> c:/ruby/lib/ruby/1.8/webrick/server.rb:92:in `start'
> c:/ruby/lib/ruby/1.8/webrick/server.rb:23:in `start'
> c:/ruby/lib/ruby/1.8/webrick/server.rb:82:in `start'
> #{RAILS_ROOT}/vendor/rails/railties/lib/webrick_server.rb:67:in
> `dispatch'
>
#{RAILS_ROOT}/vendor/rails/railties/lib/commands/servers/webrick.rb:59
> c:/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:27:in
> `require'
>
>
#{RAILS_ROOT}/vendor/rails/activesupport/lib/active_support/dependencies
> .rb:147:in
> `require'
> #{RAILS_ROOT}/vendor/rails/railties/lib/commands/server.rb:30
> c:/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:27:in
> `require'
> script/server:3
> Request
>
> Parameters: None
>
> Show session dump
> ---
> Response
> Headers: {"cookie"=>[], "Cache-Control"=>"no-cache"}
>
> --
> Ticket URL: <http://www.typosphere.org/trac/ticket/1040>
> typo <http://typosphere.org/trac/>
> web2.0 weblogging engine
>
>
> <body>
>
> <blockquote>
> <font FACE="Arial,Arial" SIZE="1"><p ALIGN="JUSTIFY">This Email may
contain confidential and privileged information and is intended for the
use of the addressee(s) only. If you are not the intended recipient
please notify the sender and delete the Email from your system. It
should not be transmitted to any other person without the consent of the
sender. Additional important notifications regarding Email transmissions
from and to members of Baring Asset Management can be accessed at <a
href="http://www.barings.com/email/index.hcst">http://www.barings.com/em
ail/index.hcst</a></font></p></body>
> </p>
> </font>
> </blockquote>
>
> <p> </p>
> </body>
> </html>
>
> _______________________________________________
> Rails mailing list
> Rails at lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
_______________________________________________
Rails mailing list
Rails at lists.rubyonrails.org
http://lists.rubyonrails.org/mailman/listinfo/rails
<body>
<blockquote>
<font FACE="Arial,Arial" SIZE="1"><p ALIGN="JUSTIFY">This Email may contain confidential and privileged information and is intended for the use of the addressee(s) only. If you are not the intended recipient please notify the sender and delete the Email from your system. It should not be transmitted to any other person without the consent of the sender. Additional important notifications regarding Email transmissions from and to members of Baring Asset Management can be accessed at <a href="http://www.barings.com/email/index.hcst">http://www.barings.com/email/index.hcst</a></font></p></body>
</p>
</font>
</blockquote>
<p> </p>
</body>
</html>
More information about the Rails
mailing list