#187 ✓resolved
Jan Berkel

count SQL invalid on Oracle

Reported by Jan Berkel | February 12th, 2008 @ 11:24 AM

select count(*) ... AS count_table is not valid SQL in oracle and will fail with error ORA-00933.

Comments and changes to this ticket

  • Chris Wanstrath

    Chris Wanstrath February 16th, 2008 @ 12:29 PM

    • State changed from “new” to “open”
    • Assigned user changed from “Chris Wanstrath” to “Mislav”
  • Mislav

    Mislav February 23rd, 2008 @ 01:53 PM

    • Title changed from “select ... AS count_table doesn't work on oracle” to “count SQL invalid on Oracle”

    Hm... that query is generated by ActiveRecord count method. That means the adapter for Oracle might be broken?

    Does every pagination call fail? Can you paste me a simple example where this error occurs (the call to paginating finder and generated SQL for both select and count)?


  • SideBurns

    SideBurns February 27th, 2008 @ 12:43 AM

    I am having the same problem. Here is what I am doing in my controller:

    @resumes = EmResume.paginate_by_sql ['select * from em_resumes where is_active = ?',1], :page=>1

    The error I get is:

    SQL command not properly ended: SELECT COUNT(*) FROM (select * from em_resumes where is_active = 1) AS count_table

    If I use:

    @resumes = EmInternetResume.paginate :page=>1, :order=>'created_on desc'

    then it works fine.

    I will be using more complex queries that require sql.

  • Jan Berkel

    Jan Berkel February 27th, 2008 @ 12:43 PM

    try applying the patch i posted, it should work on both oracle and mysql.

    my calling code looks like this

    LoggingEvent.paginate_by_sql(["select * from logging_events where stack_trace like ? or message like ?", "%#{search_for}%", "%#{search_for}%"], :include=>'log_context', :page => params[:page], :per_page => settings.items_per_page, :order => 'timestamp asc, logging_events.id asc')

    as i said above, "AS count_table" is not valid oracle sql (which gets appended by the plugin)

  • Mislav

    Mislav February 27th, 2008 @ 01:59 PM

    • State changed from “open” to “resolved”

    oh, so this is a problem with count query generated in paginate_by_sql ...

    Well, that was bound to fail in certain adapters. The real solution is to construct your own count query, because it's very hard to do that automatically (would require parsing the original select query).

    class LoggingEvent
      def self.paginate_events(search_for, page, per_page = nil)
        search_query = ["select * from #{table_name} where stack_trace like ?", "%#{search_for}%"]
        count_query = search_query.dup
        # change the original query to count
        count_query[0] = count_query[0].sub('*', 'COUNT(*)')
        # add ORDER to original select query
        search_query.first << " ORDER BY timestamp asc"
        total_entries = count_by_sql(count_query)
        paginate_by_sql(search_query, :page => page, :per_page => per_page, :total_entries => total_entries)

    But, Jan, I don't think you really need paginate_by_sql here. By what you pasted it's obvious that only a `paginate` call with :conditions is sufficient.

  • Jan Berkel

    Jan Berkel February 27th, 2008 @ 05:29 PM

    ok, changed my code it works now... works fine the way you suggested, sorry about that.

  • SideBurns

    SideBurns February 27th, 2008 @ 08:07 PM

    I actually used the patch and it works great. I think it would be good to include the patch in future editions of will_paginate. It makes the code much simplier and cleaner.

  • Mislav

    Mislav February 27th, 2008 @ 09:36 PM

    Applied because it's not expensive.

    Note that manually-written count query will probably always perform better, though.

  • Mislav

    Mislav September 27th, 2011 @ 03:17 PM

    • Tag set to count, oracle, sql, will_paginate
    • Milestone order changed from “0” to “0”

Please Sign in or create a free account to add a new ticket.

With your very own profile, you can contribute to projects, track your activity, watch tickets, receive and update tickets through your email and much more.

New-ticket Create new ticket

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile ยป

Everyone's favorite Ruby library for pagination of practically anything!

People watching this ticket


Referenced by