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

-----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(a)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(a)netzing.de _______________________________________________________________________