https://www.redmine.org/https://www.redmine.org/favicon.ico?16793021292012-08-21T10:14:34ZRedmineRedmine - Defect #11662: Invalid query returned from Issues.visible scope after accessing User#projects_by_role with a role that is not presenthttps://www.redmine.org/issues/11662?journal_id=404412012-08-21T10:14:34ZEtienne Massip
<ul></ul><p>Could you please provide a less technical description and steps to reproduce?</p> Redmine - Defect #11662: Invalid query returned from Issues.visible scope after accessing User#projects_by_role with a role that is not presenthttps://www.redmine.org/issues/11662?journal_id=404502012-08-21T13:21:52ZJoe Rocklin
<ul></ul><p>I guess the less technical description would be: "some users get a 500 error when trying to view 'My Page'" and the following error is in the log:</p>
<pre>
ActionView::Template::Error (Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')))) AND (issues.project_id in (1,2,5,6,7,8,9,10,11,12,14,154) AND ((start_date>' at line 1: SELECT `issues`.`id` AS t0_r0, `issues`.`tracker_id` AS t0_r1, `issues`.`project_id` AS t0_r2, `issues`.`subject` AS t0_r3, `issues`.`description` AS t0_r4, `issues`.`due_date` AS t0_r5, `issues`.`category_id` AS t0_r6, `issues`.`status_id` AS t0_r7, `issues`.`assigned_to_id` AS t0_r8, `issues`.`priority_id` AS t0_r9, `issues`.`fixed_version_id` AS t0_r10, `issues`.`author_id` AS t0_r11, `issues`.`lock_version` AS t0_r12, `issues`.`created_on` AS t0_r13, `issues`.`updated_on` AS t0_r14, `issues`.`start_date` AS t0_r15, `issues`.`done_ratio` AS t0_r16, `issues`.`estimated_hours` AS t0_r17, `issues`.`parent_id` AS t0_r18, `issues`.`root_id` AS t0_r19, `issues`.`lft` AS t0_r20, `issues`.`rgt` AS t0_r21, `issues`.`is_private` AS t0_r22, `issues`.`position` AS t0_r23, `issues`.`remaining_hours` AS t0_r24, `issues`.`story_points` AS t0_r25, `projects`.`id` AS t1_r0, `projects`.`name` AS t1_r1, `projects`.`description` AS t1_r2, `projects`.`homepage` AS t1_r3, `projects`.`is_public` AS t1_r4, `projects`.`parent_id` AS t1_r5, `projects`.`created_on` AS t1_r6, `projects`.`updated_on` AS t1_r7, `projects`.`identifier` AS t1_r8, `projects`.`status` AS t1_r9, `projects`.`lft` AS t1_r10, `projects`.`rgt` AS t1_r11, `projects`.`dmsf_description` AS t1_r12, `trackers`.`id` AS t2_r0, `trackers`.`name` AS t2_r1, `trackers`.`is_in_chlog` AS t2_r2, `trackers`.`position` AS t2_r3, `trackers`.`is_in_roadmap` AS t2_r4, `enumerations`.`id` AS t3_r0, `enumerations`.`name` AS t3_r1, `enumerations`.`position` AS t3_r2, `enumerations`.`is_default` AS t3_r3, `enumerations`.`type` AS t3_r4, `enumerations`.`active` AS t3_r5, `enumerations`.`project_id` AS t3_r6, `enumerations`.`parent_id` AS t3_r7, `users`.`id` AS t4_r0, `users`.`login` AS t4_r1, `users`.`hashed_password` AS t4_r2, `users`.`firstname` AS t4_r3, `users`.`lastname` AS t4_r4, `users`.`mail` AS t4_r5, `users`.`admin` AS t4_r6, `users`.`status` AS t4_r7, `users`.`last_login_on` AS t4_r8, `users`.`language` AS t4_r9, `users`.`auth_source_id` AS t4_r10, `users`.`created_on` AS t4_r11, `users`.`updated_on` AS t4_r12, `users`.`type` AS t4_r13, `users`.`identity_url` AS t4_r14, `users`.`mail_notification` AS t4_r15, `users`.`salt` AS t4_r16, `users`.`reminder_notification` AS t4_r17 FROM `issues` LEFT OUTER JOIN `projects` ON `projects`.`id` = `issues`.`project_id` LEFT OUTER JOIN `trackers` ON `trackers`.`id` = `issues`.`tracker_id` LEFT OUTER JOIN `enumerations` ON `enumerations`.`id` = `issues`.`priority_id` AND `enumerations`.`type` IN ('IssuePriority') LEFT OUTER JOIN `users` ON `users`.`id` = `issues`.`assigned_to_id` WHERE (((projects.status=1 AND projects.id IN (SELECT em.project_id FROM enabled_modules em WHERE em.name='issue_tracking')) AND ((projects.is_public = 1 AND ((issues.is_private = 0 OR issues.author_id = 97 OR issues.assigned_to_id IN (97,6,27,34,42,48,56,68,90,91,98)))) OR (projects.id IN (6,9,7,1,5,154,2,11,12,10,8) AND ((issues.is_private = 0 OR issues.author_id = 97 OR issues.assigned_to_id IN (97,6,27,34,42,48,56,68,90,91,98)))) OR (projects.id IN (14) AND ((issues.is_private = 0 OR issues.author_id = 97 OR issues.assigned_to_id IN (97,6,27,34,42,48,56,68,90,91,98)))) OR projects.id IN ()))) AND (issues.project_id in (1,2,5,6,7,8,9,10,11,12,14,154) AND ((start_date>='2012-08-19' and start_date<='2012-08-25') or (due_date>='2012-08-19' and due_date<='2012-08-25')))):
1: <h3><%= l(:label_calendar) %></h3>
2:
3: <% calendar = Redmine::Helpers::Calendar.new(Date.today, current_language, :week)
4: calendar.events = Issue.visible.find :all,
5: :conditions => ["#{Issue.table_name}.project_id in (#{@user.projects.collect{|m| m.id}.join(',')}) AND ((start_date>=? and start_date<=?) or (due_date>=? and due_date<=?))", calendar.startdt, calendar.enddt, calendar.startdt, calendar.enddt],
6: :include => [:project, :tracker, :priority, :assigned_to] unless @user.projects.empty? %>
7:
app/views/my/blocks/_calendar.html.erb:4:in `_app_views_my_blocks__calendar_html_erb___2203117175707133972_51672440'
app/views/my/page.html.erb:11:in `block in _app_views_my_page_html_erb___3004501534925273256_28909740'
app/views/my/page.html.erb:8:in `each'
app/views/my/page.html.erb:8:in `_app_views_my_page_html_erb___3004501534925273256_28909740'
</pre>
<p>It seems to be happening for users which were not listed as 'Manager' for any project in our configuration. If you were a manager of at least one project, then the error was not seen.</p>
<p>I did some more testing this morning and found that the problem also goes away (without the patch applied and without being a manager of any project) if I move a custom block (from a plugin which provides an alternate calendar view) to the bottom of the page so it is the last thing rendered. The entirety of the code for the block is:</p>
<pre>
<h3><%= l(:label_personal_calendar) %></h3>
<% calendar = Redmine::Helpers::Calendar.new(Date.today, current_language, :week)
mgr_projects = User.current.projects_by_role[Role.where(:name => "Manager").first]
mgr_project_ids = [0]
mgr_projects.each do |proj|
mgr_project_ids << proj.id
end
watched = Issue.on_active_project.watched_by(User.current.id)
rest = Issue.where("start_date BETWEEN :start_date AND :end_date OR due_date BETWEEN :start_date AND :end_date",
:start_date => calendar.startdt, :end_date => calendar.enddt)
.where("author_id = :id OR assigned_to_id IN (:id_list) OR project_id IN (:mgr_list)",
:id => User.current.id,
:id_list => [User.current.id] + User.current.group_ids,
:mgr_list => mgr_project_ids)
calendar.events = watched + rest;
%>
<%= render :partial => 'common/calendar', :locals => {:calendar => calendar } %>
</pre> Redmine - Defect #11662: Invalid query returned from Issues.visible scope after accessing User#projects_by_role with a role that is not presenthttps://www.redmine.org/issues/11662?journal_id=404522012-08-21T14:59:59ZEtienne Massip
<ul><li><strong>Resolution</strong> set to <i>Cant reproduce</i></li></ul><p>Looking at <code>User#project_by_role</code> code, I can't see a way it could return a map containing a key with no values associated.</p>
<p>Could you try with no plugin installed?</p>
<p>If that doesn't change anything, maybe you could try to change <a class="source" href="https://www.redmine.org/projects/redmine/repository/svn/entry/tags/2.0.3/app/models/user.rb#L426">source:/tags/2.0.3/app/models/user.rb#L426</a> <code>if membership.project</code> with <code>if membership.project.present?</code>?</p> Redmine - Defect #11662: Invalid query returned from Issues.visible scope after accessing User#projects_by_role with a role that is not presenthttps://www.redmine.org/issues/11662?journal_id=405672012-08-27T09:47:59ZJean-Philippe Langjp_lang@yahoo.fr
<ul></ul><p>This is due to the way @projects_by_role is initialized in User#projects_by_role. Accessing projects_by_role[] with a key that does not exist implicitely creates an empty array as its value, as demonstrated below:</p>
<pre>
irb(main):001:0> a = Hash.new {|h,k| h[k]=[]}
=> {}
irb(main):002:0> a[:foo]
=> []
irb(main):003:0> a
=> {:foo=>[]}
</pre> Redmine - Defect #11662: Invalid query returned from Issues.visible scope after accessing User#projects_by_role with a role that is not presenthttps://www.redmine.org/issues/11662?journal_id=405682012-08-27T10:13:45ZJean-Philippe Langjp_lang@yahoo.fr
<ul><li><strong>Subject</strong> changed from <i>Invalid query returned from Issues.visible scope</i> to <i>Invalid query returned from Issues.visible scope after accessing User#projects_by_role with a role that is not present</i></li><li><strong>Status</strong> changed from <i>New</i> to <i>Closed</i></li><li><strong>Assignee</strong> set to <i>Jean-Philippe Lang</i></li><li><strong>Target version</strong> set to <i>2.1.0</i></li><li><strong>Resolution</strong> changed from <i>Cant reproduce</i> to <i>Fixed</i></li></ul><p>This should be fixed by the 2 related commits.</p> Redmine - Defect #11662: Invalid query returned from Issues.visible scope after accessing User#projects_by_role with a role that is not presenthttps://www.redmine.org/issues/11662?journal_id=405712012-08-27T10:31:00ZEtienne Massip
<ul></ul><p>Jean-Philippe Lang wrote:</p>
<blockquote>
<p>This is due to the way @projects_by_role is initialized in User#projects_by_role. Accessing projects_by_role[] with a key that does not exist implicitely creates an empty array as its value, as demonstrated below:</p>
</blockquote>
<p>Gosh, I didn't know that, thanks for the lesson.</p>
<p>Weird idea though :p</p>