Bug #56687 for Fey: Except clause in SQLite

Sat Apr 17 10:06:15 2010 bitcard [...] iijo.org - Ticket created

Subject:

Except clause in SQLite

Fey generates a query that looks like: (select_clause_A) EXCEPT (select_clause_B) Running this on SQLite 3.6.10, I get the following error: SQL Error: near "(": syntax error SQLite will only accept: select_clause_A EXCEPT select_clause_B I think Fey generates similar sql for union and intersect, and again SQLite doesn't like the parenthesis for those statements (see the 'compound operater'): http://www.sqlite.org/lang_select.html Unfortunately I think MySQL does require parenthesis if you use an 'order by' clause: http://dev.mysql.com/doc/refman/5.0/en/union.html

Fri May 21 23:43:11 2010 DROLSKY [...] cpan.org - Correspondence added

On Sat Apr 17 10:06:15 2010, bitcard@iijo.org wrote: Show quoted text

> Fey generates a query that looks like: > > (select_clause_A) EXCEPT (select_clause_B) > > Running this on SQLite 3.6.10, I get the following error: > > SQL Error: near "(": syntax error > > SQLite will only accept: > > select_clause_A EXCEPT select_clause_B > > I think Fey generates similar sql for union and intersect, and again > SQLite doesn't like the parenthesis for those statements (see the > 'compound operater'): > http://www.sqlite.org/lang_select.html > > Unfortunately I think MySQL does require parenthesis if you use an > 'order by' clause: > http://dev.mysql.com/doc/refman/5.0/en/union.html

If one form or the other is standard, that's one Fey should generate. One way to fix this would be to release a Fey::SQL::SQLite which provided some SQLite specific SQL generation tweaks.

Fri May 21 23:43:14 2010 The RT System itself - Status changed from 'new' to 'open'

Tue Jan 11 00:34:17 2011 bitcard [...] iijo.org - Correspondence added

From:

bitcard [...] iijo.org

I looked into this some more today. Apparently the MySQL docs I linked to previously are just *suggesting* parens. They aren't actually *required*. I tested it out on MySQL and PostgreSQL and verified parens are not required. So this bug became a lot easier -- lets just remove the parens. Note that MySQL allows you to order and limit each clause of a union/except/intersect. This is apparently not part of the SQL standard according to the link below. However, if you remove to the parens from Fey, these types of queries will no longer work even though they did in the past. Also FYI, the link below seems to indicate that the SQL standard allows parens, but doesn't require them. http://stackoverflow.com/questions/4653124/what-does-the-sql-standard-say-about- parentheses-in-unions On Fri May 21 23:43:11 2010, DROLSKY wrote: Show quoted text

> On Sat Apr 17 10:06:15 2010, bitcard@iijo.org wrote:

> > Fey generates a query that looks like: > > > > (select_clause_A) EXCEPT (select_clause_B) > > > > Running this on SQLite 3.6.10, I get the following error: > > > > SQL Error: near "(": syntax error > > > > SQLite will only accept: > > > > select_clause_A EXCEPT select_clause_B > > > > I think Fey generates similar sql for union and intersect, and again > > SQLite doesn't like the parenthesis for those statements (see the > > 'compound operater'): > > http://www.sqlite.org/lang_select.html > > > > Unfortunately I think MySQL does require parenthesis if you use an > > 'order by' clause: > > http://dev.mysql.com/doc/refman/5.0/en/union.html

> > If one form or the other is standard, that's one Fey should generate. > > One way to fix this would be to release a Fey::SQL::SQLite which > provided some SQLite specific SQL generation tweaks.

Tue Jan 11 01:45:28 2011 bitcard [...] iijo.org - Correspondence added

From:

bitcard [...] iijo.org

I made the change to remove parens on github real quick by forking Fey from gitpan. But no matter what I click, it tries to make me submit the pull request to gitpan instead of autarch. I guess I don't understand the work flow for this kind of thing. Let me know if there is anything I can do to help out. https://github.com/kablamo/Fey On Tue Jan 11 00:34:17 2011, bitcard@iijo.org wrote: Show quoted text

> I looked into this some more today. Apparently the MySQL docs I > linked to > previously are just *suggesting* parens. They aren't actually > *required*. I > tested it out on MySQL and PostgreSQL and verified parens are not > required. So > this bug became a lot easier -- lets just remove the parens. > > Note that MySQL allows you to order and limit each clause of a > union/except/intersect. This is apparently not part of the SQL > standard according > to the link below. However, if you remove to the parens from Fey, > these types of > queries will no longer work even though they did in the past. > > Also FYI, the link below seems to indicate that the SQL standard > allows parens, > but doesn't require them. > > http://stackoverflow.com/questions/4653124/what-does-the-sql-standard- > say-about- > parentheses-in-unions > > > > On Fri May 21 23:43:11 2010, DROLSKY wrote:

> > On Sat Apr 17 10:06:15 2010, bitcard@iijo.org wrote:

> > > Fey generates a query that looks like: > > > > > > (select_clause_A) EXCEPT (select_clause_B) > > > > > > Running this on SQLite 3.6.10, I get the following error: > > > > > > SQL Error: near "(": syntax error > > > > > > SQLite will only accept: > > > > > > select_clause_A EXCEPT select_clause_B > > > > > > I think Fey generates similar sql for union and intersect, and

> again

> > > SQLite doesn't like the parenthesis for those statements (see the > > > 'compound operater'): > > > http://www.sqlite.org/lang_select.html > > > > > > Unfortunately I think MySQL does require parenthesis if you use an > > > 'order by' clause: > > > http://dev.mysql.com/doc/refman/5.0/en/union.html

> > > > If one form or the other is standard, that's one Fey should

> generate.

> > > > One way to fix this would be to release a Fey::SQL::SQLite which > > provided some SQLite specific SQL generation tweaks.