QGIS » QGIS Application

To be sure I can reproduce exactly your scenario, can you please describe the steps starting from an unfiltered layer?

Sure.

1. I started with the MASSGIS TOWNS_POLY.shp dataset from https://docs.digital.mass.gov/dataset/massgis-data-community-boundaries-towns
2. I opened this shapefile in QGIS and saved it as a Spatialite datasource
3. I used SpatialLite -> New Connection to open the resulting data source
4. I double clicked on the "towns" table to add it to the project
5. I selected "Update SQL Layer..." after right clicking on the "towns" layer
6. I updated the query to look like:

   SELECT b.town as town, b.town_id as town_id, b.geometry as geometry 
   FROM 
   "towns" as a 
   join "towns" as b on b.town = 'AMHERST' or st_touches(a.geometry, b.geometry) 
   where 
   a.town = 'AMHERST'
   

7. I selected "Update" and closed the database manager
8. I once again selected "Update SQL Layer..." from the layer menu.
9. This resulted in the syntax error, and the query now looks like:

   (SELECT b.town as town, b.town_id as town_id, b.geometry as geometry 
   FROM 
   "towns" as a 
   join "towns" as b on b.town = 'AMHERST' or st_touches(a.geometry, b.geometry) 
   where a.town = 'AMHERST'
   
   )
   

I've noticed something interesting: if I keep the entire sql expression on a single line like this:

SELECT b.town as town, b.town_id as town_id, b.geometry as geometry FROM "towns" as a join "towns" as b on b.town = 'AMHERST' or st_touches(a.geometry, b.geometry) where a.town = 'AMHERST'

...then I don't get the error when subsequently editing it, and it never gets those extra parentheses.

Thanks for the information!
I think I know where's the issue, working on it today.