1

So, this is a simple situation but I wanted to understand what's causing this issue. I have the following code (modified for example):

SELECT `Transactions`.*, CONCAT_WS(" ", `People`.`first_name`, `People`.`last_name`) AS full_name ...

On my local machine I have:

  • Windows 10
  • Apache 2.4.25
  • PHP 7.4.11
  • MySQL 5.7.25

With that combination the following code works fine.

On the remote server I have:

  • Ubuntu 20.04.1 LTS
  • Apache 2.4.41
  • PHP 7.4.3
  • MySQL 8.0.19

So, I have a section which uses a data table and the data table uses server-side processing to obtain the information. On my local it shows the information correctly, but on my remote server I always got an empty array. So I tried executing the same SQL command in my remote server and I got this error:

#1054 - Unknown column ' ' in 'field list'

My SQL was correctly formed so I thought maybe the problem was related to the CONCAT_WS function.

So I decided to modify it to:

SELECT `Transactions`.*, CONCAT_WS(' ', `People`.`first_name`, `People`.`last_name`) AS full_name ...

I basically changed CONCAT_WS(" ", to CONCAT_WS(' ', and the code worked as intended.

I am not sure if this affects in some way, but is this a MySQL change in requirements for the usage of CONCAT_WS or something else?

Is it ok if I use it with single quotes elsewhere?

Mihail Minkov
  • 2,463
  • 2
  • 24
  • 41

1 Answers1

2

I suggest you run this on both systems:

SELECT @@sql_mode;

You will find that on your 8.0 server, the sql mode includes either the modes ANSI or ANSI_QUOTES.

Explanation:

The double-quotes have different meanings in MySQL depending on which sql mode is in effect.

By default, double-quotes are the same as single-quotes: they delimit a string literal.

If the sql mode is ANSI or ANSI_QUOTES, then the double-quotes delimit an identifier, acting like the back-ticks.

So the same code can behave differently on different MySQL instances. This has nothing to do with the difference between 5.7 and 8.0, because the sql mode behaves the same on these two versions. Neither version enables the ANSI or ANSI_QUOTES modes by default, so you or someone else must have enabled that mode on your 8.0 server.

This is why in this expression:

CONCAT_WS(" ", ...)

The first argument " " is treated as a string literal on one server, and on the other server it is treated as a column whose name is (which is legal SQL, even if it's weird).

It's safest to always use single-quotes to delimit a string literal, and to always use back-ticks to delimit an identifier.

Never use double-quotes for either case in MySQL, because your code that uses double-quotes in SQL queries will break if someone changes the sql mode.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Excellent, that cleared it up. In fact, the 8.0 is a DigitalOcean managed DB server, so they added those parameters to it. My local server doesn't have them defined. Thanks for the clear answer. – Mihail Minkov Jan 18 '21 at 22:21