[odb-users] Query Result Problem

[Erez Pics]

 Hi Boris,

I am performing an aggregated count grouped query, somehow I get an
extra first row with invalid data, the rest of the result is as
expected.

//////////////////////////////////////////////////////
// this is the expected result from SQLite
//////////////////////////////////////////////////////
0|2
1|3
2|1
//////////////////////////////////////////////////////
// this is the result I get from ODB
//////////////////////////////////////////////////////
0|1 // <<< this is the problem
0|2
1|3
2|1

We have seen the result of 0|1 in different combinations of data, even
if the first row should be 0|1 we get it twice, not sure why it
happens, maybe something is wrong with my syntax.

I will be very happy to hear what you think.

[all information to reproduce the problem is in the text attached]

Thank you very very much,
Erez.
-------------- next part --------------

//////////////////////////////////////////////////////
// these are the actual SQL tables
//////////////////////////////////////////////////////

CREATE TABLE person_data (
	person_id INTEGER PRIMARY KEY NOT NULL,
	gender char(1) default 'U'
);

CREATE TABLE person_set (
	person_set_id INTEGER PRIMARY KEY AUTOINCREMENT,
	delete_flag tinyint UNSIGNED default '0',  -- soft erase flag
    person_id INT NOT NULL,	
	
	FOREIGN KEY(person_id) REFERENCES person_data(person_id) ON DELETE CASCADE ON UPDATE CASCADE 
);

CREATE TABLE person_lang_data (
    person_lang_data_id INTEGER PRIMARY KEY AUTOINCREMENT,
	person_set_id INT NOT NULL,
	data_language TINYINT UNSIGNED NOT NULL default '0',
	first_name varchar(255) default '',
	last_name varchar(255) default '',

	FOREIGN KEY(person_set_id) REFERENCES person_set(person_set_id) ON DELETE CASCADE ON UPDATE CASCADE
);

//////////////////////////////////////////////////////
// these are the ODB classes for the above tables
//////////////////////////////////////////////////////

#pragma db object table("person_data") pointer(shared_ptr) session
class CPersonData
{
public:

protected:

	// default empty constructor for ODB
	CPersonData()
	{
	}

	#pragma db id column("person_id") type("INT")
	long m_person_id;

	#pragma db column("gender") type("char(1)") default("U")
	char m_gender;
	
	// this is the inversed relationship side of the one to many
	#pragma db value_not_null inverse(m_person_id)
	std::vector<shared_ptr<CPersonSet> > m_vecPersonSet;
};

#pragma db object table("person_set") pointer(shared_ptr) session
class CPersonSet
{
public:

protected:

	// default empty constructor for ODB
	CPersonSet(void)
	{
	}

	friend class odb::access;

	#pragma db id auto column("person_set_id") type("INT")
	long m_person_set_id;

	#pragma db column("delete_flag") type("TINYINT UNSIGNED") default(0)
	short m_delete_flag;

	// this is the many side of the relationship with CPersonData
	#pragma db not_null
	weak_ptr<CPersonData> m_person_id;
	
	// this is the inversed relationship side of the one to many
	#pragma db value_not_null inverse(m_person_set_id)
	std::vector<shared_ptr<CPersonLangData> > m_vecLangData;
};

#pragma db object table("person_lang_data") pointer(shared_ptr) session
class CPersonLangData
{
public:

protected:

	// default empty constructor for ODB
	CPersonLangData()
	{
	}

	friend class odb::access;

	#pragma db id auto column("person_lang_data_id") type("INT")
	long m_person_lang_data_id;

	#pragma db not_null
	weak_ptr<CPersonSet> m_person_set_id;

	#pragma db column("data_language") type("TINYINT UNSIGNED") default(0)
	int m_data_language;

	#pragma db column("first_name") type("VARCHAR(255)") default("")
	string m_first_name;

	#pragma db column("last_name") type("VARCHAR(255)") default("")
	string m_last_name;
};

//////////////////////////////////////////////////////
// this is the data in the tables
//////////////////////////////////////////////////////

INSERT INTO "person_data" VALUES(1,'M');
INSERT INTO "person_data" VALUES(2,'F');
INSERT INTO "person_data" VALUES(3,'M');
INSERT INTO "person_data" VALUES(4,'F');

INSERT INTO "person_set" VALUES(1,0,1);
INSERT INTO "person_set" VALUES(2,0,2);
INSERT INTO "person_set" VALUES(3,0,3);
INSERT INTO "person_set" VALUES(4,0,4);
INSERT INTO "person_set" VALUES(5,0,1);

INSERT INTO "person_lang_data" VALUES(1,1,0,'eee','');
INSERT INTO "person_lang_data" VALUES(2,1,1,'eee','');
INSERT INTO "person_lang_data" VALUES(3,3,1,'eee','');
INSERT INTO "person_lang_data" VALUES(4,4,1,'eee','');
INSERT INTO "person_lang_data" VALUES(5,1,2,'eee','');
INSERT INTO "person_lang_data" VALUES(6,5,0,'eee','');
INSERT INTO "person_lang_data" VALUES(7,4,0,'eee','');

//////////////////////////////////////////////////////
// this is the query I am executing
//////////////////////////////////////////////////////
SELECT person_lang_data.data_language,COUNT(DISTINCT person_set.person_id) FROM person_lang_data JOIN person_set ON person_lang_data.person_set_id = person_set.person_set_id GROUP BY data_language;

//////////////////////////////////////////////////////
// this is the ODB syntax for the query above
//////////////////////////////////////////////////////
#pragma db view object(CPersonSet) \
	object(CPersonLangData: CPersonSet::m_person_set_id == CPersonLangData::m_person_set_id) \
	query((?) + "GROUP BY" + CPersonLangData::m_data_language)
struct view_persons_per_langauges
{
#pragma db column(CPersonLangData::m_data_language)
	int data_language;

#pragma db column("count (distinct " + CPersonSet::m_person_id + ")")
	long count_persons;
};

//////////////////////////////////////////////////////
// this is the expected result from SQLite
//////////////////////////////////////////////////////
0|2
1|3
2|1

//////////////////////////////////////////////////////
// this is the result I get from ODB
//////////////////////////////////////////////////////
0|1 // <<< this is the problem
0|2
1|3
2|1