Report execution failed: near "as": syntax error

[Andriy Tsymbala]

I've installed the plugin on trac 0.10.2 (sqlite) but getting this error when trying to open any report:

Report execution failed: near "as": syntax error

Plugin was downloaded from ​here

[Russ Tyndall]

We have been working on the reports (this morning), you might want to try reinstalling, and trac upgrading.

Are you having problems with all of the reports?

I have the most recent version of the plugin installed and working with sqlite and trac 10.1 (nothing I know of should have changed to make 10.2 incompatible).

If you are still having trouble after the upgrade, please post the sql of one of the reports that is failing, so I can compare it to the reports I have installed. (You can get this by clicking the edit button on the report screen for the report that is failing.

Also, what version of sqlite do you have installed?

Thanks for the ticket, Russ

[anonymous]

Yes, the error is appearing in all reports.

I tried to upgrade the plugin - doesn't help :(

I'm using sqlite 2.8.16

Here is SQL for "Ticket Hours" report:

SELECT __color__, __style__, ticket, summary, component ,version, severity,
  milestone, status, owner, Estimated_work, Total_work, billable,_ord
FROM (
  SELECT p.value AS __color__,
    '' as __style__,
    t.id AS ticket, summary AS summary,             -- ## Break line here
    component,version, severity, milestone, status, owner,
    CAST(EstimatedHours.value as REAL) as Estimated_work,
    CAST(totalhours.value as REAL) as Total_work, 
    CASE WHEN billable.value = 1 THEN 'Y' else 'N' END as billable,
    time AS created, changetime AS modified,         -- ## Dates are formatted
    description AS _description_,                    -- ## Uses a full row
    changetime AS _changetime,
    reporter AS _reporter
    ,0 as _ord                                        
  	
    FROM ticket as t
    JOIN enum as p ON p.name=t.priority AND p.type='priority'
    
  LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours'
        AND EstimatedHours.Ticket = t.Id
  LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours'
        AND totalhours.Ticket = t.Id
  LEFT JOIN ticket_custom as billable ON billable.name='billable'
        AND billable.Ticket = t.Id
  
    WHERE t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED) 
      AND billable.value in ($BILLABLE, $UNBILLABLE)
    
  
  UNION 
  
  SELECT '1' AS __color__,
         'background-color:#DFE;' as __style__,
         NULL as ticket, 'Total' AS summary,             
         NULL as component,NULL as version, NULL as severity, NULL as  milestone, NULL as status, NULL as owner,
         SUM(CAST(EstimatedHours.value as real)) as Estimated_work,
         SUM(CAST(totalhours.value as real)) as Total_work,
         NULL as billable,
         NULL as created, NULL as modified,         -- ## Dates are formatted
  
         NULL AS _description_,
         NULL AS _changetime,
         NULL AS _reporter
         ,1 as _ord
    FROM ticket as t
    JOIN enum as p ON p.name=t.priority AND p.type='priority'
    
  LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours'
        AND EstimatedHours.Ticket = t.Id
  
  LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours'
        AND totalhours.Ticket = t.Id
  
  LEFT JOIN ticket_custom as billable ON billable.name='billable'
        AND billable.Ticket = t.Id
    
    WHERE t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED) 
      AND billable.value in ($BILLABLE, $UNBILLABLE)
)  as tbl
ORDER BY  _ord ASC, ticket
    

[Andriy Tsymbala]

Also I've upgraded db to sqlite 3.x - still have the same error :(

[Andriy Tsymbala]

I think I found the problem. We are using sqlite 3.2.1, but only after 3.2.3 it supports CAST function. If i remove it from SQL it works ok.

My question is: will it cause any problems if I'll remove all CAST occurrences from SQLs?

Thanks in advance!

[russ]

It will not cause problems for sqlite, but it will cause problems for postgres (If you ever use that as the backend. There did not used to be casts anywhere because sqlite is dynamically typed. However, in the process of trying to make the reports work in other backends, We recently added many types.

The only problem I would see with this, is if you upgrade the plugin, and I have updated the reports, Your changes will probably be overwritten. You could prevent this by setting version value in the report_version table to be a very large number.

[anonymous]

Hello guys, i have the same problem:(. Today i've upgraded from 0.9.6 to 0.10.2 and setup these eggs : timingandestimationplugin-0.2.8-py2.3.egg TracBurndown-01.04.10-py2.3.egg

sqlite 3.3.6 . By pressing Billing and Estimations->Ticket Hours i have a Report execution failed: near "as": syntax error.

What solution? if it possible step by step.thanx in advance! Great stuff for management

[Russ Tyndall]

One thing I noticed was that for some reason the comments on the reports were wrapped when I pasted it in. You might try checking that and /or removing the comments. The one I had problems with was

-- ## Dates are formatted

I would say just look at the reports and see if you can notice anything funny (you should be able to edit them from the screen with the error message. I am not sure what is causing this as fresh installs worked for me. Maybe something strange in trac 10.2?

Please let me know what you find, Russ

[Konstantin]

Nop, i've just removed all coments but it's not happens:(...Am i lonely in this problem?:(

[Russ Tyndall]

hmmm, can you please paste in the contents of one of the reports as a comment. This way I can try changing my report to what you have and possibly be able to duplicate this error.

Thanks, Russ

[anonymous]

Here the source that doesn't work

SELECT __color__, __style__, ticket, summary, component ,version, severity,
  milestone, status, owner, Estimated_work, Total_work, billable,_ord
FROM (
  SELECT p.value AS __color__,
    '' as __style__,
    t.id AS ticket, summary AS summary,             
    component,version, severity, milestone, status, owner,
    CAST(EstimatedHours.value as REAL) as Estimated_work,
    CAST(totalhours.value as REAL) as Total_work, 
    CASE WHEN billable.value = 1 THEN 'Y' else 'N' END as billable,
    time AS created, changetime AS modified,         
    description AS _description_,                    
    changetime AS _changetime,
    reporter AS _reporter
    ,0 as _ord                                        
  	
    FROM ticket as t
    JOIN enum as p ON p.name=t.priority AND p.type='priority'
    
  LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours'
        AND EstimatedHours.Ticket = t.Id
  LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours'
        AND totalhours.Ticket = t.Id
  LEFT JOIN ticket_custom as billable ON billable.name='billable'
        AND billable.Ticket = t.Id
  
    WHERE t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED) 
      AND billable.value in ($BILLABLE, $UNBILLABLE)
    
  
  UNION 
  
  SELECT '1' AS __color__,
         'background-color:#DFE;' as __style__,
         NULL as ticket, 'Total' AS summary,             
         NULL as component,NULL as version, NULL as severity, NULL as  milestone, NULL as status, NULL as owner,
         SUM(CAST(EstimatedHours.value as real)) as Estimated_work,
         SUM(CAST(totalhours.value as real)) as Total_work,
         NULL as billable,
         NULL as created, NULL as modified,         
  
         NULL AS _description_,
         NULL AS _changetime,
         NULL AS _reporter
         ,1 as _ord
    FROM ticket as t
    JOIN enum as p ON p.name=t.priority AND p.type='priority'
    
  LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours'
        AND EstimatedHours.Ticket = t.Id
  
  LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours'
        AND totalhours.Ticket = t.Id
  
  LEFT JOIN ticket_custom as billable ON billable.name='billable'
        AND billable.Ticket = t.Id
    
    WHERE t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED) 
      AND billable.value in ($BILLABLE, $UNBILLABLE)
)  as tbl
ORDER BY  _ord ASC, ticket
    

[Russ Tyndall]

Sorry it took so long to respond, I apparently missed the notification that you had responded.

I pasted this into my running version of trac with the plugin and it works. Something else must be causing the problem. Let me try upgrading to trac 10.2 and see if that starts giving me the problem.

Russ

[Russ Tyndall]

I just upgraded to svn revision 4303 (per the milestones page as to what revision trac 10.2 is). ​http://trac.edgewall.org/milestone/0.10.2

With this version of trac, version 3.3.8 of sqlite, and head revision of this plugin, everything works for me.

I hope this helps, Russ

PS: upgrading to trac 10.2 introduced a bug dealing with the links, so I will be upgrading to trac head to see if that is fixed. and other wise, writing some code to fix this.

[Russ Tyndall]

I am closing this ticket, feel free to reopen if you continue to have this problem

[anonymous]

I have trac 10.3 sqlite 3.3.8 svn 1.4.2 time tracking plugin 0.3.4

Everything works fine in svn through tortoise and on the server. Trac is fine with apache. Trac works fine accessing svn. This is the only problem i come across. It seems as before there wasn't really a direct fix but simply upgrade to certain versions and it should work, i am at the latest versions of everything and it is not working.

Is there a problem in the report sql? Is there a problem that i have 10.3 and the last person said they have 10.2.

Thank you bryan

Here is my report sql but i'm sure it is the same as above.

SELECT __ticket__ as __group__, __style__, __ticket__,
newvalue as Work_added, author, time, _ord
FROM(
  SELECT '' as __style__, author, t.id as __ticket__,
  CAST(newvalue as REAL) as newvalue, ticket_change.time as time, 0 as _ord
  FROM ticket_change
  JOIN ticket t on t.id = ticket_change.ticket
  LEFT JOIN ticket_custom as billable on billable.ticket = t.id 
    and billable.name = 'billable'
  WHERE field = 'hours' and
    t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED) 
      AND billable.value in ($BILLABLE, $UNBILLABLE)
      AND ticket_change.time >= $STARTDATE
      AND ticket_change.time < $ENDDATE
  
  UNION 
  
  SELECT 'background-color:#DFE;' as __style__,
    'Total work done on the ticket in the selected time period ' as author,
    t.id as __ticket__, sum( CAST(newvalue as real) ) as newvalue,
    NULL as time, 1 as _ord
  FROM ticket_change
  JOIN ticket t on t.id = ticket_change.ticket
  LEFT JOIN ticket_custom as billable on billable.ticket = t.id 
    and billable.name = 'billable'
  WHERE field = 'hours' and
    t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED) 
      AND billable.value in ($BILLABLE, $UNBILLABLE)
      AND ticket_change.time >= $STARTDATE
      AND ticket_change.time < $ENDDATE
  GROUP By t.id
)  as tbl
ORDER BY __ticket__, _ord ASC, time ASC

    

[anonymous]

forgot to leave my email address

[b.steimel@…]

Replying to anonymous:

forgot to leave my email address

[b.steimel@…]

Replying to anonymous:

forgot to leave my email address

I just relized that i didn't have pySQLite updated to version 2, and when i do that i get this error now. The error only appears when using the time plugin

Mod_python error: "PythonHandler trac.web.modpython_frontend"

Traceback (most recent call last):

  File "/usr/lib/python2.4/site-packages/mod_python/apache.py", line 299, in HandlerDispatch
    result = object(req)

  File "/usr/lib/python2.4/site-packages/trac/web/modpython_frontend.py", line 87, in handler
    gateway.run(dispatch_request)

  File "/usr/lib/python2.4/site-packages/trac/web/wsgi.py", line 87, in run
    response = application(self.environ, self._start_response)

  File "/usr/lib/python2.4/site-packages/trac/web/main.py", line 377, in dispatch_request
    env = _open_environment(env_path, run_once=run_once)

  File "/usr/lib/python2.4/site-packages/trac/web/main.py", line 58, in _open_environment
    env_cache[env_path] = open_environment(env_path)

  File "/usr/lib/python2.4/site-packages/trac/env.py", line 435, in open_environment
    if env.needs_upgrade():

  File "/usr/lib/python2.4/site-packages/trac/env.py", line 314, in needs_upgrade
    for participant in self.setup_participants:

  File "/usr/lib/python2.4/site-packages/trac/core.py", line 55, in extensions
    return filter(None, [component.compmgr[cls] for cls in extensions])

  File "/usr/lib/python2.4/site-packages/trac/core.py", line 179, in __getitem__
    component = cls(self)

  File "/usr/lib/python2.4/site-packages/trac/core.py", line 98, in maybe_init
    init(self)

  File "build/bdist.linux-i686/egg/timingandestimationplugin/api.py", line 47, in __init__

  File "build/bdist.linux-i686/egg/timingandestimationplugin/dbhelper.py", line 23, in get_all

UnboundLocalError: local variable 'desc' referenced before assignment

[Russ Tyndall]

There is a new version available via svn. Make sure you have trac-admin upgraded (if you have not done this already, this patch makes no changes that require an upgrade).

Hope this helps,

Russ

[Russ Tyndall]

on rereading that, it was a bit obtuse, so to clarify, 0.3.5 does not itself require an upgrade. However, if you have not upgraded since installing the plugin, please be sure that you do.

Russ