Table display sorts only the first cck fields - gives a mysql error on others [#818038]

Hi. I'm bashing my head on some strange issue that I have trying to setup a table with sorting fields.
I created two simple fields, a text and a decimal, to sort content with.
I created a table display, and setup the sortable checkboxes on the two cck fields.

The table is correctly displayed, but:

- clicking the label to sort the first field works correctly
- clicking the label to sort the second field gives me this error:

* user warning: Unknown column 'node_data_field_test_user_settings_1.field_test_user_settings_1_' in 'field list' query: SELECT COUNT(*) FROM (SELECT DISTINCT users.name AS users_name, users.uid AS users_uid, node_data_field_test_user_settings_2.field_test_user_settings_2_value AS node_data_field_test_user_settings_2_field_test_user_setting, node.type AS node_type, node.nid AS nid, node.vid AS node_vid, node_data_field_test_user_settings_2.field_test_user_settings_1_value AS node_data_field_test_user_settings_2_field_test_user_setting_1, node_data_field_test_user_settings_1.field_test_user_settings_1_value AS node_data_field_test_user_settings_2_field_test_user_setting_2 FROM node node INNER JOIN users users ON node.uid = users.uid LEFT JOIN content_type_settings_part_1029 node_data_field_test_user_settings_2 ON node.vid = node_data_field_test_user_settings_2.vid WHERE node.type in ('settings_part_1029') ) count_alias in /var/www/maw/sites/all/modules/views/plugins/views_plugin_pager.inc on line 134.
* user warning: Unknown column 'node_data_field_test_user_settings_1.field_test_user_settings_1_' in 'field list' query: SELECT DISTINCT users.name AS users_name, users.uid AS users_uid, node_data_field_test_user_settings_2.field_test_user_settings_2_value AS node_data_field_test_user_settings_2_field_test_user_setting, node.type AS node_type, node.nid AS nid, node.vid AS node_vid, node_data_field_test_user_settings_2.field_test_user_settings_1_value AS node_data_field_test_user_settings_2_field_test_user_setting_1, node_data_field_test_user_settings_1.field_test_user_settings_1_value AS node_data_field_test_user_settings_2_field_test_user_setting_2 FROM node node INNER JOIN users users ON node.uid = users.uid LEFT JOIN content_type_settings_part_1029 node_data_field_test_user_settings_2 ON node.vid = node_data_field_test_user_settings_2.vid WHERE node.type in ('settings_part_1029') ORDER BY node_data_field_test_user_settings_2_field_test_user_setting_2 ASC LIMIT 0, 10 in /var/www/maw/sites/all/modules/views/plugins/views_plugin_query_default.inc on line 1093.

here is the export of this simple view:

$view = new view;
$view->name = 'user_settings';
$view->description = '';
$view->tag = '';
$view->view_php = '';
$view->base_table = 'node';
$view->is_cacheable = FALSE;
$view->api_version = 3.0-alpha1;
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */

/* Display: Defaults */
$handler = $view->new_display('default', 'Defaults', 'default');
$handler->display->display_options['use_ajax'] = TRUE;
$handler->display->display_options['access']['type'] = 'none';
$handler->display->display_options['cache']['type'] = 'none';
$handler->display->display_options['exposed_form']['type'] = 'basic';
$handler->display->display_options['pager']['type'] = 'full';
$handler->display->display_options['style_plugin'] = 'table';
$handler->display->display_options['style_options']['columns'] = array(
'name' => 'name',
'field_test_user_settings_1_value' => 'field_test_user_settings_1_value',
'field_test_user_settings_2_value' => 'field_test_user_settings_2_value',
);
$handler->display->display_options['style_options']['default'] = '-1';
$handler->display->display_options['style_options']['info'] = array(
'name' => array(
'sortable' => 0,
'align' => '',
'separator' => '',
),
'field_test_user_settings_1_value' => array(
'sortable' => 1,
'align' => '',
'separator' => '',
),
'field_test_user_settings_2_value' => array(
'sortable' => 1,
'align' => '',
'separator' => '',
),
);
$handler->display->display_options['style_options']['override'] = 1;
$handler->display->display_options['style_options']['sticky'] = 0;
/* Field: User: Name */
$handler->display->display_options['fields']['name']['id'] = 'name';
$handler->display->display_options['fields']['name']['table'] = 'users';
$handler->display->display_options['fields']['name']['field'] = 'name';
$handler->display->display_options['fields']['name']['alter']['alter_text'] = 0;
$handler->display->display_options['fields']['name']['alter']['make_link'] = 0;
$handler->display->display_options['fields']['name']['alter']['trim'] = 0;
$handler->display->display_options['fields']['name']['alter']['word_boundary'] = 1;
$handler->display->display_options['fields']['name']['alter']['ellipsis'] = 1;
$handler->display->display_options['fields']['name']['alter']['strip_tags'] = 0;
$handler->display->display_options['fields']['name']['alter']['html'] = 0;
$handler->display->display_options['fields']['name']['hide_empty'] = 0;
$handler->display->display_options['fields']['name']['empty_zero'] = 0;
$handler->display->display_options['fields']['name']['link_to_user'] = 1;
$handler->display->display_options['fields']['name']['overwrite_anonymous'] = 0;
/* Field: Content: Test User Settings 2 (field_test_user_settings_2) */
$handler->display->display_options['fields']['field_test_user_settings_2_value']['id'] = 'field_test_user_settings_2_value';
$handler->display->display_options['fields']['field_test_user_settings_2_value']['table'] = 'node_data_field_test_user_settings_2';
$handler->display->display_options['fields']['field_test_user_settings_2_value']['field'] = 'field_test_user_settings_2_value';
$handler->display->display_options['fields']['field_test_user_settings_2_value']['label'] = 'Test User Settings 2';
$handler->display->display_options['fields']['field_test_user_settings_2_value']['alter']['alter_text'] = 0;
$handler->display->display_options['fields']['field_test_user_settings_2_value']['alter']['make_link'] = 0;
$handler->display->display_options['fields']['field_test_user_settings_2_value']['alter']['trim'] = 0;
$handler->display->display_options['fields']['field_test_user_settings_2_value']['alter']['word_boundary'] = 1;
$handler->display->display_options['fields']['field_test_user_settings_2_value']['alter']['ellipsis'] = 1;
$handler->display->display_options['fields']['field_test_user_settings_2_value']['alter']['strip_tags'] = 0;
$handler->display->display_options['fields']['field_test_user_settings_2_value']['alter']['html'] = 0;
$handler->display->display_options['fields']['field_test_user_settings_2_value']['hide_empty'] = 0;
$handler->display->display_options['fields']['field_test_user_settings_2_value']['empty_zero'] = 0;
$handler->display->display_options['fields']['field_test_user_settings_2_value']['link_to_node'] = 0;
/* Field: Content: Test User Settings 1 (field_test_user_settings_1) */
$handler->display->display_options['fields']['field_test_user_settings_1_value']['id'] = 'field_test_user_settings_1_value';
$handler->display->display_options['fields']['field_test_user_settings_1_value']['table'] = 'node_data_field_test_user_settings_1';
$handler->display->display_options['fields']['field_test_user_settings_1_value']['field'] = 'field_test_user_settings_1_value';
$handler->display->display_options['fields']['field_test_user_settings_1_value']['label'] = 'Test User Settings 1';
$handler->display->display_options['fields']['field_test_user_settings_1_value']['alter']['alter_text'] = 0;
$handler->display->display_options['fields']['field_test_user_settings_1_value']['alter']['make_link'] = 0;
$handler->display->display_options['fields']['field_test_user_settings_1_value']['alter']['trim'] = 0;
$handler->display->display_options['fields']['field_test_user_settings_1_value']['alter']['word_boundary'] = 1;
$handler->display->display_options['fields']['field_test_user_settings_1_value']['alter']['ellipsis'] = 1;
$handler->display->display_options['fields']['field_test_user_settings_1_value']['alter']['strip_tags'] = 0;
$handler->display->display_options['fields']['field_test_user_settings_1_value']['alter']['html'] = 0;
$handler->display->display_options['fields']['field_test_user_settings_1_value']['hide_empty'] = 0;
$handler->display->display_options['fields']['field_test_user_settings_1_value']['empty_zero'] = 0;
$handler->display->display_options['fields']['field_test_user_settings_1_value']['link_to_node'] = 0;
/* Sort criterion: Content: Test User Settings 2 (field_test_user_settings_2) */
$handler->display->display_options['sorts']['field_test_user_settings_2_value']['id'] = 'field_test_user_settings_2_value';
$handler->display->display_options['sorts']['field_test_user_settings_2_value']['table'] = 'node_data_field_test_user_settings_2';
$handler->display->display_options['sorts']['field_test_user_settings_2_value']['field'] = 'field_test_user_settings_2_value';
$handler->display->display_options['sorts']['field_test_user_settings_2_value']['order'] = 'DESC';
/* Filter: Node: Type */
$handler->display->display_options['filters']['type']['id'] = 'type';
$handler->display->display_options['filters']['type']['table'] = 'node';
$handler->display->display_options['filters']['type']['field'] = 'type';
$handler->display->display_options['filters']['type']['value'] = array(
'settings_part_1029' => 'settings_part_1029',
);

what could be going on? Tried alpha and last dev, without success, and I really need .

Comment	File	Size	Author
#11	i818038.tar_.tar_.gz	2.16 KB	dawehner

Comments

Comment #1

dawehner

German

commented 6 June 2010 at 16:16

Status:

Active

» Postponed (maintainer needs more info)

Do you use the latest dev version of cck 2.x or cck 3.x?

If not try first out whether the problem is fixed there.

Comment #2

greggmarshall

Aurora/Denver, Colorado

commented 21 June 2010 at 15:50

I have a similar issue. I've tried using CCK 2.7 and 6.x-3.x-dev (2010-Jun-18), both respond the same way with the following error messages. Views version is 6.x-3.0-alpha3

user warning: Unknown column 'node_data_field_exhibitorboothnumber.field_exhibitorboothnumber_value' in 'field list' query: SELECT COUNT(*) FROM (SELECT DISTINCT node.title AS node_title, node.nid AS nid, node_data_field_exhibitorwebsite.field_exhibitorwebsite_url AS node_data_field_exhibitorwebsite_field_exhibitorwebsite_url, node_data_field_exhibitorwebsite.field_exhibitorwebsite_title AS node_data_field_exhibitorwebsite_field_exhibitorwebsite_titl, node_data_field_exhibitorwebsite.field_exhibitorwebsite_attributes AS node_data_field_exhibitorwebsite_field_exhibitorwebsite_attr, node.type AS node_type, node.vid AS node_vid, node_data_field_exhibitorwebsite.field_exhibitorboothnumber_value AS node_data_field_exhibitorwebsite_field_exhibitorboothnumber_, node_revisions.body AS node_revisions_body, node_revisions.format AS node_revisions_format, node_data_field_exhibitorboothnumber.field_exhibitorboothnumber_value AS node_data_field_exhibitorwebsite_field_exhibitorboothnumber__1 FROM node node LEFT JOIN content_type_exhibitor node_data_field_exhibitorwebsite ON node.vid = node_data_field_exhibitorwebsite.vid LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid WHERE node.type in ('exhibitor') ) count_alias in /home/rcdrupal/public_html/sites/all/modules/views/plugins/views_plugin_pager.inc on line 134.<br>
user warning: Unknown column 'node_data_field_exhibitorboothnumber.field_exhibitorboothnumber_value' in 'field list' query: SELECT DISTINCT node.title AS node_title, node.nid AS nid, node_data_field_exhibitorwebsite.field_exhibitorwebsite_url AS node_data_field_exhibitorwebsite_field_exhibitorwebsite_url, node_data_field_exhibitorwebsite.field_exhibitorwebsite_title AS node_data_field_exhibitorwebsite_field_exhibitorwebsite_titl, node_data_field_exhibitorwebsite.field_exhibitorwebsite_attributes AS node_data_field_exhibitorwebsite_field_exhibitorwebsite_attr, node.type AS node_type, node.vid AS node_vid, node_data_field_exhibitorwebsite.field_exhibitorboothnumber_value AS node_data_field_exhibitorwebsite_field_exhibitorboothnumber_, node_revisions.body AS node_revisions_body, node_revisions.format AS node_revisions_format, node_data_field_exhibitorboothnumber.field_exhibitorboothnumber_value AS node_data_field_exhibitorwebsite_field_exhibitorboothnumber__1 FROM node node LEFT JOIN content_type_exhibitor node_data_field_exhibitorwebsite ON node.vid = node_data_field_exhibitorwebsite.vid LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid WHERE node.type in ('exhibitor') ORDER BY node_data_field_exhibitorwebsite_field_exhibitorboothnumber__1 ASC LIMIT 0, 25 in /home/rcdrupal/public_html/sites/all/modules/views/plugins/views_plugin_query_default.inc on line 1093.

Comment #3

merlinofchaos commented 21 June 2010 at 16:53

Did you try 6.x-2.x-dev?

Comment #4

greggmarshall

Aurora/Denver, Colorado

commented 21 June 2010 at 18:17

Same problem with 6.x-2.x-dev downloaded about 10 minutes ago.

Comment #5

dougm commented 5 July 2010 at 18:51

I have what appears to be the same type of problem. It only occurs when I have one of the table columns being a node reference. When I have the node reference, sorting doesn't work for some columns and results in the error mentioned. If a different view of the same data (just a different display in the same view) doesn't have the node reference, it works properly. An interesting side note is that I can select the column sort, get the failure and then edit the url to remove the "_value" that is on the end of the field and it will give me what I expect. It also works in the case that doesn't fail - that is, remove _value also works. These can be seen with http://didea.3acres.org/ColorChart vs http://didea.3acres.org/ColorCard where the first fails if you sort on Color ID and the second succeeds. If you remove _value from the field in the URL in both cases, they both work.

This is getting frustrating.

Comment #6

dougm commented 5 July 2010 at 18:53

I should add that I'm using current versions of everything.

Comment #7

dougm commented 6 July 2010 at 15:47

What additional information do you need? I can make a sandbox system available that is exhibiting the problem if that would help.

Comment #8

toomanypets commented 8 July 2010 at 03:15

I'm having the same problem with 6.x-3.0-alpha3. I can click sort the first sortable column (first as seen from left to right) without problems, but click sorting any other column produces unknown column error in views_plugin_query_default.inc on line 1093.

Comment #9

toomanypets commented 8 July 2010 at 14:19

Status:

Postponed (maintainer needs more info)

» Active

Comment #10

Aldus commented 8 July 2010 at 15:47

Version:

6.x-3.0-alpha3

» 6.x-2.x-dev

I switched back to views 2.0 (last dev).
The problem is still there

Comment #11

dawehner

German

commented 9 July 2010 at 22:03

Status:

Active

» Postponed (maintainer needs more info)

Status	File	Size
new	i818038.tar_.tar_.gz	2.16 KB

Here is a feature. It works fine for me.

Such issues has to be reproducable, before they can be fixed.

Comment #12

toomanypets commented 10 July 2010 at 01:29

Status:

Postponed (maintainer needs more info)

» Active

To reproduce I created a new view (2 cck fields, nothing fancy) from scratch with 6.x-3.0-alpha3. Fail. More info:

The failure occurs with click sorting on tables. Exposed sort controls do not fail.
My CCK field names are long. I suspect the failure is related the "alias truncation" problem described in several other issues.

Deleted my view, completely uninstalled 6.x-3.0-alpha3, installed 6.x-2.11, created a new view (2 cck fields, nothing fancy). No problems. Tables are click-sortable again!

Comment #13

dawehner

German

commented 10 July 2010 at 05:52

Status:

Active

» Postponed (maintainer needs more info)

Are this multiple cck fields? Or single cck fields.?

Comment #14

dougm commented 10 July 2010 at 12:57

Status:

Postponed (maintainer needs more info)

» Active

In my case, there are multiple fields. The first is sortable and the rest are not. Exposed filters have a similar problem. The one matching the table column that sorts works but the rest take time but don't actually do anything that I can see.

Also, as I mentioned above, if I leave out the column that is based on a node reference, the table sort works. I will try completely removing views from my system and reinstalling and recreating to see what happens.

What specific information is needed? I would be happy to provide what I can.