Clean install of D7-RC4,
Calendar 7.x-1.x-dev (2010-Dec-30)
Date 7.x-1.x-dev (2010-Dec-29)
Chaos tool suite 7.x-1.x-dev (2010-Nov-10) or alpha1 (tried both)
Views 7.x-3.x-dev (2010-Dec-31) (also with 2010-Dec-30)

Open provided Calendar view
Change argument from Node: updated date to a date field that has been added to a content type. Mine is called field_when.
Save the View and look at the calendar:

'Exception: SQLSTATE[42S22]: Column not found: 1054 Unknown column \'field_data_field_when.field_when_value\' in \'where clause\''
in views_plugin_query_default->execute() (line 1193 of /sites/all/modules/views/plugins/views_plugin_query_default.inc).

Back on the Calendar View configuration in the preview there is the following SQL:

SELECT node.title AS node_title, node.nid AS nid, node.changed AS node_changed, node.type AS node_type 
FROM  
{node} node 
WHERE (( (node.status = '1') AND (node.type IN ('meeting', 'test')) )AND( ((DATE_FORMAT(CONVERT_TZ(field_data_field_when.field_when_value, 'UTC', 'America/Los_Angeles'), '%Y-%m') <= '2010-12' AND DATE_FORMAT(CONVERT_TZ(field_data_field_when.field_when_value, 'UTC', 'America/Los_Angeles'), '%Y-%m') >= '2010-12')) ))

It looks to me as though the query is being created as though the date field didn't have its own table. field_data_field_when.field_when_value is correct, but shouldn't field_data_field_when be in the FROM clause of the query?

CommentFileSizeAuthor
#34 date-1011624-34.patch1.22 KBtim.plunkett

Comments

gscerb’s picture

gscerb commented

subscribe

pipeline’s picture

pipeline commented

Same problem for me...

Stevel’s picture

Stevel commented

Marked #1018604: Error on new install - Debug: 'Exception: SQLSTATE[42S22]: Column not found: as a duplicate

sven.lauer’s picture

sven.lauer commented

subscribe

clauded’s picture

clauded commented

subscribe

Kreativs’s picture

Kreativs commented

subscribe

ryan osītis’s picture

ryan osītis commented

subscribe

monjohn’s picture

monjohn commented

subscribe

robboten’s picture

robboten commented

subscribe

NoDice’s picture

NoDice commented

subscribe

rublev_green’s picture

rublev_green commented

subscribe

waverate’s picture

waverate commented

subscribe

jonasdowney’s picture

jonasdowney commented

subscribe

Kiwigirl’s picture

Kiwigirl commented

subscribe

mbarnson’s picture

mbarnson commented

Subscribed. I'm encountering the same issue at a brand-new Drupal 7 demo site, http://utahheli.org/ . Very frustrating to go from "it just works" to blowing up when I add the block.

Full error (changed path to home directory):

Debug:
'Exception: SQLSTATE[42S22]: Column not found: 1054 Unknown column \'field_data_field_date.field_date_value\' in \'where clause\''
in views_plugin_query_default->execute() (line 1314 of /my/path/utahheli.org/sites/all/modules/views/plugins/views_plugin_query_default.inc).
Debug:
'Exception: SQLSTATE[42S22]: Column not found: 1054 Unknown column \'field_data_field_date.field_date_value\' in \'where clause\''
in views_plugin_query_default->execute() (line 1314 of /my/path/utahheli.org/sites/all/modules/views/plugins/views_plugin_query_default.inc).

Steps followed:
* Updated to -dev via CVS on otherwise bone-stock Drupal 7.0.
* re-ran update.php; no changes found.
* re-saved modules
* cleared drupal cache
* cleared browser cache
* re-edited date fields & saved. Didn't change anything as the values look reasonable. Same with display fields.
* Double-checked views, not using it anywhere except the out-of-box Date Tools option.
* Found this similar bug report and added my two cents to it :)

Thanks for figuring this out! I actually reinstalled everything from scratch and made sure Date & Calendar were the first modules I installed (along with their dependencies). Identical behavior, but useful to know it wasn't something else necessarily causing the problem.

lynnmart881’s picture

lynnmart881 commented

subscribe

drnikki’s picture

drnikki commented

same as mbarnson, subscribing.

mbarnson’s picture

mbarnson commented

Yep. You don't actually have to do anything special to cause this bug to appear. Just try to enable the calendar view in the sidebar after installing Date and Calendar and adding any node with a DateTime field. It's a complete show-stopper using the release or -dev versions of Date & Calendar on Drupal 7.0.

mbarnson’s picture

mbarnson commented

Priority modification, as this pretty much prevents the use of Calendar completely. If you try to go to the Page view via http://some.url/calendar-date you also get the same errors.

Possibly related: If one attempts to Edit the View, this error is shown on the edit screen:
Notice: Undefined index: Date in views_handler_filter_in_operator->admin_summary() (line 318 of /some/path/sites/all/modules/views/handlers/views_handler_filter_in_operator.inc)

Note FullCalendar doesn't exhibit this same behavior; as a temporary workaround for those with Drupal 7, the FullCalendar module can be used to access a date calendar.

ionz149’s picture

ionz149 commented
Priority: Normal » Critical

subby

pivica’s picture

pivica commented
Project: Calendar » Date

This has nothing to do with Calendar module but with Date module. When trying to create simple view that list nodes that have date field and adding date view argument will produce same error when view is executed.

It seems that when adding date argument to a view appropriate join to field date table will not be added to sql query and that is the reason why views with Date arguments will not work - and calendar is also a view with date argument.

flunardelli’s picture

flunardelli commented

Subscribe

lukspa’s picture

lukspa commented

As for temporary solution you can add sorting on your date field, it should provide missing JOIN statement.

harper1983’s picture

harper1983 commented

subscribe

Orjan’s picture

Orjan commented

subscribe

karens’s picture

karens commented
Status: Active » Fixed

I think I have this fixed now.

gorillaz.f’s picture

gorillaz.f commented

hi karenS,
I've post an issue in calendar project http://drupal.org/node/1033464
It seems to be similar with this topic here.
Although I get no debug message saying "column not found", but it related to date field argument that make calendar view fail to show events.
Would you please take a look at it ?

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

arlinsandbulte’s picture

arlinsandbulte commented

#1094408: Date field not showing up in views arguments in localized site might have affected or fixed this issue.
Please verify this issue exists with the March 28 -dev

zabelc’s picture

zabelc commented

@arlinsandbulte I'm seeing this in 7.x-2.x-dev from 2011-03-28

Eaglecanada’s picture

Eaglecanada commented

problem still exist with "week" view on the full calendar page ( Year, Month and Day are fine):

Notice: Undefined variable: field in date_views_argument_handler_simple->query() (line 210 ..
'Exception: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \' SEC_TO_TIME(-14400)) <= \'2011-12-05 00:00:00\' AND ADDTIME(, SEC_TO_TIME(-14400)\' at line 2'

in views_plugin_query_default->execute() (line 1314 of \sites\all\modules\views\plugins\views_plugin_query_default.inc).

Sorry, I amusing: 7.x-2.0-alpha1

karens’s picture

karens commented

Don't use alpha1 when testing if bugs are fixed, they are fixed in the dev version and the fixes will roll into the next release. I also have made a number of fixes in the last couple days. I can't replicate any problems in the latest code.

jsheffers’s picture

jsheffers commented

I am still getting this as well.

With latest dev versions of Ctools, Views, Date, and Calendar. I have also followed this guide http://drupal.org/node/755312.

Here is my setup.

I have 2 content types - one relates to the other.

Parents and Children, I have a node reference field that references the children to their parents. So in the view I'm bringing in the fields of the children content type. Normally I would add a relationship which is the node reference field, and contextual filter for the NID that uses the relationship. This setup works on the current stable versions of these modules, but not on the dev versions. Unfortunately in order to get the calendar to work I have to use all of the dev modules, so now I'm stuck. Any ideas on how to fix this.

As soon as I remove the relationship, which is a node reference field, the error goes away. That being said the relationship and contextual filter never function correctly (Children never show on Parents page). I'm using EVA to attach the view to the parents content type FYI.

tim.plunkett’s picture

tim.plunkett
he/him
Primary language English
Location Philadelphia
commented
Version: 7.x-1.x-dev » 7.x-2.x-dev
Status: Closed (fixed) » Needs review
StatusFileSize
new date-1011624-34.patch1.22 KB

Reopening this, let me know if this should be spun off into a different issue.

When adding a date field argument that is from an entity_type that differs from the $view->base_table, there is no part of the query that adjusts for this.

I borrowed this code from views_handler_field_entity::query.

tim.plunkett’s picture

tim.plunkett
he/him
Primary language English
Location Philadelphia
commented
Priority: Critical » Major
Issue tags: +D7 stable release blocker

Didn't notice the priority when I reopened it. In the core sense, I'd call this major as it doesn't whitescreen or anything.

This makes modules like entityreference and field_collection spit out errors and they are unusable for date fields without this, so tagging as a blocker.

tim.plunkett’s picture

tim.plunkett
he/him
Primary language English
Location Philadelphia
commented
Assigned: Unassigned » karens

In IRC you mentioned that I should double-check date_views_argument_handler.inc when adding to date_views_argument_handler_simple.inc and vice versa, but in this case date_views_argument_handler::query() calls parent::query() anyway, so there's no need.

karens’s picture

karens commented
Status: Needs review » Fixed

I took a different approach to fix this. There were several places where the wrong base table was being used to retrieve fields in both the filters and the arguments. But I found that when the handler is initiated we have access to the right base table for the field. So I am storing that base_table value in init() and then using that whenever we pull a list of available fields. There is also a fix to always keep the Date filter itself out of the results. It was already fixed for Node views but needed to be adjusted to work for all base tables.

Tested with user relationships to a node view but should work fine for any other tables.

http://drupalcode.org/project/date.git/commit/514969e

karens’s picture

karens commented
Title: Adding Date field to argument in Calendar View causes "Column not found" errors, table name missing » Date filters and arguments with relationships have "Column not found" errors, table name missing

Adjusting the title to reflect the current problem being fixed. We probably should have made this a separate issue, but too late now.

Status: Fixed » Closed (fixed)
Issue tags: -D7 stable release blocker

Automatically closed -- issue fixed for 2 weeks with no activity.

Issue tags: +D7 stable release blocker

Restoring issue tags, see #2125755: System messages removed all issue tags during D7 upgrade.