On 11 September 2013 07:53, Sergey Shelukhin <sergey@xxxxxxxxxxxxxxx> wrote:
--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.
The query suffers from the auto-lower-casing of unquoted table names, which is not ANSI compliant. Technically we could add quotes (and stay ANSI), but then MySQL would break without explicitly setting it to use ANSI mode, so it's a lose-lose situation if we do not want to have DB-specific code.
Whether identifiers get folded to lower case or to upper case (which IS according to the standard) should not matter to you.
If you're using case sensitive table names, the standard says to quote your identifiers.
It would appear that MySQL is doing something non-standard compliant that's biting you here.
I haven't used MySQL in 10 years, but if memory serves me correctly, it tends to use the literal file name of the table, which would be case-sensitive on a case-sensitive file system. I remember the hilarity of the same issue when we moved a MySQL database from a file system that was not case sensitive (NTFS) to one that was (EXT2FS) - we had to rewrite all our queries because MySQL could no longer find the tables in our queries. That was when we wrote off MySQL for the project we were working on back then.
If you're using case sensitive table names, the standard says to quote your identifiers.
It would appear that MySQL is doing something non-standard compliant that's biting you here.
I haven't used MySQL in 10 years, but if memory serves me correctly, it tends to use the literal file name of the table, which would be case-sensitive on a case-sensitive file system. I remember the hilarity of the same issue when we moved a MySQL database from a file system that was not case sensitive (NTFS) to one that was (EXT2FS) - we had to rewrite all our queries because MySQL could no longer find the tables in our queries. That was when we wrote off MySQL for the project we were working on back then.
From your remark it isn't entirely clear what your problem is; it would appear that your ORM is generating case-sensitive table names and that your query outside the ORM assumes that it doesn't need to quote those identifiers?
Perhaps your ORM can be told not to do that?
--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.
