| Bug #35455 | Problem with subselect and aliased tables | ||
|---|---|---|---|
| Submitted: | 20 Mar 2008 9:54 | Modified: | 22 Mar 2008 8:04 |
| Reporter: | Samuele Diella | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Parser | Severity: | S1 (Critical) |
| Version: | 5.0.51a | OS: | Linux (debian 4) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | subselect alias unknown | ||
[20 Mar 2008 9:54]
Samuele Diella
[20 Mar 2008 15:51]
Susanne Ebrecht
Many thanks for writing a bug report. It seems that your table "news" don't have a column "User". Please look with SHOW CREATE TABLE news for the right name of the column. Consider it's case sensitive. To make sure that the name is not a reserved word use backticks or double quotes at SQL_ANSI_MODE. Will this solve your problem?
[20 Mar 2008 16:05]
Samuele Diella
ty for the fast answer. This was only an example, my real query is: SELECT fatture.*, DATE_FORMAT(fatture.data, '%d/%m/%Y') AS Fdata, DATE_FORMAT(fatture.data_scadenza, '%d/%m/%Y') AS Fdatascadenza, YEAR(fatture.data) AS Fyear, clienti.ragione_soc AS RagSoc, SUM(fatture_dettaglio.costo_unitario * fatture_dettaglio.quantita) AS Imp, SUM(fatture_dettaglio.costo_unitario * fatture_dettaglio.quantita) * fatture_dettaglio.iva / 100 AS Iva, ( SELECT IF (SUM(dettaglio_emesse.costo_unitario * dettaglio_emesse.quantita) * dettaglio_emesse.iva / 100, SUM(dettaglio_emesse.costo_unitario * dettaglio_emesse.quantita) * dettaglio_emesse.iva / 100, 0) FROM fatture_dettaglio AS dettaglio_emesse INNER JOIN fatture AS fatture_emesse ON dettaglio_emesse.ID_fatture = fatture_emesse.ID INNER JOIN documenti_relazioni AS documenti_relazioni_emesse ON documenti_relazioni_emesse.tabellaori = 'fatture' AND documenti_relazioni_emesse.idori = fatture.ID AND documenti_relazioni_emesse.tabellades = 'fatture' AND fatture_emesse.ID = documenti_relazioni_emesse.iddes AND fatture_emesse.tipo_documento = '2' ) AS proforma_emesse_Iva FROM fatture INNER JOIN clienti ON fatture.ID_clienti = clienti.id LEFT JOIN fatture_dettaglio ON fatture.ID = fatture_dettaglio.ID_fatture WHERE fatture.tipo_documento = '1' GROUP BY fatture.id ORDER BY YEAR(fatture.data), ABS(fatture.numero) ASC; It returns error #1054: Unknown column 'fatture.ID' in 'on clause' and the describe for fatture is: +----------------+--------------+------+-----+------------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------+--------------+------+-----+------------+----------------+ | ID | int(11) | | PRI | NULL | auto_increment | | numero | varchar(255) | YES | | NULL | | | data | date | | | 0000-00-00 | | | ID_clienti | int(11) | YES | MUL | NULL | | | ID_fornitori | int(11) | | MUL | 0 | | | modalita | int(11) | YES | | NULL | | | banca | int(11) | YES | | NULL | | | iva | int(2) | YES | | NULL | | | oggetto | varchar(64) | | | | | | acconto | float | | | 0 | | | dataacconto | date | | | 0000-00-00 | | | incassato | char(1) | | | | | | data_inc | date | | | 0000-00-00 | | | luogoincasso | int(11) | YES | | NULL | | | varie | text | | | | | | risorsa | int(11) | YES | | NULL | | | anafatt | int(11) | YES | | NULL | | | passiva | char(1) | YES | | NULL | | | tariffa | int(11) | YES | | NULL | | | intervallo | int(11) | YES | | NULL | | | valore | float | YES | | NULL | | | pratica | int(11) | YES | | NULL | | | template | int(11) | YES | | NULL | | | datacreazione | date | YES | | NULL | | | progressivo | int(11) | YES | | NULL | | | sollecitato | char(1) | | | | | | data_soll | date | | | 0000-00-00 | | | data_scadenza | date | YES | | NULL | | | pagato | char(1) | | | | | | datapagato | date | | | 0000-00-00 | | | tipo_documento | char(1) | | | | | | sconto | float | | | 0 | | | att | char(1) | | | | | | scan | varchar(255) | | | | | +----------------+--------------+------+-----+------------+----------------+ i want is clear that the same query on mysql 4.1.21 works.
[20 Mar 2008 16:14]
Susanne Ebrecht
That's weird. What happens when you try the following; select ID from fattura; select a.ID from fattura as a; Please let us know output from: show variables like '%char%'; And also we need to know which encoding do you use for your environment. If it is possible we also need the character set setting from the old 4.1 database.
[20 Mar 2008 16:33]
Samuele Diella
i'm glad it appears weird ^^ i don't want to waste your time. On the new server (debian with mysql 5) mysql> select ID from fatture; +----+ | ID | +----+ | 1 | +----+ 1 row in set (0.00 sec) mysql> select a.ID from fatture as a; +----+ | ID | +----+ | 1 | +----+ 1 row in set (0.00 sec) mysql> show variables like '%char%'; +--------------------------+----------------------------------------------------------+ | Variable_name | Value | +--------------------------+----------------------------------------------------------+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/local/mysql-5.0.45-linux-i686/share/mysql/charsets/ | +--------------------------+----------------------------------------------------------+ 8 rows in set (0.04 sec) the system locale is it_IT (ISO-8859-1) On the old server (Slackware with mysql 4) mysql> show variables like '%char%'; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 7 rows in set (0.00 sec) and the system locale is set to en_US (ISO-8859-1)
[22 Mar 2008 8:04]
Sveta Smirnova
Please do not submit the same bug more than once. An existing bug report already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely to be the same. Because of this, we hope you add your comments to the original bug instead. Thank you for your interest in MySQL. Duplicate of bug #35242
