Hi,
I think I've encountered a bug when generating SQL for nested queries
in Oracle. The problem is a combination of the as_sql method and the
source_list method. I encountered the problem in 2.6.0, upgraded to
2.7.1 and found it still present, then checked Github and think its
still present there.
The code in question from dataset/sql.rb is shown below.
to_table_reference calls as_sql if an alias is supplied. The alias is
supplied by source_list when selecting from a Dataset. This generates
something like: ...FROM (sql) AS "T1". From what I can tell Oracle
doesn't support aliasing sub-queries with AS, you just provide the
alias E.g. ...FROM (sql) "T1"
1 def to_table_reference(table_alias=nil)
2 s = "(#{sql})"
3 table_alias ? as_sql(s, table_alias) : s
4 end
5
6 private
7
8 # SQL fragment for specifying an alias. expression should
already be literalized.
9 def as_sql(expression, aliaz)
10 "#{expression} AS #{quote_identifier(aliaz)}"
11 end
A contrived sample of reproducing this is:
DB[:tab].select(:tabtype).group_by(:tabtype).count
this produces the following SQL:
SELECT * FROM (SELECT COUNT(*) FROM (SELECT "TABTYPE" FROM "TAB" GROUP
BY "TABTYPE") AS "T1") WHERE ROWNUM <= 1
which raises this error: OCIError: ORA-00907: missing right parenthesis
Removing the AS before "T1" permits the query to run properly.
Hope this is a useful bug report and sorry for stepping trough the
code if you know that already.
Regards,
Wes
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sequel-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
