mariadb log.txt

vicentiu has joined (~vicentiu@86.127.201.159)
10:36 bronson_
I'm seeing a difference between mariadb and mysql...  hoping someone can help me figure out a good fix.
bronson_
I have a `time` column...  I'm trying to find a record using: SELECT  `foos`.* FROM `foos` WHERE `foos`.`start` = '2000-01-01 12:30:00' LIMIT 1
bronson_
which is, of course, outside time's proper range.
bronson_
mysql appears to ignore the date part and find the time correctly.  (and postgres and sqlite do this too)
bronson_
maria, however, always returns null.  When I manually truncate the date (WHERE `foos`.`start` = '12:30:00') it works fine.
litheum
bronson_: sorry, "outside time's proper range"?
litheum
oh, you're trying to compare a TIME column to a DATETIME literal?
bronson_
litheum: outside '-838:59:59.999999' to '838:59:59.999999'
degva has left IRC (Quit: Leaving)
bronson_
yes...  in fact, from the db logs, it looks like it's passing a date string.
`jpg has left IRC (Ping timeout: 250 seconds)
bronson_
It's a Rails app.  Works on MySQL, Postgres, and Sqlite.  Trying to figure out why it's not going so great with Maria.
bronson_
The MySQL and MariaDB queries appear to be identical in the logs.
litheum
i don't know what specific change in MariaDB would account for that
wlad_ has joined (~wlad@HSI-KBW-046-005-000-236.hsi8.kabel-badenwuerttemberg.de)
litheum
but the idea that comparing a datetime literal to a time value and expecting that the time component of the datetime literal would be extracted automatically and compared to the time value strikes me as very odd
litheum
why on earth is the app doing this?
bronson_
because it's always done that I suppose.  :)
bronson_
postgres is pretty anal...  if it allows it, I'd guess that there's got to be some reason behind it.
`jpg has joined (~jpg@jpg.id.au)
wlad has left IRC (Ping timeout: 252 seconds)
anthonysomerset has joined (~anthonyso@office.somersettechsolutions.co.zw)
RichardS has joined (~RichardS@aftr-5-146-249-9.unity-media.net)
bronson_
been googling and googling, can't find any standards-complient description of datetime vs time comparison
litheum
yeah i'm not sure what to tell you. open a jira task for mariadb and report the issue, i guess?
bronson_
sounds good, thanks litheum
grknight
bronson_: which version of mariadb is this?
bronson_
grknight: Ver 15.1 Distrib 10.1.9-MariaDB, for osx10.11
grknight
there is --mysql56_temporal_format.. but that's low level and may be related to other things
bumbar_ has left IRC (Ping timeout: 276 seconds)
bronson_
That's true...  I'd like to stay as close to stock as possible.  Hoping to find a better answer.  :)
grknight
but that is storage.. not with the parser
bronson_
right.
PrinceAMD has joined (~grizzly_b@unaffiliated/princeamd)
montywi
bronson_: Something you should be aware of
montywi
if you cast TIME to datetime, it's converted to a datetime with the current date
montywi
select CAST(TIME'10:20:30' AS DATETIME) -> 2016-02-09 10:20:30
litheum
montywi: i confirmed that there's very different behavior here between MySQL and MariaDB
montywi
which is the reason your select doesn't match
bronson_
ah, that's got to be it.
litheum
montywi: why would that behavior be different between MySQL and MariaDB?
litheum
montywi: http://sprunge.us/HiYe
litheum
montywi: and shouldn't the casting work the other way around? shouldn't the literal in the WHERE clause be cast to the type of the column it's compared to?
montywi
i think both are compared as strings
litheum
montywi: why would that behavior be different between MySQL and MariaDB? :)
montywi
litheum: we fixed something some time ago with datetime/time casting to match the sql standard
montywi
serg: can you remember why you did the conversion ?
litheum
montywi: breaking backward compatibility with MySQL in the process? :(
bronson_
litheum: I added PostgreSQL to your example: https://gist.github.com/bronson/0a49e7d3fe7242cb5cd3
montywi
if we have to follow the standard then that is more important, especially if we return wrong results
bronson_
montywi: postgres doesn't follow the standard here?  that would be rare.  :)
montywi
there is a lot of comparisions where mysql doesn't give any result while MariaDB gives the correct result
litheum
montywi: i don't know what to say about that. arbitrary changes to "follow the standard" while breaking backward compatibility are not very convincing when we have such an enormous number of places where we very obviously do not follow any standard
thumbs
oh, the almighty PG
bronson_
oh I'm not saying PG is any better or worse, just more anal.  :)
uebera|| has left IRC (Quit: ZNC)
montywi
From mysql 5.7 manual:
montywi
For conversion of TIME values to other temporal types, the value of CURRENT_DATE() is used for the date part.
bronson_
montywi: but shouldn't the DATETIME be cast to TIME before the comparison?
montywi
i don't know which way is right as it's not defined which way the conversion should be
montywi
normally you convert to the bigger value to not loose data
thumbs
montywi: I suppose test cases like kolbe's are the best way to find out
bronson_
welp, in this case Maria appears to be doing its own thing.
montywi
according to db2 manual, at least with timestamps one is comparing with the higher precision. In other words, a time of 10:00:02.4 can't match 10:00:02
bronson_
montywi: that's true, they all do that.
montywi
what I don't know is based on why we do compare things just now or if the intention was to do as we do
ueberall has joined (~ueberall@unaffiliated/ueberall)
bronson_
at least postgres and mysql require the usec to match too.
AL13N_lappy has joined (~alien@mail.rmail.be)
montywi
bronson_: I am still trying to find some documentation for what is the correct thing to do
thumbs
that would be helpful
litheum
regardless of what is "correct", there's still the reality that the poor fellow's application works just fine on MySQL and PG but not on MariaDB
bronson_
montywi: excellent, I'm sure you have access to much more interesting docs than I do.
bronson_
I got nowhere with 20 minutes of googling this mornign.
montywi
litheum: if one does something wrong (like in this case comparing different types without casts) then anyting can happen both between different servers but also between different versions
bronson_
litheum: actually...   this is in Rails itself: https://github.com/rails/rails/blob/87ee4f41490665602873d69a2d6bbfc9db049938/activerecord/test/cases/time_precision_test.rb#L53
litheum
montywi: maybe true. but the behavior outside of MariaDB seems pretty consistent.
bronson_
that line is the one that selects "WHERE `foos`.`start` = '2000-01-01 12:30:00' LIMIT 1" from the db.
bronson_
and it's the line that results in a failing test on Maria.
bronson_
Oop, I oversimplified when I said 'Rails app' above.  Sorry, lazy.
nvidhive has joined (uid47573@geoshell/testlemming/nvidhive)
bronson_
anyone have easy access to a SQL Server or Oracle console?  Would be nice to add them to litheum's results.
litheum
bronson_: AWS
bronson_
litheum: oo wow, 750 hours free.  good to know.
litheum
even if you have to pay for a couple hours, it's not gonna cost more than a buck or two
bronson_
true.  I gotta get real work done now, might play with it tomorrow.  Hoping someone else can beat me to it though.
montywi
note that comparing date to datetime, then the date is changed to a datetime with time 00:00:00
joshin has joined (~josh@unaffiliated/joshin)
montywi
so, I don't really get it why when comparing time, we should cut the datetime
wlad_ has left IRC (Ping timeout: 260 seconds)
wlad has joined (~wlad@HSI-KBW-046-005-000-236.hsi8.kabel-badenwuerttemberg.de)
sauvin has left IRC (Remote host closed the connection)
coellobranco has joined (~FoRcEr@132.Red-79-158-118.dynamicIP.rima-tde.net)
montywi
in MySQL 5.6, the following is not true:
montywi
SELECT CAST("2016-01-06 15:39:30" AS DATETIME)= TIME("15:39:30");
montywi
in other words, also mysql 5.6 converts time to a datetime with current time
montywi
however it looks like that when you do a comparison to column, it does things inconsistently to how it does things in other cases
valerienc has left IRC (Quit: valerienc)
vicentiu has left IRC (Remote host closed the connection)
opsec has left IRC (Quit: all your base are belong to us.)
bronson_
just had a friend punch litheum's example in to SQL Server.
opsec has joined (~opsec@unaffiliated/opsec)
bronson_
verdict: same as MySQL, Postgres, etc.  The row gets returned.
montywi
can you also check the above cast with two different years ?
thumbs
bronson_: FWIW you can use sqlfiddle.com to test that yourself
bronson_
thumbs: whoa, that's excellent.
litheum
oh dang yeah i forgot sqlfiddle has those commercial products too
thumbs
oracle, PG, MSSQL, sqlite
thumbs
(all they need is MariaDB now)
montywi
bronson_: in MySQL 5.6, this fails:
montywi
select * from t1 where time("12:30:00") = '2000-01-02 12:30:00';
montywi
so it looks like MySQL has a special comparison for time column to datetime
montywi
this also fails:
montywi
select * from t1 where time(t) = '2000-01-02 12:30:00';
montywi
it will be intersting to see what sql server does...
bronson_
montywi: SQL Server returns the row.
Jippi has joined (~cw@x1-6-90-72-40-04-fa-86.cpe.webspeed.dk)
valerienc has joined (~vparham@cpe-107-15-231-18.nc.res.rr.com)
bronson_
here's a sql server fiddle: http://sqlfiddle.com/#!6/0f95f/1
bronson_
unfortunately sqlfiddle's Oracle appears to be broken.
bronson_
at least I can't get it to work.
Jippi_ has joined (~cw@x1-6-90-72-40-04-fa-86.cpe.webspeed.dk)
montywi
bronson_: ok, found the issue in MariaDB. Looks like we convert the string to a time, but unfortunately something goes wrong in this
montywi
(we get the wrong time, something outside the range)
Jippi has left IRC (Ping timeout: 245 seconds)
Jippi_ is now known as Jippi
montywi
so this looks like a bug
bronson_
oh good.  shall I submit this Jira issue I started?
montywi
Don't know what is the right results still according to ANSI SQL, but something is strange here
montywi
please do
montywi
what looks wrong at my end is that we are converting the string to this time: "838:49:59.99999"
montywi
and then trying to compare that.  This is stored in a Item_time_literal.
montywi
bronson_: can you please add the above also to the jira issue
bronson_
montywi: will do
bbankes__ has left IRC (Ping timeout: 248 seconds)
X-virus has left IRC (Quit: Leaving)
plitter
anyone know of a good program to design database in linux with?
plitter
its for documentation and doesn't have to generate code afterwards
bronson_
oh, like visio?  good question.
bronson_
I've used Inkscape in the past but I wouldn't recommend it...  pretty fiddly.
bronson_
(I wouldn't recommend against it either...   it worked, with some effort)
bbankes__ has joined (~bbankes@2601:680:c402:3225:8092:5680:d4fb:38cb)
Naktibalda has joined (~Naktibald@cpc15-heme10-2-0-cust50.9-1.cable.virginm.net)
jbruehe has left IRC (Quit: Client exiting)
plitter
bronson_: inkscape? that sounds like a lot of work :p
plitter
I was hoping for something like vym
bronson_
well, someone else had a document with all the symbols.  I just had to put them on the page and link them up.
plitter
ahhh, that makes it a bit easier I guess...
bronson_
as long as I did it right the first time, wasn't too bad.   If I had to push stuff around the page it got TEDIOUS.
bronson_
this was years and years ago.  gotta be something better now.
plitter
exactly why I asked here (and at sql). access has something that looks the way I want to, but I don't want access
bronson_
montywi: https://mariadb.atlassian.net/browse/MDEV-9541
coellobranco has left IRC (Quit: Arch User...)
bronson_
plitter: any suggestions yet?  I'm curious.
montywi
bronson_: fix:  set old_mode=ZERO_DATE_TIME_CAST;
montywi
the documentation says:
montywi
When a TIME value is casted to a DATETIME, the date part will be 0000-00-00, not CURRENT_DATE (as dictated by the SQL standard)
montywi
will talk with bar who coded this what would make most sense