Defect #19252

2 IssuesController tests fail with SQLite3 database

Added by Björn Peemöller about 5 years ago. Updated over 4 years ago.

Status:ClosedStart date:
Priority:NormalDue date:
Assignee:-% Done:

0%

Category:Database
Target version:-
Resolution: Affected version:3.0.0

Description

Hi, running the test cases of Redmine 3.0.0 using an SQLite3 backend I encountered the following two errors:

$ ruby test/functional/issues_controller_test.rb -n test_index_with_query_grouped_by_tracker_in_reverse_order
Run options: -n test_index_with_query_grouped_by_tracker_in_reverse_order --seed 925

# Running:

F

Finished in 2.918891s, 0.3426 runs/s, 0.6852 assertions/s.

  1) Failure:
IssuesControllerTest#test_index_with_query_grouped_by_tracker_in_reverse_order [test/functional/issues_controller_test.rb:346]:
Expected: [3, 2, 1]
  Actual: [2, 3, 1]

1 runs, 2 assertions, 1 failures, 0 errors, 0 skips

and

$ ruby test/functional/issues_controller_test.rb -n test_index_with_query_grouped_by_tracker_in_normal_order
Run options: -n test_index_with_query_grouped_by_tracker_in_normal_order --seed 7058

# Running:

F

Finished in 2.245884s, 0.4453 runs/s, 0.8905 assertions/s.

  1) Failure:
IssuesControllerTest#test_index_with_query_grouped_by_tracker_in_normal_order [test/functional/issues_controller_test.rb:336]:
Expected: [1, 2, 3]
  Actual: [1, 3, 2]

1 runs, 2 assertions, 1 failures, 0 errors, 0 skips

I'm using sqlite3 (1.3.10) and Ruby 2.0.0-p598. Please ask for more information if you need some.

Associated revisions

Revision 14175
Added by Toshi MARUYAMA almost 5 years ago

Gemfile: remove activerecord-jdbcsqlite3-adapter version and pin jdbc-sqlite3 version < 3.8 (#19252)

r13829 comment "test/functional/issues_controller_test.rb failure" is due to sqlite3 3.8.

Revision 14176
Added by Toshi MARUYAMA almost 5 years ago

Merged r14175 from trunk to 3.0-stable (#19252)

Gemfile: remove activerecord-jdbcsqlite3-adapter version and pin jdbc-sqlite3 version < 3.8.

r13829 comment "test/functional/issues_controller_test.rb failure" is due to sqlite3 3.8.

Revision 14177
Added by Toshi MARUYAMA almost 5 years ago

Merged r14175 from trunk to 2.6-stable (#19252)

Gemfile: remove activerecord-jdbcsqlite3-adapter version and pin jdbc-sqlite3 version < 3.8.

r13829 comment "test/functional/issues_controller_test.rb failure" is due to sqlite3 3.8.

Revision 14329
Added by Toshi MARUYAMA almost 5 years ago

Gemfile: use jdbc-sqlite3 version >= 3.8.10.1 (#19252)

sqlite-jdbc was updated to 3.8.10.1 in upstream.
https://github.com/jruby/activerecord-jdbc-adapter/commit/f7a20b0d577fad569d6f375d3b8f1e63dd02266b

Revision 14330
Added by Toshi MARUYAMA almost 5 years ago

Merged r14329 from trunk to 3.0-stable (#19252)

Gemfile: use jdbc-sqlite3 version >= 3.8.10.1.

sqlite-jdbc was updated to 3.8.10.1 in upstream.
https://github.com/jruby/activerecord-jdbc-adapter/commit/f7a20b0d577fad569d6f375d3b8f1e63dd02266b

Revision 14331
Added by Toshi MARUYAMA almost 5 years ago

Merged r14329 from trunk to 2.6-stable (#19252)

Gemfile: use jdbc-sqlite3 version >= 3.8.10.1.

sqlite-jdbc was updated to 3.8.10.1 in upstream.
https://github.com/jruby/activerecord-jdbc-adapter/commit/f7a20b0d577fad569d6f375d3b8f1e63dd02266b

History

#1 Updated by Toshi MARUYAMA about 5 years ago

  • Status changed from New to Confirmed
  • Target version set to 3.0.1

Confirmed.

I tried Rails 4.2.1.rc3, but no changes.

sqlite3: 3.8.8
Ruby: ruby 1.9.3p551 (2014-11-13 revision 48407) [x86_64-linux]

#2 Updated by Toshi MARUYAMA about 5 years ago

Toshi MARUYAMA wrote:

sqlite3: 3.8.8
Ruby: ruby 1.9.3p551 (2014-11-13 revision 48407) [x86_64-linux]

On CentOS6 replaced at #19106#note-8.

#3 Updated by Toshi MARUYAMA about 5 years ago

I can reproduce on Windows ruby 1.9.3p551 (2014-11-13) [i386-mingw32].
It seems it uses sqlite3 >= 3.8.7.1.
https://github.com/sparklemotion/sqlite3-ruby/blame/970eea5dcdc3017ff485/tasks/native.rake#L8

#4 Updated by Jean-Philippe Lang about 5 years ago

  • Target version changed from 3.0.1 to Candidate for next minor release

Adding some debug info in IssueQuery#issues shows that ActiveRecord does not return the results in the correct order (while doing a #pluck does):

puts scope.pluck(:tracker_id)*','
puts scope.to_a.map(&:tracker_id)*','

gives:

3,2,2,1,1,1,1,1,1
2,2,3,1,1,1,1,1,1

The query that is executed has a proper ORDER BY statement: ORDER BY trackers.position DESC, issues.id DESC (trackers position equals tracker id in test fixtures). Running the query on the db manually returns the rows in the correct order so the problem is not on the database.

#5 Updated by Toshi MARUYAMA almost 5 years ago

  • Status changed from Confirmed to Closed
  • Target version deleted (Candidate for next minor release)
  • Resolution set to Wont fix

This is simplified SQL.

SELECT "issues"."id" AS t0_r0, "issues"."tracker_id" AS t0_r1
FROM "issues" 
LEFT OUTER JOIN "trackers" ON "trackers"."id" = "issues"."tracker_id" 
ORDER BY trackers.position

Tests passes on my CentOS7 sqlite3 built by sqlite-3.8.9-1.fc23.src.rpm.

$ sqlite3 --version
3.8.9 2015-04-08 12:16:33 8a8ffc862e96f57aa698f93de10dee28e69f6e09

It seems this issues fixed in sqlite 3.8.9.
Incorrect result when a LEFT JOIN provides the qualifying constraint for a partial index

#6 Updated by Toshi MARUYAMA almost 5 years ago

  • Resolution deleted (Wont fix)

#7 Updated by Toshi MARUYAMA over 4 years ago

  • Category set to Database

Also available in: Atom PDF