I am trying to find an error in a massive SQL statement (not mine) — I have cut a lot of it out to make it readable — even pared down it still throws the error
SELECT DISTINCT Profiles.ID
FROM
(select * from Profiles RIGHT JOIN FriendList ON (FriendList.Profile = 15237)
order by LastLoggedIn DESC ) as Profiles
This returns an error
Duplicate column name ‘ID’
I have tested the the last part (select * from Profiles ... order by LastLoggedIn DESC) and it works fine by itself
I have tried to troubleshoot by changing column names in the DISTINCT section without any luck.
One solution I read was to remove the DISTINCT, but that didn’t help.
I just can’t see where the duplicate column error can be coming from. Could it be a database integrity problem?
Any help much appreciated.
marc_s
729k175 gold badges1327 silver badges1455 bronze badges
asked Feb 13, 2012 at 12:59
3
Your Profile and FriendList tables both have an ID column. Because you say select *, you’re getting two columns named ID in the sub-select which is aliased to Profiles, and SQL doesn’t know which one Profiles.ID refers to (note that Profiles here is referring to the alias of the sub-query, not the table of the same name).
Since you only need the ID column, you can change it to this:
SELECT DISTINCT Profiles.ID FROM
( select Profiles.ID from Profiles RIGHT JOIN FriendList ON (FriendList.Profile = 15237)
order by LastLoggedIn DESC ) as Profiles
answered Feb 13, 2012 at 13:01
BlorgbeardBlorgbeard
101k48 gold badges226 silver badges271 bronze badges
Replace the «select *» with «select col1, col2…» and the error should become apparent (i.e. multiple columns named «ID»). Nothing to do with distinct or database integrity.
answered Feb 13, 2012 at 13:04
TundeyTundey
2,9161 gold badge23 silver badges27 bronze badges
you have a table called Profiles and you are «creating» a temp table called Profiles in your From, that would be my guess as to what is causing the problem. call your temp bananas and try SELECT DISTINCT bananas.ID FROM and see if that works
answered Feb 13, 2012 at 13:01
peroijaperoija
1,9824 gold badges21 silver badges37 bronze badges
As the error says, each of the tables that you’re joining together has a column named ID. You’ll have to specify which ID column you want (Profiles.ID or FriendList.ID) or include ID in the join conditions.
answered Feb 13, 2012 at 13:01
Profiles and FriendList both have an ID column. You are asking to call the entire join «Profiles», and then using Profiles.ID, but SQL doesn’t know which ID you mean.
answered Feb 13, 2012 at 13:02
Ned BatchelderNed Batchelder
362k73 gold badges560 silver badges658 bronze badges
This typically happens when you retrieve data from multiple tables with the same column name using a JOIN statement. You might receive an error like this:
ERROR: Column 'col_name' in field list is ambiguous
To avoid this error, you should define the table when inserting the column name in the SQL statement.
Example:
We have table departments and dept_emp with the same column name dept_no.
Now we try to join two tables using the dept_no column:
SELECT
dept_no,
dept_name
FROM
departments
INNER JOIN dept_emp
WHERE
departments.dept_no = dept_emp.dept_no;
MySQL throws back the error message:
Query 1 ERROR: Column 'dept_no' in field list is ambiguous
To fix it, specify the tabledepartment for the column dept_no:
SELECT
departments.dept_no,
dept_name
FROM
departments
INNER JOIN dept_emp
WHERE
departments.dept_no = dept_emp.dept_no;
Another case when you create a view:
CREATE VIEW order_view AS
SELECT
customers.id,
payment.id,
total
FROM
customers
INNER JOIN orders
WHERE
customers.id = orders.customer_id;
MySQL throws back the error message:
Query 1 ERROR: Duplicate column name 'dept_no'
A view also can’t have two columns of the same name. So to fix this, we can use alias:
CREATE VIEW order_view AS
SELECT
customers.id,
payment.id AS order_id,
total
FROM
customers
INNER JOIN orders
WHERE
customers.id = orders.customer_id;
Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL, and many other databases simultaneously using an intuitive and powerful graphical interface.
Download TablePlus for Mac.
Not on Mac? Download TablePlus for Windows.
On Linux? Download TablePlus for Linux
Need a quick edit on the go? Download TablePlus for iOS
делаю запрос
SELECT COUNT(*)
FROM (
SELECT
`fl_serial`.`id`,
`fl_serial`.`name_serial`,
`fl_serial`.`slug_serial`,
`fl_serial`.`description_serial`,
`fl_serial`.`nesting`,
`fl_serial`.`year`,
`fl_serial`.`country_title`,
`fl_film`.`id`,
`fl_film`.`name_film`,
`fl_film`.`slug_film`,
`fl_film`.`description_film`,
`fl_film`.`nesting`,
`fl_film`.`year`,
`fl_film`.`country_title`,
`fl_mfilm`.`id`,
`fl_mfilm`.`name_mfilm`,
`fl_mfilm`.`slug_mfilm`,
`fl_mfilm`.`description_mfilm`,
`fl_mfilm`.`nesting`,
`fl_mfilm`.`year`,
`fl_mfilm`.`country_title`,
`fl_cat_serial`.`id`,
COALESCE(`fl_serial`.`id`, `fl_film`.`id`, `fl_mfilm`.`id` ) AS `ids`,
COALESCE(`fl_serial`.`name_serial`, `fl_film`.`name_film`, `fl_mfilm`.`name_mfilm` ) AS `name`,
COALESCE(`fl_serial`.`slug_serial`, `fl_film`.`slug_film`, `fl_mfilm`.`slug_mfilm`) AS `slug`,
COALESCE(`fl_serial`.`description_serial`, `fl_film`.`description_film`, `fl_mfilm`.`description_mfilm`) AS `description`,
COALESCE(`fl_serial`.`nesting`, `fl_film`.`nesting`, `fl_mfilm`.`nesting` ) AS `nesting`,
COALESCE(`fl_serial`.`year`, `fl_film`.`year`, `fl_mfilm`.`year` ) AS `years`,
COALESCE(`fl_serial`.`country_title`, `fl_film`.`country_title`, `fl_mfilm`.`country_title` ) AS `country_title`
FROM `fl_cat_serial`
LEFT JOIN `fl_serial` ON fl_cat_serial.id_serial = fl_serial.id
LEFT JOIN `fl_film` ON fl_cat_serial.id_film = fl_film.id
LEFT JOIN `fl_mfilm` ON fl_cat_serial.id_mfilm = fl_mfilm.id
WHERE `fl_cat_serial`.`id_cat`=12
GROUP BY `ids`) `c`получаю следующую ошибку
#1060 — Duplicate column name ‘id’
почему ? И как исправить ?
Recently, at a customers site, we stumbled upon an error in one of the databases alert.log. It turned out, that it was caused by the nightly statistics gathering job. There was one table for which statistics could not be generated.
SQL> exec dbms_stats.gather_table_stats('BASIS','VG_XXXXXXXXXXX', method_opt=>'for all columns size 1');
BEGIN dbms_stats.gather_table_stats('BASIS','VG_XXXXXXXXXXX', method_opt=>'for all columns size 1'); END;
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00001: unique constraint (SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST) violated
ORA-06512: at "SYS.DBMS_STATS", line 34850
ORA-06512: at line 1
So we first tried to remove the statistics, but we were unable to.
SQL> exec dbms_stats.delete_table_stats('BASIS','VG_XXXXXXXXXXX');
BEGIN dbms_stats.delete_table_stats('BASIS','VG_XXXXXXXXXXX'); END;
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00001: unique constraint (SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST) violated
ORA-06512: at "SYS.DBMS_STATS", line 17279
ORA-06512: at line 1
SQL> exec dbms_stats.delete_table_stats('BASIS','VG_XXXXXXXXXXX',force=>true);
BEGIN dbms_stats.delete_table_stats('BASIS','VG_XXXXXXXXXXX',force=>true); END;
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00001: unique constraint (SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST) violated
ORA-06512: at "SYS.DBMS_STATS", line 17279
ORA-06512: at line 1
So we started to dig deeper into that issue. We found out, that there is a duplicate column name when querying the Data Dictionary. But on the other hand, a simple “describe” does not show that.
SQL> desc owner.vg_xxxxxxxxxx
Name Null? Type
----------------------------------------------------- -------- -----------------
[...]
XXXXXXXXXXXXXXX_ID NUMBER(19)
XXXXXXXXXXXXXXXXX_XXX NUMBER(19)
XXXXXXXXXXXXXXXXX_ID NUMBER(19)
NAMENSZUSATZ NVARCHAR2(4000)
ENTITY_TIMESTAMP TIMESTAMP(9)
OPTLOCKVERSION NOT NULL NUMBER(19)
DATENUEBERNAHMETYP NVARCHAR2(4000)
ZUGANGGEBUCHT NOT NULL NUMBER(1)
UEBERSTELLER NOT NULL NUMBER(1)
XXXXXXXXXXXXXX NOT NULL NUMBER(1)
SQL> select column_id, column_name from dba_tab_columns where table_name='VG_XXXXXXXXXXX' and column_name like 'Z%'
COLUMN_ID COLUMN_NAME
---------- --------------------
59 ZUGANGGEBUCHT
59 ZUGANGGEBUCHT
Next step was to find out, where the data from the dictionary view is comming from.
SQL> select text from dba_views where view_name='DBA_TAB_COLUMNS';
TEXT
--------------------------------------------------------------------------------
select OWNER, TABLE_NAME,
COLUMN_NAME, DATA_TYPE, DATA_TYPE_MOD, DATA_TYPE_OWNER,
DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, COLUMN_ID,
DEFAULT_LENGTH, DATA_DEFAULT, NUM_DISTINCT, LOW_VALUE, HIGH_VALUE,
DENSITY, NUM_NULLS, NUM_BUCKETS, LAST_ANALYZED, SAMPLE_SIZE,
CHARACTER_SET_NAME, CHAR_COL_DECL_LENGTH,
GLOBAL_STATS, USER_STATS, AVG_COL_LEN, CHAR_LENGTH, CHAR_USED,
V80_FMT_IMAGE, DATA_UPGRADED, HISTOGRAM, DEFAULT_ON_NULL,
IDENTITY_COLUMN, SENSITIVE_COLUMN,
EVALUATION_EDITION, UNUSABLE_BEFORE, UNUSABLE_BEGINNING
from DBA_TAB_COLS
where USER_GENERATED = 'YES'
SQL> select text from dba_views where view_name='DBA_TAB_COLS';
TEXT
--------------------------------------------------------------------------------
select
OWNER, TABLE_NAME,
COLUMN_NAME, DATA_TYPE, DATA_TYPE_MOD, DATA_TYPE_OWNER,
DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, COLUMN_ID,
DEFAULT_LENGTH, DATA_DEFAULT, NUM_DISTINCT, LOW_VALUE, HIGH_VALUE,
DENSITY, NUM_NULLS, NUM_BUCKETS, LAST_ANALYZED, SAMPLE_SIZE,
CHARACTER_SET_NAME, CHAR_COL_DECL_LENGTH,
GLOBAL_STATS,
USER_STATS, AVG_COL_LEN, CHAR_LENGTH, CHAR_USED,
V80_FMT_IMAGE, DATA_UPGRADED, HIDDEN_COLUMN, VIRTUAL_COLUMN,
SEGMENT_COLUMN_ID, INTERNAL_COLUMN_ID, HISTOGRAM, QUALIFIED_COL_NAME,
USER_GENERATED, DEFAULT_ON_NULL, IDENTITY_COLUMN, SENSITIVE_COLUMN,
EVALUATION_EDITION, UNUSABLE_BEFORE, UNUSABLE_BEGINNING
from dba_tab_cols_v$
SQL> select text from dba_views where view_name='DBA_TAB_COLS_V$';
TEXT
--------------------------------------------------------------------------------
select u.name, o.name,
c.name,
[...]
Ok, DBA_TAB_COLUMNS gets its data from DBA_TAB_COLS which in turn gets its data from DBA_TAB_COLS_V$. That view has the final SQL, that is being used to retrieve the data, I shortened it, you get the idea.
We then used the SQL from DBA_TAB_COLS_V$ to find the root cause of our duplicate column.
SQL> select
2 , sys."_CURRENT_EDITION_OBJ" o
3 , sys.hist_head$ h
4 c.col#, c.obj#, c.name, c.intcol#
5 , sys.tab$ t
6 where o.obj# = c.obj#
7 , h.obj#, h.intcol#
8 from sys.col$ c
9 or
10 (o.type# = 2 /* tables, excluding iot - overflow and nested tables */
11 and
12 , sys."_CURRENT_EDITION_OBJ" o
13 and (bitand(t.property, 512) = 512 or
14 bitand(t.property, 8192) = 8192))))
15 , sys.hist_head$ h
16 , sys.user$ u
17 , sys.coltype$ ac
18 , sys.obj$ ot
19 , sys."_BASE_USER" ut
20 , sys.tab$ t
21 where o.obj# = c.obj#
22 and o.owner# = u.user#
23 and o.obj# = t.obj#(+)
24 and c.obj# = h.obj#(+) and c.intcol# = h.intcol#(+)
25 and c.obj# = ac.obj#(+) and c.intcol# = ac.intcol#(+)
26 and ac.toid = ot.oid$(+)
27 and ot.type#(+) = 13
28 and ot.owner# = ut.user#(+)
29 and (o.type# in (3, 4) /* cluster, view */
30 or
31 (o.type# = 2 /* tables, excluding iot - overflow and nested tables */
32 and
33 not exists (select null
34 from sys.tab$ t
35 where t.obj# = o.obj#
36 and (bitand(t.property, 512) = 512 or
37 bitand(t.property, 8192) = 8192))))
38 and c.name='ZUGANGGEBUCHT';
COL# OBJ# NAME INTCOL# OBJ# INTCOL#
---------- ---------- -------------------- ---------- ---------- ----------
60 95556 ZUGANGGEBUCHT 60
68 95523 ZUGANGGEBUCHT 68
59 94177 ZUGANGGEBUCHT 59 94177 59
59 94177 ZUGANGGEBUCHT 59 94177 59
74 95522 ZUGANGGEBUCHT 74
74 95550 ZUGANGGEBUCHT 74
6 rows selected.
Have a look at the highlighted rows, there’s the duplicate column. Obviously the join to HIST_HEAD$ introduced the second row. That means, there must be something wrong inside that table. And that turned out to be true.
SQL> desc hist_head$
Name Null? Type
--------------------------- -------- -------------
OBJ# NOT NULL NUMBER
COL# NOT NULL NUMBER
BUCKET_CNT NOT NULL NUMBER
ROW_CNT NOT NULL NUMBER
CACHE_CNT NUMBER
NULL_CNT NUMBER
TIMESTAMP# DATE
SAMPLE_SIZE NUMBER
MINIMUM NUMBER
MAXIMUM NUMBER
DISTCNT NUMBER
LOWVAL RAW(1000)
HIVAL RAW(1000)
DENSITY NUMBER
INTCOL# NOT NULL NUMBER
SPARE1 NUMBER
SPARE2 NUMBER
AVGCLN NUMBER
SPARE3 NUMBER
SPARE4 NUMBER
SQL> select COL#, BUCKET_CNT, ROW_CNT, NULL_CNT, TIMESTAMP#, SAMPLE_SIZE from hist_head$ where obj#=94177 and intcol#=59;
COL# BUCKET_CNT ROW_CNT NULL_CNT TIMESTAMP# SAMPLE_SIZE
---------- ---------- ---------- ---------- ------------------- -----------
0 1 0 35656 2017-01-05 22:00:23 5502
0 1 0 35383 2017-01-05 22:00:23 5775
A quick MOS-search revealed two notes, that describe the issue, Alter Table Drop Column Failing With ORA-00600[16515] (Doc ID 2375301.1) and DBMS_STATS.DELETE_TABLE_STATS Fails With ORA-600 [16515] (Doc ID 1233745.1). The latter one has the final solution for this issue.
We first identified the older of the two rows which we then deleted.
SQL> SELECT rowid,obj#,intcol#,timestamp# FROM hist_head$ WHERE obj#=94177 and intcol#=59 order by timestamp#; ROWID OBJ# INTCOL# TIMESTAMP# ------------------ ---------- ---------- ------------------- AAAABEAABAAAWh0AAj 94177 59 2017-01-05 22:00:23 AAAABEAABAAAWh0AAi 94177 59 2017-01-05 22:00:23 SQL> DELETE FROM hist_head$ WHERE ROWID='AAAABEAABAAAWh0AAj'; 1 row deleted. SQL> COMMIT; Commit complete.
After this, DBA_TAB_COLUMNS is back to distinct column names.
SQL> select column_id, column_name from dba_tab_columns where table_name='VG_XXXXXXXXXXX' and column_name like 'Z%';
COLUMN_ID COLUMN_NAME
---------- --------------------
59 ZUGANGGEBUCHT
And of cause, the statistics gathering was and is running fine again.
@michaelyali Looks like the same problem in my case Alias doesn’t work in my case.
[Nest] 39063 — 11/30/2022, 12:11:42 PM ERROR [ExceptionsHandler] Duplicate column name ‘Fundraising_id’
@Crud({ model: { type: Fundraising, }, query: { join: { fundraiser: { eager: false, alias: 'fr', }, cards: { eager: false, alias: 'c', }, }, },
Models:
export abstract class BaseEntity { @PrimaryGeneratedColumn() id: number; @CreateDateColumn() createdAt: Date; @UpdateDateColumn() updatedAt: Date; @DeleteDateColumn() deletedAt: Date; }
@Entity() export class Fundraising extends BaseEntity { @Column() @Index({ unique: false }) name: string; @ManyToOne(() => Fundraiser, (f) => f.fundraisingList, { nullable: true, eager: true, }) @JoinColumn() fundraiser: Fundraiser; }
@Entity() export class Fundraiser extends BaseEntity { @Column() @Index({ unique: false }) name: string; @Column() photo: string; @OneToMany(() => Fundraising, (fr) => fr.fundraiser, { nullable: true, }) fundraisingList: Fundraising[]; }
Also, I want to highlight ‘It appears after I pass ?limit= ‘ as it vas in @mreschke case #281 (comment)
When I requested API with a limit I have 500
And error
Without limit it’s 200
Generated query is:
SELECT DISTINCT `distinctAlias`.`Fundraising_id` AS `ids_Fundraising_id`, `distinctAlias`.`Fundraising_id` FROM (SELECT `Fundraising`.`id` AS `Fundraising_id`, `Fundraising`.`id` AS `Fundraising_id`, `Fundraising`.`createdAt` AS `Fundraising_createdAt`, `Fundraising`.`updatedAt` AS `Fundraising_updatedAt`, `Fundraising`.`deletedAt` AS `Fundraising_deletedAt`, `Fundraising`.`isPublished` AS `Fundraising_isPublished`, `Fundraising`.`name` AS `Fundraising_name`, `Fundraising`.`description` AS `Fundraising_description`, `Fundraising`.`target` AS `Fundraising_target`, `Fundraising`.`monoJarUrl` AS `Fundraising_monoJarUrl`, `Fundraising`.`raisedMono` AS `Fundraising_raisedMono`, `Fundraising`.`raisedExternally` AS `Fundraising_raisedExternally`, `Fundraising`.`image` AS `Fundraising_image`, `Fundraising`.`isEnded` AS `Fundraising_isEnded`, `Fundraising`.`reportVideoLink` AS `Fundraising_reportVideoLink`, `Fundraising`.`reportPhoto` AS `Fundraising_reportPhoto`, `Fundraising`.`reportDescription` AS `Fundraising_reportDescription`, `fr`.`id` AS `fr_id`, `fr`.`id` AS `fr_id`, `fr`.`createdAt` AS `fr_createdAt`, `fr`.`updatedAt` AS `fr_updatedAt`, `fr`.`deletedAt` AS `fr_deletedAt`, `fr`.`name` AS `fr_name`, `fr`.`description` AS `fr_description`, `fr`.`photo` AS `fr_photo`, `Fundraising`.`fundraiserId` FROM `fundraising` `Fundraising` LEFT JOIN `fundraiser` `fr` ON `fr`.`id` = `Fundraising`.`fundraiserId` AND (`fr`.`deletedAt` IS NULL) WHERE `Fundraising`.`deletedAt` IS NULL) `distinctAlias` ORDER BY `distinctAlias`.`Fundraising_id` DESC, `Fundraising_id` ASC LIMIT 100
If it’s not the same, please notify me I will make a new issue
Deps Versions:
{
"@nestjs/core": "^9.0.0",
"@nestjsx/crud": "^5.0.0-alpha.3",
"@nestjsx/crud-typeorm": "^5.0.0-alpha.3",
"@nestjs/typeorm": "^9.0.0",
"typeorm": "^0.3.7"
}