ORA-00904: "EMAIL": invalid identifier

[bliszewski]

Querying list subscribers against an Oracle database fails with ORA-00904: "EMAIL": invalid identifier.
This impacts most (all?) activities that query list subscribers including sending messages to the list.

This appears to be Oracle specific, where double-quotation marks are treated as a literal part of the column alias.

Version

6.2.56

Installation Method

RPM from RHEL 7 package repositories with Oracle database.

Expected behavior

Query should succeed and return list subscribers.

Actual behavior

Query fails, manifesting in several ways. The SQL error can be seen in the log but errors may not be reported to the user.

• Messages can be sent to the list, but are not deliver to subscribers. In the log it looks like the error is treated as the list having not subscribers, so other activities like archiving the message will work as expected.
• The web interface returns a correct Subscriber count for the list, but /review/ returns no results even when searching for an exact subscriber email. It can however find subscribers "like" the search term.
• sympa.pl --dump_users --list=my-list creates an empty members.dump.

Additional information

Sample excerpt from log (identified names and references have been chagned)

Oct  9 13:00:25 sympa-dev sympa_msg[2311]: err main::#243 > Sympa::Spindle::spin#95 > Sympa::Spindle::ToList::_twist#58 > Sympa::Spindle::ToList::_send_msg#245 > Sympa::List::get_recipients_per_mode#1450 > Sympa::List::get_first_list_member#2724 > Sympa::DatabaseDriver::Oracle::do_query#553 > Sympa::Database::do_query#244 Unable to prepare SQL statement SELECT bounce_subscriber AS "bounce", bounce_address_subscriber AS "bounce_address", bounce_score_subscriber AS "bounce_score", custom_attribute_subscriber AS "custom_attribute", date_epoch_subscriber AS "date", user_subscriber AS "email", suspend_end_date_subscriber AS "enddate", comment_subscriber AS "gecos", inclusion_subscriber AS "inclusion", inclusion_ext_subscriber AS "inclusion_ext", inclusion_label_subscriber AS "inclusion_label", number_messages_subscriber AS "number_messages", reception_subscriber AS "reception", suspend_start_date_subscriber AS "startdate", subscribed_subscriber AS "subscribed", suspend_subscriber AS "suspend", topics_subscriber AS "topics", update_epoch_subscriber AS "update_date", visibility_subscriber AS "visibility"#012          FROM subscriber_table#012          WHERE list_subscriber = 'my-list' AND robot_subscriber = 'sympa-dev.example.com'  ORDER BY email: (S1000) ORA-00904: "EMAIL": invalid identifier (DBD ERROR: error possibly near <*> indicator at char 902 in 'SELECT bounce_subscriber AS "bounce", bounce_address_subscriber AS "bounce_address", bounce_score_subscriber AS "bounce_score", custom_attribute_subscriber AS "custom_attribute", date_epoch_subscriber AS "date", user_subscriber AS "email", suspend_end_date_subscriber AS "enddate", comment_subscriber AS "gecos", inclusion_subscriber AS "inclusion", inclusion_ext_subscriber AS "inclusion_ext", inclusion_label_subscriber AS "inclusion_label", number_messages_subscriber AS "number_messages", reception_subscriber AS "reception", suspend_start_date_subscriber AS "startdate", subscribed_subscriber AS "subscribed", suspend_subscriber AS "suspend", topics_subscriber AS "topics", update_epoch_subscriber AS "update_date", visibility_subscriber AS "visibility"#012          FROM subscriber_table#012          WHERE list_subscriber = 'my-list' AND robot_subscriber = 'sympa-dev.example.com'  ORDER BY <*>email')
Oct  9 13:00:25 sympa-dev sympa_msg[2311]: err main::#243 > Sympa::Spindle::spin#95 > Sympa::Spindle::ToList::_twist#58 > Sympa::Spindle::ToList::_send_msg#245 > Sympa::List::get_recipients_per_mode#1450 > Sympa::List::get_first_list_member#2725 Unable to get members of list Sympa::List <my-list@sympa-dev.example.com>
Oct  9 13:00:25 sympa-dev sympa_msg[2311]: info Sympa::Spindle::ToList::_send_msg() No subscriber for sending msg in list Sympa::List <my-list@sympa-dev.example.com>

The issue appears to be between then lines:
user_subscriber AS "email" which creates the column alias as "email"
and
ORDER BY email which attempts to reference the column email (without quotes).

[ikedas]

Hi @bliszewski ,
Could you please apply this patch and check if the problem will be solved? Thanks.

[bliszewski]

Hello @ikedas,
Applied the patch and it appears to be successful!

List members are returned in the review page, can be dumped with sympa.pl, and can be sent to.

[ikedas]

@bliszewski , thank you for reporting bug and confirming fix! Maybe mixed version will be released in this month.