Bad serialization when using subqueries

[juandent]

There are several queries in examples that produce a CROSS JOIN in the outer query even though the outer query does not mention the other table. See for instance examples\subquery.cpp.

The following statement produces semantically different SQL compared to the original intentions:

storage.select(columns(&Employee::firstName, &Employee::lastName, &Employee::id, &Employee::jobId), 
                           where(lesser_or_equal(
                               1,
                               select(count<JobHistory>(), where(is_equal(&Employee::id, &JobHistory::employeeId))))));

this produces the following SQL string:

SELECT "employees"."FIRST_NAME", "employees"."LAST_NAME", "employees"."EMPLOYEE_ID", "employees"."JOB_ID" 
FROM 'employees', 'job_history' 
WHERE ((1 <= (
    SELECT COUNT(*) 
    FROM 'employees', 'job_history' 
    WHERE (("employees"."EMPLOYEE_ID" = "job_history"."employee_id")))))

It does a CROSS JOIN from employees and job_history in the outer query only because the table job_history is mentioned in the subquery. To fix the error we must use the explicit from clause:

auto rows =
            storage.select(columns(&Employee::firstName, &Employee::lastName, &Employee::id, &Employee::jobId), from<Employee>(),
                           where(lesser_or_equal(
                               1,
                               select(count<JobHistory>(), where(is_equal(&Employee::id, &JobHistory::employeeId))))));

This behavior is known already but it makes many examples incorrect... your algorithm for serialization of the SQL string works very well except when there are subqueries -- then it is necessary to include an explicit from clause. You had told me the cure was worst than the disease.... but then the examples with subqueries should be updated..

Regards, Juan

[fnc12]

ok I'll update it. Thanks Juan

[fnc12]

it was fixed long time ago https://github.com/fnc12/sqlite_orm/blob/dev/examples/subquery.cpp#L1390 and I forgot to close the issue. Closing it now