missing FROM-clause entry for table "t_brunoais" at character 290

[chiara-paci]

Hi,

after enabling the readOtherTopics extension, some users are complaying about SQL errors when looking for messages, and PostgreSQL log contains many lines like these:

2021-01-09 12:42:30.032 CET [31427] phpbb@phpbb ERROR:  missing FROM-clause entry for table "t_brunoais" at character 290
2021-01-09 12:42:30.032 CET [31427] phpbb@phpbb STATEMENT:  SELECT COUNT(DISTINCT t.topic_id) as total_results FROM phpbb_topics t, phpbb_posts p
                                        WHERE p.poster_id = '93'
                                                                         AND ((p.forum_id NOT IN (1, 2, 17, 18, 21, 23, 24)
                                AND p.post_visibility = 1))
                                                                         AND p.forum_id NOT IN (1, 2, 17, 18, 21, 23, 24) AND t_brunoais.topic_id = p.topic_id  AND (t_brunoais.forum_id IN (3, 39, 38, 37, 11, 36,
 35, 34, 33, 31, 30, 6, 29, 28, 27, 26, 25, 22, 10, 20, 19, 12, 15, 13, 8, 9, 16, 7, 14, 5, 4)
                                                OR t_brunoais.topic_poster = 93 )
                                                                        AND t.topic_id = p.topic_id

2021-01-09 12:43:10.859 CET [31489] phpbb@phpbb ERROR:  missing FROM-clause entry for table "t_brunoais" at character 290
2021-01-09 12:43:10.859 CET [31489] phpbb@phpbb STATEMENT:  SELECT COUNT(DISTINCT t.topic_id) as total_results FROM phpbb_topics t, phpbb_posts p
                                                                WHERE p.poster_id = '93'
                                                                         AND ((p.forum_id NOT IN (1, 2, 17, 18, 21, 23, 24)
                                AND p.post_visibility = 1))

I suppose it's something in event/search_listener.php, but I'm not able to figure out how to fix.

Our platform is PhpBB 3.3.2, PHP 7.3.19, PostgreSQL 11.9.

[brunoais]

Do you know in which phpBB file (or my extension file) and the line of code in that file where that error appears?

[chiara-paci]

Postgres complains about a nonexistent t_brunoais table, which appears only in the file event/search_listener.php.

I suppose the problem is in method search_postgres_author_count_query_before, here (line 562):

...
  			if (sizeof($partial_read_access_fids) > 0)
			{
				// The filter has to be in place

				// Workaround for a mistake I made myself when making the event.
				$sql_sort_join = $event['sql_sort_join'];
				$sql_sort_join .= ' AND t_brunoais.topic_id = p.topic_id ';
				$event['sql_sort_join'] = $sql_sort_join;

				$sql_sort_table = $event['sql_sort_table'];
				$sql_sort_table .= TOPICS_TABLE . ' t_brunoais, ';
				$event['sql_sort_table'] = $sql_sort_table;


				$sql_fora = $event['sql_fora'];

				$sql_fora .= ' AND (' . $this->db->sql_in_set('t_brunoais.forum_id', $full_read_access_fids, false, true) . '
					OR t_brunoais.topic_poster = ' . (int) $this->user->data['user_id'] . ' )';

				$event['sql_fora'] = $sql_fora;

			}
...

and in following lines. Same lines appear in search_mysql_author_count_query_before and in search_native_author_count_query_before.

I wanted to fix it myself, but I miss the big picture and don't understand how to correct it.

[brunoais]

I also don't get what needs fixing without giving much time dedication to it.
I'll try to figure out what may be wrong.
The bad part is that I don't have a postgres DB in hand. Maybe I can install one and try to have phpBB to access it.

[chiara-paci]

In search_postgres_author_count_query_before:

                                // The filter has to be in place

				// Workaround for a mistake I made myself when making the event.
				$sql_sort_join = $event['sql_sort_join'];
				$sql_sort_join .= ' AND t_brunoais.topic_id = p.topic_id ';
				$event['sql_sort_join'] = $sql_sort_join;

				$sql_sort_table = $event['sql_sort_table'];
				$sql_sort_table .= TOPICS_TABLE . ' t_brunoais, ';
				$event['sql_sort_table'] = $sql_sort_table;

				$sql_fora = $event['sql_fora'];
				$sql_fora .= ' AND (' . $this->db->sql_in_set('t_brunoais.forum_id', $full_read_access_fids, false, true) . '
					OR t_brunoais.topic_poster = ' . (int) $this->user->data['user_id'] . ' )';

				$event['sql_fora'] = $sql_fora;

I suppose you define t_brunoais as alias of TOPICS_TABLE and manually join topics and posts table.

But in search_postgres_keywords_main_query_before you modify just the where condition (settting the join_table flag):

				// The filter has to be in place
				$event['join_topic'] = true;
				$sql_match_where = $event['sql_match_where'];
				$sql_match_where .= ' AND (' . $this->db->sql_in_set('t.forum_id', $full_read_access_fids, false, true) . '
					OR t.topic_poster = ' . (int) $this->user->data['user_id'] . ' )';
				$event['sql_match_where'] = $sql_match_where;

Could I try to use the same code on both methods?

[brunoais]

Thank you for spotting that. Please try so. Feel free to make a PR with that change for you to get the credit.

[chiara-paci]

I tried, but after editing the file the extension stopped working entirely: the protected forum topics are now visible to all.

[brunoais]

Not good. I will try gathering time to install postgres and set all up to use it to test

[brunoais]

I still haven't found time for this. I always get higher priority stuff to do. Maybe when (if?) I have holidays I can work on this...
Unfortunately, I no longer admin a phpBB bulletin board so my priorities have shifted...

[WeSuckLess]

Is any work still being done on this extension? I remember wanting to use it many moons ago (and I would still like to!) but I had search issues on the board as soon as the extension was activated - I am no coder and just came here because the word "search" :)

Thank you!

[brunoais]

Not in the last years, no.

My priorities has lead me to do way less here and the fact I'm no longer an admin of any phpBB board makes matters worse.

I'm responsive to any issues and PR that appears or comments but I don't really even have a proper dev setup in my system since I had to recover from a backup 2 years ago and still haven't redone the setup for phpBB development.

Given how my life has become WAY more busy, only after I'm done doing the urgent or important stuff I can come to this project again. (considering I started working on this when I was admin of 2 phpBB boards and the irony that I finished working on the first version of this at the exact time I stopped being admin of the last of the 2 boards... the irony...)

[WeSuckLess]

Understood! Thanks for taking some time to respond :)

I may test this again soon and I'll make sure to keep you informed of anything new that turns up...