[MDEV-26070] Unknown column in 'field list' when using mysql -e "query..." from bash Created: 2021-07-01  Updated: 2021-07-02  Resolved: 2021-07-02

Status: Closed
Project: MariaDB Server
Component/s: N/A
Affects Version/s: 10.4.19
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Mike Evans Assignee: Daniel Black
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

linux, Fedora32
bash shell, GNU bash, version 5.0.17(1)-release
Server version: 10.4.19
mysql Ver 15.1 Distrib 10.4.19-MariaDB, for Linux (x86_64) using EditLine wrapper


 Description   

From bash:
mysql --delimiter=',' -p tlogger -e "SELECT DATE(datetime) AS dat, rain - LAG(rain) OVER (ORDER BY dat) AS day_rain from rainfall GROUP BY dat;" > rain.csv
ERROR 1054 (42S22) at line 1: Unknown column 'datetime' in 'field list'

However if I am in the interactive mysql shell the query succeeds.

Table created with:

CREATE TABLE `rainfall` (
`datetime` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
`temper` decimal(6,3) DEFAULT NULL,
`rain` decimal(10,2) DEFAULT NULL,
`id` int(11) DEFAULT NULL,
`battery` int(11) DEFAULT NULL,
PRIMARY KEY (`datetime`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 Comments   
Comment by Daniel Black [ 2021-07-02 ]

delimiter is defining the separation of the queries like DELIMITER. So with delimiter = `,` the first query becomes SELECT DATE(datatime) as dat; which is why the error occurs.

In a script you'd want something like:

mysql  -p tlogger -e "SELECT DATE(datetime) AS dat, rain - LAG(rain) OVER (ORDER BY dat) AS day_rain from rainfall GROUP BY dat;" | tr '\t' ',' > rain.csv

Comment by Mike Evans [ 2021-07-02 ]

Thank you. I apologise for the noise.

Comment by Daniel Black [ 2021-07-02 ]

All good. I've done my far share of invalid bugs too. Keep learning.

Generated at Thu Feb 08 09:42:31 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.