Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP

Loading…

will_paginate creates malformed sql with empty result set on a specific query #198

Open
trcull opened this Issue · 0 comments

1 participant

@trcull

I haven't been able to figure out exactly why, but I've found that this code:

            rv = AssetPrice.
                includes([:currency, :pricing_source,{:asset_listing => :listing_ids}]).
                where(['AssetPrice.asOfDate = ? AND ListingId.listingIdSourceId = ? AND ListingId.externalId = ? ',as_of_date, listing_id_type, listing_id]).
                paginate(:page => page)

creates malformed SQL when the query returns zero rows, but does fine if the query returns more than zero rows. However, on the same database, tables and data, this code

        AssetPrice.
            includes([:currency, :pricing_source,{:asset_listing => :listing_ids}]).
            where(['AssetPrice.asOfDate = ?',as_of_date]).
            paginate(:page => page) 

Works just fine even if the results of the query are empty. In both cases, I am including the same associations, the only difference is that in one case I am searching on the associations and in the other case I am not.

Also, I've confirmed that the same query without paginating works fine. In other words, this code works fine even if it returns zero rows:

            rv = AssetPrice.
                includes([:currency, :pricing_source,{:asset_listing => :listing_ids}]).
                where(['AssetPrice.asOfDate = ? AND ListingId.listingIdSourceId = ? AND ListingId.externalId = ? ',as_of_date, listing_id_type, listing_id])

Here is the malformed SQL created (note the " ) AND ((NULL))" at the end, which is what's malformed):

  ←[1m←[36mAssetPrice Load (0.0ms)←[0m  ←[1mSELECT DISTINCT t.* FROM (SELECT ROW_NUMBER() OVER(ORDER BY [AssetPrice].ass
etListingId) AS _row_num, [AssetPrice].assetListingId, [AssetPrice].pricingSourceId, [AssetPrice].asOfDate FROM [AssetPr
ice] LEFT OUTER JOIN [currency] ON [currency].[id] = [AssetPrice].[currencyId] LEFT OUTER JOIN [PricingSource] ON [Prici
ngSource].[id] = [AssetPrice].[pricingSourceId] LEFT OUTER JOIN [AssetListing] ON [AssetListing].[id] = [AssetPrice].[as
setListingId] LEFT OUTER JOIN [ListingId] ON [ListingId].[assetListingId] = [AssetListing].[id] WHERE (AssetPrice.asOfDa
te = N'2011-12-01' AND ListingId.listingIdSourceId = N'6' AND ListingId.externalId = N'T' )) AS t WHERE t._row_num BETWE
EN 1 AND 30←[0m

...and ....

Msg 4145, Level 15, State 1, Line 1, Sqlstate 37000: SELECT [AssetPrice].[assetListingId] AS t0_r0, [AssetPrice].[pricin
gSourceId] AS t0_r1, [AssetPrice].[asOfDate] AS t0_r2, [AssetPrice].[bidPrc] AS t0_r3, [AssetPrice].[midPrc] AS t0_r4, [
AssetPrice].[askPrc] AS t0_r5, [AssetPrice].[openPrc] AS t0_r6, [AssetPrice].[highPrc] AS t0_r7, [AssetPrice].[lowPrc] A
S t0_r8, [AssetPrice].[lastPrc] AS t0_r9, [AssetPrice].[fixingPrc] AS t0_r10, [AssetPrice].[nasdaqClosePrc] AS t0_r11, [
AssetPrice].[roundLot] AS t0_r12, [AssetPrice].[tradeLot] AS t0_r13, [AssetPrice].[volume] AS t0_r14, [AssetPrice].[last
Update] AS t0_r15, [AssetPrice].[bloombergBeta] AS t0_r16, [AssetPrice].[currencyId] AS t0_r17, [AssetPrice].[marketCapi
talization] AS t0_r18, [AssetPrice].[sharesOutstanding] AS t0_r19, [AssetPrice].[sharesOutstandingFloat] AS t0_r20, [Ass
etPrice].[sharesOutstandingReal] AS t0_r21, [AssetPrice].[bloombergDividendIndicatedYieldGross] AS t0_r22, [AssetPrice].
[bloombergDividendYield12MonthGross] AS t0_r23, [AssetPrice].[bloombergDividendYield12MonthNet] AS t0_r24, [AssetPrice].
[totalReturn] AS t0_r25, [AssetPrice].[dailyReturn] AS t0_r26, [AssetPrice].[dailyNotionalVolume] AS t0_r27, [AssetPrice
].[nDayAverageNotionalVolume] AS t0_r28, [AssetPrice].[nDayAverageVolume] AS t0_r29, [currency].[id] AS t1_r0, [currency
].[isoCode] AS t1_r1, [currency].[galiamId] AS t1_r2, [currency].[name] AS t1_r3, [PricingSource].[id] AS t2_r0, [Pricin
gSource].[code] AS t2_r1, [PricingSource].[name] AS t2_r2, [PricingSource].[galiamId] AS t2_r3, [AssetListing].[id] AS t
3_r0, [AssetListing].[assetIssueId] AS t3_r1, [AssetListing].[assetListingTypeId] AS t3_r2, [AssetListing].[name] AS t3_
r3, [AssetListing].[exchangeId] AS t3_r4, [AssetListing].[primaryCurrencyId] AS t3_r5, [AssetListing].[galiamId] AS t3_r
6, [ListingId].[id] AS t4_r0, [ListingId].[assetListingId] AS t4_r1, [ListingId].[listingIdSourceId] AS t4_r2, [ListingI
d].[externalId] AS t4_r3 FROM [AssetPrice] LEFT OUTER JOIN [currency] ON [currency].[id] = [AssetPrice].[currencyId] LEF
T OUTER JOIN [PricingSource] ON [PricingSource].[id] = [AssetPrice].[pricingSourceId] LEFT OUTER JOIN [AssetListing] ON
[AssetListing].[id] = [AssetPrice].[assetListingId] LEFT OUTER JOIN [ListingId] ON [ListingId].[assetListingId] = [Asset
Listing].[id] WHERE (AssetPrice.asOfDate = N'2011-12-01' AND ListingId.listingIdSourceId = N'6' AND ListingId.externalId
 = N'T' ) AND ((NULL))

Here are all my versions and Gems:
Database: Microsoft SQLServer 2008

C:\dev\ibor-maint>jruby -v
jruby 1.6.4 (ruby-1.8.7-p330) (2011-08-23 17ea768) (Java HotSpot(TM) 64-Bit Server VM 1.7.0) [Windows 7-amd64-java]


C:\dev\ibor-maint>jruby -S bundle list
Gems included by the bundle:
  * actionmailer (3.1.0)
  * actionpack (3.1.0)
  * activemodel (3.1.0)
  * activerecord (3.1.0)
  * activerecord-jdbc-adapter (1.2.0)
  * activerecord-jdbcmssql-adapter (1.2.0)
  * activeresource (3.1.0)
  * activesupport (3.1.0)
  * arel (2.2.1)
  * authlogic (3.1.0)
  * bcrypt-ruby (3.0.1)
  * bouncy-castle-java (1.5.0146.1)
  * builder (3.0.0)
  * bundler (1.0.18)
  * coffee-rails (3.1.1)
  * coffee-script (2.2.0)
  * coffee-script-source (1.1.2)
  * composite_primary_keys (4.1.1)
  * diff-lcs (1.1.3)
  * erubis (2.7.0)
  * execjs (1.2.9)
  * hike (1.2.1)
  * i18n (0.6.0)
  * jdbc-jtds (1.2.5)
  * jquery-rails (1.0.14)
  * jruby-openssl (0.7.4)
  * mail (2.3.0)
  * mime-types (1.16)
  * multi_json (1.0.3)
  * polyglot (0.3.2)
  * rack (1.3.3)
  * rack-cache (1.0.3)
  * rack-mount (0.8.3)
  * rack-ssl (1.3.2)
  * rack-test (0.6.1)
  * rails (3.1.0)
  * rails3-generators (0.17.4)
  * railties (3.1.0)
  * rake (0.9.2)
  * rdoc (3.9.4)
  * rspec (2.6.0)
  * rspec-core (2.6.4)
  * rspec-expectations (2.6.0)
  * rspec-mocks (2.6.0)
  * rspec-rails (2.6.1)
  * sass (3.1.7)
  * sass-rails (3.1.2)
  * sprockets (2.0.0)
  * therubyrhino (1.72.8)
  * thor (0.14.6)
  * tilt (1.3.3)
  * treetop (1.4.10)
  * tzinfo (0.3.29)
  * uglifier (1.0.3)
  * will_paginate (3.0.pre2)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.