Details
-
Type:
Bug
-
Status:
Closed
-
Priority:
Major
-
Resolution: Duplicate
-
Affects Version/s: 1.7.7, 1.8.8, 1.9.4
-
Component/s: Database activity module
-
Labels:None
-
Environment:PostgreSQL 8.x
-
Database:PostgreSQL
-
Affected Branches:MOODLE_17_STABLE, MOODLE_18_STABLE, MOODLE_19_STABLE
-
Fixed Branches:MOODLE_17_STABLE, MOODLE_18_STABLE, MOODLE_19_STABLE
Description
Data(base) module autolinking filter tries to compare an integer value an an text field, which of course fails in Postgres.
A patch (for 1.9.x) is attached.
Error and stack trace:
====
ERROR: operator does not exist: text = integer LINE 1: ...D dr.id = dc.recordid AND df.type = 'text' AND df.param1 = 1 ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
SELECT dc.id AS contentid, dr.id AS recordid, dc.content AS content, d.id AS dataid FROM mdl_data d, mdl_data_fields df, mdl_data_records dr, mdl_data_content dc WHERE (d.course = '9' or d.course = '1')AND d.id = df.dataid AND df.id = dc.fieldid AND d.id = dr.dataid AND dr.id = dc.recordid AND df.type = 'text' AND df.param1 = 1
- line 692 of lib/dmllib.php: call to debugging()
- line 972 of lib/dmllib.php: call to get_recordset_sql()
- line 44 of mod/data/filter.php: call to get_records_sql()
- line 1785 of lib/weblib.php: call to data_filter()
- line 1535 of lib/weblib.php: call to filter_text()
- line 31 of blocks/html/block_html.php: call to format_text()
- line 317 of blocks/moodleblock.class.php: call to block_html->get_content()
- line 240 of lib/blocklib.php: call to block_base->is_empty()
- line 79 of course/format/topics/format.php: call to blocks_have_content()
- line 226 of course/view.php: call to require()
Attachments
Issue Links
| This issue duplicates: | ||||
| MDL-18243 | MSSQL doesn't allow text or ntext columns in WHERE clause |
|
|
|
Asigning to me and addressing to 1.8.8 and 1.9.4.
Proper solution to this problem isn't as simple as commented in the patch (that is ok only for MySQL and PostgreSQL). Problem is that "TEXT" columns cannot be compared properly using = under MSSQL / Oracle so we need to, alternatively:
1) sql_cast_char2int(): Cast all the values in param1 to integer: Requires all the values being integers or CAST will break.
2) Use sql_compare_text() that should be safe under all DBs to compare TEXTS. Slower under MSSQL and Oracle.
In this case, it seems that the param1 column can contain any type of values (integers, empry strings, NULLs...)... so I'll try the second approach (use sql_compare_text() )
Thanks for report!