Project

General

Profile

Bug #2861

Mysql And MariaDB Query And Tuple

Added by Anonymous over 8 years ago. Updated about 8 years ago.

Status:
Feedback
Priority:
Normal
Assignee:
Michael Vilsker
Target version:
Start date:
03/25/2014
Due date:
% Done:

0%

Estimated time:

Description

I get the error "fatal error, duplicate column name "version""

the tuple is defined as this:

using userData = boost::tuple<Wt::Dbo::ptrUsers::User, Wt::Dbo::ptrUsers::AuthInfo, Wt::Dbo::ptrUsers::AuthInfo::AuthIdentityType>;

and this is the query:

Wt::Dbo::Query query = Database::DatabaseManager::instance().session.query("SELECT u, uain, uaid"

\" FROM user u\"

\" LEFT JOIN user_auth_info uain ON u.Name = uain.user_Name\"

\" LEFT JOIN user_auth_identity uaid ON uain.id = uaid.user_auth_info_id\");

Generated SQL is:

Wt: fatal error: error creating prepared statement: 'select count(1) from (SELECT u.""version"", u.""Thumbnail"", u.""Name"", u.""Id_Number"", u.""Phone_Number"", u.""Email_Address"", u.""Date_Created"", u.""Last_Login"", u.""Address"", u.""View_Rank"", u.""Edit_Rank"", u.""Remove_Rank"", u.""Active"", u.""Group_Name"", u.""Title_Name"", uain.""id"", uain.""version"", uain.""user_Name"", uain.""password_hash"", uain.""password_method"", uain.""password_salt"", uain.""status"", uain.""failed_login_attempts"", uain.""last_login_attempt"", uain.""email"", uain.""unverified_email"", uain.""email_token"", uain.""email_token_expires"", uain.""email_token_role"", uaid.""id"", uaid.""version"", uaid.""user_auth_info_id"", uaid.""provider"", uaid.""identity"" FROM user u LEFT JOIN user_auth_info uain ON u.Name = uain.user_Name LEFT JOIN user_auth_identity uaid ON uain.id = uaid.user_auth_info_id limit ? offset ?) as dbocount': Duplicate column name 'version'

system info:

Linux-Fedora-20-x86_64 And also fedora 19

mysql 5.5 and mariaDB 5.5

Wt 3.3.2

#1

Updated by Koen Deforche about 8 years ago

  • Status changed from New to InProgress
  • Assignee changed from Koen Deforche to Michael Vilsker
  • Target version changed from 3.3.2 to 3.3.3

Michael, can we reproduce this using MariaDB and/or MySQL?

#2

Updated by Michael Vilsker about 8 years ago

There is a problem with the query above:

user_auth_info does not have column user_Name

Then next query should work:

Wt::Dbo::Query query = session.query(

"SELECT u, uain, uaid"

\" FROM user u \"

\" LEFT JOIN auth_info uain ON u.id = uain.user_id \"

\" LEFT JOIN auth_identity uaid ON uaid.auth_info_id = uain.id;\"

);

#3

Updated by Koen Deforche about 8 years ago

  • Status changed from InProgress to Feedback
#4

Updated by Mohamed Samir about 8 years ago

the query that produces the error is correct it's just using a specialized id column using std::string instead of the default unsigned long long id.

I've tried this with koen and it is a BUG.

#5

Updated by Koen Deforche about 8 years ago

Hey,

We tried and could not reproduce the problem (after correcting the query to run for our database schema). We thus need a test case that reproduces the problem in a self-contained way. Ideally, if you can add it to test/dbo/DboTest.C, that would probably be easiest for you and us.

Regards,

koen

#6

Updated by Koen Deforche about 8 years ago

  • Target version changed from 3.3.3 to 3.3.4

Also available in: Atom PDF