Opened 8 years ago

Closed 4 years ago

#3874 closed defect (wontfix)

Error: no such function: IF

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

Attachments (0)

Change History (11)

comment:1 in reply to:  description Changed 8 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 8 years ago by Ariel Balter

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

comment:3 Changed 8 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 8 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 8 years ago by Ryan J Ollos

Cc: ryano@… added

comment:6 Changed 8 years ago by anonymous

Cc: Ryan J Ollos added; ryano@… removed

comment:7 Changed 7 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 5 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 5 years ago by Ryan J Ollos

Description: modified (diff)
Owner: changed from RottenChester to Ryan J Ollos

comment:10 Changed 5 years ago by Steffen Hoffmann

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.

comment:11 Changed 4 years ago by Ryan J Ollos

Resolution: wontfix
Status: newclosed

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.

Modify Ticket

as closed The owner will remain Ryan J Ollos.
The resolution will be deleted. Next status will be 'reopened'.

Add Comment

E-mail address and name can be saved in the Preferences.

Note: See TracTickets for help on using tickets.