New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Issue with product paging - total number match but some product missing while others duplicate in different page #2851

Closed
colokoala opened this Issue Apr 9, 2013 · 13 comments

Comments

Projects
None yet
3 participants
@colokoala

colokoala commented Apr 9, 2013

I have multiple taxonomies, one of which only have a root taxon with the same name as the taxonomy.
Under that taxon, I added around 40 products a few days ago and the paging worked correctly in store page under that taxon.
Today I created a few new products and put under that taxon, using exactly the same way I did before.
Now in the store page under that taxon, the first page shows the products sorted with the added date (and ID in db),
but in the second page, some products from the first page are also showing.
The number of products in all the pages match the number under that taxon.
That means some products are duplicated and some products are missing in the pages.

Looking into the log, for different page with same taxon, the difference in db query is only the "OFFSET" value.
For page 1 it's 0, for page 2 it's 12, for page 3 it's 24, etc.

SELECT DISTINCT(spree_products.id), spree_products.*
FROM "spree_products" INNER JOIN "spree_products_taxons" ON "spree_products_taxons"."product_id" = "spree_products"."id" 
    INNER JOIN "spree_taxons" ON "spree_taxons"."id" = "spree_products_taxons"."taxon_id"
WHERE "spree_products"."deleted_at" IS NULL
    AND "spree_taxons"."id" IN (34)
    AND ("spree_products".available_on <= '2013-04-06 07:31:55.034422')
LIMIT 12 OFFSET 0

If I run the query directly against the DB, here is the result (showing product ids only):

Page 1 (Offset 0):
100
101
102
103
104
105
106
107
108
109
110
111
Page 2 (Offset 12)
149
120
117
102
144
100
108
125
105
101
111
104

See the result in page 2 are not sorted in product id and there are 7 out of 12 products from page 1 !

My gemfile:

source 'https://rubygems.org'
ruby '1.9.3'

gem 'rails', '3.2.11'

# Bundle edge Rails instead:
# gem 'rails', :git => 'git://github.com/rails/rails.git'

gem 'pg'

# Gems used only for assets and not required
# in production environments by default.
group :assets do
  gem 'sass-rails',   '~> 3.2.3'
  gem 'coffee-rails', '~> 3.2.1'

  # See https://github.com/sstephenson/execjs#readme for more supported runtimes
  # gem 'therubyracer', :platforms => :ruby

  gem 'uglifier', '>= 1.0.3'
end

gem 'jquery-rails'

# To use ActiveModel has_secure_password
# gem 'bcrypt-ruby', '~> 3.0.0'

# To use Jbuilder templates for JSON
# gem 'jbuilder'

# Use unicorn as the app server
# gem 'unicorn'

# Deploy with Capistrano
# gem 'capistrano'

# To use debugger
# gem 'debugger'

# Using spree
gem 'spree', '1.2.4'
gem 'spree_auth_devise', :github => 'spree/spree_auth_devise', :branch => '1-2-stable'

gem 'spree_static_content', :git => 'git://github.com/spree/spree_static_content.git', :branch => '1-2-stable'

gem 'spree_gateway', :git => 'git://github.com/spree/spree_gateway.git', :branch => "1-1-stable" # make sure to include after spree
gem 'spree_paypal_express', :git => 'git://github.com/spree/spree_paypal_express.git', :branch => "1-2-stable"

gem "spree_product_zoom", :git => "git://github.com/spree/spree_product_zoom.git"

gem 'thin'

Log: https://gist.github.com/radar/3f4d46060369173bf591

Thanks.

@radar

This comment has been minimized.

Show comment
Hide comment
@radar

radar Apr 9, 2013

Member

Hey @colokoala, please remember to format your issues on GitHub so they look neat. Use triple backticks to group code-y stuff together.

Member

radar commented Apr 9, 2013

Hey @colokoala, please remember to format your issues on GitHub so they look neat. Use triple backticks to group code-y stuff together.

@radar

This comment has been minimized.

Show comment
Hide comment
@radar

radar Apr 9, 2013

Member

Do you also get this problem on spree 1-2-stable?

Member

radar commented Apr 9, 2013

Do you also get this problem on spree 1-2-stable?

@colokoala

This comment has been minimized.

Show comment
Hide comment
@colokoala

colokoala Apr 9, 2013

I changed gemfile to

gem 'rails', '3.2.13'
gem 'spree', :git => 'git://github.com/spree/spree.git', :branch => '1-2-stable'

And I still have the issue in the UI.
The DB request is identical to what I had also.

colokoala commented Apr 9, 2013

I changed gemfile to

gem 'rails', '3.2.13'
gem 'spree', :git => 'git://github.com/spree/spree.git', :branch => '1-2-stable'

And I still have the issue in the UI.
The DB request is identical to what I had also.

@radar

This comment has been minimized.

Show comment
Hide comment
@radar

radar Apr 29, 2013

Member

Hey @colokoala, is there an easy set of steps that I can follow to reproduce this issue on a brand new spree store? That'd help me a lot in being able to quickly solve this issue.

Member

radar commented Apr 29, 2013

Hey @colokoala, is there an easy set of steps that I can follow to reproduce this issue on a brand new spree store? That'd help me a lot in being able to quickly solve this issue.

@colokoala

This comment has been minimized.

Show comment
Hide comment
@colokoala

colokoala Apr 30, 2013

I really don't know.
Sorry for that.

On 2013-4-29, at 16:46, "Ryan Bigg" notifications@github.com wrote:

Hey @colokoala, is there an easy set of steps that I can follow to reproduce this issue on a brand new spree store? That'd help me a lot in being able to quickly solve this issue.


Reply to this email directly or view it on GitHub.

colokoala commented Apr 30, 2013

I really don't know.
Sorry for that.

On 2013-4-29, at 16:46, "Ryan Bigg" notifications@github.com wrote:

Hey @colokoala, is there an easy set of steps that I can follow to reproduce this issue on a brand new spree store? That'd help me a lot in being able to quickly solve this issue.


Reply to this email directly or view it on GitHub.

@radar

This comment has been minimized.

Show comment
Hide comment
@radar

radar May 1, 2013

Member

@colokoala Absolutely certain that you can't provide those steps? If I can't investigate this issue quickly then I will move on to other issues that I know I can solve quickly.

Member

radar commented May 1, 2013

@colokoala Absolutely certain that you can't provide those steps? If I can't investigate this issue quickly then I will move on to other issues that I know I can solve quickly.

@colokoala

This comment has been minimized.

Show comment
Hide comment
@colokoala

colokoala May 1, 2013

Hi£¬Ryan£¬I really dont know what made this happen. But as I said I can get the same products when i run the query directly against DB. So seems to me it's not a spree issue but a DB state issue. Maybe I can send you my DB data to your private email£¿

On 2013-4-30, at 17:37, "Ryan Bigg" notifications@github.com wrote:

@colokoala Absolutely certain that you can't provide those steps? If I can't investigate this issue quickly then I will move on to other issues that I know I can solve quickly.

¡ª
Reply to this email directly or view it on GitHub.

colokoala commented May 1, 2013

Hi£¬Ryan£¬I really dont know what made this happen. But as I said I can get the same products when i run the query directly against DB. So seems to me it's not a spree issue but a DB state issue. Maybe I can send you my DB data to your private email£¿

On 2013-4-30, at 17:37, "Ryan Bigg" notifications@github.com wrote:

@colokoala Absolutely certain that you can't provide those steps? If I can't investigate this issue quickly then I will move on to other issues that I know I can solve quickly.

¡ª
Reply to this email directly or view it on GitHub.

@radar

This comment has been minimized.

Show comment
Hide comment
@radar

radar May 1, 2013

Member

Yes, DB data to my private email (ryan@spreecommerce.com) in an SQL format would mean then that I could insert that into my database and probably see the same issue you are seeing. If you could do that, that would be great.

Member

radar commented May 1, 2013

Yes, DB data to my private email (ryan@spreecommerce.com) in an SQL format would mean then that I could insert that into my database and probably see the same issue you are seeing. If you could do that, that would be great.

@radar

This comment has been minimized.

Show comment
Hide comment
@radar

radar May 6, 2013

Member

Thanks for sending that through. I have been able to replicate this problem on my local machine today. I will look into why this is happening.

Member

radar commented May 6, 2013

Thanks for sending that through. I have been able to replicate this problem on my local machine today. I will look into why this is happening.

@radar

This comment has been minimized.

Show comment
Hide comment
@radar

radar May 6, 2013

Member

Hey @colokoala, could you please try my 1-2-stable branch?

gem 'spree', :github => "radar/spree", :branch => "1-2-stable"

It looks to me like I've fixed it there and just want you to confirm for me.

Member

radar commented May 6, 2013

Hey @colokoala, could you please try my 1-2-stable branch?

gem 'spree', :github => "radar/spree", :branch => "1-2-stable"

It looks to me like I've fixed it there and just want you to confirm for me.

radar added a commit that referenced this issue May 6, 2013

@radar radar closed this in 89cb914 May 7, 2013

@colokoala

This comment has been minimized.

Show comment
Hide comment
@colokoala

colokoala May 8, 2013

Hi, Ryan,I couldn't verify directly use my app due to dependency problem.But from the query result it seems the issue is fixed.Can you tell me how and when I can get the fix using official git.I was using 1.2.4 and don't want to jump too much.Thanks a lot.
ColoKoala

Date: Mon, 6 May 2013 00:19:13 -0700
From: notifications@github.com
To: spree@noreply.github.com
CC: colokoala@hotmail.com
Subject: Re: [spree] Issue with product paging - total number match but some product missing while others duplicate in different page (#2851)

Hey @colokoala, could you please try my 1-2-stable branch?

gem 'spree', :github => "radar/spree", :branch => "1-2-stable"

It looks to me like I've fixed it there and just want you to confirm for me.


Reply to this email directly or view it on GitHub.

colokoala commented May 8, 2013

Hi, Ryan,I couldn't verify directly use my app due to dependency problem.But from the query result it seems the issue is fixed.Can you tell me how and when I can get the fix using official git.I was using 1.2.4 and don't want to jump too much.Thanks a lot.
ColoKoala

Date: Mon, 6 May 2013 00:19:13 -0700
From: notifications@github.com
To: spree@noreply.github.com
CC: colokoala@hotmail.com
Subject: Re: [spree] Issue with product paging - total number match but some product missing while others duplicate in different page (#2851)

Hey @colokoala, could you please try my 1-2-stable branch?

gem 'spree', :github => "radar/spree", :branch => "1-2-stable"

It looks to me like I've fixed it there and just want you to confirm for me.


Reply to this email directly or view it on GitHub.

radar added a commit that referenced this issue May 8, 2013

Use DISTINCT ON to make in_taxon scope return truly distinct products…
… in PostgreSQL

Fixes #2851

Conflicts:
	core/app/models/spree/product/scopes.rb
@radar

This comment has been minimized.

Show comment
Hide comment
@radar

radar May 12, 2013

Member

The fix should be available on the current git right now.

On Wed, May 8, 2013 at 4:34 PM, colokoala notifications@github.com
wrote:

Hi, Ryan,I couldn't verify directly use my app due to dependency problem.But from the query result it seems the issue is fixed.Can you tell me how and when I can get the fix using official git.I was using 1.2.4 and don't want to jump too much.Thanks a lot.
ColoKoala
Date: Mon, 6 May 2013 00:19:13 -0700
From: notifications@github.com
To: spree@noreply.github.com
CC: colokoala@hotmail.com
Subject: Re: [spree] Issue with product paging - total number match but some product missing while others duplicate in different page (#2851)
Hey @colokoala, could you please try my 1-2-stable branch?
gem 'spree', :github => "radar/spree", :branch => "1-2-stable"
It looks to me like I've fixed it there and just want you to confirm for me.

Reply to this email directly or view it on GitHub.

Reply to this email directly or view it on GitHub:
#2851 (comment)

Member

radar commented May 12, 2013

The fix should be available on the current git right now.

On Wed, May 8, 2013 at 4:34 PM, colokoala notifications@github.com
wrote:

Hi, Ryan,I couldn't verify directly use my app due to dependency problem.But from the query result it seems the issue is fixed.Can you tell me how and when I can get the fix using official git.I was using 1.2.4 and don't want to jump too much.Thanks a lot.
ColoKoala
Date: Mon, 6 May 2013 00:19:13 -0700
From: notifications@github.com
To: spree@noreply.github.com
CC: colokoala@hotmail.com
Subject: Re: [spree] Issue with product paging - total number match but some product missing while others duplicate in different page (#2851)
Hey @colokoala, could you please try my 1-2-stable branch?
gem 'spree', :github => "radar/spree", :branch => "1-2-stable"
It looks to me like I've fixed it there and just want you to confirm for me.

Reply to this email directly or view it on GitHub.

Reply to this email directly or view it on GitHub:
#2851 (comment)

@cbetti

This comment has been minimized.

Show comment
Hide comment
@cbetti

cbetti Aug 7, 2013

Not a spree user, but isn't this issue due to the fact that SQL doesn't guarantee consistent ordering of results? According to this: http://www.postgresql.org/docs/9.2/static/queries-limit.html

You will get this issue unless ORDER BY is used. Does DISTINCT ON fix this or hide the issue?

cbetti commented Aug 7, 2013

Not a spree user, but isn't this issue due to the fact that SQL doesn't guarantee consistent ordering of results? According to this: http://www.postgresql.org/docs/9.2/static/queries-limit.html

You will get this issue unless ORDER BY is used. Does DISTINCT ON fix this or hide the issue?

radar added a commit that referenced this issue Sep 25, 2013

Fix product paging issue
Fixes #2851
Fixes #3064

Conflicts:
	frontend/app/controllers/spree/taxons_controller.rb

radar added a commit that referenced this issue Sep 25, 2013

Fix product paging issue
Fixes #2851
Fixes #3064

Conflicts:
	frontend/app/controllers/spree/taxons_controller.rb

radar added a commit that referenced this issue Sep 25, 2013

paul-at added a commit to paul-at/spree_product_sort that referenced this issue Oct 27, 2013

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment