Solving the problem "ambiguous column name" in SQL-Queries

This is merely a historical archive of years 2008-2021, before the migration to mailman3.

A maintained and still updated list archive can be found at https://lists.osmocom.org/hyperkitty/list/OpenBSC@lists.osmocom.org/.

Luca Bertoncello bertoncello at netzing.de
Tue Jun 22 09:10:44 UTC 2010


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi, list!

I wrote an E-Mail on 18.06.2010, asking why many errors:

<0012> db.c:157 DBI: 1: ambiguous column name: id
<0012> db.c:157 DBI: 1: ambiguous column name: updated

appear (by sending, over Telnet, "sms send pending" and
"subscriber ... name ...").

Now I decided to search in the code for a reason.
There are two functions in db.c (db_sms_get_unsent_by_subscr [line 1055]
and get_equipment_by_subscr [line 301]) having queries with ambiguous
column name.

I suggest to correct the queries, adding the table name.
Unfortunately, I'm not sure, which names are correct...
I think, for db_sms_get_unsent_by_subscr it should be correct so:

        result = dbi_conn_queryf(conn,
                "SELECT * FROM SMS,Subscriber "
                "WHERE sms.receiver_id >= %llu AND sms.sent is NULL "
                        "AND sms.receiver_id = subscriber.id "
                        "AND subscriber.lac > 0 "
                "ORDER BY sms.receiver_id, sms.id LIMIT 1",
                min_subscr_id);

or better, using JOIN:

        result = dbi_conn_queryf(conn,
                "SELECT * FROM SMS JOIN Subscriber "
                "ON (SMS.received_id = Subscriber.id) "
                "WHERE sms.receiver_id >= %llu AND sms.sent is NULL "
                "AND subscriber.lac > 0 "
                "ORDER BY sms.receiver_id, sms.id LIMIT 1",
                min_subscr_id);

and for get_equipment_by_subscr so:

        result = dbi_conn_queryf(conn,
                "SELECT equipment.* FROM Equipment,EquipmentWatch "
                "WHERE EquipmentWatch.equipment_id=Equipment.id "
                "AND EquipmentWatch.subscriber_id = %llu "
                "ORDER BY Equipment.updated DESC", subscr->id);

or, with the JOIN:

        result = dbi_conn_queryf(conn,
                "SELECT equipment.* FROM Equipment JOIN EquipmentWatch "
                "ON (EquipmentWatch.equipment_id = Equipment.id) "
                "WHERE EquipmentWatch.subscriber_id = %llu "
                "ORDER BY Equipment.updated DESC", subscr->id);

but, as I said, I'm not sure...
Can someone confirm me, that I understood the queries correctly and my
correction proposals are right?
If they are right, I'll write a patch and submit it to the list.

I think, correcting this bug is important, since the queries with this
ambiguous column name will NOT be executed.

Thanks a lot
- -- 
_______________________________________________________________________
Luca Bertoncello
Entwicklung                               Mail:  bertoncello at netzing.de 


NETZING Solutions AG                      Tel.:  0351/41381 - 0
Kesselsdorfer Str. 216, 01169 Dresden     Fax:   0351/41381 - 12
HRB 18926 / Ust.ID DE211326547            Mail:  netzing.ag at netzing.de
_______________________________________________________________________
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFMIH4WAXzltVKV/2wRAr/mAJ9cQGOgl+ZTiutmehhS1aaeFegAmgCfWDxu
gUh/bQqXuK6PKdzWQ7gq6oo=
=GgRb
-----END PGP SIGNATURE-----


More information about the OpenBSC mailing list