Your problem has nothing to do with CTEs, you are combining implicit (SQL-89) joins and explicit (SQL-92) joins, and referencing the implicitly joined table from the explicitly joined tables. The rules for these have changed in Firebird 3 to be compliant with the SQL specification and to avoid hard to diagnose bugs.
See the Firebird 3 release notes: Support for Mixed-Syntax Joins is Gone : https://www.firebirdsql.org/file/documentation/release_notes/html/en/3_0/rnfb30-compat-sql.html#rnfb30-compat-sql_psql-nomixedjoins Specifically, you need to change: > from prj_task_dependencies , sucessoras , prj_tasks t > > inner join prj_tasks t_req on t_req.task_id = > prj_task_dependencies.dependencies_req_task_id > > and t_req.task_status = 0 > > inner join prj_tasks t_dep on t_dep.task_id = > prj_task_dependencies.dependencies_task_id > > and t_dep.task_status = 0 > > where prj_task_dependencies.dependencies_task_id = > sucessoras.task_id > > and t.task_id = sucessoras.task_id to from prj_task_dependencies inner join sucessoras on prj_task_dependencies.dependencies_task_id = sucessoras.task_id inner join prj_tasks t on t.task_id = sucessoras.task_id inner join prj_tasks t_req on t_req.task_id = prj_task_dependencies.dependencies_req_task_id and t_req.task_status = 0 inner join inner join prj_tasks t_dep on t_dep.task_id = prj_task_dependencies.dependencies_task_id and t_dep.task_status = 0 Alternatively, you could make all these joins implicit: from prj_task_dependencies , sucessoras , prj_tasks t, prj_tasks t_req, prj_tasks t_dep where prj_task_dependencies.dependencies_task_id = sucessoras.task_id and t.task_id = sucessoras.task_id and t_req.task_id = prj_task_dependencies.dependencies_req_task_id and t_req.task_status = 0 and t_dep.task_id = prj_task_dependencies.dependencies_task_id and t_dep.task_status = 0 But personally I find that extremely hard to read, and really prefer the SQL-92 explicit joins. Mark On 2018-02-01 17:25, Rudi Feijó [email protected] [firebird-support] wrote: > Good afternoon. > I’ve been assigned to debug a query that was working on 2.5 but stopped > workin on 3.0. > > > > As of now I have little information on the context of where this query > is > used, but I’m assuming it might be something simple. > > > > Apparently what’s causing the error is using this FROM sintax with > multiple > tables separated by comma (FROM tablea, tableb, tablec). > > I’m assuming that because the error is always thrown on the line > subsequent > to the “from” line > > > Was there any change to this type of from syntax in firebird 3.0? > Below is the query. If needed I can create a test gdb with data. > > Thanks in advance > > > > > > > > with recursive sucessoras (task_id) as > > ( > > select prj_task_dependencies.dependencies_task_id as > task_id > > from prj_task_dependencies > > inner join prj_tasks t_req on t_req.task_id = > prj_task_dependencies.dependencies_req_task_id > > and t_req.task_status = 0 > > inner join prj_tasks t_dep on t_dep.task_id = > prj_task_dependencies.dependencies_task_id > > and t_dep.task_status = 0 > > where prj_task_dependencies.dependencies_req_task_id = 98 > > union all > > select > > prj_task_dependencies.dependencies_task_id as task_id > > from prj_task_dependencies, sucessoras > > inner join prj_tasks t_req on t_req.task_id = > prj_task_dependencies.dependencies_req_task_id > > and t_req.task_status = 0 > > inner join prj_tasks t_dep on t_dep.task_id = > prj_task_dependencies.dependencies_task_id > > and t_dep.task_status = 0 > > where prj_task_dependencies.dependencies_req_task_id = > sucessoras.task_id > > ) > > > > select > > > > distinct(sucessoras.task_id) , > > t.idbpo_proc, > > t.task_name, > > t.task_project as idclienteprojeto, > > t.task_constraint, > > t.task_constraint_date, > > t.TASK_BASELINE_DURATION as THIS_TASK_BASELINE_DURATION, > > t.TASK_BASELINE_DURATION_D as THIS_TASK_BASELINE_DURATION_D, > > t.TASK_DURATION_TYPE as THIS_TASK_DURATION_TYPE, > > t.task_baseline_start as THIS_TASK_BASELINE_START, > > t.task_baseline_end as THIS_TASK_BASELINE_END, > > > > cast(t.task_baseline_start as time) as > THIS_TIME_TASK_BASELINE_START, > > cast(t.task_baseline_end as time) as > THIS_TIME_TASK_BASELINE_END, > > > > t.recorrencia_semanal_domingo, > > t.recorrencia_semanal_segunda, > > t.recorrencia_semanal_terca, > > t.recorrencia_semanal_quarta, > > t.recorrencia_semanal_quinta, > > t.recorrencia_semanal_sexta, > > t.recorrencia_semanal_sabado, > > > > (select max(t_req.task_baseline_end) > > from prj_task_dependencies td > > inner join prj_tasks t_req on t_req.task_id = > td.dependencies_req_task_id > > and t_req.task_status = 0 > > inner join prj_tasks t_dep on t_dep.task_id = > td.dependencies_task_id > > and t_dep.task_status = 0 > > where td.dependencies_task_id = sucessoras.task_id) as > PRED_TASK_BASELINE_END > > > > from prj_task_dependencies , sucessoras , prj_tasks t > > inner join prj_tasks t_req on t_req.task_id = > prj_task_dependencies.dependencies_req_task_id > > and t_req.task_status = 0 > > inner join prj_tasks t_dep on t_dep.task_id = > prj_task_dependencies.dependencies_task_id > > and t_dep.task_status = 0 > > where prj_task_dependencies.dependencies_task_id = > sucessoras.task_id > > and t.task_id = sucessoras.task_id
