Leaf Error: Unknown column


#1

Best regards,

I am using memsql and i have a problem, when one query is executed, the system return the follow message SQL Error [1054] [42S22]: Leaf Error : Unknown column ‘quo.atribuido_a’ in ‘field list’ but the column there is on database and same so the error persist. Someone can help me? What Can i do?


#2

do you have it as a select `quo.atribuido_a` … from quo?
if so it will not work. try `quo`.atribuido_a.


#3

No, quo is alias to quotas table, see bellow the query.

SELECT * FROM vendedores ven left JOIN quotas quo ON(quo.atribuido_a = ven.id);


#4

That statement should work…
can you do:
show create table quotas;
maybe there is a typo in the column name?


#5

It is not problem because when I try do it select * from quotas the query its work, and appear all fields, but if i use inner or left join doesn’t work


#6

This is suggesting that your atribuido_a column is a column in quotas, not quo. But you are using quo.atribuido_a = ven.id in your join query.


#7

I’ve found that the problem is in the left join, when I execute the query
" select * from vendedores ven inner join quotas quo on (ven.id = quo.atribuido_a)"
its work, but if I try to execute this
" select * from vendedores ven left join quotas quo on (ven.id = quo.atribuido_a) " the error that I mentioned above occurs.

I need a help…


#8

Oop sorry I read your query wrongly. I agree with you that this looks suspicious.
Please attach the output of:

show create table vendedores

and

show create table quotas

so we can investigate further.


#9

result of show create table vendedores;

CREATE TABLE vendedores (
id bigint(20) NOT NULL AUTO_INCREMENT,
email varchar(150) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
nome varchar(250) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
cargo varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT ‘vendedor’,
winrate double(12,2) NOT NULL,
ano_entrada date NOT NULL,
ano_saida date DEFAULT NULL,
status varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT ‘ativo’,
alterado_em datetime DEFAULT null,
alterado_por bigint(20) DEFAULT null,
deleted varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
PRIMARY KEY (id)
) AUTO_INCREMENT=11

result of show create table quotas

CREATE TABLE quotas (
id bigint(20) NOT NULL AUTO_INCREMENT,
atribuido_a bigint(20) DEFAULT NULL,
tipo varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
ano varchar(4) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
mes int(11) DEFAULT null,
faturamento_minimo double(12,2) DEFAULT 0,
valor double(12,5) DEFAULT NULL,
classe varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
deleted varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
alterado_em varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
alterado_por varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
PRIMARY KEY (id)
) AUTO_INCREMENT=205


#10

Above you said something about the inner join not working in one comment and the inner join working in a different comment. Can you confirm whether inner join works or errors?

Can you run this script to check for potential inconsistencies in the table schema:

memsql_consistency_checker.sh

Usage: ./memsql_consistency_checker.sh [-u MASTERUSER] [-p MASTERPASS] [-h MASTERHOST] [-d DATABASE]...
Check tables and schemas across aggregators and leafs for consistency
We assume that Leaf User/Password is consistent across Leaves
 
    -?,--help       display this help and exit
    -h,--hostname   the master aggregator hostname, defaults to 127.0.0.1
    -lu,--leafuser  the user for the leafs, defaults to --user
    -lp,--leafpass  the password for the leafs, defaults to --pass
    -d,--database   run the consistency check only against specified db
    -p,--password   the password for the master aggregator if necessary
    -P,--port       the port for the master aggregator, defaults to 3306
    -u,--user       the user for the master aggregator, defaults to cur user
    -v              verbose mode. Can be used multiple times for increased
                    verbosity.