Opened 8 years ago

Closed 4 years ago

#3874 closed defect (wontfix)

Error: no such function: IF

Reported by: anonymous Owned by: rjollos
Priority: high Component: WikiStatsPlugin
Severity: normal Keywords: SQL specific syntax
Cc: martin_s, rjollos Trac Release: 0.11

Description (last modified by rjollos)

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 abalter

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

comment:3 Changed 8 years ago 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
                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 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' ''')

comment:5 Changed 8 years ago by rjollos

  • Cc ryano@… added

comment:6 Changed 7 years ago by anonymous

  • Cc rjollos 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 hasienda

  • 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 rjollos

  • Description modified (diff)
  • Owner changed from Rottenchester to rjollos

comment:10 Changed 5 years ago 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.

comment:11 Changed 4 years ago by rjollos

  • Resolution set to wontfix
  • Status changed from new to closed

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 Comment

Modify Ticket

as closed The owner will remain rjollos.
The resolution will be deleted. Next status will be 'reopened'.

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

Note: See TracTickets for help on using tickets.