SQL syntax error while using Limit with UnionAll in MySQL

[Sanjay85500]

I'm using UnionAll with Limit and using MySQL compiler to get SqlResult, while I'm executing the command it is throwing SQL syntax error. Below is the code var query = new Query("table1").UnionAll(q => q.From("table2")).Limit(10); var sql = new MySqlCompiler().Compile(query); sql result is SELECT * FROM table1 LIMIT 10 UNION ALL SELECT * FROM table2

but the syntax should be (SELECT * FROM table1 LIMIT 10) UNION ALL (SELECT * FROM table2)

[ahmad-moussawi]

For ref: https://stackoverflow.com/questions/1415328/combining-union-and-limit-operations-in-mysql-query

as a workaround for now, you can wrap the original query in a subquery, it's not identical but same result

var query = new Query().From(
    new Query("table1").Limit(10).As("sub")
).UnionAll(q => q.From("table2"));

Check https://sqlkata.com/playground/mysql?code=var%20query%20%3D%20new%20Query().From(new%20Query(%22table1%22).Limit(10).As(%22sub%22)).UnionAll(q%20%3D%3E%20q.From(%22table2%22))%3B

[Sanjay85500]

Hi, @ahmad-moussawi thanks for the above comment it works for me, according to this docs it should give a proper syntax maybe there is an issue with SqlKata.

[ahmad-moussawi]

Yes indeed

[ahmad-moussawi]

Why did you close it :-)? It's a valid case and should be fixed, I will reopen it

[ahmad-moussawi]

I am rethinking about this, and concluding that this should be the expected result, since if we are instructing the QueryBuilder to build a wrong query, it should not correct it itself. So I will close this one, but if you have another opinion, I am open for suggestions