SQL returns an empty tuple??

[juandent]

Strange behavior: it compiles correctly, it generates the correct SQL, but it returns an empty tuple:

/*
	 *	select d.* from dept d left outer join emp e
		on(d.deptno = e.depno)
		where e.depno is null
	 *
	 */
	using als_d = alias_d<Department>;
	using als_e = alias_e<Employee>;

	auto statement = storage.prepare(select(asterisk<als_d>(), from<als_d>(), left_join<als_e>(on
	(c(alias_column<als_d>(&Department::m_deptno)) == alias_column<als_e>(&Employee::m_depno))), where(is_null(alias_column<als_e>(&Employee::m_depno)))));
	auto sql = statement.expanded_sql();
	auto rows = storage.execute(statement);

rows has one row but it is tuple<>!!

[fnc12]

@juandent please provide a schema code for me to repro. Thanks

[juandent]

Hi Eugene Sorry to see this now since I am not at home but will sent it asap at 9am I hope you can take a look at this tim Regards Juan Dent

…

Sent from my iPhone

On 11 Mar 2022, at 2:02 AM, Yevgeniy Zakharov ***@***.***> wrote:  @juandent please provide a schema code for me to repro. Thanks — Reply to this email directly, view it on GitHub, or unsubscribe. Triage notifications on the go with GitHub Mobile for iOS or Android. You are receiving this because you were mentioned.

[juandent]

Hi Eugene!

Here is the schema:

struct Employee
{
	int m_empno;
	std::string m_ename;
	std::string m_job;
	std::optional<int> m_mgr;
	std::string m_hiredate;
	double m_salary;
	std::optional<double> m_commission;
	int m_deptno;
};

struct Department
{
	int m_deptno;
	std::string m_deptname;
	std::string m_loc;

};

struct EmpBonus
{
	int m_id;
	int m_empno;
	std::string m_received;	// date
	int m_type;
};

using namespace sqlite_orm;

auto storage = make_storage("SQLCookbook.sqlite",
	make_table("Emp",
		make_column("empno", &Employee::m_empno, primary_key(), autoincrement()),
		make_column("ename", &Employee::m_ename),
		make_column("job", &Employee::m_job),
		make_column("mgr", &Employee::m_mgr),
		make_column("hiredate", &Employee::m_hiredate),
		make_column("salary", &Employee::m_salary),
		make_column("comm", &Employee::m_commission),
		make_column("deptno", &Employee::m_deptno),
		foreign_key(&Employee::m_deptno).references(&Department::m_deptno)),
	make_table("Dept",
		make_column("deptno", &Department::m_deptno, primary_key(), autoincrement()),
		make_column("deptname", &Department::m_deptname),
		make_column("loc", &Department::m_loc)),
	make_table("Emp_bonus",
		make_column("id", &EmpBonus::m_id, primary_key(), autoincrement()),
		make_column("empno", &EmpBonus::m_empno),
		make_column("received", &EmpBonus::m_received),
		make_column("type", &EmpBonus::m_type),
		foreign_key(&EmpBonus::m_empno).references(&Employee::m_empno)));

int main()
{
	using namespace sqlite_orm;

	storage.sync_schema();
	storage.remove_all<EmpBonus>();
	storage.remove_all<Employee>();
	storage.remove_all<Department>();

	std::vector<Employee> vec =
	{
		Employee{7369, "Smith", "Clerk", 7902, "17-DEC-1980",800,std::nullopt, 20},
		Employee{7499, "Allen", "SalesMan", 7698, "20-FEB-1981", 1600, 300, 30},
		Employee{7521,"Ward", "SalesMan", 7698,"22-feb-1981",1250,500, 30},
		Employee{7566,"Jones", "Manager", 7839, "02-abr-1981",2975, std::nullopt,20},
		Employee{7654,"Martin","SalesMan", 7698, "28-sep-1981", 1250,1400,30},
		Employee{7698,"Blake", "Manager", 7839, "01-may-1981", 2850, std::nullopt, 30},
		Employee{7782, "Clark", "Manager", 7839, "09-jun-1981", 2450, std::nullopt, 10},
		Employee{7788, "Scott", "Analyst", 7566, "09-Dec-1982", 3000, std::nullopt, 20},
		Employee{7839, "King", "President", std::nullopt, "17-nov-1981", 5000, std::nullopt,10},
		Employee{7844,"Turner","SalesMan", 7698, "08-Sep-1981", 1500, 0, 30},
		Employee{7876, "Adams", "Clerk", 7788, "12-JAN-1983", 1100, std::nullopt, 20},
		Employee{7900,"James", "Clerk", 7698,"03-DEC-1981", 950, std::nullopt, 30},
		Employee{7902,"Ford", "Analyst", 7566, "03-DEC-1981", 3000, std::nullopt, 20},
		Employee{7934, "Miller", "Clerk", 7782,"23-JAN-1982", 1300, std::nullopt, 10}
	};

	std::vector<Department> des =
	{
		Department{10, "Accounting", "New York"},
		Department{20, "Research", "Dallas"},
		Department{30, "Sales", "Chicago"},
		Department{40, "Operations", "Boston"}
	};

	std::vector<EmpBonus> bonuses =
	{
		EmpBonus{-1, 7369, "14-Mar-2005", 1},
		EmpBonus{-1, 7900, "14-Mar-2005", 2},
		EmpBonus{-1, 7788, "14-Mar-2005", 3}
	};

	storage.replace_range(des.begin(), des.end());
	storage.replace_range(vec.begin(), vec.end());
	storage.insert_range(bonuses.begin(), bonuses.end());
}

Regards,
Juan

[fnc12]

it's a bug. I'm fixing it

[fnc12]

fix is here #952

[fnc12]

merged

[juandent]

Runs perfectly fine!
Thanks

[trueqbit]

PR #952 fixed it at the 'column result' layer, however the resulting statement is not entirely correct.

You want to get:

SELECT 'd'.* 
FROM 'Dept' 'd' 
LEFT JOIN 'Emp' 'e' ON ('d'."deptno" = 'e'."deptno")  
WHERE ('e'."deptno" IS NULL)

vs.

SELECT     * 
FROM 'Dept' 'd' 
LEFT JOIN 'Emp' 'e' ON ('d'."deptno" = 'e'."deptno")  
WHERE ('e'."deptno" IS NULL)

otherwise the result set contains all columns instead of from 'Dept' only.

See PR #973 for an amendment.