# Ticket #3874 (closed defect: wontfix)

Opened 5 years ago

## Error: no such function: IF

Reported by: Assigned to: anonymous rjollos high WikiStatsPlugin normal SQL specific syntax martin@scharrer-online.de, rjollos 0.11

By using trac 0.11.1 and WikiStatsPlugin I'm getting the error when using the Stats macro:

no such function: IF


I think it's from the SQL query in stats.py line 56-67:

SUM(IF(version = 1, 1, 0)) as pages_created,
SUM(IF(version = 1, 0, 1)) as pages_edited,


I tried to simple remove this two lines, then I get the error:

no such column: pages_created


## Change History

### (in reply to: ↑ description ) 10/17/08 14:29:43 changed by anonymous

Sqlite doesn't have any if function.

There's some fix as seen at: http://code.activestate.com/recipes/438802/

I've a small fix, using sign, but I got unexpected results. anyway, if you create the _sign function somewhere in the file (eg, near the begining, before the class definition), and replace the query by:

        db.create_function("sign", 1, _sign)
sql = '''SELECT
author,
SUM(1-abs(sign(version-1))) as pages_created,
SUM(abs(sign(version-1))) as pages_edited,
MIN(time) as first_edit,
MAX(time) as last_edit
FROM wiki '''


It seems to work (I go 0 edit and tons of creations, so I'm a bit doubtful about the result anyway)...

### 05/08/09 21:53:28 changed by abalter

I get the same problem. Looking forward to a real fix!

### 05/30/09 02:33:32 changed by mkc

SQLite does not support IF() syntax, but we should just be able to use a SELECT CASE to get the same result while continuing to work with other database backends.

In addition to the syntax error when using a SQLite backend, the output code refers to fields by their relative positions with row[1] showing up as "Edited" and row[2] as "Pages Created". I believe the order of these two fields should be swapped in the SELECT statement.

The following should work for the base statement:

        sql = '''SELECT
author,
SUM(CASE WHEN version = 1 THEN 0 ELSE 1 END) as pages_edited,
SUM(CASE WHEN version = 1 THEN 1 ELSE 0 END) as pages_created,
MIN(time) as first_edit,
MAX(time) as last_edit
FROM wiki '''


### 05/30/09 02:39:53 changed by mkc

Slightly related, for UserCount?() to work properly in SQLite, the double quoted string should be single quoted to avoid it being interpreted as the field name:

            cursor.execute('''SELECT count(*)
FROM session_attribute
WHERE name = 'name' ''')


### 05/30/09 03:17:17 changed by rjollos

• cc changed from martin@scharrer-online.de to martin@scharrer-online.de, ryano@physiosonics.com.

### 08/07/09 08:20:49 changed by anonymous

• cc changed from martin@scharrer-online.de, ryano@physiosonics.com to martin@scharrer-online.de, rjollos.

### 09/21/09 15:27:03 changed by anonymous

function if(boolean, integer, integer) does not exist LINE 3: SUM(IF(version = 1, 1, 0)) as pages_created,

HINT: No function matches the given name and argument types. You might need to add explicit type casts.

Database: Postresql

### 02/26/12 11:36:00 changed by hasienda

• keywords set to SQL specific syntax.

One of the misc no generic SQL issues.

Without looking into all details here, the single-best approach is generally to dump any special or even proprietary SQL specific to only 1 or 2 out of the three currently supported Trac db backends.

There's not much in the box with label SQL understood by any SQL db. So it boils down to very simple SQL (no SUM for that matter), some special functions from trac.db like db.cast(), db.like() or db.like_escape(), and the rest of processing must be done in the Python script itself.

Sure, that doesn't unleash the full power of some db backends, but this is unavoidable for the sake of compatibility. AMEN ;-)

### 03/02/12 02:22:29 changed by rjollos

• owner changed from Rottenchester to rjollos.
• description changed.

### 07/23/12 23:05:48 changed by hasienda

As mentioned before, this issue can't be fixed within the current SQL statements alone.

For a re-make inside of AccountManagerPlugin (see #9852) I'll postpone most of the processing from the db to the Python layer for compatibility. I'll reference this ticket, but for the reasons given before I suggest a wontfix within the boundaries of this plugin's code.

### 10/18/12 21:16:31 changed by rjollos

• status changed from new to closed.
• resolution set to wontfix.

This plugin is deprecated, and a notice has been posted to the WikiStatsPlugin page with more details. Please comment here if you have any concerns.

### Add/Change #3874 (Error: no such function: IF)

Change Properties