Opened 13 years ago

Last modified 9 years ago

#3874 closed defect

Error: no such function: IF — at Version 9

Reported by: anonymous Owned by: Ryan J Ollos
Priority: high Component: WikiStatsPlugin
Severity: normal Keywords: SQL specific syntax
Cc: Martin Scharrer, Ryan J Ollos Trac Release: 0.11

Description (last modified by Ryan J Ollos)

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 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 (9)

comment:1 in reply to:  description Changed 13 years ago by anonymous

Sqlite doesn't have any if function.

There's some fix as seen at:

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
                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)...

comment:2 Changed 13 years ago by Ariel Balter

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

comment:3 Changed 13 years ago by Michael Ching

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
                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 '''

comment:4 Changed 13 years ago by Michael Ching

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' ''')

comment:5 Changed 13 years ago by Ryan J Ollos

Cc: ryano@… added

comment:6 Changed 12 years ago by anonymous

Cc: Ryan J Ollos added; ryano@… removed

comment:7 Changed 12 years ago 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

comment:8 Changed 10 years ago by Steffen Hoffmann

Keywords: SQL specific syntax added

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(), 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 ;-)

comment:9 Changed 10 years ago by Ryan J Ollos

Description: modified (diff)
Owner: changed from RottenChester to Ryan J Ollos
Note: See TracTickets for help on using tickets.