Adding time gives SQL error due to blank version?

[sammcj]

We're getting an error when trying to add time to any tickets after upgrading to Redmine 2.6.5 and the latest timesheets plugin.

I've also tried older versions of the timesheets plugin but it doesn't help:

ActiveRecord::StatementInvalid (PG::InvalidTextRepresentation: ERROR:  invalid input syntax for integer: ""
LINE 1: ...ND projects.status <> 9) AND (versions.id NOT IN ('')) AND (...
                                                             ^
: SELECT "versions"."id" AS t0_r0, "versions"."project_id" AS t0_r1, "versions"."name" AS t0_r2, "versions"."description" AS t0_r3, "versions"."effective_date" AS t0_r4, "versions"."created_on" AS t0_r5, "versions"."updated_on" AS t0_r6, "versions"."wiki_page_title" AS t0_r7, "versions"."status" AS t0_r8, "versions"."sharing" AS t0_r9, "versions"."in_timesheet" AS t0_r10, "versions"."is_order" AS t0_r11, "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"."inherit_members" AS t1_r12 FROM "versions" LEFT OUTER JOIN "projects" ON "projects"."id" = "versions"."project_id" WHERE (projects.lft >= 353 AND projects.rgt <= 354 AND projects.status <> 9) AND (versions.id NOT IN ('')) AND (projects.status <> 9 AND projects.id IN (SELECT em.project_id FROM enabled_modules em WHERE em.name='issue_tracking'))):
  app/controllers/versions_controller.rb:40:in `block (2 levels) in index'
  app/controllers/versions_controller.rb:32:in `index'

Environment:
  Redmine version                2.6.5.stable.12119
  Ruby version                   2.1.5-p273 (2014-11-13) [x86_64-linux]
  Rails version                  3.2.21
  Environment                    production
  Database adapter               PostgreSQL
SCM:
  Subversion                     1.6.17
  Cvs                            1.12.13
  Git                            1.7.10.4
  Filesystem                     
Redmine plugins:
  a_common_libs                  1.1.1
  ixa_hacks                      0.0.1
  redmine_agile                  1.3.9
  redmine_app__space             1.0.3
  redmine_app_timesheets         1.5.3
  redmine_contacts               3.4.5
  redmine_contacts_helpdesk      2.4.0
  redmine_favorite_projects      1.0.1
  redmine_issue_checklist        2.0.5
  redmine_rouge                  0.0.1
  redmine_spent_time_in_issue_description 2.6

[maxrossello]

Hi, as I do not have a PostgreSQL installation right now, can you please check what is the syntax that it is required for an empty IN clause? Would

versions.id NOT IN ("")

be accepted or must it be

versions.id NOT IN ()

?

Thank you

[sammcj]

Hi Max,

Neither look right to me bud:

redmine=# SELECT "versions"."id" AS t0_r0, "versions"."project_id" AS t0_r1, "versions"."name" AS t0_r2, "versions"."description" AS t0_r3, "versions"."effective_date" AS t0_r4, "versions"."created_on" AS t0_r5, "versions"."updated_on" AS t0_r6, "versions"."wiki_page_title" AS t0_r7, "versions"."status" AS t0_r8, "versions"."sharing" AS t0_r9, "versions"."in_timesheet" AS t0_r10, "versions"."is_order" AS t0_r11, "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"."inherit_members" AS t1_r12 FROM "versions" LEFT OUTER JOIN "projects" ON "projects"."id" = "versions"."project_id" WHERE (projects.lft >= 353 AND projects.rgt <= 354 AND projects.status <> 9) AND (versions.id NOT IN ()) AND (projects.status <> 9 AND projects.id IN (SELECT em.project_id FROM enabled_modules em WHERE em.name='issue_tracking'));
ERROR:  syntax error at or near ")"
LINE 1: ...ND projects.status <> 9) AND (versions.id NOT IN ()) AND (pr...
                                                             ^
Time: 0.456 ms
redmine=# SELECT "versions"."id" AS t0_r0, "versions"."project_id" AS t0_r1, "versions"."name" AS t0_r2, "versions"."description" AS t0_r3, "versions"."effective_date" AS t0_r4, "versions"."created_on" AS t0_r5, "versions"."updated_on" AS t0_r6, "versions"."wiki_page_title" AS t0_r7, "versions"."status" AS t0_r8, "versions"."sharing" AS t0_r9, "versions"."in_timesheet" AS t0_r10, "versions"."is_order" AS t0_r11, "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"."inherit_members" AS t1_r12 FROM "versions" LEFT OUTER JOIN "projects" ON "projects"."id" = "versions"."project_id" WHERE (projects.lft >= 353 AND projects.rgt <= 354 AND projects.status <> 9) AND (versions.id NOT IN ('')) AND (projects.status <> 9 AND projects.id IN (SELECT em.project_id FROM enabled_modules em WHERE em.name='issue_tracking'));
ERROR:  invalid input syntax for integer: ""
LINE 1: ...ND projects.status <> 9) AND (versions.id NOT IN ('')) AND (...
                                                             ^
Time: 0.396 ms

I'm just heading off but I'll continue tomorrow :)

[maxrossello]

Ok I'll try to remove the clause completely in the case, then. Thank you

[sammcj]

I think perhaps you want (NOT (NULL)) ?

SELECT "versions"."id" AS t0_r0, "versions"."project_id" AS t0_r1, "versions"."name" AS t0_r2, "versions"."description" AS t0_r3, "versions"."effective_date" AS t0_r4, "versions"."created_on" AS t0_r5, "versions"."updated_on" AS t0_r6, "versions"."wiki_page_title" AS t0_r7, "versions"."status" AS t0_r8, "versions"."sharing" AS t0_r9, "versions"."in_timesheet" AS t0_r10, "versions"."is_order" AS t0_r11, "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"."inherit_members" AS t1_r12 FROM "versions" LEFT OUTER JOIN "projects" ON "projects"."id" = "versions"."project_id" WHERE (projects.lft >= 353 AND projects.rgt <= 354 AND projects.status <> 9) AND (versions.id NOT IN (NULL)) AND (projects.status <> 9 AND projects.id IN (SELECT em.project_id FROM enabled_modules em WHERE em.name='issue_tracking'));
 t0_r0 | t0_r1 | t0_r2 | t0_r3 | t0_r4 | t0_r5 | t0_r6 | t0_r7 | t0_r8 | t0_r9 | t0_r10 | t0_r11 | t1_r0 | t1_r1 | t1_r2 | t1_r3 | t1_r4 | t1_r5 | t1_r6 | t1_r7 | t1_r8 | t1_r9 | t1_r10 | t1_r11 | t1_r12
-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+--------+--------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+--------+--------+--------
(0 rows)

Time: 1.020 ms

[maxrossello]

I have to check deeper, but I think it's just needed to exclude orders (versions) for which the user has insufficient permissions, so just enforcing NOT NULL is not enough. The problem is that the set to exclude results empty, but postgres complains with an empty set

[maxrossello]

Can you please try the following fix with your db?

Change line 28 in lib/timesheets_app_project_patch.rb from:

@rolled_up_versions = @rolled_up_versions.where("#{Version.table_name}.id NOT IN (?)", noperm + noallow + [""]) if Setting.plugin_redmine_app_timesheets["public_versions"].nil?

to:

@rolled_up_versions = @rolled_up_versions.where("#{Version.table_name}.id NOT IN (?)", noperm + noallow + [""]) if Setting.plugin_redmine_app_timesheets["public_versions"].nil? and !(noperm.nil? and noallow.nil?)

[maxrossello]

Sorry the fixed line should be instead:

 @rolled_up_versions = @rolled_up_versions.where("#{Version.table_name}.id NOT IN (?)", noperm + noallow + [""]) if Setting.plugin_redmine_app_timesheets["public_versions"].nil? and !(noperm.empty? and noallow.empty?)

[sammcj]

Thanks Max, just done - will test shortly and let you know.

[sammcj]

Didn't seem to have any affect?

ActiveRecord::StatementInvalid (PG::InvalidTextRepresentation: ERROR:  invalid input syntax for integer: ""
LINE 1: ...s"."project_id" WHERE ((time_entries.order_id IN ('')) OR (t...
                                                             ^
: SELECT SUM("time_entries"."hours") AS sum_id FROM "time_entries" LEFT OUTER JOIN "projects" ON "projects"."id" = "time_entries"."project_id" WHERE ((time_entries.order_id IN ('')) OR (time_entries.user_id = 303 AND time_entries.order_id IN ('609','553','552','926','643','556','554')) OR ((projects.status <> 9 AND projects.id IN (SELECT em.project_id FROM enabled_modules em WHERE em.name='time_tracking')) AND (projects.is_public = 't' OR projects.id IN (166,1,142,109,186,193,194,197,201,207,208,209)))) AND ((projects.id = 295 OR (projects.lft > 121 AND projects.rgt < 122)))):
  app/controllers/projects_controller.rb:158:in `show'

[maxrossello]

Right, probably I missed the point. It should be at line 39-40 little below, but it's a bit more complex.

Can you please run rails console and test which of the following syntaxes work with PostgreSQL?

Version.where('id IN ("?")', []).all
Version.where('id IN (?)', []).all
Version.where('id IN (?)', nil).all
Version.where('id IN ("?")', nil).all

[KarstenWolff]

The first and last one raise an error with PostgreSQL. Second and third run without errors:

2.1.0 :014 > Version.where('id IN (?)', []).all
  Version Load (1.9ms)  SELECT "versions".* FROM "versions" WHERE (id IN (NULL))
 => []
2.1.0 :015 > Version.where('id IN (?)', nil).all
  Version Load (1.1ms)  SELECT "versions".* FROM "versions" WHERE (id IN (NULL))
 => []
2.1.0 :016 >

[maxrossello]

Thank you KarstenWolff.

Well actually the error changed with the first fix. Now I suggest to also change line 64 in lib/timesheets_app_project_patch.rb as follows:

statement = "(#{TimeEntry.table_name}.order_id IN ('#{orders.join('\',\'')}')) OR (#{TimeEntry.table_name}.user_id = #{user.id} AND #{TimeEntry.table_name}.order_id IN ('#{own_orders.join('\',\'')}')) OR #{statement}" unless orders.blank?

There may be other points in code needing a similar fix, so please report back.

Thank you!