SQLite Forum

Error: near "order": syntax error and results differ between MariaDB and SQLite
Login

Error: near "order": syntax error and results differ between MariaDB and SQLite

(1) By Basil Mohamed Gohar (basilgohar) on 2020-07-24 18:31:08 [link] [source]

The following query executes fine in a MariaDB instance, but with the same data, fails in SQLite with the only error message being, 'Error: near "order": syntax error'.

SELECT
    course_instance_periods.id
FROM
    student_school_years
JOIN(
        school_years,
        course_instances,
        course_instance_periods,
        school_year_periods,
        course_instance_period_students
    )
ON
    (
        student_school_years.school_year_id = school_years.id AND course_instances.school_year_id = school_years.id AND course_instance_periods.course_instance_id = course_instances.id AND student_school_years.student_id = course_instance_period_students.student_id AND course_instance_periods.school_year_period_id = school_year_periods.id AND course_instance_period_students.course_instance_period_id = course_instance_periods.id
    )
WHERE
    student_school_years.id = 5
ORDER BY
    school_year_periods.order

However, when I eliminate the ORDER BY clause, I get no error, but also no records. The same query returns 4 rows in MariaDB. Can someone explain to me why I am getting different results?

(2) By Larry Brasfield (LarryBrasfield) on 2020-07-24 19:13:46 in reply to 1 [link] [source]

Consult SQL As Understood by SQLite. There is no reason to expect that any random query understood by MariaDB is going to be understood by SQLite.

I submit that using keywords as identifiers is a practice sure to yield varying results across DBMS implementations. You had best learn to avoid it. (Note the similarity between "ORDER" and "order" above.)

(4) By Basil Mohamed Gohar (basilgohar) on 2020-07-24 20:18:46 in reply to 2 [source]

Thank you for the reference. I did not come across it in my searches.

Of course, I also don't expect SQLite and MariaDB to be equivalent in all ways, I just was trying to find the cause in this case so I can write better cross-DBMS queries.

I'll explore the field name issue, seems promising.

(3) By Keith Medcalf (kmedcalf) on 2020-07-24 19:27:06 in reply to 1 [link] [source]

First get rid of all the parenthesis. They are not doing what you think they are doing (whatever that may be).

Secondly, there are two occurrences of the word "order" in your query. AFTER you have removed the parenthesis if you still get the same error message you will need to determine if the error is related to the first occurrence or the second one. The easiest way to do that is to quote the field name:

ORDER BY `school_year_periods`.`order`

Now if you get an error message it will either be near order or near `order` and you will be closer to figuring out your problem.

I would immediately presume that the entirety of the problem is related to your overzealous (and improper) use of parenthesis, and that once that error is fixed the problem will disappear.

(5) By Basil Mohamed Gohar (basilgohar) on 2020-07-24 20:21:25 in reply to 3 [link] [source]

Thanks, Keith. I appreciate the straight answer. It seems there's an emerging consensus that "order" is not a good field name, and also that quoting is more in order (haaaa) when using field names. That's just laziness on my part since I tend to be overzealous in quoting as well as parentheses, except for this case!

I'll try out what's been advised here and report back. Thanks again!

(6) By Keith Medcalf (kmedcalf) on 2020-07-24 22:17:52 in reply to 5 [link] [source]

It is also interesting that you note that there are zero rows produced when you remove the ORDER BY. This would indicate that (at least) one of your equijoins is not doing what you think it is doing, which means that your referential integrity is likely busted somewhere because you do not have the type of data in some cell that you think you do.

Unlike other RDBMS, SQLite is capable of storing EVERY (null, text, integer, real, blob) data in any particular cell of any row of any table at any time notwithstanding your declared datatype for that column. (well, perhaps not NULL since that is subject to the NULL/NOT NULL constraint).

This means that notwithstanding that you declared two columns to be of type "integer" that does not mean that any particular instance of that column in the table will contain integer data in that column. It may contain data of any type whatsoever -- and even though the data may "look" equal when printed on the screen does not mean that it actually compares equal.

Note also that the "type names" for declarations are "integer", "real", "text" and "blob" (and also "numeric" to mean either integer or real depending on whether the value is fractional or will not fit in an integer). There are various algorithms which attempt to "guess what you might have meant" if you declare something with some other phraseology that may or may not have the result you intended. For example you can declare a column to be a "bloody big struggling wrestling fan" and that will be "interpreted" that you meant to say "numeric" but just didn't know how to spell it properly.

https://sqlite.org/datatype3.html

The long and the short is that quite a lot depends on your schema definition and the method you used to get your data into SQLite3 as to what it actually may happen to contain.

(7) By Basil Mohamed Gohar (basilgohar) on 2020-07-25 01:04:43 in reply to 6 [link] [source]

Keith, I really appreciate your long-and-thorough responses. I am a bit embarrassed to admit that while the unquoted school_year_periods.order field was definitely problematic, I was incorrect in stating that the data was the same for both the MariaDB and SQLite instances. I had stopped midway in the import in the SQLite database due to some errors and confirmed that it did not actually have the records I was looking for.

I also appreciate pointing out the overzealousness of my parenthesitis as I was able to rewrite it and get the proper results without them.

The rewritten query looks like this:

SELECT
    course_instance_periods.id
FROM
    student_school_years
JOIN school_years ON student_school_years.school_year_id = school_years.id
JOIN course_instances ON course_instances.school_year_id = school_years.id
JOIN course_instance_periods ON course_instance_periods.course_instance_id = course_instances.id
JOIN school_year_periods ON course_instance_periods.school_year_period_id = school_year_periods.id
JOIN course_instance_period_students ON course_instance_period_students.course_instance_period_id = course_instance_periods.id
WHERE
    student_school_years.student_id = course_instance_period_students.student_id AND student_school_years.id = 5
ORDER BY
    school_year_periods.`order`';

I'm sure more could be said about what I'm trying to do here, but I got the results I was looking for, my tests are passing, and it works for both MariaDB as well as SQLite. I also am glad I learned a bit more about SQLite itself in the process.

I once again appreciate your frank and helpful answers as well as your in-depth explanation of further possible causes. I think that will be more useful than I expect in my future endeavors. Apologies for the misleading root cause of the problem due solely to my own carelessness. :-)