Details
-
Type:
Sub-task
-
Status:
Closed
-
Priority:
Minor
-
Resolution: Fixed
-
Affects Version/s: 2.0
-
Fix Version/s: 2.0
-
Component/s: Database SQL/XMLDB
-
Labels:None
-
Affected Branches:MOODLE_20_STABLE
-
Fixed Branches:MOODLE_20_STABLE
Description
From the changelog of sqlite version 3.6.0 beta:
"The result column names generated for compound subqueries have been simplified to show only the name of the column of the original table and omit the table name. This makes SQLite operate more like other SQL database engines."
This might be where I was seeing inconsistent behaviour where subquery column name return naming
Attachments
Activity
Hide
Eloy Lafuente (stronk7)
added a comment -
Oh sorry Dan. I read this some days ago but (not sure why) forgot to comment on it 
Feel free to add all sort of complicated queries there! Thanks!
Ciao 
Show
Eloy Lafuente (stronk7)
added a comment - Oh sorry Dan. I read this some days ago but (not sure why) forgot to comment on it
Feel free to add all sort of complicated queries there! Thanks!
Ciao
Feel free to add all sort of complicated queries there! Thanks!
Ciao 
It seems that > 3.6.0 does seem to be the version where it works consistently. These two queries were run on the exact same database, the one on 3.5.9 fails:
SQLite version 3.5.9
Enter ".help" for instructions
sqlite> SELECT parent_states.filter, CASE WHEN fa.active IS NULL THEN 0 ELSE fa.active END AS localstate, parent_states.inheritedstate FROM (SELECT f.filter, MAX(f.sortorder) AS sortorder, CASE WHEN MAX(f.active * ctx.depth ) > -MIN(f.active * ctx.depth ) THEN 1 ELSE -1 END AS inheritedstate FROM mdl_filter_active f JOIN mdl_context ctx ON f.contextid = ctx.id WHERE ctx.id IN (1,3) GROUP BY f.filter HAVING MIN(f.active) > -9999 ) parent_states LEFT JOIN mdl_filter_active fa ON fa.filter = parent_states.filter AND fa.contextid = 63 ORDER BY parent_states.sortorder;
SQL error: no such column: parent_states.filter
Where 3.6.14.2 works fine:
SQLite version 3.6.14.2
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT parent_states.filter, CASE WHEN fa.active IS NULL THEN 0 ELSE fa.active END AS localstate, parent_states.inheritedstate FROM (SELECT f.filter, MAX(f.sortorder) AS sortorder, CASE WHEN MAX(f.active * ctx.depth ) > -MIN(f.active * ctx.depth ) THEN 1 ELSE -1 END AS inheritedstate FROM mdl_filter_active f JOIN mdl_context ctx ON f.contextid = ctx.id WHERE ctx.id IN (1,3) GROUP BY f.filter HAVING MIN(f.active) > -9999 ) parent_states LEFT JOIN mdl_filter_active fa ON fa.filter = parent_states.filter AND fa.contextid = 63 ORDER BY parent_states.sortorder;