Solved. The problem was that I was addressing 'single_value_field' without the context of the derived table. Changed: JOIN table2 ON (table2.id = single_value_field) To: JOIN table2 ON (table2.id = *new_table_alias*.single_value_field)
On Monday, May 13, 2019 at 6:14:08 PM UTC+3, Gilad Hoshmand wrote: > > Hi all, > > I built a join query which breaks up a field that has '|' char as > separator of values to multiple rows with single values (ie. field value = > '1|2|3' , rows will be multiplied with single_value_field = 1 , > single_value_field = 2 etc..) > Filters some rows ('Having ... ' ) > Then join with a table that I need to get values from. > > I tested it on mysql 5.7 and it works fine. > > I'm using db.executesql(...) to execute the query because I find it hard > to make with the DAL but I'm getting an error. > The query is: > > SELECT > * > FROM > (SELECT * , SUBSTRING_INDEX(SUBSTRING_INDEX(field1, '|', numbers.n), > '|', - 1) AS single_value_field > FROM > (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT > 4 UNION ALL SELECT 5) numbers > INNER JOIN table1 ON CHAR_LENGTH(field1) - CHAR_LENGTH(REPLACE(field1, > '|', '')) >= numbers.n - 1 > HAVING (field1 IS NOT NULL > AND field1 != '||' > AND single_value_field IS NOT NULL > AND single_value_field != '')) new_table_alias > JOIN > table2 ON (table2.id = single_value_field) > WHERE (table2.field2 = '' OR table2.field2 is null) > > > My Error arises on db.executesql(...) only when I include the last Join > clause: > JOIN > table2 ON (table2.id = single_value_field) > > If it is omitted - no problems. > > Full error(private bits renamed as in query): > > <class 'gluon.contrib.pymysql.err.Error'> (<type > 'exceptions.AssertionError'>, AssertionError('Protocol error, expecting > EOF',))Version > web2py™ Version 2.14.6-stable+timestamp.2016.05.10.00.21.47Traceback > > 1. > 2. > 3. > 4. > 5. > 6. > 7. > 8. > 9. > 10. > 11. > 12. > 13. > 14. > 15. > 16. > 17. > 18. > 19. > 20. > > Traceback (most recent call last): > File "/path/gluon/restricted.py", line 227, in restricted > exec ccode in environment > File " > <https://192.168.16.230:1234/admin/edit/StoreDot/controllers/automated_reports.py>/path/controllers/myController.py" > > <https://192.168.16.230:1234/admin/edit/StoreDot/controllers/automated_reports.py>, > line 93, in <module> > File "/path/gluon/globals.py", line 417, in <lambda> > self._caller = lambda f: f() > File "/path/controllers/myController.py" > <https://192.168.16.230:1234/admin/edit/StoreDot/controllers/automated_reports.py>, > line 15, in index > rows = db.executesql(query); > File "/path/gluon/packages/dal/pydal/base.py", line 1019, in executesql > adapter.execute(query) > File "/path/gluon/packages/dal/pydal/adapters/base.py", line 1388, in > execute > return self.log_execute(*a, **b) > File "/path/gluon/packages/dal/pydal/adapters/base.py", line 1382, in > log_execute > ret = self.get_cursor().execute(command, *a[1:], **b) > File "/path/gluon/contrib/pymysql/cursors.py", line 117, in execute > self.errorhandler(self, exc, value) > File "/path/gluon/contrib/pymysql/connections.py", line 200, in > defaulterrorhandler > raise Error(errorclass, errorvalue) > Error: (<type 'exceptions.AssertionError'>, AssertionError('Protocol error, > expecting EOF',)) > > > Thank you all! > -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/web2py/0a849aa9-95fa-4e1a-9d1c-33f13e75a548%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.