Welcome Guest, Not a member yet? Register   Sign In
MySQL 8 error on subselect parenthesis


I upgraded the database from MySQL 5.7 to 8.0 and now we have some query generated by CI query builder that can't be executed.

For example:
$this->db->from("(SELECT col1 FROM table) AS alias");

generates this query:
SELECT ... FROM ((SELECT col1 FROM table) AS alias)

And it worked with 5.7 but 8.0 doesn't like the exterior parenthesis. The query needs to be:
SELECT ... FROM (SELECT col1 FROM table) AS alias

What can I do?


I haven't tried it, but first thing that pops in mind is, if you are not using Query Builder functionality, maybe try

PHP Code:
$result $this->db->query("(SELECT col1 FROM table) AS alias"); 

This should run the SQL query text as is.

I use the query builder functionality, I simplified the example but the query is more complex and use ->select(), ->where(), ... If there's no other way I won't use the query builder but it's helps me to simplify the query construction.


Have you tried it this way (https://codeigniter.com/user_guide/datab...y-bindings) it allows you to write more complex queries.

Here is an example complex query that I wrote for another thread... https://forum.codeigniter.com/thread-708...#pid354392

Theme © iAndrew 2016 - Forum software by © MyBB