This is an obscure error that I encountered today and about which I found very little information. In my particular case (see my previous post regarding the Spider Database UI), I encountered this problem as soon as I added a feature by which selected records of the current user table could be used to qualify the records shown for the parent and children relationships.
What this means is that, previously, I was simply showing all records of the parent and child relationships, which was being loaded in the index function of the table viewer controller:
@parent_dataset = load_fk_tables(@parent_tables)
@child_dataset = load_fk_tables(@child_tables)
Now however, I was qualifying the parent and child tables based on the (optional) selected records of the selected user table:
@parent_dataset = load_fk_tables(@parent_tables, self.parent_qualifiers)
@child_dataset = load_fk_tables(@child_tables, self.child_qualifiers)
The error in the subject line of this post began occurring at this point. Interestingly, as soon as I forced the record to load through any means, such as:
records = DynamicTable.where(where).paginate(page: page, per_page: items_per_page)
or:
d = records[0]
or:
records.each do |record| q=5 end
The problem went away. I am actually completely at a loss to explain why lazy-loading is the issue — digging into the will_paginate and active_record source code, I didn’t find anything that would particularly lead me to understanding the issue. However, I do know that forcing the load solved the problem. Possible explanations:
- This may simply have to do with having more than one set of tables set up for lazy loading. I notice this problem occurs only when I have multiple tables in the “lazy load” state being referenced by the view.
- This may be related to some interaction with will_paginate, as it seems that the offending query deals with selecting rows within a page range and also involves hidden fields (like ‘__rn’) that will_paginate adds to the query.
- It also has something to do with TinyTds. There are some interesting posts about this error and TinyTds, but they’re not related, as far as I can tell because there are no un-aliased fields. None-the-less, because TinyTDS is emitting the error, it is clearly suspect somewhere in the process.
Regarding number 3, here’s the query it’s trying to execute:
SELECT TOP (7) [__rnt].* FROM ( SELECT ROW_NUMBER() OVER (ORDER BY [Person].[StateProvince].[StateProvinceID] ASC) AS [__rn], [Person].[BusinessEntityAddress].* FROM [Person].[StateProvince] ) AS [__rnt] WHERE [__rnt].[__rn] > (0) ORDER BY [__rnt].[__rn] ASC'
You will note that __rnt is definitely not defined! So somewhere in the lazy load process, __rnt gets lost.
Hopefully this post will provide a clue to others that encounter this bizarre problem.
Marc Clifton 