When creating views and adding a date-filter which uses different date-fields from different content-types the created query is malformed and throws a "Unknown column" error:
Steps to reproduce the issue:
- install a fresh drupal & activate views, cck & date
- add a date-field "field_date1" to one content-type (e.g. story)
- add a different date-field "field_date2" to a different content-type (e.g. page)
- create a view and add a date-filter with both of the fields.
The resulting error is:
user warning: Unknown column 'node_data_field_date1.field_date2_value' in 'where clause' query:
The query is:
SELECT node.nid AS nid, node.title AS node_title FROM datetest_node node LEFT JOIN datetest_content_type_story node_data_field_date1 ON node.vid = node_data_field_date1.vid LEFT JOIN datetest_content_type_page node_data_field_date2 ON node.vid = node_data_field_date2.vid WHERE (DATE_FORMAT(STR_TO_DATE(node_data_field_date1.field_date1_value, '%Y-%m-%dT%T'), '%Y-%m-%d') > '2009-09-17') AND (DATE_FORMAT(STR_TO_DATE(node_data_field_date1.field_date2_value, '%Y-%m-%dT%T'), '%Y-%m-%d') > '2009-09-17') LIMIT 0, 10 in .../sites/all/modules/views/includes/view.inc on line 755.
Apparently the query builder assigned the wrong table to the second date-field. the correct field should be "node_data_field_date2.field_date2_value" in this case.
This bug was NOT present in date 2.3 which is insecure, unfortunately. I marked this issue as critical because for now there is just the choice between a working but insecure or a secure but not-working module.
Comment | File | Size | Author |
---|---|---|---|
#81 | date_multi_arg.patch | 1.28 KB | Liam Mitchell |
#81 | date_multi_filter.patch | 1.19 KB | Liam Mitchell |
#64 | ian's-date_browser-view.txt | 14.62 KB | iantresman |
#62 | date_browser_view.txt | 25.07 KB | baff |
#55 | screen-capture-14.jpg | 63.3 KB | ambereyes |
Comments
Comment #1
tyr CreditAttribution: tyr commentedAfter a little bit of research I found out that the patch/hack for issue #385688: Views filters & arguments not working when using a date relationship. caused the problem.
Comment #2
arlinsandbulte CreditAttribution: arlinsandbulte commentedMarking this issue as a duplicate of #385688: Views filters & arguments not working when using a date relationship. then...
Comment #3
oggsmith CreditAttribution: oggsmith commentedit's not really a duplicate of #385688: Views filters & arguments not working when using a date relationship., it's an new issue caused by an attempt to fix #385688: Views filters & arguments not working when using a date relationship.
Comment #4
Gribnif CreditAttribution: Gribnif commentedI agree that the solution committed in commit 263582 makes the original problem even worse.
Comment #5
ssemigr CreditAttribution: ssemigr commentedsubscribing
Comment #6
butler360 CreditAttribution: butler360 commentedI think I have the same problem, too. Subscribing.
Comment #7
KarenS CreditAttribution: KarenS commentedI commented out that commit to fix this problem. Now I need to try to fix the original problem without creating this one. It's way late I know, sorry!
Comment #8
chrixix CreditAttribution: chrixix commentedI just upgraded from date 6.x-2.4 to 6.x-2.6 and I've got the same error on a view with 2 date fields passed as arguments. When I try with only 1 date field, it works, and everything was OK with date 6.x-2.4.
Here's the warning :
Any idea about what's wrong?
Thanks
Comment #9
kuopionkiipeilyseura CreditAttribution: kuopionkiipeilyseura commentedGot a similar problem with version 6.x-2.6 and the calendar 6.x-2.2 module. After updating to new version of date module, calendar views were broken and no dates were visible on the calendar. Reverting back to 6.x-2.4 of date fixed the issue.
Comment #10
fmesco CreditAttribution: fmesco commentedI had the same problem. Upgraded to 2.6 and all of my calendar views were blank. Reverted back to 2.4 fixed the issue.
Comment #11
e-fee CreditAttribution: e-fee commentedSame issue here with the newest version - will be forced to re-migrate to old version because it's for a critical feature of a customer's website!
Comment #12
hchall CreditAttribution: hchall commentedsubscribing with identical scenario to chrixix.
Thanks to fmesco for suggesting the fix. Reverting back to date-6.x-2.4 worked but, interestingly, reverting to date-6.x-2.5 did not initially do the trick.
Thanks to the developers for working on a resolution to this issue.
Comment #13
bomarmonk CreditAttribution: bomarmonk commentedSubscribe. Reverting to older version of Date until this is fixed. Thanks for all of your work on this, Karen and company.
Comment #14
itserich CreditAttribution: itserich commentedSubscribing. Think I have the same problem, calendar events disappear when updating Date. I did not get any error messages, date links simply disappeared from the calendar.
Also getting this weird error but it does not appear to be causing problems. Who knows?
The date field 'Content: Plan Proposed (field_bk_plan_proposed_1)' used by the display 'Year view' cannot be set to 'Group multiple values'.
The error applies to Day/Week/etc. views but I can not tell if it is causing any actual problems.
Comment #15
chrixix CreditAttribution: chrixix commentedobviously, it's a date 6.x-2.5 issue ; I had the problem when I upgraded to 6.x-2.5, and when I saw a 6.X-2.6 release of the module a few hours later, I thought it would solve my problem... but this was an utopia. So, I'm like everybody here : I reverted to date 6.x-2.4. I hope there'll be an issue to this!
Comment #16
tigron CreditAttribution: tigron commentedOK, I thought I was going crazy, thinking something was wrong with my view. Glad to see there are other more knowledgeable giving feedback.
Comment #17
droath CreditAttribution: droath commentedSubscribe.
Comment #18
tuphill CreditAttribution: tuphill commentedsubscribe
Comment #19
Tony Sharpe CreditAttribution: Tony Sharpe commentedSame problem - subscribe
Comment #20
thebuckst0p CreditAttribution: thebuckst0p commentedSubscribe. Any progress on fixing this other than reverting?
Comment #21
mdpenguin CreditAttribution: mdpenguin commentedSubscribe
Comment #22
Liam Mitchell CreditAttribution: Liam Mitchell commentedMy first go at bug-fixing and patch submission. Patched it against the dev version but there is not much difference to 2.6 so try it out for me.
Tracked it down to date_api_argument_handler.inc
This section in particular:
With multiple date fields being processed by this loop, only the last table alias is saved as related_table_alias and referenced later.
Hope it helps.
DONT use the patch in this comment, use the one further down. Realised it was wrong after I posted it.
Comment #23
Liam Mitchell CreditAttribution: Liam Mitchell commentedChanged component to Date API
Comment #24
SaxxIng CreditAttribution: SaxxIng commentedI've tested this patch and it works well (almost it resolves the issue problem, but I don't know if it has some controindication).
Saxx
Comment #25
Liam Mitchell CreditAttribution: Liam Mitchell commentedOK, after testing further I realised my changes never actually passed on the generated table alias. Don't know anything about when table aliases are used but its probably generated for a reason.
Use this patch instead.
Comment #26
SaxxIng CreditAttribution: SaxxIng commentedI can confirm that also this patch works correctly!
Comment #27
itserich CreditAttribution: itserich commentedI have no idea what this means, but sounds good.
Thank you!
Comment #28
awolfey CreditAttribution: awolfey commentedWorks for me. Thanks Liam!
Comment #29
tyr CreditAttribution: tyr commentedPatch #25 seems to work here, too. Thanks a lot!
Liam, did you test it against issue #385688: Views filters & arguments not working when using a date relationship. (whose hack originally caused this bug)?
Comment #30
Liam Mitchell CreditAttribution: Liam Mitchell commentedNo, I didn't see that thread. I'll post it there too.
You're right, thats where is all started. This is where the code I patched came from: http://drupal.org/node/385688#comment-2218938
Comment #31
ophelia CreditAttribution: ophelia commentedThanks so much - this patch worked great! I was trying to display 2 CCK types in the Calendar (each had different date fields), and Views kept throwing an error when I tried to use both date fields in the Argument. This patch fixed it beautifully!! : )
Comment #32
hchall CreditAttribution: hchall commentedI really appreciate the efforts of Liam Mitchell to patch this problem. However, I am unclear as to how developers can recommend versions for installation if these versions require patches to function.
Would someone please change the the status of 'Date 6.x-2.6' from "recommended version" to "also available" as it appears in the update modules "Available updates" page?
I got tricked into updating to both Date 6.x-2.5 and -2.6 before these versions were ready for production sites. It's just lucky that I could copy over the directory with the last working version (-2.4) without reverting to a backup SQL database.
Much site content now depends upon this important module and developer efforts are very much appreciated!
Thanks!
Comment #33
NickGee CreditAttribution: NickGee commentedThank you Liam, date_table_alias_bug2.patch worked great!
Comment #34
izmeez CreditAttribution: izmeez commentedThanks Liam for the patch in #25. I just applied this to the latest Date 6.x-2.x-dev version dated 2010-10-11 and it applied without difficulty and solved my problems showing as errors in watchdog.
Since others have also found this patch works I am changing the status to RTBC. Hope that's ok.
Thanks very much,
Izzy
Comment #35
jamesialford CreditAttribution: jamesialford commentedLiam, do you have an update with this patch in it. I do not know how to apply a patch and I am scared to do it. I am not that much of a techie.
Thank you
James
Comment #36
itserich CreditAttribution: itserich commentedI have looked around a lot for details on how to apply patches.
There are plenty of directions such as "Apply Tortoise SVN" or such but nothing that makes it plain enough for newcomers.
If anyone knows of any step by step, current, tutorials, please post.
Thanks.
Comment #37
izmeez CreditAttribution: izmeez commentedThere are instructions on drupal.org on how to apply patches or you can do it manually. This patch is only a few lines so you can do it manually. Open it in a text viewer or editor like notepad++ then identify which file(s) are being altered, look for the lines preceded by a "-" and remove those line. Add the lines preceded by a "+".
Comment #38
itserich CreditAttribution: itserich commentedOh thanks, I was reacting generally.
I have applied manual patches.
It is the long ones that I can't get done.
And Notepad++ is great.
Thank you.
Comment #39
izmeez CreditAttribution: izmeez commentedFor more details on patches you may have already seen the book page:
http://drupal.org/patch/apply
Comment #40
baff CreditAttribution: baff commented#25 worked for me - thanks!
Does it become part of the module?
Comment #41
DeFr CreditAttribution: DeFr commentedCross posting related issues: the patch here looks conceptually quite similar to the one in #385688-91: Views filters & arguments not working when using a date relationship. (using the related_table_alias for the query if it's set). Would be nice to get everyone in a single issue, agree on a patch, and get it commited.
Comment #42
Dave Hirschman CreditAttribution: Dave Hirschman commentedSubscribe
Comment #43
akolahi CreditAttribution: akolahi commented#25 also worked for me. Thanks!!!!
Comment #44
KarenS CreditAttribution: KarenS commentedFixed on a duplicate issue, #385688: Views filters & arguments not working when using a date relationship., but same idea. This should be working right now.
Comment #45
DarrellDuane CreditAttribution: DarrellDuane commentedI can also confirm that #25 worked for me to fix the issue in the views module with Relationships that weren't working for Views Arguments to Date Fields. Thanks! I had to ensure that I used date-6.x-2.x-dev for it to work.
Comment #46
thlor CreditAttribution: thlor commentedThanks, #25 seems to have solved the problem for me too (6.x-2.6). It would be nice to see it in the next stable release!
Comment #47
Liam Mitchell CreditAttribution: Liam Mitchell commentedGood to know it works. If this is still an issue with the latest dev version, change the status from fixed back to reviewed and tested by community so people know it still isn't fixed.
Comment #48
tyr CreditAttribution: tyr commentedActually, the situation is "complicated"... for example: the original issue of using date-fields in a filter (see title) was "fixed" in 6.x-2.6, but is again present in the latest dev-version (and patch #25 doesn't help there, too)! :(
To make the whole mess a little more transparent I tested some example views with a fresh installation of Drupal and different versions of date (2.6 and dev; unpatched and patched with #25). The results are attached.
So, neither patch #25 nor date-6.x-dev do improve the overall situation by much. :(
Comment #49
bomarmonk CreditAttribution: bomarmonk commentedSubscribe... reverting to the old version of the date and calendar module.
Comment #50
bomarmonk CreditAttribution: bomarmonk commentedThis is still a big problem with the latest development versions as of 12/17/2010. Still getting an "Unknown column" error and my calendar is blank. I've cleared my caches and updated my database, etc... any clues for a temporary work around? The patch in 25 is apparently committed, but this has no effect on my install: Drupal 6.2, PHP 5.3.2-1ubuntu4.2, and MySQL 5.1.41.
Comment #51
KarenS CreditAttribution: KarenS commentedI cannot reproduce this error on the latest code. I have a view with three different date fields, 2 are CCK fields and one is a system date. There are no errors and the query is correct.
If you are still seeing problems I need more information about exactly how to reproduce an error. This means I need to know exactly how the date fields you are using are configured and how you have configured the view.
Comment #52
KarenS CreditAttribution: KarenS commentedOK, figured out how to reproduce -- you need different fields and the fields must NOT be in the view. The fix from #385688: Views filters & arguments not working when using a date relationship. seemed to fix that.
Comment #53
bomarmonk CreditAttribution: bomarmonk commentedThanks Karen; I'm not sure why, but my calendar is still blank (with the latest development versions of calendar and date), and after flushing caches, resaving the content types for my calendar, etc.. Any ideas?
Comment #54
ambereyes CreditAttribution: ambereyes commentedUsing the 6.x-2.x-dev from 12/24: The problem is given in the error message. For some reason, the field definitions in the where clause do not match those created in the select portion. In my case the query looks like this.
And the error message is
Unknown column 'node_data_field_service_date.field_event_date_value' in 'where clause' query
As can be clearly seen that field is incorrect, it should be node_data_field_event_date.field_event_date_value but the view mixed in the second field definition in with the first.
Comment #55
ambereyes CreditAttribution: ambereyes commentedI then applied the patch in #25 which corrected the field names, but now I am getting all kinds of anomalies.
1. All my december dates are listed on a single date - today.
2. All my other entries in previous and subsequent months list themselves over 10 times in each date -- although at least they are on the right date.
Cleared caches - via devel and views cache.
View data is below.
Comment #56
ambereyes CreditAttribution: ambereyes commentedComment #57
ambereyes CreditAttribution: ambereyes commentedI finally fixed it by going back to date-6.x-2.6 and calendar-6.x-2.2 and applying the patch in #25.
I have no idea why it fixed my problems, but the new dev did not fix it for me.
Comment #58
bomarmonk CreditAttribution: bomarmonk commentedambereyes, following in your footsteps: returning to date-6.x-2.6 and calendar-6.x-2.2 and applying the patch in #25 does get rid of the unknown column error, but I'm still having trouble getting two content types to display on the calendar... hmmm. I might have messed up something with my views arguments and fields, but they seem to be set properly.
Comment #59
bomarmonk CreditAttribution: bomarmonk commentedI've tried setting up my date:date (node) arguments several different ways (all in one argument, in separate arguments for each date field-- both "from" and "to" and the single date field for my other content type). I have the view filtering for the right content types, and the node title and date fields included as my fields for the view (everything else I've cloned from the default calendar view). However, I can only get one content type to display at a time. If I add the arguments in certain ways, the entire calendar goes blank. Otherwise, I can only get one content type to show at one time. Here is a copy of my view (I'm trying again with the latest development version of date and calendar):
Comment #60
bomarmonk CreditAttribution: bomarmonk commentedSince the "unknown column" error no longer seems to be the problem, I've created a new issue for my problem with multiple content types with date fields here:
http://drupal.org/node/1010848
Comment #61
bomarmonk CreditAttribution: bomarmonk commentedAmber Eyes, things appear to be working now that I've deleted my date: date arguments and rebuilt them in my view; Amber can you confirm this could resolve your problem? For now, I'm marking this issue as fixed, as the problem is no longer present after using the latest official release and deleting my date arguments.
Comment #62
baff CreditAttribution: baff commentedI have updated to newest dev version - but without patch from #25 I have still the sql errors
I have cleared caches, view attached, with #25 patch it is working
* user warning: Unknown column 'node_data_field_datum.field_datum_value' in 'where clause' query: SELECT DISTINCT node.nid AS nid, GREATEST(node.changed, node_comment_statistics.last_comment_timestamp) AS node_comment_statistics_last_updated, node.sticky AS node_sticky, node.type AS node_type FROM node node LEFT JOIN og_ancestry og_ancestry ON node.nid = og_ancestry.nid LEFT JOIN node node_og_ancestry ON og_ancestry.group_nid = node_og_ancestry.nid INNER JOIN term_node term_node ON node.vid = term_node.vid INNER JOIN node_comment_statistics node_comment_statistics ON node.nid = node_comment_statistics.nid WHERE ((node.status <> 0) AND (term_node.tid = 6075)) AND (((DATE_FORMAT(STR_TO_DATE(node_data_field_datum.field_datum_value, '%Y-%m-%dT%T'), '%Y-%m-%d') <= '2011-01-04' AND DATE_FORMAT(STR_TO_DATE(node_data_field_datum.field_datum_value2, '%Y-%m-%dT%T'), '%Y-%m-%d') >= '2011-01-04')) OR ((DATE_FORMAT(STR_TO_DATE(node_data_field__kommerzielles_datum.field__kommerzielles_datum_value, '%Y-%m-%dT%T'), '%Y-%m-%d') <= '2011-01-04' AND DATE_FORMAT(STR_TO_DATE(node_data_field__kommerzielles_datum.field__kommerzielles_datum_value2, '%Y-%m-%dT%T'), '%Y-%m-%d') >= '2011-01-04')) OR ((DATE_FORMAT(STR_TO_DATE(node_data_field_privatdatum.field_privatdatum_value, '%Y-%m-%dT%T'), '%Y-%m-%d') <= '2011-01-04' AND DATE_FORMAT(STR_TO_DATE(node_data_field_privatdatum.field_privatdatum_value, '%Y-%m-%dT%T'), '%Y-%m-%d') >= '2011-01-04'))) ORDER BY node_comment_statistics_last_updated DESC, node_sticky DESC in /www/htdocs/w00b40cd/com/at/sites/all/modules/views/includes/view.inc on line 775.
* user warning: Unknown column 'node_data_field__kommerzielles_datum.field__kommerzielles_datum_' in 'where clause' query: SELECT DISTINCT node.nid AS nid, node.title AS node_title, node.language AS node_language, node.type AS node_type, node.vid AS node_vid, og_ancestry.nid AS og_ancestry_nid, node_og_ancestry_node_data_field_gruppenbild.field_gruppenbild_fid AS node_og_ancestry_node_data_field_gruppenbild_field_gruppenbild_fid, node_og_ancestry_node_data_field_gruppenbild.field_gruppenbild_list AS node_og_ancestry_node_data_field_gruppenbild_field_gruppenbild_list, node_og_ancestry_node_data_field_gruppenbild.field_gruppenbild_data AS node_og_ancestry_node_data_field_gruppenbild_field_gruppenbild_data, node_og_ancestry.nid AS node_og_ancestry_nid, node_og_ancestry.language AS node_og_ancestry_language, node_og_ancestry.type AS node_og_ancestry_type, node_og_ancestry.vid AS node_og_ancestry_vid, node_data_field_datum.field_datum_value AS node_data_field_datum_field_datum_value, node_data_field_datum.field_datum_value2 AS node_data_field_datum_field_datum_value2, node_data_field_datum.field_datum_rrule AS node_data_field_datum_field_datum_rrule, node_data_field_datum.delta AS node_data_field_datum_delta, 0 AS all_tid_all_tids, 0 AS all_tid, GREATEST(node.changed, node_comment_statistics.last_comment_timestamp) AS node_comment_statistics_last_updated, node.sticky AS node_sticky FROM node node LEFT JOIN og_ancestry og_ancestry ON node.nid = og_ancestry.nid LEFT JOIN node node_og_ancestry ON og_ancestry.group_nid = node_og_ancestry.nid INNER JOIN term_node term_node ON node.vid = term_node.vid LEFT JOIN content_type_gruppe node_og_ancestry_node_data_field_gruppenbild ON node_og_ancestry.vid = node_og_ancestry_node_data_field_gruppenbild.vid LEFT JOIN content_field_datum node_data_field_datum ON node.vid = node_data_field_datum.vid INNER JOIN node_comment_statistics node_comment_statistics ON node.nid = node_comment_statistics.nid WHERE ((node.status <> 0) AND (term_node.tid = 6075)) AND (((DATE_FORMAT(STR_TO_DATE(node_data_field_datum.field_datum_value, '%Y-%m-%dT%T'), '%Y-%m-%d') <= '2011-01-04' AND DATE_FORMAT(STR_TO_DATE(node_data_field_datum.field_datum_value2, '%Y-%m-%dT%T'), '%Y-%m-%d') >= '2011-01-04')) OR ((DATE_FORMAT(STR_TO_DATE(node_data_field__kommerzielles_datum.field__kommerzielles_datum_value, '%Y-%m-%dT%T'), '%Y-%m-%d') <= '2011-01-04' AND DATE_FORMAT(STR_TO_DATE(node_data_field__kommerzielles_datum.field__kommerzielles_datum_value2, '%Y-%m-%dT%T'), '%Y-%m-%d') >= '2011-01-04')) OR ((DATE_FORMAT(STR_TO_DATE(node_data_field_privatdatum.field_privatdatum_value, '%Y-%m-%dT%T'), '%Y-%m-%d') <= '2011-01-04' AND DATE_FORMAT(STR_TO_DATE(node_data_field_privatdatum.field_privatdatum_value, '%Y-%m-%dT%T'), '%Y-%m-%d') >= '2011-01-04'))) ORDER BY node_comment_statistics_last_updated DESC, node_sticky DESC in /www/htdocs/w00b40cd/com/at/sites/all/modules/views/includes/view.inc on line 775.
Comment #63
BTMash CreditAttribution: BTMash commentedMine is a very similar problem in which I have a single content type with multiple date fields (a date range field and a singular dates with times, both of which have two different tables: 'content_type_event' for the date range and 'content_field_event_date' for the singular dates. My view is set up to use the date range as the date argument handler but display the singular event dates to the user as part of the fields. My view looks like:
The query it attempts to run
When I upgraded from 2.6 to 2.7, the errors described above started to occur. I tried via rebuilding my view over and the problem still seemed to occur (I also tried by applying the patch and rebuilding the view - no luck). As you can see, it does not do a left join against the table that contains the date range (in my schema, it is named 'content_type_event'). When I reverted date to the 2.6 version, everything worked again (to be more specific the problem is stemming from the date_api_argument_handler.inc file though I'm unsure on which changeset might be a part of the issue. I verified this by reverting the include file to the 2.6 version after which things look to work).
Comment #64
iantresman CreditAttribution: iantresman commentedI've just upgrade from Date 2.6 to v2.7, and am also getting the same problem when I select the date_browser:
user warning: Unknown column 'node_data_field_date.field_date_value' in 'where clause' query: SELECT node.nid AS nid, node.type AS node_type FROM node node WHERE (DATE_FORMAT(STR_TO_DATE(node_data_field_date.field_date_value, '%Y-%m-%dT%T'), '%Y') <= '2011' AND DATE_FORMAT(STR_TO_DATE(node_data_field_date.field_date_value2, '%Y-%m-%dT%T'), '%Y') >= '2011') in /mydomain/public_html/sites/all/modules/views/includes/view.inc on line 775.
I've tried using alternative arguments, without success.
Comment #65
hbananda CreditAttribution: hbananda commentedI am also getting the same "Unknown column..." error in date_browser upon updating.
Comment #66
kruser CreditAttribution: kruser commentedSubscribe. Same problem here...
Comment #67
iantresman CreditAttribution: iantresman commentedMy temporary workaround, since the page still displays, is to just hide the Warning message using the CSS Injector module to set:
body.page-date-browser div.error {display:none}
Comment #68
noah CreditAttribution: noah commentedSame problem here, though in my case it's an argument rather than a filter -- worked fine with 6.2.6, upgraded to 6.2.7 and I get the "Unknown column" errors and no dates showing up. I've tried a bunch of patches that I found here and in other issue threads, but nothing fixes the issue.
UPDATE: I isolated the issue by reverting date_api_argument_handler.inc to the 6.2.6 version, then gradually changing it to match 6.2.7. The issue is at line 279:
The problem goes away when the outermost condition here is removed (i.e., if (!empty($this->relationship))) -- this condition is new to 6.2.7. I don't understand what's going on here enough to implement a real fix, but for now just commenting out this condition has made my view work again.
Comment #69
reserve CreditAttribution: reserve commentedI have the same problem with Durpal 7.
Make an content type with an date field (field_date)
In views under date argument i change the "Node: Update date" to "Fields: field_date value"
got this error
'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 /home/bfsk/public_html/devel/bfsk/sites/all/modules/views/plugins/views_plugin_query_default.inc).
Comment #70
hbananda CreditAttribution: hbananda commentedNoah's fix worked for me as well.
Comment #71
inforeto CreditAttribution: inforeto commentedRun into this problem by updating to 6.x-2.7 and the fix from #68 by noah worked.
Comment #72
reserve CreditAttribution: reserve commentedwill this fiks work for the 7.x version`?
Comment #73
sonicthoughts CreditAttribution: sonicthoughts commentedsame issue. updated to 2.7 to address a patch on relative dates and introduced this same problem. will try the fix #68.
Comment #74
spydmobile CreditAttribution: spydmobile commentedsame error on 2.7 so using noah fix.
Comment #75
bneel CreditAttribution: bneel commentedSame issue,
I switch the file date_api_argument_handler.inc version 6.2.7. with the 6.2.6 version as indicated in #68.
Know it workrs
Ben
Comment #76
roball CreditAttribution: roball commentedYes, reverting back
modules/date/includes/date_api_argument_handler.inc
from Date 6.x-2.7 to that from the previous version (6.x-2.6) also fixed the problem for me. Issue #1016396: Unknown column ... in ... modules/views/includes/view.inc on line 775 is a duplicate.Comment #77
quixxel CreditAttribution: quixxel commentedSimilar issue here, but #68 did'nt work for me.
I've updated to Views 6.x-2.12, Date 6.x-2.7 and Calendar 6.x-2.4
but on the page /date-browser I'm getting the following error:
Unknown column 'node_data_field_datum.field_datum_value' in 'where clause' query: SELECT DISTINCT node.nid AS nid, node.type AS node_type FROM node node INNER JOIN node_access na ON na.nid = node.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 0 AND na.realm = 'content_access_author') OR (na.gid = 1 AND na.realm = 'content_access_rid') OR (na.gid = 0 AND na.realm = 'resume_owner'))) AND ( (DATE_FORMAT(STR_TO_DATE(node_data_field_datum.field_datum_value, '%Y-%m-%dT%T'), '%Y-%m') <= '2011-05' AND DATE_FORMAT(STR_TO_DATE(node_data_field_datum.field_datum_value2, '%Y-%m-%dT%T'), '%Y-%m') >= '2011-05') ) in modules/views/includes/view.inc in Zeile 775.
Comment #78
noah CreditAttribution: noah commentedquixxel, it looks like you might be using the date field in a filter rather than an argument, is that right? If that's the case try looking around line 415 of date_api_filter_handler.inc -- making the same adjustment to that chunk of code might work for a filter (i.e., commenting out the "if (!empty($this->relationship))" condition).
Comment #79
noah CreditAttribution: noah commentedGah, not sure why the version number changed above. Issue is definitely with 6.x-2.7. Just setting it back.
Comment #80
quixxel CreditAttribution: quixxel commentednoah,
your assumption was correct, but if I comment out the "if (!empty($this->relationship))" condition) in line 415 of date_api_filter_handler.inc, I got a blank screen on the page /date-browser and the error changed to:
Unknown column 'node_data_field_datum.field_datum_value' in 'where clause' query: SELECT DISTINCT node.nid AS nid, node.type AS node_type FROM node node WHERE (DATE_FORMAT(STR_TO_DATE(node_data_field_datum.field_datum_value, '%Y-%m-%dT%T'), '%Y-%m') <= '2011-01' AND DATE_FORMAT(STR_TO_DATE(node_data_field_datum.field_datum_value2, '%Y-%m-%dT%T'), '%Y-%m') >= '2011-01') in modules/views/includes/view.inc in Zeile 775
Comment #81
Liam Mitchell CreditAttribution: Liam Mitchell commentednoah,
my patch in #25 addresses the problem of multiple date fields in arguments, looking at
date_api_filter_handler.inc
it looks like exactly the same problem with filters as well. The same fix should work for both arguments and filters with multiple fields.There is a lot I don't understand about the inner workings of relationships, aliases etc but from what i gather the bug comes down to this:
The code that was written to handle table aliases was not written to handle aliases for multiple date fields.
set_relationship()
it loops over each field and sets a single variable$this->related_table_alias
.query()
,$this->related_table_alias
is used to reference the date fields but of course it will fail because it is using the same alias for different fields.My patch (submitted again) is to turn
$this->related_table_alias
into an array with$field['table_name']
as the key, allowing it to handle multiple fields.This is patched against the latest dev version because looks like issue still exists but should work on 2.7 too. Filter patch included but not tested. Please test and let everyone know your results.
Comment #82
agill CreditAttribution: agill commentedi applied your patch Liam which didn' t resolve the problem, after i removed the outer condition like suggested in 68# the view works again. my view does not have a relation so
that (!empty($this->relationship)) results to false allways in my case.
But the code capsuled by the condition needs to run, because when i deactivate the outer condition to let the inner code set $this->related_table_alias to
$this->query->queue_table($field['table_name'], $this->relationship) everything looks good.
i don´t know enough to explain this behavior...
Comment #83
Fr0s7 CreditAttribution: Fr0s7 commentedSubscribe
Comment #84
roball CreditAttribution: roball commentedHas someone already created a patch for this critical issue?
Comment #85
rickyd1 CreditAttribution: rickyd1 commentedI used these two patches on 6.x 2.7 version and it fixed my problems.
Everything seems to be working fine now.
Thanks
Comment #86
KarenS CreditAttribution: KarenS commentedI think I found the best way to fix this, different than the patch. Just committed it to cvs. The tarball won't be updated until tonight.
Comment #87
webscape CreditAttribution: webscape commentedI did some module updates this morning and of course one of the updates was this Date module. Do we have a new version that will work? I haven't played with patches before so I don't know how to make these patches work.
Comment #88
nlambert CreditAttribution: nlambert commentedWith the last dev, the problem dissapears :-)
Comment #89
Prodigy CreditAttribution: Prodigy commentedJust upgraded to latest .dev.
Changing the option action to take if argument is not present to display all values worked for me.
Comment #90
jpcwebb CreditAttribution: jpcwebb commentedI also have 2.7 and as per message #82, commenting out the lines as per #68 did the trick for me whereas the patches wouldn't apply (note, I tried to apply via patch manager, so that may have been something to do with it)
Comment #91
jsenich CreditAttribution: jsenich commentedI just tried the latest dev version after noticing the problem with 2.7 and I still run into the "Unknown column" issue when trying to use a date argument. Reverting back to 2.7 and commenting out the code as per #82 did work for me though.
Comment #92
radj CreditAttribution: radj commentedConfirmed #68 works around the issue.
Comment #93
jmseigneur CreditAttribution: jmseigneur commentedSubscribing
Comment #94
sdsheridan#68 works for me too. Had an issue where i had a CCK image field and date field that I wanted to use in a Calendar view, and was getting the "unknown column" error.
Shawn
Comment #95
spydmobile CreditAttribution: spydmobile commented#68 fixed me right up too :) thanks Noah.
Franco
Comment #96
ahmed.sajid CreditAttribution: ahmed.sajid commentedThat fixed my problem.
Thanks a lot Noah.
Ahmed.
Comment #97
arlinsandbulte CreditAttribution: arlinsandbulte commentedPlease Note: according to KarenS in #86, she applied a fix for this on January 17, 2011.
The Date 6x-2.7 release was created on January 3, 2011.
So, until 6.x-2.8 is released, this issue should be fixed in -dev and you should try that to verify.
#88 & #89 have verified that the -dev has fixed this.
But, #90 indicates this is not fixed in -dev.
Comment #98
davemaxg CreditAttribution: davemaxg commentedI had the same error and tried using the patch, but it didn't work for me. It turns out the issue was resolved by modifying permissions for affected users in the content_permissions module. I knew it was a permissions issue because it didn't affect all users but I'm new to Drupal so it took me a while to figure out where to look.
Comment #99
tyler.frankenstein CreditAttribution: tyler.frankenstein commentedUpdating to date-6.x-2.x-dev worked for me. (6.2.8 dev)
Comment #101
cabplan CreditAttribution: cabplan commentedOk had the same error and #68 fixed it but now my Events are not showing up on the public side, only the admin side, I checked all the permissions and the Anonymous should be able to see the Date and Events but it does not show up, how do I fix this?
Nevermind, I found it, oversight on the permissions.
Comment #102
rj CreditAttribution: rj commentedI am having the same issue, except it's an argument with a default summary action and a relationship; remove the relationship and it works fine. Does this sound related to this issue or should I post a new issue? Thanks.
Comment #103
xingchong CreditAttribution: xingchong commented#68 workaround works for me too
Comment #104
crimsondryad CreditAttribution: crimsondryad commentedTons of people seem to be confirming that #68 works. Can we please get a release for this soon? :)
Comment #105
rj CreditAttribution: rj commented#68 did not fix my issue, and neither did the changes made to -dev (see #86). I was able to duplicate the issue on a fresh install using -dev but will wait until a new release before I submit an issue, as I'm not entirely sure it's related to this issue.
Comment #106
baff CreditAttribution: baff commentednewest dev version fixed it for me
Comment #107
omerida CreditAttribution: omerida commented#68 fixed it for me using 2.7 on a view using a single date field as an argument.
Also can confirm that "upgrading" to the dev version fixed the "Unknown column" error.
Now I'm torn, what to do in production? Run with a hacked 2.7 module or run a dev module release on our live site (which I'd prefer not to do).
Comment #108
roball CreditAttribution: roball commentedIMO, 2.8 should be released asap to get rid of this critical bug in the current official "stable" version.
Comment #109
crimsondryad CreditAttribution: crimsondryad commentedWe need a like button so we can approve without posting again. :P Wait. Did I *really* recommend Facebook? ::face palm::
Comment #110
reaneyk CreditAttribution: reaneyk commentedsubscribe
Comment #111
vulfox CreditAttribution: vulfox commentedMy very similar issue:
(user warning: Unknown column 'node_data_field_event_date_and_time.delta' in 'field list' query: SELECT node.nid AS nid, node.title AS node_title, node_data_field_event_date_and_time.field_event_date_and_time_value AS node_data_field_event_date_and_time_field_event_date_and_time_value, bla bla bla...)
Was resolved by disabling the calendar module which I actually don't need because I just present list of events with views.
But is it the same issue? I just searched with "user warning: Unknown column AND views" and finally ended up here.
EDIT: never mind - back to square one. Stupid me.
Comment #112
dgtlmoon CreditAttribution: dgtlmoon commentedI can confirm this in current stable and dev releases.
I have a view with an argument set as the "Content: Year"
Comment #113
dgtlmoon CreditAttribution: dgtlmoon commented[deleted]
Comment #114
dgtlmoon CreditAttribution: dgtlmoon commentedPatch at comment #68 failed to resolve this issue for me (latest stable release, wont work on latest dev release)
So kind of frustrating, I can't do a site that has an argument for a content-type as a date.
Comment #115
dgtlmoon CreditAttribution: dgtlmoon commentedFortunately for me I'm just using the date field for year selection (so far)
Here's a snippet for your own update hook how i migrated away from date module and to a plain integer field.
Comment #116
anektod CreditAttribution: anektod commenteduser warning: Unknown column 'node_data_field_date.field_date_value' in 'where clause' query: SELECT node.nid AS nid, node.type AS node_type FROM node node WHERE (DATE_FORMAT(STR_TO_DATE(node_data_field_date.field_date_value, '%Y-%m-%dT%T'), '%Y-%m') <= '2011-06' AND DATE_FORMAT(STR_TO_DATE(node_data_field_date.field_date_value, '%Y-%m-%dT%T'), '%Y-%m') >= '2011-06') in C:\wamp\www\sites\all\modules\views\includes\view.inc on line 775.
What is the solution to this my problem ?
Comment #117
gthing CreditAttribution: gthing commentedI am having this same issue in the latest stable 6.x version. I am using a date field as an argument on my view.
WORKAROUND:
I was finally able to find that if I added the field being used as an argument (or presumably filter) to the fields being loaded for display, my error went away and everything worked as expected. It should work without that, but in the meantime this workaround fixed the problem for me. I set that field to be excluded from display so it is loaded but now shown.
Comment #118
inforeto CreditAttribution: inforeto commentedThat'd be a workaround, given that using the field explicitly adds the table to the query.
Afaik the filters also add the required tables to the query in that same way.
But i'm not sure about the arguments or what causes the table to be missing.
Comment #119
James Marks CreditAttribution: James Marks commentedIssue still exists under current dev version (6.x-2.x-dev 2011-May-27) for me. Applying the fix supplied in #68 to version 6.x-2.7 resolves the error but I can't confirm that doing so doesn't raise some other as-of-yet undetected problem.
Comment #120
dmurkerson CreditAttribution: dmurkerson commentedWe also used the fixed listed in post #68 and it worked for us.
Comment #121
gthing CreditAttribution: gthing commentedThis might be related: http://drewish.com/content/2010/06/correctly_accessing_cck_fields_in_sql...
The table name changes when the field is used on multiple content types. I'm not sure if date corrects for this but it's something to look into.
Comment #122
span CreditAttribution: span commentedYeah, I'm also getting this in 2.7. Fix in #68 works.
Comment #123
stella CreditAttribution: stella commentedFrom comment #117:
I was getting this error even though I had that date field in the view as a field, argument (summary listing) and sort.
Upgrading to the dev version resolved the error for me.
Comment #124
Rino-1 CreditAttribution: Rino-1 commentedThank you Stella.
Your workaround works for me in OA 1. I used date 2.7 with patch #68 and copying this date_api_argument_handler.inc into date 2.8 also works. I prefer your solution.
Comment #125
mrothmay CreditAttribution: mrothmay commentedSubscribing
Comment #126
BootstrapJohnny CreditAttribution: BootstrapJohnny commentedMy current situation... and my two cents:
modules: Date 2.7, Views 2.12, Calendar 2.4
patches: applied manually both from #68 and Liam's from #25
No improvement. I have the date field in my fields list, argument set up normally, filtering only on node type and a "status" cck field value.
Observation: When I created a new date format to eliminate the date, and only went with time (g:i a) showing as 4:45 pm, then assigned that to the "Medium" date format, then assigned "Medium" to the field output format in the view, the problem went away.
This created a new problem in on my site, in that I was using the normal "medium" format elsewhere in my site, and suddenly it showed only the time (not captured in the other locations due to setting date field granularity to day, rather than minute or second). So, I set it back and suddenly the problem returned. What I can't seem to do is get views fields to accept my custom date format of "time", and both Short and Medium formats are used elsewhere, so I can't use those, and it seems silly to make my "long" date format show time. That may be exactly what I have to do, as a work-around, but here's my question:
Is there currently a way (I'm not aware of) to show my custom (Time) date format in the field display drop-down in Views? This will solve my problem, though clearly not everyone else's.
I hope this helps illustrate a possible fix that is real and works for everyone, though I'm not technical enough to know what that might be... Good Luck, all!
********
UPDATE:
I upgraded to the .dev version of Date (2.8) and that shows the custom time format Time {YAY!!!} (which I changed to "h:i A" to match with the changes I note below) and I added the mysql changes manually to the Views/includes/handlers.inc file as recommended in the patch on an issue I can't find now... here is what I changed the file to (starting on line 931 in function views_date_sql_format for Views 2.12):
I hope this helps somebody! It fixed my problem...
~Cheers!
Bootstrap Johnny
Comment #127
brewern CreditAttribution: brewern commentedComment #68 fixed the issue for me! Thanks!
My problem was I got the same SQL error (not from a different content type) when using an argument for getting content of a particular date from the a CCK date field.
Comment #128
rhibbitts CreditAttribution: rhibbitts commentedComment #68 fixed the issue for me as well. Thanks, Noah.
Comment #129
erikhopp CreditAttribution: erikhopp commentedI just ran into this during a Date module upgrade from 2.6 to 2.7. The method in #117 of adding the list of fields to display (but excluding from display) worked for me. In my opinion, much better than #68 - which also worked for me - but required hacking a contrib module.
Regardless, this still isn't fixed in the latest stable release.
Comment #130
drm CreditAttribution: drm commentedI saw that the Views query is not including the join to the table with the date content, so I resolved this by adding the argument field to the display field, display excluded. Apologies if one of the 100+ comments above already says this since I don't have time to read them all.
Comment #131
tragic.rich CreditAttribution: tragic.rich commentedNone of the above solutions appear to be working for me. I tried the solutions in comment #25, and #68 applying the patch, but to no avail. The argument field is set as Date: Date (node) and is set to filter by the current date, I don't see a way to add this and 'exclude it from display' as mentioned in #117.
I am using date-6.x-2.7 and views-6.x-2.16, this is the error I receive on my calendar page:
user warning: Unknown column 'node_data_field_date.field_date_value' in 'where clause' query: SELECT node.nid AS nid, node.type AS node_type FROM node node WHERE ((node.type in ('event')) AND (node.status = 1)) AND ((DATE_FORMAT(ADDTIME(node_data_field_date.field_date_value, SEC_TO_TIME(-21600)), '%Y-%m') <= '2011-11' AND DATE_FORMAT(ADDTIME(node_data_field_date.field_date_value2, SEC_TO_TIME(-21600)), '%Y-%m') >= '2011-11')) in //sites/all/modules/views/includes/view.inc on line 814.
Any help is greatly appreciated!
Comment #132
raven_web CreditAttribution: raven_web commentedI got the same error on an imported view. I enabled the default "calendar" view and had no problems with that, so I just used that instead.
Comment #133
roball CreditAttribution: roball commentedThis should no longer be a problem with Date 6.x-2.8.
Comment #134
dgtlmoon CreditAttribution: dgtlmoon commented@roball Why should it no longer be a problem? what changed? what was comitted? so more information would be great!
Comment #135
roball CreditAttribution: roball commentedThe commit mentioned in #86 is now in a release (2.8) and solved the problem for some users, including myself. If others still have problems I would find it better to open a new issue for that since it does not seem to be directly related to this already so long issue. Thanks.
Comment #136
dgtlmoon CreditAttribution: dgtlmoon commentedok great :)
Comment #138
GaëlGI had the same problem, still with 2.8, but I figure out I used it somewhat wrong. The argument field to choose is the one from the Date group, not from the Content one. Then it works.