Defect #19485

Column 'address' in where clause may be ambiguous

Added by Andrey Sennikov over 3 years ago. Updated over 3 years ago.

Status:ClosedStart date:
Priority:NormalDue date:
Assignee:Jean-Philippe Lang% Done:

0%

Category:Code cleanup/refactoring
Target version:3.0.4
Resolution:Fixed Affected version:3.0.1

Description

Hello.
i`ve got the following error after upgrading from 2.6.1 to 3.0.1

Mysql2::Error: Column 'address' in where clause is ambiguous:
 SELECT  DISTINCT `users`.* FROM `users` INNER JOIN `email_addresses` ON `email_addresses`.`user_id` = `users`.`id` WHERE `users`.`type` 
 IN ('User', 'AnonymousUser') AND (LOWER(address) IN ('myemail@mail.com'))  ORDER BY `users`.`id` ASC LIMIT 1

emails received by cronjob every minute.
rake redmine:email:receive_imap RAILS_ENV="production" host=imap.gmail.com port=993 username=email@email password=password ssl=true move_on_success=read project=public-project247 category=HelpDesk unknown_user=create no_permission_check=1 allow_override=tracker,priority,project,status,category,assigned_to

Please give advise for solving this issue

Environment:
Redmine version 3.0.1.stable
Ruby version 1.9.3-p194 (2012-04-20) [x86_64-linux]
Rails version 4.2.0
Environment production
Database adapter Mysql2
SCM:
Git 1.7.10.4
Filesystem
Redmine plugins:
redmine_agile 1.3.8
redmine_email_fetcher 0.3.0
redmine_issue_history 1.0.0
redmine_wysiwyg 1.0.0

plugins
Redmine Agile plugin (Light version)
redmine_issue_history
redmine_wysiwyg


Related issues

Duplicated by Redmine - Defect #19595: Broken "receiving emails" Closed
Duplicated by Redmine - Defect #19884: address column in WHERE clause is ambiguous Closed

Associated revisions

Revision 14284
Added by Jean-Philippe Lang over 3 years ago

Column 'address' in where clause may be ambiguous (#19485).

History

#1 Updated by Toshi MARUYAMA over 3 years ago

  • Description updated (diff)

#2 Updated by RafaƂ Lisowski over 3 years ago

are you sure that you don't have any other plugins?
In my logs sql look the same and is working:

SELECT  DISTINCT `users`.* FROM `users` 
  INNER JOIN `email_addresses` ON `email_addresses`.`user_id` = `users`.`id` 
  WHERE `users`.`type` IN ('User', 'AnonymousUser') AND (LOWER(address) IN ('l.ka@company.com'))  
  ORDER BY `users`.`id` ASC LIMIT 1

#3 Updated by Toshi MARUYAMA over 3 years ago

  • Status changed from New to Needs feedback

I think your "users" table has "address" column created by plugin which you use or used.

#4 Updated by Go MAEDA over 3 years ago

The same error is reported in #19595 and that issue has a workaround.

#5 Updated by Toshi MARUYAMA over 3 years ago

#6 Updated by Go MAEDA over 3 years ago

A workaround by Andrey Sennikov (#19595).

Index: app/models/user.rb
===================================================================
--- app/models/user.rb    (revision 14160)
+++ app/models/user.rb    (working copy)
@@ -130,7 +130,7 @@
   scope :having_mail, lambda {|arg|
     addresses = Array.wrap(arg).map {|a| a.to_s.downcase}
     if addresses.any?
-      joins(:email_addresses).where("LOWER(address) IN (?)", addresses).uniq
+      joins(:email_addresses).where("LOWER(email_addresses.address) IN (?)", addresses).uniq
     else
       none
     end

#7 Updated by Toshi MARUYAMA over 3 years ago

  • Subject changed from error in imap fetch task after update to 3.0.1 to Column 'address' in where clause is ambiguous

#8 Updated by Toshi MARUYAMA over 3 years ago

  • Duplicated by Defect #19884: address column in WHERE clause is ambiguous added

#9 Updated by Toshi MARUYAMA over 3 years ago

  • Category changed from Email receiving to Database

#10 Updated by Future Link Corporation pkgsrc team over 3 years ago

For us, it was the People plugin which caused this problem. In any case, less ambiguity in the SELECT statement wouldn't be a bad thing. Can this patch be integrated? It seems we fixed it in the same way.

#11 Updated by Jean-Philippe Lang over 3 years ago

  • Target version set to 3.0.4

#12 Updated by Jean-Philippe Lang over 3 years ago

  • Subject changed from Column 'address' in where clause is ambiguous to Column 'address' in where clause may be ambiguous
  • Category changed from Database to Code cleanup/refactoring
  • Status changed from Needs feedback to Closed
  • Assignee set to Jean-Philippe Lang
  • Resolution set to Fixed

Fixed in r14284.

Also available in: Atom PDF