After an evaluation, GNOME has moved from Bugzilla to GitLab. Learn more about GitLab.
No new issues can be reported in GNOME Bugzilla anymore.
To report an issue in a GNOME project, go to GNOME GitLab.
Do not go to GNOME Gitlab for: Bluefish, Doxygen, GnuCash, GStreamer, java-gnome, LDTP, NetworkManager, Tomboy.
Bug 661073 - SQL Errors when a relationship name has capital letters
SQL Errors when a relationship name has capital letters
Status: RESOLVED FIXED
Product: glom
Classification: Other
Component: data mode
git master
Other Linux
: Normal normal
: ---
Assigned To: Murray Cumming
Murray Cumming
Depends on: 661164
Blocks:
 
 
Reported: 2011-10-06 11:31 UTC by Murray Cumming
Modified: 2011-10-27 09:27 UTC
See Also:
GNOME target: ---
GNOME version: ---


Description Murray Cumming 2011-10-06 11:31:38 UTC 
If a relationship name (not the title) has capital letters, things go wrong.

For instance, I saw this on the terminal until I renamed the relationship to use all lowercase:

Glib::RefPtr<Gnome::Gda::DataModel> Glom::DbUtils::query_execute_select(const Glib::RefPtr<const Gnome::Gda::SqlBuilder>&, bool): Error while executing SQL: 
  SELECT scene_costume.scene_costume_id, scene_costume.comments, scene_costume.scene_id, scene_costume.costume_id, relationship_Costume.name FROM scene_costume LEFT JOIN costume AS relationship_Costume ON ((scene_costume.costume_id = relationship_Costume.costume_id))  WHERE (scene_costume.scene_costume_id = 0)

static bool Glom::ConnectionPool::handle_error_cerr_only(): Internal error (Database): missing FROM-clause entry for table "relationship_Costume"
LINE 1: ...nship_Costume ON (("scene_costume"."costume_id" = "relations...
Comment 1 Murray Cumming 2011-10-07 08:47:35 UTC 
Using --debug_sql:

A SQL query with a lowercase relationship name:

SELECT songs.songs_id, songs.name, songs.album_id, relationship_album.name, songs.comments FROM songs LEFT JOIN albums AS relationship_album ON ((songs.album_id = relationship_album.album_id))  WHERE (songs.songs_id = 0)

And the one that fails when renaming the relationship to have a capital letter:

SELECT songs.songs_id, songs.name, songs.album_id, relationship_Album.name, songs.comments FROM songs LEFT JOIN albums AS relationship_Album ON ((songs.album_id = relationship_Album.album_id))  WHERE (songs.songs_id = 0)

So there is otherwise no change in the generated SQL. However, shouldn't all these field names and relationship names be quoted?
Comment 2 Murray Cumming 2011-10-07 08:51:38 UTC 
The second query (with the capital Albums) causes no problem in psql (well, pgadmin).
Comment 3 Murray Cumming 2011-10-07 09:14:26 UTC 
This is the query as generated inside libgda's postgres provider by gda_postgres_provider_statement_to_sql().

(Using ALBUMS instead of Albums for the relationship name. Ignore that.)

SELECT "songs"."songs_id", "songs"."Name", "songs"."album_id", "relationship_ALBUMS"."name", "songs"."comments" FROM "songs" LEFT JOIN "albums" AS relationship_ALBUMS ON (("songs"."album_id" = "relationship_ALBUMS"."album_id"))  WHERE ("songs"."songs_id" = 0)

pgadmin also rejects that, because one use of it is not quoted. This one works:


SELECT "songs"."songs_id", "songs"."Name", "songs"."album_id", "relationship_ALBUMS"."name", "songs"."comments" FROM "songs" LEFT JOIN "albums" AS "relationship_ALBUMS" ON (("songs"."album_id" = "relationship_ALBUMS"."album_id"))  WHERE ("songs"."songs_id" = 0)
Comment 4 Murray Cumming 2011-10-07 10:11:42 UTC 
This is caused by libgda bug #661164
Comment 5 Murray Cumming 2011-10-15 19:53:12 UTC 
This is fixed by the latest code in libgda master, but I will keep this bug open until there is a libgda tarball release version that we can depend on to avoid this in Glom.
Comment 6 Murray Cumming 2011-10-27 09:27:13 UTC 
Glom 1.19.x (git master) now depends on libgda 5.0.0, which fixes this problem.