mysql5.7 query with multiple tables error

Question

mysql> desc apn_licence;
+------------+------------------+------+-----+-------------------+-------+
| Field      | Type             | Null | Key | Default           | Extra |
+------------+------------------+------+-----+-------------------+-------+
| vdomain    | char(32)         | NO   | PRI | test              |       |
| vhost      | char(32)         | NO   | PRI |                   |       |
| licence    | char(64)         | NO   |     |                   |       |
| tcpport    | int(11)          | NO   |     | 0                 |       |
| srvtype    | tinyint(4)       | NO   |     | 0                 |       |
| auxcmd     | tinyint(3)       | YES  |     | 0                 |       |
| agentuid   | int(10) unsigned | NO   |     | 1                 |       |
| owneruid   | int(10) unsigned | NO   |     | 1                 |       |
| sno        | char(32)         | YES  |     | 0                 |       |
| pno        | char(32)         | YES  |     | 0                 |       |
| updatedate | timestamp        | NO   |     | CURRENT_TIMESTAMP |       |
| builddate  | timestamp        | NO   |     | CURRENT_TIMESTAMP |       |
+------------+------------------+------+-----+-------------------+-------+
12 rows in set (0.00 sec)

mysql> desc apn_agent;
+--------------+---------------------+------+-----+---------+----------------+
| Field        | Type                | Null | Key | Default | Extra          |
+--------------+---------------------+------+-----+---------+----------------+
| uid          | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| euid         | int(10) unsigned    | YES  |     | 0       |                |
| username     | char(16)            | NO   | UNI |         |                |
| password     | char(16)            | YES  |     | NULL    |                |
| priv_licence | tinyint(3) unsigned | NO   |     | 0       |                |
| priv_admin   | tinyint(3) unsigned | NO   |     | 0       |                |
| priv_srv     | tinyint(3) unsigned | NO   |     | 0       |                |
| plant_asign  | int(11)             | YES  |     | 0       |                |
+--------------+---------------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

mysql> desc apn_routine;
+------------+----------------------+------+-----+-------------------+-------+
| Field      | Type                 | Null | Key | Default           | Extra |
+------------+----------------------+------+-----+-------------------+-------+
| acxid      | int(10) unsigned     | NO   | PRI | 0                 |       |
| sid        | int(10) unsigned     | NO   | PRI | 0                 |       |
| wanipaddr  | int(10) unsigned     | YES  |     | 0                 |       |
| udpport    | smallint(5) unsigned | NO   |     | 8400              |       |
| serial     | bigint(20) unsigned  | YES  |     | 0                 |       |
| nserial    | bigint(20) unsigned  | YES  |     | 0                 |       |
| rivec      | char(32)             | YES  |     | NULL              |       |
| stat       | smallint(5) unsigned | YES  |     | 4                 |       |
| vtype      | smallint(5) unsigned | YES  |     | 0                 |       |
| gwtype     | smallint(5) unsigned | YES  |     | 2                 |       |
| vactlevel  | int(10) unsigned     | YES  |     | 0                 |       |
| vnatlevel  | int(10) unsigned     | YES  |     | 0                 |       |
| vdomain    | char(32)             | NO   |     |                   |       |
| vhost      | char(32)             | NO   |     |                   |       |
| vsubkey    | char(32)             | YES  |     | public            |       |
| passwd     | char(64)             | YES  |     | NULL              |       |
| gwmac      | char(18)             | YES  | MUL | 00:0D:B0:00:00:00 |       |
| vsubnet    | int(10) unsigned     | YES  |     | 0                 |       |
| vsubmask   | int(10) unsigned     | YES  |     | 0                 |       |
| vgwip      | int(10) unsigned     | YES  |     | 0                 |       |
| vgwnexthop | int(10) unsigned     | YES  |     | 0                 |       |
| groupstamp | timestamp            | NO   |     | CURRENT_TIMESTAMP |       |
| joinstamp  | timestamp            | NO   |     | CURRENT_TIMESTAMP |       |
+------------+----------------------+------+-----+-------------------+-------+
23 rows in set (0.00 sec)

With mysql-3.23.58

select l.*, a.username from apn_licence as l, apn_agent as a 
  left join apn_routine as r on l.vdomain=r.vdomain and l.vhost=r.vhost 
  where
    l.agentuid=a.uid 
  group by l.vdomain 
  order by l.tcpport DESC,l.vdomain,l.vhost;

is OK. But with mysql-5.7.26,it get the error as:

ERROR 1054 (42S22): Unknown column 'l.vdomain' in 'on clause'

Why are you using `GROUP BY` if you are not selecting any aggregates? — Tim Biegeleisen, Oct 10 '19 at 02:53

score 0 · Answer 1 · answered Oct 10 '19 at 03:10

0

Looks like there are invisible garbage characters in your query.

Try retyping the query (don't copy and paste or you'll most likely include the garbage character) and most probably it should work.

answered Oct 10 '19 at 03:10

oreopot

3,392
2
19
28

score 0 · Answer 2 · answered Oct 10 '19 at 03:27

Thanks all,I found the answer in [MySQL unknown column in ON clause

The correct query is

select l.*, a.username from (apn_licence as l, apn_agent as a) 
  left join apn_routine as r on l.vdomain=r.vdomain and l.vhost=r.vhost 
  where
    l.agentuid=a.uid 
  group by l.vdomain 
  order by l.tcpport DESC,l.vdomain,l.vhost;

mysql5.7 query with multiple tables error

2 Answers2